Solved

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

Posted on 2010-09-22
15
265 Views
Last Modified: 2012-05-10
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
Comment
Question by:angel7170
  • 7
  • 6
  • 2
15 Comments
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33738549
you can not uae "@" in access ..use "?" instead an add the parameters accordingly
0
 
LVL 21

Expert Comment

by:chapmanjw
ID: 33738585
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
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33738589
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
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33738758
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
 

Author Comment

by:angel7170
ID: 33742992
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
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33743991
you are using 3 "?" so you have to declare 3 parameters...one by one...
0
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33744029
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:angel7170
ID: 33744343
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
 
LVL 21

Accepted Solution

by:
chapmanjw earned 250 total points
ID: 33744502
Do you have your connection string hard coded to a specific location or a relative location?
0
 

Author Comment

by:angel7170
ID: 33744602
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
 

Author Comment

by:angel7170
ID: 33744627
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
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33744728
you to crate a new connection string...the My.Settings.AssetDBConnectionString must change....
what Access are u using?
0
 
LVL 18

Assisted Solution

by:John (Yiannis) Toutountzoglou
John (Yiannis) Toutountzoglou earned 250 total points
ID: 33744897
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
 

Author Comment

by:angel7170
ID: 33745127
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
 

Author Closing Comment

by:angel7170
ID: 33745143
Worked as excepted
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now