multiple SQL queries stored as vb.net variable

Question

I know there are a few other posts about putting sql results into a vb.net variable but I just couldn't wrap my head around how they did it and how I could do it in my project.

So what I'm trying to do is query my database for 4 different values and then display each value in a certain part of the form. I was going to store the each value into a variable and then do an textbox1.text = i

Updated code for 10/5/2014

 Private Sub LBmembers_SelectedIndexChanged(sender As Object, e As EventArgs) Handles LBmembers.SelectedIndexChanged

    Dim i As String = LBmembers.SelectedItem
    Dim dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
    Dim dbSource = "Data Source= C:\members.mdb "
    Dim SqlQuery As String = "SELECT StartTime, EndTime, ShipCode, CycleTime, WorkPercent, Share FROM tblMembers WHERE Member = @ID;"
    Using con = New OleDb.OleDbConnection(dbProvider & dbSource)
        Using cmd = New OleDb.OleDbCommand(SqlQuery, con)
            con.Open()
            cmd.Parameters.AddWithValue("@ID", OleDb.OleDbType.VarWChar).Value = i
            Using reader = cmd.ExecuteReader()
                If reader.Read() Then
                    TBtimestart.Text = reader.ToString(0)
                    TBtimeend.Text = reader.ToString(1)
                    Dim j = Convert.ToInt32(reader.ToString(2))
                    TBtimecycle.Text = reader.GetInt32(3).ToString
                    TBmemberpercent.Text = reader.GetInt32(4)
                    TBmembershare.Text = reader.GetInt32(5)
                    If j = 1 Then
                        RBpro.Checked = True
                    ElseIf j = 2 Then
                        RBret.Checked = True
                    ElseIf j = 3 Then
                        RBcov.Checked = True
                    ElseIf j = 4 Then
                        RBskiff.Checked = True
                    ElseIf j = 5 Then
                        RBmack.Checked = True
                    ElseIf j = 6 Then
                        RBhulk.Checked = True
                    Else
                        RBpro.Checked = False
                        RBret.Checked = False
                        RBcov.Checked = False
                        RBskiff.Checked = False
                        RBmack.Checked = False
                        RBhulk.Checked = False
                        Exit Sub
                    End If
                End If
            End Using
            con.Close()
        End Using
    End Using
End Sub

The exception

InvalidCastException was unhandled Specified cast is not valid.

Pops up on the line TBtimecycle.text = reader.GetInt32(3).ToString

The reader is also reading "System.Data.OleDb.OleDbDataReader" when I insert the test code "TBgrossisk.Text = reader.ToString()" into the using statement

Steve · Accepted Answer · 2014-10-05 19:53:22Z

1

If you look carefully to the syntax of the SELECT statement you will see that you can request any column of the table with just one query.

Private Sub ListBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles LBmembers.SelectedIndexChanged
    if string.IsNullOrWitheSpace(ListBox1.SelectedItem.ToString()) Then
        MessageBox.Show("Select an item from the Listbox")
    End If

    Dim member As String = ListBox1.SelectedItem
    Dim dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
    Dim dbSource = "Data Source= C:\members.mdb "
    Dim SqlQuery As String = "SELECT StartTime, EndTime, ShipCode, CycleTime " & _ 
                             "FROM tblMembers WHERE Member = @ID;"
    Using con = New OleDb.OleDbConnection(dbProvider & dbSource)
        Using cmd = New OleDb.OleDbCommand(SqlQuery, con)
            con.Open()
            cmd.Parameters.AddWithValue("@ID", OleDb.OleDbType.VarWChar).Value = member
            Using reader = cmd.ExecuteReader
               if reader.Read() Then
                  TextBox1.Text = reader.GetString(0)
                  TextBox2.Text = reader.GetString(1)
                  Dim j = reader.GetInt32(2)
                  If j = 1 Then
                      Radio1.Checked = True
                  ElseIf j = 2 Then
                      Radio2.Checked = True
                  ElseIf j = 3 Then
                      Radio3.Checked = True
                  ElseIf j = 4 Then
                      Radio4.Checked = True
                  ElseIf j = 5 Then
                      Radio5.Checked = True
                  ElseIf j = 6 Then
                      Radio6.Checked = True
                  Else
                      Exit Sub
                  End If
                  TextBox4.Text = reader.GetInt32(3).ToString()
               Else
                  MessageBox.Show("The search for '" & member & "' doesn't find any data")
               End If
           End Using
        End Using
    End Using
End Sub

Instead of using ExecuteScalar that returns just the first column of the first row retrieved you could use an OleDbDataReader returned by the method ExecuteReader. This object allows to access the various column in the current record using an index (or the column name). Read more about OleDbDataReader in MSDN

edited Oct 5, 2014 at 19:53

answered Oct 4, 2014 at 21:58

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

23 Comments

Gotrek65 Over a year ago

"No value given for one or more required parameters" on line "Using reader = cmd.executereader"

Steve Over a year ago

Are you sure that your tblMembers has these 5 columns? They should be exactly named as Column1 to Column5.

Gotrek65 Over a year ago

I omitted their proper names for security reasons but yes it does have 5 columns. Column 1 is the member's name(which is unique and the primary key) Would I get the error if say there was no data in columns 2-4?

Steve Over a year ago

No, you will get a different error. This one usually means that the Access engine cannot find a column or a table with the name you have written in the query. So, for example, if your column1 is named IDMember then use that name in the query and likewise for the other columns. Please update your question with the real names, so I could try to update my answer

Gotrek65 Over a year ago

Wow I just caught my error the first column in the select statement is backwards

|

Collectives™ on Stack Overflow

multiple SQL queries stored as vb.net variable

1 Answer 1

23 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

23 Comments

Your Answer

Sign up or log in

Post as a guest

Related