SQL Update Statement Trouble

[Thomas7399]

Hi all,

I have another question. I am trying to use an update statement to update an item in a listbox. Please see below:

Imports System.Data.SqlServerCe
Public Class Edit_Sensor_Form

' Shared variables  
Dim con As SqlCeConnection = New SqlCeConnection("Data Source=C:\Users\Bryan\documents\visual studio 2010\Projects\SiteSecure v1.1\SiteSecure v1.1\Database1.sdf")
Dim myDA As New SqlCeDataAdapter
Dim myDataSet As New DataSet
Dim DataGridView1 As New DataGrid
Dim ret As SqlCeDataReader
Dim mySelectQuery As String = "Select * FROM Sensor_Table"
Dim myUpdateQuery As String = "UPDATE Sensor_Table SET Equipment_ID = @id, Location = @Loc, Supervisor = @Sup, Alarm_Start = @Astr, Alarm_Stop = @Asto WHERE Index = @LBIndex"
Dim SelectedIndex As Integer

Private Sub Edit_Sensor_Form_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
    Try
        SelectedIndex = My.Forms.Form1.ListBox1.SelectedIndex
        Dim cmd As New SqlCeCommand(mySelectQuery, con)
        con.Open()
        myDA.SelectCommand = cmd
        myDA.Fill(myDataSet)
        TextBox1.Text = IIf(IsDBNull(myDataSet.Tables(0).Rows(SelectedIndex)("Equipment_ID")), "", myDataSet.Tables(0).Rows(SelectedIndex)("Equipment_ID"))
        TextBox2.Text = IIf(IsDBNull(myDataSet.Tables(0).Rows(SelectedIndex)("Location")), "", myDataSet.Tables(0).Rows(SelectedIndex)("Location"))
        TextBox3.Text = IIf(IsDBNull(myDataSet.Tables(0).Rows(SelectedIndex)("Supervisor")), "", myDataSet.Tables(0).Rows(SelectedIndex)("Supervisor"))
        DateTimePicker1.Value = IIf(IsDBNull(myDataSet.Tables(0).Rows(SelectedIndex)("Alarm_Start")), "", myDataSet.Tables(0).Rows(SelectedIndex)("Alarm_Start"))
        DateTimePicker2.Value = IIf(IsDBNull(myDataSet.Tables(0).Rows(SelectedIndex)("Alarm_Stop")), "", myDataSet.Tables(0).Rows(SelectedIndex)("Alarm_Stop"))
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
End Sub
'Edit Sensor Function
Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
    Try
        SelectedIndex = My.Forms.Form1.ListBox1.SelectedIndex
        Dim cmd As New SqlCeCommand(myUpdateQuery, con)
        cmd.Parameters.Add(cmd.CreateParameter).ParameterName = "@id"
        cmd.Parameters.Add(cmd.CreateParameter).ParameterName = "@Loc"
        cmd.Parameters.Add(cmd.CreateParameter).ParameterName = "@Sup"
        cmd.Parameters.Add(cmd.CreateParameter).ParameterName = "@AStr"
        cmd.Parameters.Add(cmd.CreateParameter).ParameterName = "@ASto"
        cmd.Parameters.Add(cmd.CreateParameter).ParameterName = "@LBIndex"
        cmd.Parameters.Item("@id").Value = TextBox1.Text
        cmd.Parameters.Item("@Loc").Value = TextBox2.Text
        cmd.Parameters.Item("@Sup").Value = TextBox3.Text
        cmd.Parameters.Item("@Astr").Value = DateTimePicker1.Value
        cmd.Parameters.Item("@ASto").Value = DateTimePicker2.Value
        cmd.Parameters.Item("@LBIndex").Value = Convert.ToString(SelectedIndex)
        cmd.ExecuteNonQuery()
        Me.Close()

    Catch ex As Exception
        MsgBox(ex.Message)
    End Try

End Sub

End Class

I keep getting the following error upon clicking Button2:

There was an error parsing the query. [Token line number = 1, Token line offset = 127, Token in error = Index ]

Index is an integer in my table, is the primary key, and auto-increments from zero as records are added to my database. I can't figure out why I keep getting this error. When I replace "WHERE Index = @LBIndex" with something like "WHERE Equipment_ID = xyz", then things work fine (Equipment_ID is nvchar). Please help!

[Reverend Jim]

Index is a reserved word. You can either rename the field to something other than Index, or you can specify the field as [Index] as in

Dim myUpdateQuery As String = "UPDATE Sensor_Table         " & _
                              "   SET Equipment_ID = @id,  " & _
                              "       Location = @Loc,     " & _
                              "       Supervisor = @Sup,   " & _
                              "       Alarm_Start = @Astr, " & _
                              "       Alarm_Stop = @Asto   " & _
                              " WHERE [Index] = @LBIndex"

[Thomas7399]

Wow! Thanks for the help. Works great!