Passing date parameter in SQLCommand to SQL Database

[handokowijaya]

I'm trying to pass a date parameter using sqlcommand into sql server, however for some reason the date parameter caused the stored procedure to fail. Here's my code:

...sqlcmd initialization

With cmdHdrUpdate
With .Parameters
.Item("@DepositID").Value = dr("DepositID")
.Item("@AccountID").Value = dr("AccountID")
.Item("@DepositDate").Value = dr("DepositDate")
end with
end with
sqlcmd.executenonquery

Here's the result i got from Profiler:
exec uspDepositUpdate @DepositID=4769,@AccountID=9,
@DepositDate=''2003-06-03 00:00:00:000'',
@DepositType='Check',@Amount=$925.0000

Now, after tinkering around i found out that if the @DepositDate values doe sn't have 2 single quote then this query will run fine. However how do i do that since i have no control in the way sqlcommand translate the vb.net date value into sql date parameter.

Does anybody knows the solution for this problem??

Pls help, thanks
han

 

[lingsn]

May be try out

cdate(dr("DepositDate"))

 

[cjard]

First, tell the parameters colelction that youre passing a date!

.Item("@DepositDate").SqlDbType = SqlDbType.DateTime


Then put the value in as a date, not a string!

.Item("@DepositDate").Value = myDateTimePicker.Value
.Item("@DepositDate").Value = DateTime.Now;

etc..

 

[cjard]

and if dr("DepositDate") is a string, not a date, then select it as a date, not a string.. and cast it as a date when it comes in..

Note that CDate() is not a casting function. DirectCast() is a casting function