Calling SQL Server Store Procedure 
Author Message
 Calling SQL Server Store Procedure

Hi,

I'm calling a SQL Server store procedure which takes datetime as one of the
input paramater.  So I took it as a string instead from the input and cast
it into datetime data type format, but VB is still complaining.

the code:

dateStr = "November 23, 2002"
dateStr1 = "October 01, 1999"
theDate = CDate(dateStr)
theDate1 = CDate(dateStr1)

set rs = db.Execute("dbo.mb_test_procedure theDate, theDate1, 66, 100")

When the execute command is executed it complaint saying:
   "Syntax error converting datetime from character string"

the Data Type of the inputs for the store procedure is nvarchar(20).  I have
changed it to varchar(20) and it still doesn't work.  Can someone help

Thanks
Henry



Sat, 08 Jan 2005 03:19:19 GMT  
 Calling SQL Server Store Procedure
My recommendation is to not convert it into a date datatype in
vb, but to pass it as a string, accepting it as a varchar. Then do
the manipulation in the stored procedure.
There

Jack

______________________________________
Pick their brains at www.ciquery.com


Quote:
> Hi,

> I'm calling a SQL Server store procedure which takes datetime as one of
the
> input paramater.  So I took it as a string instead from the input and cast
> it into datetime data type format, but VB is still complaining.

> the code:

> dateStr = "November 23, 2002"
> dateStr1 = "October 01, 1999"
> theDate = CDate(dateStr)
> theDate1 = CDate(dateStr1)

> set rs = db.Execute("dbo.mb_test_procedure theDate, theDate1, 66, 100")

> When the execute command is executed it complaint saying:
>    "Syntax error converting datetime from character string"

> the Data Type of the inputs for the store procedure is nvarchar(20).  I
have
> changed it to varchar(20) and it still doesn't work.  Can someone help

> Thanks
> Henry



Sat, 08 Jan 2005 13:22:23 GMT  
 Calling SQL Server Store Procedure
If you pass it as a string (after all, that's all you can do anyway with
your style of running a stored procedure) SQL Server will coerce it to the
correct datatype for you.

Howver, make sure that your data format cannot be mis-interpreted in
different locales - the best formats are:

    Long Month: eg. 01 Jan 2002      (DD MMM YYYY)
    Year First eg. 2002 03 04        (YYYY MM DD)

If you go down the route of creating a comman object with parameters then
you *can* specify the datatype for the parameter but you would still pass a
string value to the Parameter.Value and it would again coerce it.

Hope this helps.

Chris Barber.


Quote:
> Hi,

> I'm calling a SQL Server store procedure which takes datetime as one of
the
> input paramater.  So I took it as a string instead from the input and cast
> it into datetime data type format, but VB is still complaining.

> the code:

> dateStr = "November 23, 2002"
> dateStr1 = "October 01, 1999"
> theDate = CDate(dateStr)
> theDate1 = CDate(dateStr1)

> set rs = db.Execute("dbo.mb_test_procedure theDate, theDate1, 66, 100")

> When the execute command is executed it complaint saying:
>    "Syntax error converting datetime from character string"

> the Data Type of the inputs for the store procedure is nvarchar(20).  I
have
> changed it to varchar(20) and it still doesn't work.  Can someone help

> Thanks
> Henry



Sat, 08 Jan 2005 17:27:29 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Calling SQL Server Store Procedure

2. HELP - ASP Calling SQL Server Stored procedure

3. Calling SQL Server Stored Procedures

4. Calling SQL Server stored procedure

5. Calling SQL Server Stored Procedures

6. Calling a SQL Server 2000 store procedure from MS-Access

7. HELP: Calling Stored Procedure w/large output from VB6 (using SQL Server)

8. HELP: Calling Stored Procedure w/large output from VB6 (using SQL Server)

9. Calling Crystal Report from VB, repo is based on Stored Procedure in Sql Server

10. HELP: Calling Stored Procedure w/large output from VB6 (using SQL Server)

11. Call Parameterized SQL Server Stored Procedure from ADO

12. Calling a store procedure from vb6 with sql server

 

 
Powered by phpBB® Forum Software