?
Solved

How to update from VB.net into MS Access DB

Posted on 2006-04-26
17
Medium Priority
?
404 Views
Last Modified: 2010-04-23

Hey guys how is everyone doing today?.

As for me I'm doing pretty good , but I think I'm messing up in an update SQL statement when I try to pass
certain values into Access.

Well here is the scene

I have 2 text boxes. 1 of the textbox the User inserts their User ID and the second one contains the user Name
Now I know I can't update the USER ID because it is a Primary key in Access...but what I do want to update is the Name from that
particular User ID.

So in other words I want to be able to put the user ID and then update their attributes.

Here is a small example of the code.

/////////////////////////

 Dim FirstName = txtFirstName.Text.Trim
        Dim UserID = txtUserID.Text.Trim


Dim connString As String = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = X:\members.mdb;"
        Dim myConnection As OleDbConnection = New OleDbConnection
        Dim updateCmd As OleDbCommand = New OleDbCommand

        myConnection.ConnectionString = connString
        myConnection.Open()


        With updateCmd

            .Connection = myConnection
            .CommandText = " Update [userTable] Set FirstName = @FirstName" & _   *////Im sure there is something wrong here?
                            " Where User_ID=@User_ID "

            .CommandType = CommandType.Text

            .Parameters.Add(New OleDbParameter("@User_ID", OleDbType.Char, 10))
            .Parameters.Add(New OleDbParameter("@FirstName", OleDbType.Char, 25))


            .Parameters("@User_ID").Value = UserID
            .Parameters("@FirstName").Value = FirstName



        End With


        Try
            updateCmd.ExecuteNonQuery()
        Catch OleDbExceptionErr As OleDbException
            Debug.WriteLine(OleDbExceptionErr.Message)
        Catch InvalidOperationExceptionErr As InvalidOperationException
            Debug.WriteLine(InvalidOperationExceptionErr.Message)



        End Try


        'Cleaning up the Mess
        updateCmd.Dispose()
        updateCmd = Nothing
     

        myConnection.Dispose()
        myConnection = Nothing
////////////////////////

I may have maybe a syntax error in the SQL statement or maybe just the way I'm approaching the objective of this code.

Thankyou in advance guys :)

0
Comment
Question by:masterat03
  • 6
  • 5
  • 4
  • +1
17 Comments
 
LVL 4

Expert Comment

by:g_johnson
ID: 16546909
what error are you getting and on what line?
0
 

Author Comment

by:masterat03
ID: 16546937
Well technically im not getting an error message, but my database does not update the value that I put in
0
 
LVL 4

Expert Comment

by:g_johnson
ID: 16546986
if an update statement fails but does not error, that means it is not finding a record to update.

you need to make sure the your @userid parameter value is correct and in the table
0
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.

 

Author Comment

by:masterat03
ID: 16547222


I double check everything I even check to see if I was pointing to the correct path of the DB file.
I know for sure that USer_ID is a field name and so is FirstName

That has to be something else...because if I'm able to retrieve and see the info and I'm also pointing
the update SQL to the same path that I do to retrieve the data

I even use breakpoints to see if perhaps the data is not being carried out , but it is.
As for USerID and Firstname both are strings and as well in the Access file they are both
Text format
0
 
LVL 6

Expert Comment

by:willbdman
ID: 16547259
You can check if your code is attempting update but failing by changing the following code:-

 Try
Dim intAffected As Integer = 0
            intAffected = updateCmd.ExecuteNonQuery()
Debug.WriteLine("Successfully Updated: " & intAffected.ToString())
        Catch OleDbExceptionErr As OleDbException
            Debug.WriteLine(OleDbExceptionErr.Message)
        Catch InvalidOperationExceptionErr As InvalidOperationException
            Debug.WriteLine(InvalidOperationExceptionErr.Message)
Catch exc As Exception    
Debug.WriteLine(exc.Message)


        End Try


the extra catch clause catches any exceptions not handled by your previous exceptions.

the integer saves how many rows have been updated by the query - if this is zero something is definately not right somewhere.

Post the table you are trying to update so that we can help isolate the problem.

0
 
LVL 34

Expert Comment

by:Sancler
ID: 16547328
Try reordering these lines

            .Parameters.Add(New OleDbParameter("@User_ID", OleDbType.Char, 10))
            .Parameters.Add(New OleDbParameter("@FirstName", OleDbType.Char, 25))


            .Parameters("@User_ID").Value = UserID
            .Parameters("@FirstName").Value = FirstName

like this

            .Parameters.Add(New OleDbParameter("@FirstName", OleDbType.Char, 25))
            .Parameters("@FirstName").Value = FirstName

            .Parameters.Add(New OleDbParameter("@User_ID", OleDbType.Char, 10))
            .Parameters("@User_ID").Value = UserID

Access tends to ignore parameter names and just use the parameters in the order in which it encounters them in the SQL statement it is reading.  I'm not certain this is so when a Where clause is involved, and I can't check right now, but it's worth a shot perhaps.

Roger
0
 

Author Comment

by:masterat03
ID: 16547342
I put a breakpoint and analyzed it.
I check the value its definetly 0

Sorry for sounding like a noob...you mean post as to describe the table for you...or what?!?!
0
 

Author Comment

by:masterat03
ID: 16547497
Hey Sancler You are right about the moving stuff...but it still has drawbacks.

For example the first time I moved it the way you said


////////////

like this

            .Parameters.Add(New OleDbParameter("@FirstName", OleDbType.Char, 25))
            .Parameters("@FirstName").Value = FirstName

            .Parameters.Add(New OleDbParameter("@User_ID", OleDbType.Char, 10))
            .Parameters("@User_ID").Value = UserID



/////////////////

I tested it and it work.
BUT When I decided to add in the next set of instruction which was another Field name with the original
Example

/////////

.Connection = myConnection
            .CommandText = " Update [UserTable] Set FirstName =@FirstName, Set LastName= @LastName " & _
                            " Where User_ID= @User_ID"

            .CommandType = CommandType.Text


            .Parameters.Add(New OleDbParameter("@FirstName", OleDbType.Char, 25))
            .Parameters("@FirstName").Value = FirstName

            .Parameters.Add(New OleDbParameter("@LastName", OleDbType.Char, 25))
            .Parameters("@LastName").Value = LastName

            .Parameters.Add(New OleDbParameter("@User_ID", OleDbType.Char, 10))
            .Parameters("@User_ID").Value = UserID

/////////////////

It doesnt work....if I put only just 2 of them...it works....

The reason I added another field was to test it out...I usually do that with big tables I just work with 2 fields and then see if it works..if it does then i start to add
the rest of the fields that may require updates.

But eventually again when i first used 2 of them it works and I moved it like you said...but If I do the same with 3 of them..it does update at all.



I left the USerID at the bottom because just like you said since it is a Where clause...I figure it was the Last part of the SQL statement so I left it at the end
0
 
LVL 34

Expert Comment

by:Sancler
ID: 16548793
Try substituting this

            .CommandText = " Update [UserTable] Set FirstName =@FirstName, LastName= @LastName " & _
                            " Where User_ID= @User_ID"

for this

            .CommandText = " Update [UserTable] Set FirstName =@FirstName, Set LastName= @LastName " & _
                            " Where User_ID= @User_ID"

That is, get rid of the second use of the word "Set"

Roger
0
 

Author Comment

by:masterat03
ID: 16550172

Well it works but it has big side effects.

It seems when you take away the second "Set"
For some crazy reason it puts the same value on the first textbox and second textbox.

Example if you put let say User ID and then it shows you their first and last name attributes.

If you do this


         .CommandText = " Update [UserTable] Set FirstName =@FirstName, LastName= @LastName " & _
                            " Where User_ID= @User_ID"

and let say you put the First Name "John" and the Last Name "Doe"
When you execute the Update Button Then what ever you put in the First Name will also copy over on the Last Name

Example : "John" "John" instead of "John" "Doe"




0
 
LVL 6

Expert Comment

by:willbdman
ID: 16550736
Yeah just the table layout, as it reads in access table design view.
0
 
LVL 34

Assisted Solution

by:Sancler
Sancler earned 400 total points
ID: 16550881
Microsoft Access 2002

UserTable definition

User_ID - Text - PrimaryKey
Title - Text
LastName - Text
FirstName - Text

Entries before

abc|Mr|Brown|Jack
def|Miss|White|Jane

Code

    Private Sub doStuff()
        Dim FirstName As String = "Jim"
        Dim LastName As String = "Green"
        Dim UserID As String = "abc"


        Dim connString As String = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = C:\Test\test.mdb;"
        Dim myConnection As OleDbConnection = New OleDbConnection
        Dim updateCmd As OleDbCommand = New OleDbCommand

        myConnection.ConnectionString = connString
        myConnection.Open()


        With updateCmd

            .Connection = myConnection
            .CommandText = " Update [UserTable] Set FirstName = @FirstName, LastName = @LastName Where User_ID=@User_ID "


            .CommandType = CommandType.Text


            .Parameters.Add(New OleDbParameter("@FirstName", OleDbType.Char, 25))
            .Parameters("@FirstName").Value = FirstName


            .Parameters.Add(New OleDbParameter("@LastName", OleDbType.Char, 25))
            .Parameters("@LastName").Value = LastName

            .Parameters.Add(New OleDbParameter("@User_ID", OleDbType.Char, 25))
            .Parameters("@User_ID").Value = UserID



        End With


        Try
            updateCmd.ExecuteNonQuery()
        Catch OleDbExceptionErr As OleDbException
            Debug.WriteLine(OleDbExceptionErr.Message)
        Catch InvalidOperationExceptionErr As InvalidOperationException
            Debug.WriteLine(InvalidOperationExceptionErr.Message)



        End Try


        'Cleaning up the Mess
        updateCmd.Dispose()
        updateCmd = Nothing


        myConnection.Dispose()
        myConnection = Nothing

    End Sub

Entries after

abc|Mr|Green|Jim
def|Miss|White|Jane

It works OK for me.  I've also tested it putting the Fields in the UserTable in different orders.

With

            .CommandText = " Update [UserTable] Set FirstName =@FirstName, Set LastName= @LastName Where User_ID= @User_ID"

I get a syntax error in update statement reported in the debug window by this line

            Debug.WriteLine(OleDbExceptionErr.Message)

With the orders of the firstname and lastname parameters reversed I get no error, but the firstname put in the lastname field and vice versa.

What more can I say?

Roger
0
 
LVL 6

Accepted Solution

by:
willbdman earned 400 total points
ID: 16551047
This worked for me roger:-


Private Sub DoStuff()

Dim connString As String = _
            "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=C:\Test\test.mdb;" & _
            "Password=;" & _
            "Jet OLEDB:Engine Type=5;" & _
            "Mode=Share Deny None;"

        Dim myConnection As New OleDb.OleDbConnection(connString)

        Dim updateCmd As New OleDb.OleDbCommand

        With updateCmd

            .Connection = myConnection
            .CommandText = "UPDATE [UserTable] " & _
                                        "SET LastName=@LastName, FirstName=@FirstName " & _
                                        "WHERE User_ID=@User_ID"

            .Parameters.Add("@LastName", Me.tbxLastName.Text)
            .Parameters.Add("@FirstName", Me.tbxFirstName.Text)
            .Parameters.Add("@User_ID", Me.tbxUserID.Text)


        End With

        Try
            myConnection.Open()
            updateCmd.ExecuteNonQuery()
            MsgBox("Success")

        Catch ex As Exception

            MsgBox("Fail" & Chr(10) & Chr(10) & ex.Message)

        Finally

            myConnection.Close()

        End Try

        updateCmd.Dispose()
        updateCmd = Nothing


        myConnection.Dispose()
        myConnection = Nothing

End Sub
0
 
LVL 34

Expert Comment

by:Sancler
ID: 16551345
I would expect it to.  It addresses both of the issues that have been giving problems.  (1) adding the parameters in the order they are referred to in the SQL statement (rather than relying on their names) and (2) only using "Set" once in the SQL statement.

Roger
0
 

Author Comment

by:masterat03
ID: 16552758
Ok guys back from my long sleep...well sort of.

Anyhow I tried it out with this example that wildbdman posted

With updateCmd

            .Connection = myConnection
            .CommandText = "UPDATE [UserTable] " & _
                                        "SET LastName=@LastName, FirstName=@FirstName " & _
                                        "WHERE User_ID=@User_ID"

            .Parameters.Add("@LastName", Me.tbxLastName.Text)
            .Parameters.Add("@FirstName", Me.tbxFirstName.Text)
            .Parameters.Add("@User_ID", Me.tbxUserID.Text)




and this one works for sure.
I tried to add more fields to it and it updates accordingly.

Now what I have to ask is why does it need the "Me" attach to the textbox.
I mean I know it works, but I need to know why to understand why so many hiccups along the way lol.

Also I like the way you put the myconnection.open () in a try-block statement.

And last but not least do you guys know of a good book to learn ADO from. I ve been having the most trouble with that topic even though
I know SQL, but it seems that each programming language adds its own little style to use either update,delete,insert.

If you guys do know of a good book based on VB.net and which also cover SQL using vb I would definetly appreciate that :)

And another thing is there a way I can split points between sancler and wilddbman
You guys where both helpful and if there is a way to split I would like to do so for your hard earn help you guys gave me :)

0
 
LVL 6

Expert Comment

by:willbdman
ID: 16552862
'Me' just denotes the form on which the textbox resides.
(it is the same as 'this' in C#.NET)

It does work without the Me - but it makes the code easier to understand in my opinion.

Always best to contain all connection stuff in a try statement, finishing with a finally which tidies up the databgase connection (myConnection.Close())
Can also be good to remove all the parameters in the finally statement as well.

I use the following books:-

VB.NET Unleashed - covers pretty much everything on VB.NET, including vast chapters on ADO.NET

http://www.amazon.com/gp/product/067232234X/104-6763678-9908728?v=glance&n=283155


ASP.NET Unleashed - Covers all ASP.NET, including vast chapters on ADO.NET for websites

http://www.amazon.com/gp/product/0672320681/104-6763678-9908728?v=glance&n=283155


To split points click on split just above the question box.
0
 
LVL 34

Expert Comment

by:Sancler
ID: 16553485
I'm not really into books, so this might not be saying much.  But I haven't come across a good one so far as ADO.NET and MS Access is concerned.  The problem is that MS Access is very quirky in terms of "normal" SQL and - although I've seen plenty of stuff saying (with examples) "how to do this in SQL" and "how to do this in OleDb" - I haven't come across anything which sets out to highlight the crucial differences.  They might be discernable by a close study and comparison of SQL and OleDb example code, and sometimes there is a comment about some specific difference.  What I might be tempted by, if I could find one, is a book that comprehensively said "although you do it this way with SQL, you do it this way with OleDb".  But I haven't found one.

Thanks for the points.

Roger
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

840 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