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

     Private Sub RefreshData()
    If Not conn.state = ConnectionState.Open Then
    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
    Me.dgvInventory.DataSource = dt
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;"

    If Not conn.State = ConnectionState.Open Then
    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))

    MsgBox("Data Successfully Added!", MsgBoxStyle.Information, "Success")

Edited by Johnny Joe

1 Year
Discussion Span
Last Post by Miurei

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))


Hope it can help you.


here is the link of the system that im trying to develop.can you check the form inventory and help me to code the edit button.im just a newbie in vb 2008.

the scenario is that when theres a selected row in the datagrid and click the edit button the data in selectedrow will display in textbox and would allow mo to edit it



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 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

        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
        End If

    End Sub
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.