[RESOLVED] CRUD OleDb and search in vb.net

[roy88]

Dear All Master,

I want to create CRUD to database dbf and also use search textbox based on ITM & ITC column which directly opens database datagridview.
how to give a title in the row header of the datagridview attached a screenshot that I marked in yellow.
I have created a buttonsave sql, it worked but the problem is that the buttonupdate sql doesn't work because my dbf database doesn't have a primary key with auto id. so it causes the error "Input string was not in a correct format.". I have also made buttonsearch, only I want to search based on the contents in the ITM column & ITC column
note : I use vs 2010

Public Class Form1
   
    Private Sub dgvUserDetails_RowPostPaint(ByVal sender As Object, ByVal e As DataGridViewRowPostPaintEventArgs)
        Dim b As SolidBrush = New SolidBrush(DataGridView1.RowHeadersDefaultCellStyle.ForeColor)
        e.Graphics.DrawString((e.RowIndex + 1).ToString, e.InheritedRowStyle.Font, b, (e.RowBounds.Location.X + 10), (e.RowBounds.Location.Y + 4))
    End Sub
Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        dbConnection()
        sql = "INSERT INTO ITEM (ITM,ITC,QOH) VALUES ('" & txtFname.Text & "','" & txtLname.Text & "','" & cbGender.Text & "')"
        cmd = New OleDbCommand(sql, con)
        cmd.ExecuteNonQuery()
        MessageBox.Show("Successfully Added to your Database", "New Record Added", MessageBoxButtons.OK, MessageBoxIcon.Information)
        con.Close()

        'btnReadDatabase_Click(sender, e);
        PopulateDataGridView()
    End Sub

 Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
        dbConnection()
        Dim indexID As Integer
        x = DataGridView1.Rows.IndexOf(DataGridView1.CurrentRow)
        indexID = Convert.ToInt32((DataGridView1.Rows(x).Cells(0).Value.ToString()))

        sql = "UPDATE ITEM SET ITM= '" & txtFname.Text & "',ITC='" & txtLname.Text & "',QOH='" & cbGender.Text & "' WHERE ID=" & indexID & ""

        cmd = New OleDbCommand(sql, con)
        cmd.ExecuteNonQuery()
        MessageBox.Show("Successfully Updated...", "Update")
        con.Close()

        'btnReadDatabase_Click(sender, e);
        PopulateDataGridView()
    End Sub
Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
        dbConnection()
        sql = "SELECT * FROM ITEM WHERE ITM='" & txtSearch.Text & "' OR ITC='" & txtSearch.Text & "'"
        da = New OleDbDataAdapter(sql, con)
        Dim dt As New DataTable() 'ds = new DataSet();
        da.Fill(dt) 'da.Fill(ds, "dbInformation");
        DataGridView1.DataSource = dt 'dataGridView1.DataSource = ds.Tables[0];
        con.Close()
    End Sub
End Class

thanks
roy88

[roy88]

Public Class Form1
   
    Private Sub dgvUserDetails_RowPostPaint(ByVal sender As Object, ByVal e As DataGridViewRowPostPaintEventArgs)
        Dim b As SolidBrush = New SolidBrush(DataGridView1.RowHeadersDefaultCellStyle.ForeColor)
        e.Graphics.DrawString((e.RowIndex + 1).ToString, e.InheritedRowStyle.Font, b, (e.RowBounds.Location.X + 10), (e.RowBounds.Location.Y + 4))
    End Sub
Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        dbConnection()
        sql = "INSERT INTO ITEM (ITM,ITC,QOH) VALUES ('" & txtFname.Text & "','" & txtLname.Text & "','" & cbGender.Text & "')"
        cmd = New OleDbCommand(sql, con)
        cmd.ExecuteNonQuery()
        MessageBox.Show("Successfully Added to your Database", "New Record Added", MessageBoxButtons.OK, MessageBoxIcon.Information)
        con.Close()

        'btnReadDatabase_Click(sender, e);
        PopulateDataGridView()
        clearData()
    End Sub
 Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
        dbConnection()
        sql = "UPDATE ITEM SET ITM= '" & txtFname.Text & "',ITC='" & txtLname.Text & "',QOH='" & cbGender.Text & "' WHERE ITC='" & txtLname.Text & "'"
        cmd = New OleDbCommand(sql, con)
        cmd.ExecuteNonQuery()
        MessageBox.Show("Successfully Updated...", "Update")
        con.Close()

        'btnReadDatabase_Click(sender, e);
        PopulateDataGridView()
        clearData()
    End Sub
Private Sub searchTextBox_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles searchTextBox.TextChanged
        TryCast(DataGridView1.DataSource, DataTable).DefaultView.RowFilter = String.Format("ITM LIKE '%{0}%'", searchTextBox.Text)

    End Sub