how to code the edit and delete button if i use this code in my add button.

[Johnny Joe]

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

[Santanu.Das]

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.

[Johnny Joe]

https://drive.google.com/file/d/0B5EObiMLJcuPX1QtSE80TkQ5ME0

i tried that before but getting errors can you look at my code in this project at my form inventory. i dont know how to code the edit and delete button of that form.thank you

[Santanu.Das]

please describe your prolems and post the pictures of exceptions created at runtime.

[Johnny Joe]

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

https://drive.google.com/file/d/0B5EObiMLJcuPX1QtSE80TkQ5ME0/edit

[Reverend Jim]

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

[Miurei]

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