0

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.

3
Contributors
3
Replies
4
Views
7 Years
Discussion Span
Last Post by speedy gonzalos
0

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?

0

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

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.