CODEWITHSUNDEEP
sql-serverexcelvba
808sama
808sama

Reputation: 3

VBA strip string of double quotes when passed to stored procedure

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

Answers (1)

Mathieu Guindon
Mathieu Guindon

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

Related Questions