How to filter data and then update it in datagridview?

[karan desai]

I wanted to filter the data, make changes and the save back to the database.
The SQL data adapter and SQL dataset are clashing with TableDataAdapte r i.e., db1TableAdpater and DatabseDataSet i.e GreetingDataset .
I wanted to make them working for the above purpose.

I am also open for other methods of executing the code.

Please help!

Here's the code.

Imports System.Data
Imports System.Console
Imports System.Data.SqlClient
Imports System.Data.OleDb

Public Class Form1

    Dim sqlcon As New SqlConnection
    Dim ds As New DataSet
    Dim dc As SqlCommand
    Dim daa As New SqlDataAdapter
    Dim bs As New BindingSource
    Public Const ConnectionString As String = "Data Source=SQLORACLE;Initial Catalog=greeting;User ID=classic;password=stripes"


    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'GreetingDataSet.db1' table. You can move, or remove it, as needed.
        'Me.Db1TableAdapter.Fill(Me.GreetingDataSet.db1)

        sqlcon.ConnectionString = "Data Source=SQLORACLE;Initial Catalog=greeting;User ID=classic;password=stripes"
        Try
            sqlcon.Open()
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Connection to H247 Failed", MessageBoxButtons.OK)
        End Try
        If sqlcon.State = 1 Then
            Me.Text = "Connection successful"
        End If
        Call populate_combo(cboest, "ESTNO")
        cboest.Sorted = True
        cboest.Enabled = False
        cbover.Enabled = False
        Db1DataGridView.Visible = False
        Db1BindingNavigator.Visible = False
    End Sub

    Sub populate_Adapter_Dataset()
        Dim daa As SqlDataAdapter
        daa = New SqlClient.SqlDataAdapter("Select * from db1 where ESTNO = '" & cboest.Text & "' AND ESTVERSION = '" & cbover.Text & "' ", sqlcon)
        daa.Fill(ds, "db1")

        Db1DataGridView.DataSource = ds.Tables("db1")
        Me.Db1TableAdapter.Fill(Me.GreetingDataSet.db1)
    End Sub

    Public Sub populate_combo(ByRef cmb1 As ComboBox, ByVal cbest As String)
        dc = New SqlCommand("Select distinct " & cbest & " from db1", sqlcon)
        Dim da As SqlClient.SqlDataReader = dc.ExecuteReader
        cmb1.Items.Clear()
        While da.Read
            cmb1.Items.Add(da("" & cbest & ""))
        End While
        da.Close()
    End Sub

    Public Sub populate_combo1(ByRef cmb2 As ComboBox, ByVal cbver As String)
        dc = New SqlCommand("Select distinct estversion from db1 where estno = '" & cboest.Text & "' ", sqlcon)
        Dim da As SqlClient.SqlDataReader = dc.ExecuteReader
        cmb2.Items.Clear()
        While da.Read
            cmb2.Items.Add(da("" & cbver & ""))
        End While
        da.Close()
    End Sub

    Private Sub Db1BindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Db1BindingNavigatorSaveItem.Click
        Me.Validate()
        Me.Db1BindingSource.EndEdit()
        Me.Db1TableAdapter.Update(Me.GreetingDataSet.db1)

    End Sub

    Private Sub cmdselect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdselect.Click
        cboest.Enabled = True
        cmdselect.Enabled = False
    End Sub

    Private Sub cboest_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboest.SelectedIndexChanged
        cbover.Enabled = True
        Call populate_combo1(cbover, "estversion")
    End Sub

    Private Sub cbover_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbover.SelectedIndexChanged
        Call populate_Adapter_Dataset()
        cboest.Enabled = False
        cbover.Enabled = False
        cmdselect.Enabled = True
        Db1DataGridView.Visible = True
        Db1BindingNavigator.Visible = True
    End Sub
End Class