Having trouble pulling from SQL database

[Thomas7399]

Hi,

I am brand new to using SQL with VB. I am trying to be able to insert data from any given row in my SQL table. The row to be inserted is selected by the user on a listbox on Form1. Here is my code:

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 SqlCeDataAdapter
Dim myDataSet As DataSet
Dim DataGridView1 As DataGrid
Dim ret As SqlCeDataReader
Dim mySelectQuery As String = "Select * FROM Sensor_Table"
Dim SelectedIndex As Integer

Private Sub Edit_Sensor_Form_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
    SelectedIndex = My.Forms.Form1.ListBox1.SelectedIndex
    Dim cmd As New SqlCeCommand(mySelectQuery, con)
    con.Open()
    myDA.SelectCommand = cmd
    myDA.Fill(myDataSet)
    TextBox1.Text = myDataSet.Tables(0).Rows(SelectedIndex).Item("Equipment_ID").ToString
End Sub

End Class

I believe my code is getting hung up at "myDA.SelectCommand = cmd". TextBox1.Text is empty (blank) when I execute my code. Can anyone please shed some light on this?

[Begginnerdev]

Could be throwing a null reference exception as well.

Try replacing:

TextBox1.Text = myDataSet.Tables(0).Rows(SelectedIndex).Item("Equipment_ID").ToString

With:

TextBox1.Text = IIF(IsDBNull(myDataSet.Tables(0).Rows(SelectedIndex)("Equipment_ID")),"",myDataSet.Tables(0).Rows(SelectedIndex)("Equipment_ID"))

[TnTinMN]

What I can not understand is how that code even runs such that he can see an empty textbox. Only the command variable (cmd) has an instance assigned to it.

Suggest changing:

Dim myDA As SqlCeDataAdapter
Dim myDataSet As DataSet

to:

Dim myDA As New SqlCeDataAdapter
Dim myDataSet As New DataSet

[john.knapp]

While you're at it, put some error handling in...

Imports System.Data.SqlServerCe
Public Class Edit_Sensor_Form
    Try
        ' Shared variables  
        Dim con As SqlCeConnection = _
                New SqlCeConnection("Data Source=|DataDirectory|\Database1.sdf")
        Dim myDA As New SqlCeDataAdapter
        Dim myDataSet As New DataSet
        Dim DataGridView1 As New DataGrid
        Dim ret As New SqlCeDataReader
        Dim mySelectQuery As String = "Select * FROM Sensor_Table"
        Dim SelectedIndex As Integer

        Private Sub Edit_Sensor_Form_Load(sender As System.Object, _
                                            e As System.EventArgs) _
                                            Handles MyBase.Load

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

        End Sub

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

End Class

[john.knapp]

hmm, just looked at that thin-air-coding I did.... I don't think I can put a Try..Catch block at the class level - oops! <whew! self-corrected>

Try this instead:

Imports System.Data.SqlServerCe
Public Class Edit_Sensor_Form
    ' Shared variables  
    Dim con As SqlCeConnection = _
    New SqlCeConnection("Data Source=|DataDirectory|\Database1.sdf")
    Dim myDA As New SqlCeDataAdapter
    Dim myDataSet As New DataSet
    Dim DataGridView1 As New DataGrid
    Dim ret As New SqlCeDataReader
    Dim mySelectQuery As String = "Select * FROM Sensor_Table"
    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"))
        Catch ex as Exception
            MsgBox(ex.Message)
        End Try
    End Sub

End Class

[Thomas7399]

Thanks all, my code now works! I will plan to add error handling later.

Now I am trying to update the record that I just loaded into my textboxes. Please see my code 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 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 = @Ind"
Dim SelectedIndex As Integer

Private Sub Edit_Sensor_Form_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
    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"))
End Sub

Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
    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 = "@Ind"
    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("@Ind").Value = Convert.ToString(SelectedIndex)
    If con.State = ConnectionState.Closed Then con.Open()
    cmd.ExecuteNonQuery()
End Sub

End Class

I keep getting the following error at the "cmd.ExecuteNonQuery()" line:

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

I have a feeling (based on my newb lack of SQL experience) that it has something to do with my Index. Index is a int column in my table.

Any help is appreciated! Thanks

[Thomas7399]

BTW, the when Button2 is pressed is when I am looking to update the row in my table.

[john.knapp]

Thanks all, my code now works! I will plan to add error handling later.

Bad idea! If you had error handling the first time, you would have been able to figure out that your DataAdapter wasn't working and why...

Get in the habit of typing Try and tabbing twice in every new procedure you write.
Going back and adding error handling is a major PITA!

[Thomas7399]

Thanks John --- You are right.

Is the DataAdapter the reason I am not able to update the row in my table? I am still a bit confused...

[Thomas7399]

BTW, I just went back and added error handling to all of my procedures!

[john.knapp]

Edit: Type Try and hit <Enter>, not Tab twice...

[Thomas7399]

I am still not able to figure out my error. Does anyone have any ideas? Thanks

[Begginnerdev]

If you take a screen shot of the the exception that is thrown, along with the message - We can trouble shoot it for you (hopefully)

Try placing MsgBox(ex.ToString) in the Try/Catch block.

Try
    'Your Code Here
Catch ex As Exception
    MsgBox(ex.ToString)
End Try