ADO, Dates and SQL Server stored procedures

We are working with VB 6.0 and SQL Server 7.0, and we use stored procedures in order to insert data in the DB tables. We are in trouble inserting dates.

The date format in the DB is "mm/dd/yyyy" and the ODBC returns the following error message:

Source: Microsoft OLE DB Provider for ODBC Drivers --- SqlState: S1C00 --- Navigate Error: 0 --- Description: [Microsoft][ODBC SQL Server Driver]Optional feature not implemented
>>> It may not be the exact description in English because we are working with a Spanish release.

We try to put the date in the DB format before inserting sending it to the stored procedure, but when we define the parameter type (adDate) the date returns to its original format ("dd/mm/yyyy"). Can anyone help us?

Thank you.

How are you calling the SP from ADO?

Have you tried using the Connection.Execute (SQL) method?
If this is already the case, what is the actual string you are trying to execute?

J.

We execute directly a command of the ADODB object doing
CommandName.Execute.
First we fill up the parameters and then we execute the command, but the problem is that the date parameters don´t work fine. We think it´s because of the date format that the data type "adDate" has.
The stored procedure works well if we don´t use date parameters.

[RIVES]

Have you ever tried of just passing a string with 10 characters to the stored procedure.

--SQL Server

Create Procedure sp_update_with_date
(@record_id char(4),
@date_updated char(10)
)

as

Update table1 Set date_column = @date_updated where record_id = @record_id

return

--VB

Be sure to format the parameter to MM/DD/YYYY

strDate = FORMAT(mydate, "MM/DD/YYYY")

Then just pass it to the stored proc.

Note:

Since you are using VB6 on SQL server, why don't you bypass the ODBC and go directly to the ole db connection. Thats better. In this case, you don't have to create an odbc value for your database connectivity. You will be using the SQL Server client connectivity...be sure to install the client utilities to the client computer though.

Still, just a suggestion.

Also using ODBC can be much much slower and SQL Server also has problems droping tempdb data when you use ODBC to connect to the database. You could end up with the server being unusable as tempdb is full up.