i  used pcode as my primary key which has a datatype of short text



     Private Sub RefreshData()
    If Not conn.state = ConnectionState.Open Then
        conn.Open()
    End If
    Dim DATA1 As New OleDb.OleDbDataAdapter("SELECT pcode as [Item Code], psup as [Supplier], pcat as [Category], pdescript as [Description], pstock as [In Stock], piprice as [Price], ptprice as [Selling Price], pdate as [Date Added] FROM inventory", conn)

    Dim dt As New DataTable
    DATA1.Fill(dt)
    Me.dgvInventory.DataSource = dt
    conn.Close()
End Sub
    Dim conn As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & My.Application.Info.DirectoryPath.ToString() & "\invent.accdb;Persist Security Info=False;")

    conn = New OleDb.OleDbConnection
    conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & My.Application.Info.DirectoryPath.ToString() & "\invent.accdb;Persist Security Info=False;"
    Me.RefreshData()

    If Not conn.State = ConnectionState.Open Then
        conn.Open()
    End If

    Dim sql As String
    sql = "INSERT into inventory (pcode, psup, pcat, pdescript, pstock, piprice, ptprice, pdate) values (@icode, @isup, @icat, @idesc, @istock, @iprice, @iptprice, @idate) "

    Dim cmd As New OleDb.OleDbCommand(sql, conn)
    cmd.Parameters.Add(New OleDb.OleDbParameter("@icode", txtPcode.Text))
    cmd.Parameters.Add(New OleDb.OleDbParameter("@isup", txtPsup.Text))
    cmd.Parameters.Add(New OleDb.OleDbParameter("@icat", txtPcat.Text))
    cmd.Parameters.Add(New OleDb.OleDbParameter("@idesc", txtPdesc.Text))
    cmd.Parameters.Add(New OleDb.OleDbParameter("@istock", txtPstock.Text))
    cmd.Parameters.Add(New OleDb.OleDbParameter("@iprice", txtPprice.Text))
    cmd.Parameters.Add(New OleDb.OleDbParameter("@iptprice", txtPtprice.Text))
    cmd.Parameters.Add(New OleDb.OleDbParameter("@idate", txtPdate.Text))

    cmd.ExecuteNonQuery()
    conn.Close()
    RefreshData()
    MsgBox("Data Successfully Added!", MsgBoxStyle.Information, "Success")

Edited 1 Year Ago by Johnny Joe

Do not understand the need of the codes from line 5 to 15. I can asume that they are not for add button.

You already declare the connection object in line 16. So no need of the lines 18 & 19. You can remove them. Next lines are right to add record into the datatable.

for editing codes are as same as codes for addition of a record except SQL Statement
it should be

sql = "UPDATE inventory SET pcode = @icode, psup = @isup, pcat = @icat, pdescript = @idesc, pstock = @istock, piprice = @iprice, ptprice = @iptprice, pdate = @idate"

Next lines for editing are same.

For deleting sql statement should be

sql = "DELETE FROM inventory WHERE pcode = @icode"
Dim cmd As New OleDb.OleDbCommand(sql, conn)
cmd.Parameters.Add(New OleDb.OleDbParameter("@icode", txtPcode.Text))

cmd.ExecuteNonQuery()
conn.Close()

Hope it can help you.

OleDbCommand does not support named parameters. You should use

sql = "INSERT into inventory (pcode, psup, pcat, pdescript, pstock, piprice, ptprice, pdate) values (?,?,?,?,?,?,?,?)"

Just make sure you add the values in the same order as you specified the field names. See MSDN article. If your database is MS SQL then you can use SqlClient with named parameters instead of OleDb

Edited 1 Year Ago by Reverend Jim

hope this helps, i used to put this on my edit button BUT mine is listview and your is datagrid.....

Private Sub edit_Click(sender As Object, e As EventArgs) Handles edit.Click

        editForm.Show()
        If Not Listview1.SelectedItems.Count = 0 Then '// check if item is selected.
            With Listview1.SelectedItems.Item(0)
                editForm.x.Text = .Text '// column 1.
                editForm.TextBox1.Text = .SubItems(1).Text '// column 2.
                editForm.TextBox2.Text = .SubItems(2).Text '// column 3.
                editForm.TextBox3.Text = .SubItems(3).Text '// column 4.
                '// etc...
            End With
            'Form2.ShowDialog()
        End If

        Me.Hide()
    End Sub
This article has been dead for over six months. Start a new discussion instead.