I am working on vb.net Windows Application with an Access database. The program that i am trying to develop is for stock/inventory control.
The update works when i update the first time, but then when I want to update another record it just wont update. I have to stop running the application and then re run and the update will work for the first time only again.
How i can update as many times as i want without having to re-run the application?
I have a datagrid that displays the data from the database tables, i then have some text boxes which i use to input the changes that i want. I use an Update button that updates the database. The code for the application, including the update button is as follows:

Imports System.Data
Imports System.Configuration

Public Class Form2
    Dim con As New OleDb.OleDbConnection
    Dim adp As OleDb.OleDbDataAdapter
    Dim cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand()
    Dim ds As New DataSet

    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'StockControlDataSet.Qry_Categories' table. You can move, or remove it, as needed.
        Me.Qry_CategoriesTableAdapter.Fill(Me.StockControlDataSet.Qry_Categories)

        con.ConnectionString = ConfigurationManager.ConnectionStrings _
                          ("WRC_SMS1.My.MySettings.WRCConStr").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.Qry_CategoriesDataGridView.DataSource = ds
        'Me.Qry_CategoriesDataGridView.DataMember = "Qry_Categories"
        'con.Close()

    End Sub


    Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
        'open(connection)

        'con.Open()

        If (con.State = ConnectionState.Closed) Then
            con.Open()
        End If

        cmd.Connection = con
        Try
            cmd.CommandText = "UPDATE LK_Categories SET CategoryName = mCategoryName, Description=mDescription WHERE PK_CategoryID = mCatID"
            '"UPDATE LK_Categories SET CategoryName = @CategoryName WHERE PK_CategoryID = 16"
            Dim param As New OleDb.OleDbParameter
            Dim param2 As New OleDb.OleDbParameter
            Dim param3 As New OleDb.OleDbParameter


            'add content txtProdName as parameter
            param.ParameterName = "mCategoryName"
            param.Value = CategoryNameTextBox.Text()
            cmd.Parameters.Add(param)

            param3.ParameterName = "mDescription"
            param3.Value = DescriptionTextBox.Text()
            cmd.Parameters.Add(param3)

            param2.ParameterName = "mCatID"
            param2.Value = PK_CategoryIDTextBox.Text
            cmd.Parameters.Add(param2)

            Try
                cmd.ExecuteNonQuery()


            Catch ex As Exception

                MessageBox.Show(ex.Message)
            End Try

        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
        
            MessageBox.Show("customer data successfully updated")
            'CategoryNameTextBox.Text = ""
            'DescriptionTextBox.Text = ""
        End Try
        'con.Close()

    End Sub

    Private Sub btnInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInsert.Click
        If (con.State = ConnectionState.Closed) Then
            con.Open()
        End If

        cmd.Connection = con
        Try
            cmd.CommandText = "INSERT INTO LK_Categories (CategoryName, Description) VALUES(mCategoryName,mDescription)"
            Dim param As New OleDb.OleDbParameter
            Dim param2 As New OleDb.OleDbParameter

            'add content txtProdName as parameter
            param.ParameterName = "mCategoryName"
            param.Value = CategoryNameTextBox.Text()
            cmd.Parameters.Add(param)

            param2.ParameterName = "mDescription"
            param2.Value = DescriptionTextBox.Text()
            cmd.Parameters.Add(param2)

            Try
                cmd.ExecuteNonQuery()


            Catch ex As Exception

                MessageBox.Show(ex.Message)
            End Try

        Catch ex As Exception

            MessageBox.Show("cust data not inserted because " _
           & ex.Message)

        Finally

            'con.Close()
            MessageBox.Show("customer data successfully inserted")

        End Try

    End Sub

End Class

Any help to resolve this problem would be most appreciated as this is the first time that i am coding in vb.net! Thanks in advance.

Recommended Answers

All 3 Replies

May be you are missing some values on second time.

I'm with adatapost on this .. everything looks OK so you might be missing a value. Does the code throw an error or does it simply not update the data as expected?

The code does not throw an error, it just doesnt update the database. What do you mean by 'it must be missing a value', please could you elaborate maybe with an example. Thanks

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.