Variables from a form to stored procedure

[ohmbru2]

I am not new to VBA, but I am to stored procedures. I need to use 2 variables from a from in my SP and I'm not quite sure how to do that. I found some info and code to help me in this forum and books online, but it's not working as I expected. I think I'm close but need a nudge in the right direction. Here's my SP and VBA code:

**********
SP
**********
CREATE PROCEDURE PremTaxReport
AS

DECLARE @PTRYear varchar
DECLARE @Co varchar
INSERT INTO dbo.PremTaxReport (Type, StateCode,EffDate, TranAmt, Face)
SELECT dbo.StateAbbrev.Type, dbo.vwGLGNT.StateCode, dbo.vwGLGNT.EffDate,dbo.vwGLGNT.TranAmt,dbo.vwGLGNT.Face
FROM dbo.vwGLGNT
WHERE (DatePart(yyyy,[EffDate])) = @PTRYear and Co = @Co
GO


********************
VBA
*********************
Dim adoCnn As ADODB.Connection
Dim adoCmd As ADODB.Command

Set adoCnn = New ADODB.Connection
Set adoCmd = New ADODB.Command

With adoCnn
.ConnectionString = "Driver={SQL Server};" & _
"Server=tamlrpcdbs1;" & _
"Database=glgnt;" & _
"Trusted_Connection=Yes"
"Provider=SQLOLEDB.1;Persist Security Info=False;User ID=password;Initial Catalog=GLGNT;Data Source=TAMLRPCDBS1"
If .State = 0 Then .Open
End With
With adoCmd
.ActiveConnection = adoCnn
.CommandText = "PremTaxReport"
.CommandType = adCmdStoredProc
.Parameters.Refresh
.Parameters(0) = Me!MyYear
.Parameters(1) = Me!Company
.Execute
End With
'**********************************************
END VBA
***********************************************

 

[PWise]

try

.CommandText = "PremTaxReport" [b]& " " & me.pramPTRYear & " " & me.pramco [/b]

 

[danvlas]

1. You haven't got any parameter in your stored procedure. You have variables.

CREATE PROCEDURE PremTaxReport
(@PTRYear varchar,
@Co varchar)

AS

INSERT INTO dbo.PremTaxReport (Type, StateCode,EffDate, TranAmt, Face)
SELECT dbo.StateAbbrev.Type, dbo.vwGLGNT.StateCode, dbo.vwGLGNT.EffDate,dbo.vwGLGNT.TranAmt,dbo.vwGLGNT.Face
FROM dbo.vwGLGNT
WHERE (DatePart(yyyy,[EffDate])) = @PTRYear and Co = @Co
GO

2. In VBA, you should add the parameters right before setting their values:

.Parameters.Append .CreateParameter("@PTRYear", adVarChar, adParamInput,50)
.Parameters.Append .CreateParameter("@PTRCo", adVarChar, adParamInput,50)

Now your code should work...

HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[ohmbru2]

Thanks Dan, I appreciate your help.

When I run the VBA, I get a 3421 run time error on this line:

.Parameters(1) = Me!Company

(Application uses a value of the wrong type for the current operation).

Dim adoCnn As ADODB.Connection
Dim adoCmd As ADODB.Command

Set adoCnn = New ADODB.Connection
Set adoCmd = New ADODB.Command

With adoCnn
.ConnectionString = "Driver={SQL Server};" & _
"Server=tamlrpcdbs1;" & _
"Database=glgnt;" & _
"Trusted_Connection=Yes"
If .State = 0 Then .Open
End With
With adoCmd
.ActiveConnection = adoCnn
.CommandText = "PremTaxReport"
.CommandType = adCmdStoredProc
.Parameters.Refresh
.Parameters.Append .CreateParameter("@PTRYear", adVarChar, adParamInput, 50)
.Parameters.Append .CreateParameter("@PTRCo", adVarChar, adParamInput, 50)
.Parameters(0) = Me!MyYear
.Parameters(1) = Me!Company
.Execute

End With

Am I assigning the 2nd parameter one correctly?

 

[danvlas]

What data type should your parameters be? Since you had declared the variables as CHAR, I assumed they were right...


I'd change the code to:

With adoCmd
      .ActiveConnection = adoCnn
      .CommandText = "PremTaxReport"
      .CommandType = adCmdStoredProc
      .Parameters.Append .CreateParameter("@PTRYear", [b]adInteger[/b], adParamInput, , Me!MyYear)
      .Parameters.Append .CreateParameter("@PTRCo", [COLOR=red]adVarChar[/color], adParamInput, 50, Me!Company)
      .Execute

   End With

Make sure you have the right data type mentioned in the code. Moreover, you should check that you don't pass null or invalid parameters.

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html