Having trouble pulling from SQL database

Question

Thomas7399 0 Newbie Poster

11 Years Ago

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?

vb.net

4 Contributors
12 Replies
241 Views
1 Week Discussion Span
Latest Post 11 Years Ago Latest Post by Begginnerdev

All 12 Replies

TnTinMN 418 Practically a Master Poster

11 Years Ago

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

Begginnerdev commented: Good catch, didn't see that one! +6

john.knapp 25 Posting Whiz in Training

11 Years Ago

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

TnTinMN commented: good point +6

john.knapp 25 Posting Whiz in Training

11 Years Ago

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!

Begginnerdev commented: I add it on the first draft! +6

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Begginnerdev 256 Junior Poster · Answer 1 · 2012-12-20T14:04:08+00:00

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

john.knapp 25 Posting Whiz in Training · Answer 2 · 2012-12-21T02:19:30+00:00

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 0 Newbie Poster · Answer 3 · 2012-12-21T02:31:21+00:00

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 0 Newbie Poster · Answer 4 · 2012-12-21T02:32:34+00:00

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

Thomas7399 0 Newbie Poster · Answer 5 · 2012-12-21T02:40:01+00:00

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 0 Newbie Poster · Answer 6 · 2012-12-21T02:43:31+00:00

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

john.knapp 25 Posting Whiz in Training · Answer 7 · 2012-12-21T15:14:23+00:00

john.knapp 25 Posting Whiz in Training

11 Years Ago

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

Thomas7399 0 Newbie Poster · Answer 8 · 2012-12-22T22:59:56+00:00

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

Begginnerdev 256 Junior Poster · Answer 9 · 2012-12-27T21:33:35+00:00

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

Having trouble pulling from SQL database

Recommended Answers Collapse Answers

All 12 Replies

Recommended Answers