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" wrote:

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.

--
urkec

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.

"urkec" wrote:

"NormaD" wrote:

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.

--
urkec