Setting Connection Property to pass 3 parameters to an SQL Proc.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to connect to a SQL Stored Proc and pass 3 parameters. I can
sucessfully pass both date parameters, but the string parameter, @SalesRep in
the procedure gives an error message. "Compile Error, Snytax Error". Below
is the connection string I am attempting to use:

With CMD
.ActiveConnection = CN
.CommandText = "SALES_REP_INFORMATION"
.CommandType = adCmdStoredProc
.Parameters.Append.CreateParameter("@SalesRep", adChar, adParamInput,
strSalesRep)
.Parameters.Append .CreateParameter("@BeginDate", adDate, adParamInput,
, datBeginDate) 'needs to be date
.Parameters.Append .CreateParameter("@EndDate", adDate, adParamInput, ,
datEndDate) 'needs to be date

End With

If I comment out the @Sales Rep the the information passes the date range to
the stored procedure correctly. I am collecting the parameters from specific
ranges on the Excel Sheet. The parameter for @SalesRep is a char(19) within
the stored proc but I am gathering the information as a string in VB.

Help I have check spelling, etc. and am having no luck. Anyone got any
suggestions for other areas to check.

Thanks

Normad
 
NormaD said:
I am trying to connect to a SQL Stored Proc and pass 3 parameters. I can
sucessfully pass both date parameters, but the string parameter, @SalesRep in
the procedure gives an error message. "Compile Error, Snytax Error". Below
is the connection string I am attempting to use:

With CMD
.ActiveConnection = CN
.CommandText = "SALES_REP_INFORMATION"
.CommandType = adCmdStoredProc
.Parameters.Append.CreateParameter("@SalesRep", adChar, adParamInput,
strSalesRep)
.Parameters.Append .CreateParameter("@BeginDate", adDate, adParamInput,
, datBeginDate) 'needs to be date
.Parameters.Append .CreateParameter("@EndDate", adDate, adParamInput, ,
datEndDate) 'needs to be date

End With

If I comment out the @Sales Rep the the information passes the date range to
the stored procedure correctly. I am collecting the parameters from specific
ranges on the Excel Sheet. The parameter for @SalesRep is a char(19) within
the stored proc but I am gathering the information as a string in VB.

Help I have check spelling, etc. and am having no luck. Anyone got any
suggestions for other areas to check.

Thanks

Normad

On this line:

.Parameters.Append.CreateParameter("@SalesRep", adChar, adParamInput,
strSalesRep)

you need a space between Append and .CreateParameter:

.Parameters.Append .CreateParameter("@SalesRep", adChar, adParamInput,
strSalesRep)


Hope this helps.
 
Thank you for your help. That was one of my problems the other was setting
the correct type of variable to pass information into my Stored Procedure.
It took a lot of just plain trial and error, the Excel side would use let me
declare a variant type and the stored procedure allowed me to use a
sql_variant after that everything ran smoothly.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top