Passing date parameter in SQLCommand to SQL Database

handokowijaya

Member
Joined
Dec 14, 2006
Messages
5
Programming Experience
1-3
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
 
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..
 
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
 
Back
Top