Run MS Sproc with 4 parameters (1 Viewer)

Tupacmoche · Sep 24, 2018

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.

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;

cheekybuddha · Sep 25, 2018

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]

Galaxiom · Sep 25, 2018

Tupacmoche said:
DECLARE @TxtHowMuch as int,
@TxtHowMany as int,

,@TxtHowMuch / @TxtHowMany

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

Tupacmoche · Sep 25, 2018

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.

Minty · Sep 25, 2018

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

cheekybuddha · Sep 25, 2018

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

Tupacmoche · Sep 25, 2018

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?

cheekybuddha · Sep 25, 2018

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

Tupacmoche · Sep 25, 2018

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.

cheekybuddha · Sep 25, 2018

Did you look in the Immediate Window (Ctrl+G) ?

Tupacmoche · Sep 25, 2018

Here is a screen shot.

Tupacmoche · Sep 25, 2018

cheekybuddha,

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

Tupacmoche · Sep 25, 2018

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"

cheekybuddha · Sep 25, 2018

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?

Tupacmoche · Sep 25, 2018

Linked tables.

cheekybuddha · Sep 25, 2018

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!

Tupacmoche · Sep 25, 2018

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

cheekybuddha · Sep 25, 2018

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

Tupacmoche · Sep 25, 2018

Error is now gone but have new one. Run-Time error 3420 Object invalid or no longer set.

cheekybuddha · Sep 25, 2018

Which line returns the error?

Run MS Sproc with 4 parameters (1 Viewer)

Registered User.

AWF VIP

Super Moderator

Registered User.

AWF VIP

AWF VIP

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

Attachments

Registered User.

Attachments

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

AWF VIP

Similar threads

Users who are viewing this thread