473,325 Members | 2,712 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

VB.NET 2003 - Updating a MS Database (Help!)

I'm fairly new to vb.net so any input is appreciated. I'm getting errors when I try to update the database using the following code:

Expand|Select|Wrap|Line Numbers
  1. sql = "SELECT * FROM Employees WHERE Employee = '" & lblUser.Text & "'"
  2.         Global.SetupDs(ds1)
  3.  
  4.         If txtOldPW.Text <> ds1.Tables(0).Rows(0).Item("Password") Then
  5.             MsgBox("Old password does not match employee records.")
  6.             txtOldPW.Text = ""
  7.             txtNewPW.Text = ""
  8.             txtConfirmNewPW.Text = ""
  9.             Exit Sub
  10.         End If
  11.  
  12.         ds1.Tables(0).Rows(0).Item("Password") = txtNewPW.Text
  13.         da.Update(ds1)
  14.         ds1.Clear()
  15.         MsgBox("Password Updated.")
  16.  
SetupDs is a sub in my global module that uses the SQL string I provide and a connection I've setup to retrieve the data I want to put in my dataset. I've also publicly declared a commandbuilder in the global module:

Expand|Select|Wrap|Line Numbers
  1.     Public da As OleDb.OleDbDataAdapter
  2.     Public con As New OleDb.OleDbConnection
  3.     Public sql As String
  4.     Public cb As New OleDb.OleDbCommandBuilder(da)
  5.  
I'm getting the following when I try to update:

"An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll

Additional information: Update requires a valid UpdateCommand when passed DataRow collection with modified rows."

Any help would be great... I haven't gotten a resposne from anywhere else I've tried :-/
Jun 24 '08 #1
8 2445
Curtis Rutland
3,256 Expert 2GB
After you instantiate your data adapter [after you say da = new OleDbDataAdapter(selectCommand,connection)] you should include these lines.
Expand|Select|Wrap|Line Numbers
  1. da.UpdateCommand = cb.GetUpdateCommand()
  2. da.InsertCommand = cb.GetInsertCommand()
  3. da.DeleteCommand = cb.GetDeleteCommand()
  4.  
You made the command builder, but didn't use it to build the commands.
Jun 24 '08 #2
Thanks for the reply. I inserted the lines you mentioned, but now I'm getting errors about initializing the select command:

"An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll

Additional information: The DataAdapter.SelectCommand property needs to be initialized."

I was hoping it'd be as easy as the other 3, (i.e. "da.selectcommand = cb.getselectcommand()") but there was no getselectcommand(), under cb. I'm guessing this is because select has to do with retrieving data instead of updating it?
Jun 24 '08 #3
Curtis Rutland
3,256 Expert 2GB
Thanks for the reply. I inserted the lines you mentioned, but now I'm getting errors about initializing the select command:

"An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll

Additional information: The DataAdapter.SelectCommand property needs to be initialized."

I was hoping it'd be as easy as the other 3, (i.e. "da.selectcommand = cb.getselectcommand()") but there was no getselectcommand(), under cb. I'm guessing this is because select has to do with retrieving data instead of updating it?
See, you already wrote the select command. It's in the variable "sql"

You need to understand the process here. You are getting things out of order. Here's what I usually do: (check connectionstrings.com for your connection string; make sure to add the "Imports" statement at the top of your code page.)
Expand|Select|Wrap|Line Numbers
  1. Imports System.Data.OleDb
  2. .
  3. .
  4. .
  5. 'this connstr for MS SQL 2005, find correct one
  6. connStr = "Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;"
  7. Dim con As New OleDbConnection(connStr)
  8. Dim sql As String
  9. sql = "SELECT * FROM Employees WHERE Employee = '" & lblUser.Text & "'"
  10. Dim da As New OleDbDataAdapter(sql, con)
  11. Dim cb As New OleDbCommandBuilder(da)
  12.  
If you do things in this order, you should be able to do what you need to do.
Jun 24 '08 #4
Thanks for your continued help and patience, as I mentioned I'm new to .net so some of the basic concepts are a bit puzzling for me. I changed my code so that the connection, dataAdapter, and command builder come in the order you mentioned, but I'm still getting errors on the "daEmployee.update(dsEmployee)" line.

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
  2.         Dim MainForm As New Main
  3.         Dim con As New OleDb.OleDbConnection
  4.         con.ConnectionString = "PROVIDER = Microsoft.Jet.Oledb.4.0;Data Source = C:\Documents and Settings\CZ\My Documents\Databases\QSD_be.mdb"
  5.         Dim sqlEmployee As String
  6.         sqlEmployee = "SELECT * FROM Employees WHERE Employee = '" & lblUser.Text & "'"
  7.         Dim daEmployee As New OleDb.OleDbDataAdapter(sqlEmployee, con)
  8.         Dim dsEmployee As New DataSet
  9.         Dim cb As New OleDb.OleDbCommandBuilder(daEmployee)
  10.  
  11.         'fill datasets
  12.         con.Open()
  13.         daEmployee.Fill(dsEmployee)
  14.         con.Close()
  15.  
  16.         'setup da commands
  17.         daEmployee.UpdateCommand = cb.GetUpdateCommand()
  18.         daEmployee.InsertCommand = cb.GetInsertCommand()
  19.         daEmployee.DeleteCommand = cb.GetDeleteCommand()
  20.  
  21.         If Not Len(txtOldPW.Text) > 0 Then
  22.             MsgBox("Please enter old password.")
  23.             Exit Sub
  24.         End If
  25.  
  26.         If Not Len(txtNewPW.Text) > 0 Then
  27.             MsgBox("Please enter new password.")
  28.             Exit Sub
  29.         End If
  30.  
  31.         If Not Len(txtConfirmNewPW.Text) > 0 Then
  32.             MsgBox("Please confirm new password.")
  33.             Exit Sub
  34.         End If
  35.  
  36.         If txtNewPW.Text <> txtConfirmNewPW.Text Then
  37.             MsgBox("New password does not match confirm new password.")
  38.             txtNewPW.Text = ""
  39.             txtConfirmNewPW.Text = ""
  40.             Exit Sub
  41.         End If
  42.  
  43.         If Len(txtNewPW.Text) < 5 Or Len(txtNewPW.Text) > 10 Then
  44.             MsgBox("New password must be between 5 and 10 characters")
  45.             txtNewPW.Text = ""
  46.             txtConfirmNewPW.Text = ""
  47.             Exit Sub
  48.         End If
  49.  
  50.         If txtOldPW.Text <> dsEmployee.Tables(0).Rows(0).Item("Password") Then
  51.             MsgBox("Old password does not match employee records.")
  52.             txtOldPW.Text = ""
  53.             txtNewPW.Text = ""
  54.             txtConfirmNewPW.Text = ""
  55.             Exit Sub
  56.         End If
  57.  
  58.         dsEmployee.Tables(0).Rows(0).Item("Password") = txtNewPW.Text
  59.  
  60.         daEmployee.Update(dsEmployee)
  61.         MsgBox("Password Updated.")
  62.  
  63.         MainForm.Show()
  64.         Me.Close()
  65.         dsEmployee.Clear()
  66.     End Sub
  67.  
As you can see I moved everything local so i can avoid confusion when dealing with public variables.

The error is "An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll" with no additional data.
Jun 24 '08 #5
Curtis Rutland
3,256 Expert 2GB
I'm sorry, tonight I don't have time to answer this. But don't worry, tomorrow I'll help you out.

Don't think that I'm ignoring you. :)
Jun 25 '08 #6
No problem, I'll take help whenever I can get it :)
Jun 25 '08 #7
Curtis Rutland
3,256 Expert 2GB
Ok, you're going about this in an inefficient way. The method you're using is better for updating multiple records and fields at once. Also, you seem to be doing some things out of order. So I put together a bit of a tutorial for you. This is the way I would do what you are doing. (Ok, so if I were doing it, I'd do it in C#, but this is the VB.NET translation version :D )

Remember to put the "Imports" statmement as the first line of the code page.
Expand|Select|Wrap|Line Numbers
  1. Imports System.Data.OleDb
  2. .
  3. .
  4. .
  5. Dim sqlSelect, sqlUpdate, connStr As String
  6. 'set up select command
  7. sqlSelect = String.Format("select Password from employees where Employee = '{0}'", lblUser.Text)
  8. 'set up connection string
  9. connStr = "PROVIDER=Microsoft.Jet.Oledb.4.0;Data Source = C:\Documents and Settings\CZ\My Documents\Databases\QSD_be.mdb"
  10. 'set up connection object
  11. Dim conn As New OleDbConnection(connStr)
  12. 'set up command object
  13. Dim cmd As New OleDbCommand(sqlSelect, conn)
  14. 'set up string to hold existing password and new password
  15. Dim oldPass, newPass As String
  16.  
  17. 'try to open the conn and execute the command
  18. Try
  19.     conn.Open()
  20.     oldPass = Convert.ToString(cmd.ExecuteScalar())
  21.     conn.Close()
  22. Catch ex As Exception
  23.     MessageBox.Show("SQL Error")
  24. Finally 'if there were errors, make sure that conn is closed
  25.     If (conn.State <> ConnectionState.Closed) Then
  26.         conn.Close()
  27.     End If
  28. End Try
  29.  
  30. 'do all your logic to see if you need a new password
  31. '...
  32. '...
  33. 'set up update command
  34. sqlUpdate = String.Format("update Employees set Password = '{0}' where Employee = '{1}'", newPass, lblUser.Text)
  35. 'add the update string to the command object
  36. cmd.CommandText = sqlUpdate
  37. 'try to execute the command
  38. Try
  39.     conn.Open()
  40.     oldPass = Convert.ToString(cmd.ExecuteScalar())
  41.     conn.Close()
  42. Catch ex As Exception
  43.     MessageBox.Show("SQL Error")
  44. Finally 'if there were errors, make sure that conn is closed
  45.     If (conn.State <> ConnectionState.Closed) Then
  46.         conn.Close()
  47.     End If
  48. End Try
  49.  
If you have any questions about what is going on here, just reply and ask. I'll be happy to explain it.

P.S. Sorry it took me so long to get back to you, I've been working really hard.
Jun 27 '08 #8
Thanks for the response. This is the first project where I've tried to split the database up and manipulate the data from the front end. I'm used to having the data in one file, so this whole business of connections and commands and such is a bit new to me.

I'm still struggling with the update command. I followed your advice and wrote sqlcommands for the select and update, but I'm getting the same error on the updatecmd.executescalar().

Expand|Select|Wrap|Line Numbers
  1. Dim selectSQL, updateSQL As String
  2.         selectSQL = "SELECT Password FROM Employees WHERE Employee = '" & lblUser.Text & "';"
  3.         updateSQL = "UPDATE Employees SET Password = '" & txtNewPW1.Text & "' WHERE Employee = '" & lblUser.Text & "';"
  4.         Dim selectCmd As New OleDbCommand(selectSQL, con)
  5.         Dim updateCmd As New OleDbCommand(updateSQL, con)
  6.         Dim oldPass As String
  7.  
  8. 'bunch of code where I check string lengths etc
  9.  
  10. 'Try
  11.         con.Open()
  12.         oldPass = selectCmd.ExecuteScalar()
  13.         If txtPassword.Text <> oldPass Then
  14.             MsgBox("Password provided does not match current employee password.")
  15.             txtPassword.Text = ""
  16.             txtNewPW1.Text = ""
  17.             txtNewPW2.Text = ""
  18.             Exit Sub
  19.         End If
  20.  
  21.         updateCmd.ExecuteScalar()
  22.  
  23. 'Catch ex As Exception
  24.         'MsgBox("Error in password update process")
  25.         'Exit Sub
  26. 'Finally
  27.         If con.State <> ConnectionState.Closed Then
  28.             con.Close()
  29.         End If
  30. 'End Try
  31.  
  32.         MsgBox("Password Updated.")
  33.  
I commented out the try/catch to see what the exact error was, its the same one I had earlier
Jun 30 '08 #9

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Test Person | last post by:
Hi I'm having trouble with updating my database which is SqlServer. I'm using the update command and only the dataset is being changed not the actual row in the database. Best Regards
7
by: Foxster | last post by:
in access i have two tables (tblplayer, tblpoints) in tblplayer i have field totalpoint (number) in tlbpoints i have field pointgame (number) the two files are related via idplayer now i made a...
3
by: Mark Kolber | last post by:
I just migrated to Office 2003. When I opened an important Access database, Access 2003 promptly destroyed it. Unfortunately, there was a glitch in my backup routine and two folders, including...
2
by: Commander Ace | last post by:
Hi, I'm having some problems with updating an SQL Database with modified fields from a datatable. I got the code from a Wrox book, 'Professional VB.NET 2003', the same code can be found here:...
2
by: Greg | last post by:
I'm using the Framework 1.1, so I don't have access to the new DataGridView object. What I'm wondering is, is there a really simple way to bind a plain datagrid to a database in such a way that...
3
by: Dot Net Daddy | last post by:
Hello, I have some textareas assigned some values which is taken from the database on page_load. Also there is an update button. But when I change the text and click the update button it does...
2
by: bpluo | last post by:
Hi, I have a specific problem. `How can one programmtically (using Visual basic, either in access or in .net) replace some tables of a 2003 Access database by the tables in an another 2003...
1
by: joel | last post by:
hi guys... i am a student, i am studying and i am a newbie in VB.net and i am using SQL Server 2000. I have this datatable which i insert rows (may take up to 2 or more rows because i would...
2
by: abdulhadi | last post by:
I have a table contains the results of matches and table contains the statistics how could I fill the statistics table by getting information from the result table the statistics table columns...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.