none
Using Values from Comboboxes in a Datagid? RRS feed

  • Question

  • Instead of the message box code I now want to use the SelectedValue from each combobox.

    I have tried this but it does not work:

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            'MsgBox($"{ComboBox1.SelectedValue} -- {ComboBox1.Text} / {ComboBox2.SelectedValue} -- {ComboBox2.Text}")
            Dim sql As String = Nothing
            Dim cboVal1 As Integer
            Dim cboVal2 As Integer
            cboVal1 = ComboBox1.SelectedValue
            cboVal2 = ComboBox2.SelectedValue
    
            Using connection As New SqlConnection(connectionString)
                connection.Open()
                sql = "Select VID, EVENT, Event_Sub from Verse WHERE (Event= @cboVal1 AND Event_Sub=@cboVal2)"
            End Using

    The previous Sub routine fills the comboboxes:

     Private Sub frmSecond_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            'TODO: This line of code loads data into the 'Verses_FindDataSet3.Verse' table. You can move, or remove it, as needed.
            Me.VerseTableAdapter.Fill(Me.Verses_FindDataSet3.Verse)
            Dim sql As String = Nothing
            Dim ds As New DataSet()
            Dim stepInfo As String = String.Empty
            Try
                stepInfo = "Step: instatiate connection"
                Using connection As New SqlConnection(connectionString)
                    stepInfo = "Step: test open connection"
                    connection.Open()
                    stepInfo = "Step: load first data"
                    sql = "Select SID, EVENT from Event_Type"
                    Using adaptor As New SqlDataAdapter(sql, connection)
                        adaptor.Fill(ds, "Tab_Event_Type")
                    End Using
                    stepInfo = "Step: load second data"
                    sql = "Select BID, SUBEVENT from Sub_Event"
                    Using adaptor As New SqlDataAdapter(sql, connection)
                        adaptor.Fill(ds, "Tab_Sub_Event")
                    End Using
                End Using
                stepInfo = "Step: bind first combobox"
                ComboBox1.DataSource = ds.Tables("Tab_Event_Type")
                ComboBox1.ValueMember = "SID"
                ComboBox1.DisplayMember = "Event"
                stepInfo = "Step: bind second combobox"
                ComboBox2.DataSource = ds.Tables("Tab_Sub_Event")
                ComboBox2.ValueMember = "BID"
                ComboBox2.DisplayMember = "Event_Sub"
            Catch ex As Exception
                MessageBox.Show($"Error: {stepInfo}{vbNewLine}{ex.ToString}")
            End Try
        End Sub

    I need the SID and BID values as the Verse table saves the values of the ID columns to select type of verse required.

    Your help would be appreciated!

    Tuesday, March 5, 2019 4:59 PM

Answers

  • Try this

    Dim sqlAdapter As SqlDataAdapter
    Dim cboVal1 As Integer
    Dim cboVal2 As Integer
    Dim dt As New DataTable()
    cboVal1 = CInt(ComboBox1.SelectedValue)
    cboVal2 = CInt(ComboBox2.SelectedValue)
    
    Using connection As New SqlConnection(connectionString)
        connection.Open()
    
        Dim selectStatement = $"Select VID, EVENT, Event_Sub From Verse where Event= {cboVal1} and Event_Sub= {cboVal2}"
        sqlAdapter = New SqlDataAdapter(selectStatement, connection)
        dt = New DataTable()
        sqlAdapter.Fill(dt)
        DataGridView1.DataSource = dt
    End Using

    If using an older version of Visual Studio do the SQL statement as shown below

    Dim selectStatement = "Select VID, EVENT, Event_Sub From Verse where Event=" & cboVal1.ToString() & " and Event_Sub= " & cboVal2.ToString()


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    Thursday, March 7, 2019 3:57 PM
    Moderator

All replies

  • Here is how to get at the selected item.

    Public Class Form3
        Private Sub Form3_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Dim ops = New DataOperations
            ComboBox1.DataSource = ops.GetTable1
            ComboBox1.DisplayMember = "EVENT"
        End Sub
        ''' <summary>
        ''' Cast current ComboBox item to a DataRowView -> DataRow then access fields
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        Private Sub getCurrentButton_Click(sender As Object, e As EventArgs) Handles getCurrentButton.Click
            Dim row = CType(ComboBox1.SelectedItem, DataRowView).Row
            MessageBox.Show($"Id {row.Field(Of Integer)("SID")} Value {row.Field(Of String)("EVENT")} same as {ComboBox1.Text}")
        End Sub
    End Class
    ''' <summary>
    ''' A mock up for simulating data read from a database table
    ''' </summary>
    Public Class DataOperations
        Public Function GetTable1() As DataTable
            Dim dt As New DataTable
            dt.Columns.Add(New DataColumn() With {.ColumnName = "SID", .DataType = GetType(Integer)})
            dt.Columns.Add(New DataColumn() With {.ColumnName = "EVENT", .DataType = GetType(String)})
    
            dt.Rows.Add(New Object() {1, "E1"})
            dt.Rows.Add(New Object() {2, "E2"})
            dt.Rows.Add(New Object() {3, "E3"})
    
            Return dt
        End Function
    End Class


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Tuesday, March 5, 2019 5:28 PM
    Moderator
  • Hi Rocky,
    please remember to mark the replies as answers if they help! See your post.

    To load VID from Table Verse try this:

        Using connection As New SqlConnection(connectionString)
          connection.Open()
          Sql = "Select VID, EVENT, Event_Sub from Verse WHERE (Event= @cboVal1 AND Event_Sub=@cboVal2)"
          Using cmd As New SqlCommand(Sql, connection)
            cmd.Parameters.AddWithValue("@cboVal1", cboVal1)
            cmd.Parameters.AddWithValue("@cboVal2", cboVal2)
            Dim vid = cmd.ExecuteScalar
          End Using
        End Using


    --
    Viele Grüsse / Best Regards
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks

    • Proposed as answer by Alex Li-MSFT Wednesday, March 6, 2019 2:27 AM
    Tuesday, March 5, 2019 5:40 PM
  • Hi,

    try the code:

      Dim cboVal1 As Integer = ComboBox1.SelectedValue
            Dim cboVal2 As Integer = ComboBox2.SelectedValue
            Using conn = New SqlConnection(constr)
                conn.Open()
                sda = New SqlDataAdapter("Select VID, EVENT, Event_Sub From Verse where Event=" & cboVal1 & " and Event_Sub=" & cboVal2 & "", conn)
                dt = New DataTable()
                sda.Fill(dt)
                DataGridView1.DataSource = dt
            End Using

    Best Regards,

    Alex



    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, March 6, 2019 2:51 AM
  • I tried the code directly above, but had to change a few things to suit my existing declarations.

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            'MsgBox($"{ComboBox1.SelectedValue} -- {ComboBox1.Text} / {ComboBox2.SelectedValue} -- {ComboBox2.Text}")'
            Dim sql As String = Nothing
            Dim cboVal1 As Integer
            Dim cboVal2 As Integer
            Dim dt As New DataTable()
            cboVal1 = ComboBox1.SelectedValue
            cboVal2 = ComboBox2.SelectedValue
    
            Using connection As New SqlConnection(connectionString)
                connection.Open()
                'Sql = "Select VID, EVENT, Event_Sub from Verse WHERE (Event= @cboVal1 AND Event_Sub=@cboVal2)"
                sql = New SqlDataAdapter("Select VID, EVENT, Event_Sub From Verse where Event=" & cboVal1 & " and Event_Sub=" & cboVal2 & "", connection)
                dt = New DataTable()
                sql.Fill(dt)
                DataGridView1.DataSource = dt
            End Using
        End Sub

    I commented out the old sql line and added the modified one, but it reports that " Value of SqlDataAdaptor can not be converted to string and also the "sql.Fill(dt)" is not a member of string. 

    Can anyone help here as I am still learning. Not easy when you are a senior citizen!!


    TEH


    • Edited by Rocky48 Thursday, March 7, 2019 3:52 PM
    • Proposed as answer by Peter Fleischer Thursday, March 7, 2019 8:19 PM
    • Unproposed as answer by Peter Fleischer Thursday, March 7, 2019 8:19 PM
    Thursday, March 7, 2019 3:51 PM
  • Try this

    Dim sqlAdapter As SqlDataAdapter
    Dim cboVal1 As Integer
    Dim cboVal2 As Integer
    Dim dt As New DataTable()
    cboVal1 = CInt(ComboBox1.SelectedValue)
    cboVal2 = CInt(ComboBox2.SelectedValue)
    
    Using connection As New SqlConnection(connectionString)
        connection.Open()
    
        Dim selectStatement = $"Select VID, EVENT, Event_Sub From Verse where Event= {cboVal1} and Event_Sub= {cboVal2}"
        sqlAdapter = New SqlDataAdapter(selectStatement, connection)
        dt = New DataTable()
        sqlAdapter.Fill(dt)
        DataGridView1.DataSource = dt
    End Using

    If using an older version of Visual Studio do the SQL statement as shown below

    Dim selectStatement = "Select VID, EVENT, Event_Sub From Verse where Event=" & cboVal1.ToString() & " and Event_Sub= " & cboVal2.ToString()


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    Thursday, March 7, 2019 3:57 PM
    Moderator
  • Tried this does not filter the table in the datagrid!

    TEH

    Thursday, March 7, 2019 5:24 PM
  • Thanks that works, except I only wanted the verse to be displayed, but that was easy to fix.

    Thank you!


    TEH

    Thursday, March 7, 2019 7:22 PM