unable to update multiple colums

cool kid

hi there,
Im doing a reservation seats. In my database i have a columns t1,t2,t3 and etc where i want to assign it to 'booked'. But i keep getting error near "=". I tried and it seems the error is from the array part

Dim s As String = lblSelected.Text.ToString()
Dim forbook() As String
forbook = Split(s, ",")
For i As Integer = 0 To UBound(forbook)
mycn2.Open()
sql2 = "UPDATE bookingDetails SET '" & forbook(i).ToString() & "'='booked' WHERE bookingDate=@bookingDate AND startTime=@startTime AND endTime=@endTime AND storeId=@storeId"
cmd2.Parameters.AddWithValue("@bookingDate",DateTime.Parse(bookingDAte.ToString(formatDate)))
cmd2.Parameters.AddWithValue("@startTime", startTime)
cmd2.Parameters.AddWithValue("@endTime", endTime)
cmd2.Parameters.AddWithValue("@storeId", storeId)
cmd2.ExecuteNonQuery()
Next
mycn.close()

Any help would be much appreciated. Thanks in advance.

9 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vulpes14y ago
Accepted answer
Try:

Dim s As String = ",t1,t2,t3"
Dim items As String() = s.Split(New String(){","}, StringSplitOptions.RemoveEmptyEntries)
For Each item As String in items
Console.WriteLine(item)
Next
cool kid14y ago
Alright. Thanks Vulpes.
Vulpes14y ago
Not to my knowledge.

SQL doesn't support arrays so you have to use comma separated strings, or the like, instead.
cool kid14y ago
Yup. But it's working now. I figure it out. The type in the database I declare it wrongly.
Thanks. You helped me alot.
Im wondering how can I set an array into a parameter instead of using string concatenation
in SQL statement?
Is there a way?
Vulpes14y ago
I gather that you have 24 imagebuttons on a panel with IDs: t1, t2, ..., t24 and, if the value of the corresponding column in the database has a value of "booked", then you want to set the ImageUrl property accordingly and to disable the button.

So are you saying that none of the buttons becomes disabled when you run this code?
cool kid14y ago
I have one last question. I wanna show the selected seats. But nothing come out.

While rea.Read()
For j As Integer = 1 To 24
Dim t As String = "t" & j //column name
If (rea(t.ToString()).ToString() = "booked") Then
Dim img As ImageButton = DirectCast(pnl2.FindControl(t.ToString()), ImageButton)
img.ImageUrl = "~/images/bookTable.png"
img.Enabled = False
End If
Next
End While

Is there anything missing in my codes? Thanks in advance.
cool kid14y ago
Thanks alot vulpes!
cool kid14y ago
Okay thank you vulpes. I have another question. How do i split string like this ",t1,t2,t3" ?
Thanks in advance
Vulpes14y ago
I think the problem is that you shouldn't have single quotes around the column name, just it's value. So I'd try:

sql2 = "UPDATE bookingDetails SET " & forbook(i).ToString() & " = 'booked' WHERE bookingDate=@bookingDate AND startTime=@startTime AND endTime=@endTime AND storeId=@storeId"