Originally Posted by speedy gonzalos

I am trying to create an Inventory/Stock Control system as a windows application using vb.net with an Access database. This is my first time trying to use vb.net and im having difficulty making the application interactive. I am unable to update records to the database from the front end.

I am creating this application in Visual Studio 2008. The first problem that i would like to resolve is trying to update records to the database. My front-end consists of a datagrid at the top of the screen, which brings back all of the records from Qry_Categories. I then have text boxes below the datagrid that replicate the data in the grid above it. So which ever row is highlighted in the datagrid, that data is then automatically filled in the text boxes below it.

I then have an update button. To update a record the user will highlight the row in the datagrid, this information will then get filled in the text boxes. The user will make changes to any of the text boxes and then click on the 'Update' button. This is where I am having the problems. Please can someone provide me with the update button code that will work, as i have tried so many and none seem to work.

This is my code so far:

Help with Code Tags

VB.NET Syntax (Toggle Plain Text)

 
Public Class Form1
    Dim con As New OleDb.OleDbConnection
    Dim adp As OleDb.OleDbDataAdapter
    Dim cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand(adp)
    Dim ds As New DataSet
 
 
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 'TODO: This line of code loads data into the 'StockControlDataSet.LK_Categories' table. You can move, or remove it, as needed.
        Me.LK_CategoriesTableAdapter.Fill(Me.StockControlDataSet.LK_Categories)
 
        con.ConnectionString = ConfigurationManager.ConnectionStrings _
                             ("WRC_SMS_1.My.MySettings.StockControlConnectionString").ConnectionString.ToString()
        ' Fill the data grid viewer        
        con.Open()
        cmd = New OleDb.OleDbCommand("Select * from Qry_Categories", con)
        adp = New OleDb.OleDbDataAdapter(cmd)
        adp.Fill(ds, "Qry_Categories")
        Me.LK_CategoriesDataGridView.DataSource = ds
        Me.LK_CategoriesDataGridView.DataMember = "Qry_Categories"
    End Sub
 
    Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
        'open(connection)
        con.Open()
 
        cmd.Connection = con
        Try
            cmd.CommandText = "UPDATE LK_Categories SET CategoryName = @CategoryName WHERE PK_CategoryID = @CatID"
            '"UPDATE LK_Categories SET CategoryName = @CategoryName WHERE PK_CategoryID = 16"
            Dim param As New OleDb.OleDbParameter
            Dim param2 As New OleDb.OleDbParameter
 
 
            'add content txtProdName as parameter
            param.ParameterName = "@CategoryName"
            param.Value = txtbxCatName.Text()
            cmd.Parameters.Add(param)
 
            param2.ParameterName = "@CatID"
            param2.Value = txtCatID.Text
            cmd.Parameters.Add(param2)
 
            cmd.ExecuteNonQuery()
 
            MessageBox.Show("customer data successfully updated")
 
        Catch ex As Exception
 
            MessageBox.Show("cust data not updated because " _
           & ex.Message)
 
        Finally
            'close connection if it is open
            If (con.State = ConnectionState.Open) Then
                con.Close()
            End If
        End Try
        con.Close()
 
        End Sub

Public Class Form1 Dim con As New OleDb.OleDbConnection Dim adp As OleDb.OleDbDataAdapter Dim cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand(adp) Dim ds As New DataSet Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'TODO: This line of code loads data into the 'StockControlDataSet.LK_Categories' table. You can move, or remove it, as needed. Me.LK_CategoriesTableAdapter.Fill(Me.StockControlDataSet.LK_Categories) con.ConnectionString = ConfigurationManager.ConnectionStrings _ ("WRC_SMS_1.My.MySettings.StockControlConnectionString").ConnectionString.ToString() ' Fill the data grid viewer con.Open() cmd = New OleDb.OleDbCommand("Select * from Qry_Categories", con) adp = New OleDb.OleDbDataAdapter(cmd) adp.Fill(ds, "Qry_Categories") Me.LK_CategoriesDataGridView.DataSource = ds Me.LK_CategoriesDataGridView.DataMember = "Qry_Categories" End Sub Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click 'open(connection) con.Open() cmd.Connection = con Try cmd.CommandText = "UPDATE LK_Categories SET CategoryName = @CategoryName WHERE PK_CategoryID = @CatID" '"UPDATE LK_Categories SET CategoryName = @CategoryName WHERE PK_CategoryID = 16" Dim param As New OleDb.OleDbParameter Dim param2 As New OleDb.OleDbParameter 'add content txtProdName as parameter param.ParameterName = "@CategoryName" param.Value = txtbxCatName.Text() cmd.Parameters.Add(param) param2.ParameterName = "@CatID" param2.Value = txtCatID.Text cmd.Parameters.Add(param2) cmd.ExecuteNonQuery() MessageBox.Show("customer data successfully updated") Catch ex As Exception MessageBox.Show("cust data not updated because " _ & ex.Message) Finally 'close connection if it is open If (con.State = ConnectionState.Open) Then con.Close() End If End Try con.Close() End Sub

The Qry_Categories has the following field names:
PK_CategoryID
CategoryName
Description

I would really appreciate the help as it is urgent i get this application up and running. Thanks in advance