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:

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

Welcome speedy gonzalos,
Do not use @ with parameter operand if database is Ms-Access.

cmd.CommandText = "UPDATE LK_Categories SET CategoryName = mCategoryName WHERE PK_CategoryID =mCatID"

Or use ? (anonymous)

cmd.CommandText = "UPDATE LK_Categories SET CategoryName = ? WHERE PK_CategoryID =?"

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:

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

Thanks ever so much for your help that worked!

I also removed this bit of the code from the top:

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

The only problem now is that i can update only 1 record at a time. If i want to update 2 or more records at the same time, the changes will only show on the datagrid view but not update the database.

I will have to re-run the application each time i want to update a record. Can you tell me how i can make unlimited changes to the database withoout having to close and then re open the application?

Guys. I discovered that whoever designed vista was so paranoid about security that they imposed all sorts of restrictions which hinder progress, especially for a programmer like me.
After trying repeatedly, i was begining to think that either my vb 2008 was a fake or trial version, or it trully didn`t have some features - especially writing to an access data base.
However, after I logged on as an administrator and set my permissions to full control, my system has started to feel like mine once again.
I have absolute control over installing files to restricted locations as well as making registry entries indiscriminately.
Vista sucks indeed!!!

This article has been dead for over six months. Start a new discussion instead.