Database

Dim bm As BindingManagerBase<br />
    Dim currentrow As Integer<br />
    Dim ds As New DataSet()<br />
    Dim bval As Boolean<br />
    Dim dt As DataTable<br />
    Dim dr As DataRow<br />
    Dim cn As SqlConnection = New SqlClient.SqlConnection("Data Source = ; Database = Pubs;uid=sa")<br />
    Dim da As SqlDataAdapter = New SqlClient.SqlDataAdapter("Select * from publishers", cn)<br />
    Dim flag As Integer<br />
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load<br />
<br />
        da.Fill(ds, "publishers")<br />
        bm = Me.BindingContext(ds.Tables("Publishers"))<br />
        bm.Position = 0<br />
        Loaddataincontrols()<br />
        Setbuttons(True)<br />
        LockTbox()<br />
    End Sub<br />
<br />
Public Sub Loaddataincontrols()<br />
        TextBox1.Text = ds.Tables("Publishers").Rows(bm.Position)("Pub_id").ToString<br />
        TextBox2.Text = ds.Tables("Publishers").Rows(bm.Position)("Pub_name").ToString<br />
        TextBox3.Text = ds.Tables("Publishers").Rows(bm.Position)("city").ToString<br />
        TextBox4.Text = ds.Tables("publishers").Rows(bm.Position)("State").ToString<br />
        TextBox5.Text = ds.Tables("Publishers").Rows(bm.Position)("Country").ToString<br />
    End Sub<br />
<br />
Private Sub CmdAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CmdAdd.Click<br />
        'dr = (ds.Tables("Publishers").NewRow)<br />
        Setbuttons(False)<br />
        UnLockTbox()<br />
        ClearControls()<br />
        flag = 1<br />
        'LoaddatainDataset()<br />
        'ds.Tables("Publishers").Rows.Add(dr)<br />
    End Sub<br />
    Public Sub UnLockTbox()<br />
        TextBox1.ReadOnly = False<br />
        TextBox2.ReadOnly = False<br />
        TextBox3.ReadOnly = False<br />
        TextBox4.ReadOnly = False<br />
        TextBox5.ReadOnly = False<br />
    End Sub<br />
    Public Sub ClearControls()<br />
        TextBox1.Text = ""<br />
        TextBox2.Text = ""<br />
        TextBox3.Text = ""<br />
        TextBox4.Text = ""<br />
        TextBox5.Text = ""<br />
    End Sub<br />
    Public Sub LoaddatainDataset()<br />
        dr("Pub_id") = TextBox1.Text<br />
        dr("Pub_name") = TextBox2.Text<br />
        dr("City") = TextBox3.Text<br />
        dr("State") = TextBox4.Text<br />
        dr("Country") = TextBox5.Text<br />
    End Sub<br />
<br />
    Private Sub CmdModify_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CmdModify.Click<br />
        UnLockTbox()<br />
        flag = 3<br />
        Setbuttons(False)<br />
        '   dr = ds.Tables("Publishers").Rows(bm.Position)<br />
        '  dr.BeginEdit()<br />
        ' LoaddatainDataset()<br />
        ' dr.EndEdit()<br />
        'Setbuttons(False)<br />
        'da.Update(ds, "Publishers")<br />
        'da.Fill(ds)<br />
        'bm.Position = 0<br />
    End Sub<br />
<br />
    Private Sub cmdRemove_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdRemove.Click<br />
        '     dr = ds.Tables("Publishers").Rows(bm.Position)<br />
        '    ds.Tables("Publishers").Rows.Remove(dr)<br />
        dt = ds.Tables("Publishers")<br />
        dr = dt.Rows.Find(TextBox1.Text)<br />
        flag = 2<br />
    End Sub<br />
<br />
    Private Sub cmdUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdUpdate.Click<br />
        Dim da As SqlDataAdapter = New SqlClient.SqlDataAdapter("Select * from publishers", cn)<br />
        If flag = 1 Then<br />
            dt = ds.Tables("Publishers")<br />
            dr = dt.NewRow()<br />
            LoaddatainDataset()<br />
            dt.Rows.Add(dr)<br />
        ElseIf flag = 2 Then<br />
            dr.Delete()<br />
        ElseIf flag = 3 Then<br />
            dt = ds.Tables("Publishers")<br />
            dr = dt.Rows.Find(TextBox1.Text)<br />
            dr.BeginEdit()<br />
            LoaddatainDataset()<br />
            dr.EndEdit()<br />
        End If<br />
        flag = 0<br />
        CreateCommandAndUpdate("Data Source = ; Database = Pubs;uid=sa", "Select * from Publishers")<br />
        da.Fill(ds)<br />
<br />
<br />
        '        LoaddatainDataset()<br />
        '       da.Update(ds, "Publishers")<br />
        '      da.Fill(ds)<br />
        bm.Position = 0<br />
        Loaddataincontrols()<br />
        Setbuttons(True)<br />
    End Sub<br />
<br />
    Public Function CreateCommandAndUpdate( _<br />
    ByVal connectionString As String, _<br />
    ByVal queryString As String) As DataSet<br />
        da.SelectCommand = New SqlCommand(queryString, cn)<br />
<br />
        Dim commandBuilder As SqlCommandBuilder = _<br />
            New SqlCommandBuilder(da)<br />
<br />
<br />
<br />
        ' Code to modify the data in the DataSet here. <br />
<br />
        ' Without the OleDbCommandBuilder this line would fail.<br />
        da.Update(ds)<br />
        da.Fill(ds)<br />
        'End Using<br />
        CreateCommandAndUpdate = ds<br />
    End Function