2

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
IggyDaDog
  • 53
  • 4
  • 2
    You can't use parameters to specify identifiers; only values. It's just like in VB code: you can use a method parameter to pass a value in but you can't use a method parameter to specify the name of a variable. People will tell you to always use parameters with SQL code and they are right, but only when you CAN use them. If you need to specify a column with a variable then you have no choice but to use string concatenation of some sort. That opens you up to all the same issues as usual, so you must be sure to validate the column name before use. – jmcilhinney Jan 27 '19 at 13:24
  • 1
    That said, I would suggest that your database design is wrong. If you want to be able to update multiple questions for a single quiz then each one should be a row, not a column. You would have a Question table with a foreign key to the Quiz table, so you knew which quiz each question related to. With that design, you wouldn't have to specify a variable column name. – jmcilhinney Jan 27 '19 at 13:26
  • 1
    Ah, okay that makes sense. I already had a questions table but the question fields were gonna link to the table; but I see that's wrong now... Thanks @jmcilhinney – IggyDaDog Jan 27 '19 at 13:30
  • **[Database Normalization](https://www.studytonight.com/dbms/database-normalization.php)** – Ňɏssa Pøngjǣrdenlarp Jan 27 '19 at 18:17

1 Answers1

1

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
GMB
  • 216,147
  • 25
  • 84
  • 135
  • I had tried that but it didn't work for me. I did what was mentioned in the comments and that will work fine for what I'm trying to do. – IggyDaDog Jan 27 '19 at 15:15