Reputation: 3
I am trying to update a row in a table in my database, which is of data type: date
.
I am using a stored procedure, and I am retrieving the value to update to from a TextBox as a string. My problem is that a string has double quotes (") and when I'm passing my value in the string, it keeps the double quotes and SQL-Server only supports single quotes ('').
intIndividualSessionID = Me.IndividualSession_ListBox.Column(1)
sessionDate = Me.DateTextBox.Value
startTime = Me.StartTimeTextBox.Value
endTime = Me.EndTimeTextBox.Value
'Setup ADO objects to run stored procedure
Set cn = New ADODB.Connection
cn.Open strCon
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = cn
.CommandType = adCmdText
'Create string to include parameters
CommandText = "Exec UpdateIndividualSession " & intClientID & "," & intIndividualSessionID & "," & "sessionDate" & "," & "startTime" & "," & "endTime" & ";"
.CommandTimeout = 0
.Execute
End With
this gives me an error when I run.
Error converting datatype nvarchar to date."
Upvotes: 0
Views: 206
Reputation: 71247
You should be using Parameter
objects, not concatenating strings - you are passing the literal value sessionDate
, not your local sessionDate
variable's value.
SQL Server uses single quotes to delimit strings, so I'm not sure why you are using double quotes here, and you could double them up to escape them but not nudging you away from that direction would be unwise.
Make the CommandType
be adCmdStoredProc
, and the CommandText
should be just the name of the stored procedure - then use the Command
object to create and append the Parameters
:
.CommandType = adCmdStoredProc
.CommandText = "UpdateIndividualSession"
.Parameters.Append .CreateParameter(...)
.Parameters.Append .CreateParameter(...)
.Parameters.Append .CreateParameter(...)
.Parameters.Append .CreateParameter(...)
.Execute
That's the beauty of using Command
and a stored procedure: now it's the server's job to deal with the parameter values, and no delimiter gets in the way.
Make sure sessionDate
is declared with Dim sessionDate As Date
, and consider using an explicit conversion function instead of a direct assignment, but the general idea is to send a Date
value for an adDate
server parameter.
Upvotes: 3