[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 274
  • Last Modified:

Data not being updated in Access Database while using the VB.net Code

Hello,

I'm trying to update records in the access database using the code as below. It's updating the data in the dataset and not in the database.

Not sure if it has to do anything with connection. please assist. Thank you
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        
        Dim conn As New System.Data.OleDb.OleDbConnection

        conn.ConnectionString = (My.Settings.AssetDBConnectionString)
                conn.Open()
        Dim cmd2 As New OleDb.OleDbCommand("update TBL_ASSETS set ITEM=@a1,CATEGORY=@a2 where (ITEM=@a1) ", conn)

        cmd2.Parameters.AddWithValue("@a1", Me.txt_Item_S.Text)
        cmd2.Parameters.AddWithValue("@a2", Me.cmb_Category_S.Text)
        
        cmd2.ExecuteNonQuery()

        conn.Close()
        MsgBox("Record Updated Successfully")

        Me.TBL_ASSETSDataGridView.Refresh()

    End Sub

Open in new window

0
angel7170
Asked:
angel7170
  • 7
  • 6
  • 2
2 Solutions
 
John (Yiannis) ToutountzoglouCommented:
you can not uae "@" in access ..use "?" instead an add the parameters accordingly
0
 
chapmanjwCommented:
You could simply not parameterize it (not the best route, but then again neither is Access for web applications):

Dim cmd2 As New OleDb.OleDbCommand("update TBL_ASSETS set ITEM=" + Me.txt_Item_S.Text + ",CATEGORY=" + Me.cmb_Category_S.Text + " where (ITEM=" + Me.txt_Item_S.Text + ") ", conn)

And remove the two cmd2.Parameter2 lines.
0
 
John (Yiannis) ToutountzoglouCommented:
Dim cmd2 As New OleDb.OleDbCommand("update TBL_ASSETS set ITEM=?,CATEGORY=? where (ITEM=?) ", conn)
cmd2.Parameters.AddWithValue("?", Me.txt_Item_S.Text)
cmd2.Parameters.AddWithValue("?", Me.cmb_Category_S.Text)
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
John (Yiannis) ToutountzoglouCommented:
Or as @chapmanjw suggest
Dim OLEStr as String

OLEStr ="UPDATE TBL_ASSETS SET" 
OLEStr += ITEM= '" & Me.txt_Item_S.Text & "',"
OLEStr += " CATEGORY= '" & Me.cmb_Category_S.Text & "'"
OLEStr += " where ITEM= '" & Me.txt_Item_S.Text & "'"

cmd2 = New OleDb.OleDbCommand(OLEStr, conn)

Open in new window

0
 
angel7170Author Commented:
Thanks

But I got the error message below  pointing to code 'cmd2. ExecuteNonQuery() when I tried all the three suggestions given.

OleDBException was unhandled
No value given for one or more required parameters.

Please assist. Thank you
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        
        Dim conn As New System.Data.OleDb.OleDbConnection

        conn.ConnectionString = (My.Settings.AssetDBConnectionString)
                conn.Open()
        'Dim cmd2 As New OleDb.OleDbCommand("update TBL_ASSETS set ITEM=" + Me.txt_Item_S.Text + ",CATEGORY=" + Me.cmb_Category_S.Text + " where (ITEM=" + Me.txt_Item_S.Text + ") ", conn)
        Dim cmd2 As New OleDb.OleDbCommand("update TBL_ASSETS set ITEM=?,CATEGORY=? where (ITEM=?) ", conn)
        cmd2.Parameters.AddWithValue("?", Me.txt_Item_S.Text)
        cmd2.Parameters.AddWithValue("?", Me.cmb_Category_S.Text)

        cmd2.ExecuteNonQuery()

        conn.Close()
        MsgBox("Record Updated Successfully")

        Me.TBL_ASSETSDataGridView.Refresh()

    End Sub

Open in new window

0
 
John (Yiannis) ToutountzoglouCommented:
you are using 3 "?" so you have to declare 3 parameters...one by one...
0
 
John (Yiannis) ToutountzoglouCommented:
dim Param1 as New OledbParameter("?", Me.txt_Item_S.Text)
dim Param2 as New OledbParameter("?", Me.cmb_Category_S.Text)
dim Param3 as New OledbParameter("?", Me.txt_Item_S.Text)

cmd2.Parameters.Add(Param1)
cmd2.Parameters.Add(Param2)
cmd2.Parameters.Add(Param3)
0
 
angel7170Author Commented:
ok, now the data is being updated in the database located in the
F:\My Documents\Visual Studio 2008\Projects\Asset Management\Asset Management\Asset Management

but not in location
F:\My Documents\Visual Studio 2008\Projects\Asset Management\Asset Management\Asset Management\bin\Debug

I think there's something to do with the connection string or the database in the debug folder is not getting updated whenever there is a change in the record.
But When I add a record to the table I could see the added record immediately in my datagridview but when I update it's not showing the update in the datagridview.

not sure what could cause this?

Please help.
0
 
chapmanjwCommented:
Do you have your connection string hard coded to a specific location or a relative location?
0
 
angel7170Author Commented:
I'm just using
 this code in the update button
My.Settings.AssetDBConnectionString

I checked my app.config file and the below location is what shown
F:\My Documents\Visual Studio 2008\Projects\Asset Management\Asset Management\Asset Management

But When I use "ADD" button I don't use any hard coded to a specific location..
0
 
angel7170Author Commented:
I'm running this app in "Debug" mode, also I have my dataset properties "Copy to Output Directory" as "Copy always".

But seems like it's not working..
0
 
John (Yiannis) ToutountzoglouCommented:
you to crate a new connection string...the My.Settings.AssetDBConnectionString must change....
what Access are u using?
0
 
John (Yiannis) ToutountzoglouCommented:
use connectionstring with standard security
conn.ConnectionString = (Provider=Microsoft.ACE.OLEDB.12.0;Data Source=F:\My Documents\Visual Studio 2008\Projects\Asset Management\Asset Management\Asset Management.accdb;Persist Security Info=False;)


If you are using Access 2007 otherwise check http://www.connectionstrings.com/access to get your connection string
0
 
angel7170Author Commented:
Finally, it worked...Thank you very much chapmanjw and jtoutou



Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim conn As New System.Data.OleDb.OleDbConnection

        conn.ConnectionString = ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\AssetDB.accdb;Persist Security Info = False;")
        conn.Open()
          Dim cmd2 As New OleDb.OleDbCommand("update TBL_ASSETS set ITEM=?,CATEGORY=? where (ITEM=?) ", conn)
        'cmd2.Parameters.AddWithValue("?", Me.txt_Item_S.Text)
        'cmd2.Parameters.AddWithValue("?", Me.cmb_Category_S.Text)

        Dim Param1 As New OleDb.OleDbParameter("?", Me.txt_Item_S.Text)
        Dim Param2 As New OleDb.OleDbParameter("?", Me.cmb_Category_S.Text)
        Dim Param3 As New OleDb.OleDbParameter("?", Me.txt_Item_S.Text)

        cmd2.Parameters.Add(Param1)
        cmd2.Parameters.Add(Param2)
        cmd2.Parameters.Add(Param3)

        cmd2.ExecuteNonQuery()

        conn.Close()
        MsgBox("Record Updated Successfully")
        TBL_ASSETSTableAdapter.Fill(AssetDBDataSet.TBL_ASSETS)
        Me.TBL_ASSETSDataGridView.Invalidate()
        Me.TBL_ASSETSDataGridView.Refresh()

    End Sub
0
 
angel7170Author Commented:
Worked as excepted
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 7
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now