CODEWITHSUNDEEP
sqlvb.netsql-updateoledb
IggyDaDog
IggyDaDog

Reputation: 53

How to create an update statement when the field is entered as a variable/parameter

I'm trying to update a table in a database, but every time the button a button is pressed, it should update a different column of the same record. Any ideas on how to do this?

The table has columns QuizID (autonumber), QuizName (string), OutOf (integer), Question1 (integer), Question2 etc. Every time the button is pressed it should update the next QuestionX column.

Using cmd As New OleDbCommand("UPDATE Quizzes SET @questionColumn = @questionID WHERE QuizName = @quizName", myConnection)
   cmd.Parameters.Add("@questionColumn", OleDbType.Char).Value = "Question" & questionNumber.ToString()
   cmd.Parameters.Add("@questionID", OleDbType.Integer).Value = questionID
   cmd.Parameters.Add("@quizName", OleDbType.Char).Value = txtQuizName.Text
   cmd.ExecuteNonQuery()
End Using

questionNumber is an integer that increments by 1 every time the button is pressed, and questionID and txtQuizName.Text are previously acquired values.

I expected that QuestionX column would update, but the code throws an error saying @questionColumn is not updatable; but if I replace that with Question1 and comment out the parameter, it works fine but doesn't (obviously) change which column it is updating.

What have I done wrong this time?

Upvotes: 2

Views: 56

Answers (1)

GMB
GMB

Reputation: 222402

You cannot bind a column name to a parameter. You would need to build the query dynamically.

Using cmd As New OleDbCommand("UPDATE Quizzes SET Question" & questionNumber.ToString() & " = @questionID WHERE QuizName = @quizName", myConnection)
   cmd.Parameters.Add("@questionID", OleDbType.Integer).Value = questionID
   cmd.Parameters.Add("@quizName", OleDbType.Char).Value = txtQuizName.Text
   cmd.ExecuteNonQuery()
End Using

Upvotes: 1

Related Questions