Using the Replace() function in a parameterized query

Question

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: https://stackoverflow.com/a/6794245/692250). But with only one parameter, there shouldn't be any problem with this.

that command looks ok. For debugging try it without the parameter (ie hardcode the value you want). This is just to check that you're pointed at the right db, not inside a transaction etc. — FloatingKiwi
– FloatingKiwi, Commented Sep 5, 2016 at 9:05
@FloatingKiwi, I did. And looking at the Access DB, it yields the desired results. — Paul Williams
– Paul Williams, Commented Sep 5, 2016 at 23:10

Gord Thompson · Accepted Answer · 2016-09-05 10:23:53Z

1

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 & ";")

answered Sep 5, 2016 at 10:23

Gord Thompson

125k39 gold badges252 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Paul Williams Over a year ago

Now to figure out why it isn't working for the LIKE operator of sorts.

Collectives™ on Stack Overflow

Using the Replace() function in a parameterized query

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related