Paul Williams Paul Williams - 3 months ago 12
Vb.net Question

Using the Replace() function in a parameterized query

I have the below mentioned code trying to perform an update/replace function on each row in the database where it finds the "Search" Pattern being stored in the parameter. However, when I run the query in the VB.NET program, the program is skipping over the row that it is supposed to be hitting.

For example, say the

intQuestionID
variable is set to 4. And within the
QuestionStacks
table is a value
;4;0;14;24;34;
. The SQL should be replacing the first
;4;
with
;0;
but it is not. In fact, nothing is being changed. Even if the value is a sole
;4;
.

Dim sqlClearQuestion As New OleDb.OleDbCommand("UPDATE QuestionStacks SET QuestionData = REPLACE([QuestionData], "";@0;"", "";0;"") ", DBConnection)
sqlClearQuestion.Parameters.AddWithValue("@0", intQuestionID)
sqlClearQuestion.ExecuteNonQuery()


I've checked this query by running the SQL in the Query Builder in Access 2016 and it works fine as intended. However, attempting to use it in this VB.NET function, it is not yielding the desired result. What change is needed for this?

N.B. I know that
.AddWithValue
on Access SQL isn't needed, and is actually changed to something else (Read: http://stackoverflow.com/a/6794245/692250). But with only one parameter, there shouldn't be any problem with this.

Answer

One should always be suspicious when they see delimiters or any other decoration surrounding a parameter placeholder. Parameter placeholders should appear alone, with any required special characters in the parameter value. For example, a very common mistake is to try and use

' no good
cmd.CommandText = "SELECT * FROM Students WHERE FirstName LIKE '?%'"
cmd.Parameters.AddWithValue("?", theName)

when the correct approach is to use

' good
cmd.CommandText = "SELECT * FROM Students WHERE FirstName LIKE ?"
cmd.Parameters.AddWithValue("?", theName & "%")

In your particular case, you'll want to use

cmd.CommandText = "UPDATE QuestionStacks SET QuestionData = REPLACE([QuestionData], ?, "";0;"") "
cmd.Parameters.AddWithValue("?", ";" & intQuestionID & ";")