none
Having problems with updating database RRS feed

  • Question

  • Hi, i've been developing a program that can be used to monitor shop inventory. I have a problem regarding the update feature. The code has no conflicting issues. It's just that after clicking the update button, the database itself didn't update. I've been trying to find a solution but i couldn't. I'll put the code down below.

    Imports System.Data.OleDb
    Public Class frmUpdate
        Dim con1 As New OleDbConnection("Provider = Microsoft.ACE.OLEDB.12.0;Data Source= MainSource.accdb")

        Private Sub btnUp_Click(sender As Object, e As EventArgs) Handles btnUp.Click
            If txtID.Text = "" Or txtItem.Text = "" Or ComboBox1.Text = "" Or txtPrice.Text = "" Or txtStock.Text = "" Or TextBox1.Text = "" Or TextBox2.Text = "" Then
                MessageBox.Show("Please complete the required fields..", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Else
                Dim sqlupdate As String
                sqlupdate = "UPDATE Inventory SET Item_Name=@Item_Name, Category=@Category, Stock_Quantity=@Stock_Quantity, Item_Price=@Item_Price, Supplier_ID=@Supplier_ID, Supplier_Name=@Supplier_Name WHERE Item_ID=@Item_ID"
                Dim cmd As New OleDbCommand(sqlupdate, con1)
                cmd.Parameters.Add(New OleDbParameter("@Item_ID", txtID.Text))
                cmd.Parameters.Add(New OleDbParameter("@Item_Name", txtItem.Text))
                cmd.Parameters.Add(New OleDbParameter("@Category", ComboBox1.Text))
                cmd.Parameters.Add(New OleDbParameter("@Stock_Quantity", txtStock.Text))
                cmd.Parameters.Add(New OleDbParameter("@Item_Price", txtPrice.Text))
                cmd.Parameters.Add(New OleDbParameter("@Supplier_ID", TextBox1.Text))
                cmd.Parameters.Add(New OleDbParameter("@Supplier_Name", TextBox2.Text))
                con1.Open()
                cmd.ExecuteNonQuery()
                con1.Close()
                ClearTextBox(Me)
                Me.Close()
                RefreshDGV()
            End If

        End Sub

    Hope you guys can help finding a solution for this because it's due for a presentation next week.

    Thanks in advance.  

    • Moved by Tina-Shi Wednesday, January 21, 2015 6:30 AM the issue is related to the ado.net
    Tuesday, January 20, 2015 3:06 PM

All replies

  • This is not the right place for the post.

    However, your code looks fine

    Check these two things:

    1- Check if you're checking the right DB for the data.

    2- Try to get the update query with the same values from the code and run manually against your DB to see what will happen.


    Fouad Roumieh


    Tuesday, January 20, 2015 3:21 PM
  • Thanks for the reply but can you explain in detail about no.2? I don't really understand how to do that.
    Tuesday, January 20, 2015 4:56 PM
  • How do you know that database didn't update?  Are you sure you are checking the correct Item_ID?  Try a Select SQL Query for same item in VS code and see if you get any results.

    jdweng

    Tuesday, January 20, 2015 5:39 PM
  • The window for the update section just closes as expected. I had set the window to automatically close after clicking the update button. But after the closing, the old data remains in the display. The new updated data didn't. The update failed. No error message was displayed. I'm gonna try the SELECT query now.
    Tuesday, January 20, 2015 5:42 PM
  • When you did the update was the database opened?  Make sure you close the database before running the VS code.

    jdweng

    Tuesday, January 20, 2015 6:55 PM
  • The database is closed. I'll provide you with the code from the main window.

    Private Sub frmMain_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Dim sql As String
            sql = "SELECT * FROM INVENTORY"
            Dim adapter As New OleDbDataAdapter(sql, con1)
            Dim dt As New DataTable("INVENTORY")
            adapter.Fill(dt)
            DataGridView1.DataSource = dt
            Dim sql1 As String
            sql1 = "SELECT * FROM INVENTORY"
            Dim adapter1 As New OleDbDataAdapter(sql1, con1)
            Dim cmd1 As New OleDbCommand(sql1, con1)
            Dim dt1 As New DataTable("INVENTORY")

            Dim sql2 As String
            sql2 = "SELECT * FROM Supplier"
            Dim adapter2 As New OleDbDataAdapter(sql2, con1)
            Dim dt2 As New DataTable("Supplier")
            adapter2.Fill(dt2)
            DataGridView9.DataSource = dt2
            Dim sql3 As String
            sql3 = "SELECT * FROM Supplier"
            Dim adapter3 As New OleDbDataAdapter(sql3, con1)
            Dim cmd3 As New OleDbCommand(sql3, con1)
            Dim dt3 As New DataTable("Supplier")

            con1.Open()
            Dim myreader As OleDbDataReader = cmd1.ExecuteReader
            myreader.Read()

            Dim myreader2 As OleDbDataReader = cmd3.ExecuteReader
            myreader2.Read()
            con1.Close()

            Dim sql4 As String
            sql4 = "SELECT * FROM Purchase"
            Dim adapter4 As New OleDbDataAdapter(sql4, con1)
            Dim dt4 As New DataTable("Purchase")
            adapter4.Fill(dt4)
            DataGridView2.DataSource = dt4
            Dim sql5 As String
            sql5 = "SELECT * FROM Purchase"
            Dim adapter5 As New OleDbDataAdapter(sql5, con1)
            Dim cmd5 As New OleDbCommand(sql5, con1)
            Dim dt5 As New DataTable("Purchase")

            Dim sql6 As String
            sql6 = "SELECT * FROM Restock"
            Dim adapter6 As New OleDbDataAdapter(sql6, con1)
            Dim dt6 As New DataTable("Restock")
            adapter6.Fill(dt6)
            DataGridView6.DataSource = dt6
            Dim sql7 As String
            sql7 = "SELECT * FROM Restock"
            Dim adapter7 As New OleDbDataAdapter(sql7, con1)
            Dim cmd7 As New OleDbCommand(sql7, con1)
            Dim dt7 As New DataTable("Restock")

            con1.Open()
            Dim myreader6 As OleDbDataReader = cmd7.ExecuteReader
            myreader6.Read()
            Dim myreader5 As OleDbDataReader = cmd5.ExecuteReader
            myreader5.Read()
            con1.Close()

            Dim sql14 As String
            sql14 = "SELECT * FROM Supplier"
            Dim adapter14 As New OleDbDataAdapter(sql14, con1)
            Dim dt14 As New DataTable("Supplier")
            adapter14.Fill(dt14)
            DataGridView9.DataSource = dt14
            Dim sql15 As String
            sql15 = "SELECT * FROM Supplier"
            Dim adapter15 As New OleDbDataAdapter(sql15, con1)
            Dim cmd15 As New OleDbCommand(sql15, con1)
            Dim dt15 As New DataTable("Supplier")

            Dim myreader8 As OleDbDataReader = cmd15.ExecuteReader
            myreader8.Read()
            con1.Close()

            Me.ReportViewer1.RefreshReport()
        End Sub

    Thanks for the help.

    Tuesday, January 20, 2015 6:59 PM
  • This is your Query:

    UPDATE Inventory SET Item_Name=@Item_Name, Category=@Category, Stock_Quantity=@Stock_Quantity, Item_Price=@Item_Price, Supplier_ID=@Supplier_ID, Supplier_Name=@Supplier_Name WHERE Item_ID=@Item_ID

    just replace the params (@Item_ID,@Item_Name,@Category...) with the same values you passing from the Form (you can get these values by setting a break point in your code) then go to your DB and run it to see if it will update the table successfully.

    Do you know how to run a query in Access?


    Fouad Roumieh

    Wednesday, January 21, 2015 6:14 AM