Run MS Sproc with 4 parameters

T

Tupacmoche

Registered User.
Local time
Today, 10:16
Joined
Apr 28, 2008
Messages
291
Hi Form Masters,

I developed a stored procedure that will populate an SQL 2008 R2 table based on the parameters selected in an Access form. I'm not sure how to implement the passing and call to the sproc. Simple put the form collects the pledge amt, the number of payments, when the payments start, and finally the frequency (year, bi-yearly,monthly). It then creates the payment schedule. Here is my code any help is appreciated. :confused:

DECLARE @TxtHowMuch asint,
@TxtHowMany asint,
@TxtPledgeStartDt asdatetime,
@TxtFrequency asnvarchar(50),-- Yearly, Monthly, Qurterly
@PaymentNumber asint,
@GID asint,
@cnt INT= 0 --Constant

--These values will be brought in from Access
SET @TxtHowMuch = 100000
SET @TxtHowMany = 10
SET @TxtPledgeStartDt ='2018-01-01'
SET @TxtFrequency ='Yearly'
SET @PaymentNumber = 1
SET @GID = 1
While @cnt < @TxtHowMany
BEGIN
INSERTINTO [dbo].[tblPledgePayments]
(PP_schedule, GiftID, PP_DueDate, PP_Amount)
Select
@PaymentNumber, @GID,
Case
WHEN @TxtFrequency ='Yearly'THENDateAdd(YEAR, @cnt, @TxtPledgeStartDt)
WHEN @TxtFrequency ='Monthly'THENDateAdd(MONTH, @cnt, @TxtPledgeStartDt)
WHEN @TxtFrequency ='Quarterly'THENDateAdd(QUARTER, @cnt, @TxtPledgeStartDt)
END
,@TxtHowMuch / @TxtHowMany
SET @cnt = @cnt + 1;
SET @PaymentNumber = @PaymentNumber + 1;
END;


 
Hi,

You haven't shown the header of your SP.

Instead of declaring your variables within the SP, use them as parameters instead.

Assuming your SQL is correct, then:
Code: 
IF object_id('add_pledge_payments') > 0
  DROP PROCEDURE add_pledge_payments;
GO
  
CREATE PROCEDURE add_pledge_payments
  @TxtHowMuch       INT,
  @TxtHowMany       INT,
  @TxtPledgeStartDt DATETIME,
  @TxtFrequency     NVARCHAR(50),-- Yearly, Monthly, Qurterly
AS
BEGIN

  DECLARE @cnt            INT = 0,
          @PaymentNumber  INT = 1,
          @GID            INT = 1

  While @cnt < @TxtHowMany
    BEGIN
      INSERTINTO [dbo].[tblPledgePayments] 
        (PP_schedule, GiftID, PP_DueDate, PP_Amount)
      SELECT 
        @PaymentNumber, 
        @GID,
        CASE
          WHEN @TxtFrequency ='Yearly' THEN DateAdd(YEAR, @cnt, @TxtPledgeStartDt)
          WHEN @TxtFrequency ='Monthly' THEN DateAdd(MONTH, @cnt, @TxtPledgeStartDt)
          WHEN @TxtFrequency ='Quarterly' THEN DateAdd(QUARTER, @cnt, @TxtPledgeStartDt)
        END,  
        @TxtHowMuch / @TxtHowMany 

      SET @cnt = @cnt + 1;
      SET @PaymentNumber = @PaymentNumber + 1;
    END;

END

Then, in Access, you can create a pass-thru query to call the SP. If you are using a command button on your form you can use code like:
Code: 
Private Sub cmdAddPledges_Click()

  Dim strSQL As String strCn As String

  strCn = "Your connection string goes here"

  strSQL = "add_pledge_payments " & Me.txtHowMuch & ", " & me.txtHowMany & ", '" & _
             Format(Me.txtPledgeStartDt, "yyyy-mm-dd") & "', '" & Me.txtFrequency & "';"

  With CurrentDb.CreateQueryDef()
    .Connect = strCn
    .SQL = strSQL
    .ReturnsRecords = False
    .Execute dbFailOnError
  End With

End Sub
(Untested, but should get you started!)

hth,

d

[edited: not all parameters should have been passed]
 
Last edited:
Tupacmoche said:
DECLARE @TxtHowMuch as int,
@TxtHowMany as int,

,@TxtHowMuch / @TxtHowMany
Click to expand...

Note that dividing integer by an integer will give an integer result which will be rounded if it is a fraction.
 
cheekybuddha

Thanks for the code looks great! Sorry, I did not send the correct script. The changes you mentioned, I had in the production script. I implemented it a bit differently but with an error. Perhaps you can see it. Here is the code:



Private Sub btnCS_Click()

Dim cmd As ADODB.Command

Set cmd = New ADODB.Command

cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "dbo.usp_EgatePledgeSchedule"

With cmd.Parameters

.Append cmd.CreateParameter("@P_HowMuch", adInteger, adParamInput, , txt_strHM)
.Append cmd.CreateParameter("@P_HowMany", adInteger, adParamInput, , txt_strHMP)
.Append cmd.CreateParameter("@P_PledgeStartDt", adDate, adParamInput, , txt_strSD)
.Append cmd.CreateParameter("@P_Frequency", adChar, adParamInput, , txt_strFreq)
.Append cmd.CreateParameter("@P_GID", adBigInt, adParamInput, , txt_strGID)

End With 'cmd.Parameters

cmd.Execute

Set cmd = Nothing

End Sub


The error message is :


Run time error 3708

Parameter object is improperly defined. Inconsistent or incomplete information was provided. I don't see what's wrong.:confused:
 
Last edited by a moderator:
I think you don't need to append/create parameters as they already exists, you simply need to supply them. Have a read here https://stackoverflow.com/questions...bject-is-improperly-defined-inconsistent-or-i

I normally just use something like

Code: 
    Set cn = New ADODB.connection
    cn.Open CurrentDb.connection
    cn.Execute ("EXEC dbo.[YourStoredProcName] " & iOrdID)  '[COLOR="SeaGreen"] One parameter in this case a integer value[/COLOR]
    cn.Close
    Set cn = Nothing
 
Hi,

Will you post the full CREATE PROCEDURE definition for usp_EgatePledgeSchedule.

You first mentioned 4 parameters, but in your code above you pass 5.

That could be the cause of the error, or perhaps the datatypes aren't matching (adChar => NVARCHAR, adBigInt => INT)

However, I agree with Minty, it's probably easier just to build an SQL string and .Execute it.

Using ADO:
Code: 
  Dim strSQL As String

  strSQL = "usp_EgatePledgeSchedule " & Me.txt_strHM & ", " & me.txt_strHMP & ", '" & _
             Format(Me.txt_strSD, "yyyy-mm-dd") & "', '" & Me.txt_strFreq & "', " & Me.txt_strGID & ";"
  CurrentProject.Connection.Execute strSQL, , adCmdText + adExecuteNoRecords

hth,

d
 
Last edited:
Hi cheekybuddha,

I did add another parameter that, I realized I needed which I did not include. I added the other parameter to the Access code and I got this error message:

Run-time error '3210':
The connection string is too long. The connection string cannot exceed 255 Characters.

Here is the Access vba code:

Private Sub btnCS_Click()
Dim strSQL As String
Dim strCn As String
strCn = CurrentProject.Connection
strSQL = "usp_EgatePledgeSchedule " & Me.txt_strHM & ", " & Me.txt_strHMP & ", '" & _
Format(Me.txt_strSD, "yyyy-mm-dd") & "', '" & Me.txt_strFreq & "', '" & Me.txt_strGID & "';"
With CurrentDb.CreateQueryDef()
.Connect = strCn
.SQL = strSQL
.ReturnsRecords = False
.Execute dbFailOnError
End With
End Sub

And here is the script you asked for:

USE [Med]
GO
/****** Object: StoredProcedure [dbo].[usp_EgatePledgeSchedule] Script Date: 9/24/2018 4:55:15 PM ******/
SETANSI_NULLSON
GO
SETQUOTED_IDENTIFIERON
GO
-- =============================================
-- Author: Robert G. Seminario
-- Create date: September 17, 2018
-- Description: Populates Pledge Schedule based on user parameters
-- =============================================
CREATEPROCEDURE [dbo].[usp_EgatePledgeSchedule](
--ADD PARAMETERS HERE
@P_HowMuch int,
@P_HowMany asint,
@P_PledgeStartDt asdatetime,
@P_Frequency asnvarchar(50),
@P_GID asint
)
AS
BEGIN

DECLARE @PaymentNumber asint= 1,
@cnt INT= 0 --Constant
While @cnt < @P_HowMany
BEGIN
INSERTINTO [dbo].[tblPledgePayments]
(PP_schedule, GiftID, PP_DueDate, PP_Amount)
Select
@PaymentNumber, @P_GID,
Case
WHEN @P_Frequency ='Yearly'THENDateAdd(YEAR, @cnt, @P_PledgeStartDt)
WHEN @P_Frequency ='Monthly'THENDateAdd(MONTH, @cnt, @P_PledgeStartDt)
WHEN @P_Frequency ='Quarterly'THENDateAdd(QUARTER, @cnt, @P_PledgeStartDt)
END
,@P_HowMuch / @P_HowMany
SET @cnt = @cnt + 1;
SET @PaymentNumber = @PaymentNumber + 1;
END;
END

How is this error over come. This will make it work?:confused:
 
Hi,

You seem to be passing the whole connection object to strCn. I'm not sure how you don't get an error at that point!

Try:
Code: 
' ...
strCn = CurrentProject.Connection.ConnectionString
Debug.Print strCn
' ...

Are you sure your db's CurrentProject.Connection points to your SQLServer?

I added a Debug.Print line so that the actual string will be output to the Immediate Window (Ctrl+G)

If it doesn't work, then paste the output here (but be sure to anonymise your server address, username and password, if they show)

hth,

d
 
cheekybuddha,

Yes, the current project points to the SQL tables since they are all linked tables. I added the Debug.Print code but when, I step into the code with F8 key it does nothing.:confused:
 
Here is a screen shot.
 

Attachments

  • DebugInformation.jpg
    DebugInformation.jpg
    90.6 KB · Views: 89
cheekybuddha,

Sorry, I ran Debug further down and got the following screen shot.
 

Attachments

  • DebugInfoTOOLong.jpg
    DebugInfoTOOLong.jpg
    92.6 KB · Views: 77
cheekybuddha,

Here is the entire string:

: strCn : "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=J:\Reports\Request\Egate\ETest.accdb;Mode=Share Deny None;Extended Properties="";Jet OLEDB:System database=C:\Users\seminr01"
 
OK, as I suspected, that is the connection string for the current accdb database you are using, and not the connection to your SQL server!

How are you connecting to the server? Are you using a DSN? Are you using linked tables?
 
By the way, the reason there is nothing in the Immediate Window is because you did not copy and paste all the code I suggested in post #8.

You only have Debug.Print whereas you need Debug.Print strCn

Just having Debug.Print on its own will print an empty line in the Immediate Window!
 
Last edited:
What is odd is that when I hover the mouse pointer over the linked tables it show:

ODBC;Description=XYZ;DRIVER=SQL Server;SERVER= XYZ;Trusted_Connection=Yes;APP=2007 Microsoft Office system;;TABLE=dbo.tblPledgePayments
 
Yes, that would be expected.

So, use that connection string - I would leave off the table, since you don't need to connect to a specific table to run your SP.

Try:
Code: 
Private Sub btnCS_Click()
  Dim strSQL As String
  Dim strCn As String
  strCn = "ODBC;Description=XYZ;DRIVER=SQL Server;SERVER= XYZ;Trusted_Connection=Yes;APP=2007 Microsoft Office system;"
  strSQL = "usp_EgatePledgeSchedule " & Me.txt_strHM & ", " & Me.txt_strHMP & ", '" & _
              Format(Me.txt_strSD, "yyyy-mm-dd") & "', '" & Me.txt_strFreq & "', '" & Me.txt_strGID & "';"
  With CurrentDb.CreateQueryDef()
    .Connect = strCn
    .SQL = strSQL
    .ReturnsRecords = False
    .Execute dbFailOnError
  End With
End Sub

You may have to add your password to the string, but you can cross that bridge when/if you come to it.

hth,

d
 
Error is now gone but have new one. Run-Time error 3420 Object invalid or no longer set.
 
You must log in or register to reply here.

Similar threads

J
Dynamic Parameters on Stored Procedures in MS Access query
Replies
4
Views
1,178
jco23
J
T
Run Sproc with parameter in VBA
Replies
11
Views
774
Galaxiom
G
J
Help with VBScript date
2
Replies
22
Views
1,813
jocph
J
V
Dealing with Null value on SQL Statement
2
Replies
24
Views
2,158
theDBguy
theDBguy
A
run time error 3061 too few parameters expected 2
Replies
8
Views
2,173
pbaldy
pbaldy

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom