Need help with Saving Info into Database!

[Disasterpiece]

I'm having a problem Saving changes made to an Access DB. I can load the info from the DB into a Datagrid, and can Add rows, but when I try to use the Update Methods I get an unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll.

 

Can't figure out what's going on.

 

Here is my code that is attempting to write to the DB:

 

 

 

Private Sub frmNewUser_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing
       Dim daUsers As New OleDb.OleDbDataAdapter(sqlStr, connStr)
       Dim commandBuilder As OleDb.OleDbCommandBuilder

       commandBuilder = New OleDb.OleDbCommandBuilder(daUsers)
       daUsers.InsertCommand = commandBuilder.GetInsertCommand()
       daUsers.Update(dsUsers, "Users")
       dsUsers.AcceptChanges()
       daUsers.Dispose()
End Sub

 

 

 

 

 

dsUsers is a globally declared New Dataset() if that helps.

 

Thanks!

[Disasterpiece]

Here's my entire Code in case it helps:

 

Option Strict On

Public Class frmNewUser
   Inherits System.Windows.Forms.Form

   Private dsUsers As DataSet


#Region " Windows Form Designer generated code "


   Dim dtUsers As New DataTable()
   Dim connStr As String = "Provider=Microsoft.Jet.OleDb.4.0;" & _
       "Data Source = Users.MDB"
   Dim sqlStr As String = "SELECT * From Users"


   Private Sub btnProceed_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnProceed.Click
       Dim daUsers As New OleDb.OleDbDataAdapter(sqlStr, connStr)
       Dim newRow As DataRow

       'dsUsers = New DataSet()


       dtUsers = dsUsers.Tables("Users")

       newRow = dtUsers.NewRow()

       newRow.BeginEdit()

       'newRow = DsUsers1.Users.NewRow

       newRow("Username") = txtUserName.Text
       newRow("Password") = txtPW.Text
       newRow("Bank_Name") = txtBankName.Text
       newRow("Starting_Balance") = txtStartingBalance.Text
       newRow("Minimum_Balance") = txtMinBalance.Text
       newRow("Service_Charge") = txtSVCChg.Text
       newRow("ATM_Fee") = txtATMFee.Text

       newRow.EndEdit()

       dtUsers.Rows.Add(newRow)


   End Sub

   Private Sub frmNewUser_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
       Dim daUsers As New OleDb.OleDbDataAdapter(sqlStr, connStr)

       dsUsers = New DataSet()


       daUsers.Fill(dsUsers, "Users")
       'daUsers.Dispose()
       dgUsers.DataSource = dsUsers.Tables("Users")


   End Sub

   Private Sub frmNewUser_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing
       Dim daUsers As OleDb.OleDbDataAdapter
       Dim commandBuilder As OleDb.OleDbCommandBuilder

       daUsers = New OleDb.OleDbDataAdapter(sqlStr, connStr)
       commandBuilder = New OleDb.OleDbCommandBuilder(daUsers)
       daUsers.InsertCommand = commandBuilder.GetInsertCommand()
       daUsers.Update(dsUsers, "Users")
       'dsUsers.AcceptChanges()
       'daUsers.Dispose()
   End Sub

   Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
       Me.Close()
   End Sub
End Class

[Nerseus]

Can you post the entire error message? Also, use Debug.WriteLine (or something similar) to view the InsertCommand string that the CommandBuilder creates and post that, too.

 

Check that you don't have any reserved words for column names. For example, if you have "First" and "Last" as column names, you won't be able to use the CommandBuilder to generate your SQL for you since it won't properly bracket reserved words.

 

I don't think this would be your problem, but it might be: make sure you create an UpdateCommand as well as an InsertCommand, if you're also updating rows that is.

 

-Ner

[Disasterpiece]

Can you post the entire error message? Also, use Debug.WriteLine (or something similar) to view the InsertCommand string that the CommandBuilder creates and post that, too.

 

I will post this once I get home from work. My teacher said there is something wrong with the Insert statement and she'll try to figure it out this afternoon. Hopefully she'll give me an answer.

 

Check that you don't have any reserved words for column names. For example, if you have "First" and "Last" as column names, you won't be able to use the CommandBuilder to generate your SQL for you since it won't properly bracket reserved words.

 

This isn't an issue in my case.

 

I don't think this would be your problem, but it might be: make sure you create an UpdateCommand as well as an InsertCommand, if you're also updating rows that is.

 

I have the update command right after the insert command, if that's what you are talking about.

 

Thanks.

[Nerseus]

I was talking about the UpdateCommand (the SQL for performing updates to the database), not the Update method :)

 

I think (I can't check right now), that "users" is a reserved word. I should have mentioned to make sure column AND table names are not reserved words.

 

-Nerseus

[Disasterpiece]

Hmm...What about "Usernames" ?

 

As far as an UpdateCommand, would I need that if all I'm doing is adding records with this form? I do not wish to update them at this time, that will be handled by a menu choice within the main program.

[Disasterpiece]

Here's the Error Message I get:

 

Unhandled Exception: System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement.

at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)

at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)

at Semester_Project.frmNewUser.frmNewUser_Closing(Object sender, CancelEventArgs e) in C:\Old D Drive\Kevin\SchoolWork\2002-2003 VT Second Semester\Visual Basic\Semester Project\frmNewUser.vb:line 347

at System.Windows.Forms.Form.OnClosing(CancelEventArgs e)

at System.Windows.Forms.Form.WmClose(Message& m)

at System.Windows.Forms.Form.WndProc(Message& m)

at System.Windows.Forms.ControlNativeWindow.OnMessage(Message& m)

at System.Windows.Forms.ControlNativeWindow.WndProc(Message& m)

at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

at System.Windows.Forms.UnsafeNativeMethods.SendMessage(IntPtr hWnd, Int32 msg, Int32 wParam, Int32 lParam)

at System.Windows.Forms.Control.SendMessage(Int32 msg, Int32 wparam, Int32 lparam)

at System.Windows.Forms.Form.Close()

at Semester_Project.frmNewUser.btnCancel_Click(Object sender, EventArgs e) in C:\Old D Drive\Kevin\SchoolWork\2002-2003 VT Second Semester\Visual Basic\Semester Project\frmNewUser.vb:line 353

at System.Windows.Forms.Control.OnClick(EventArgs e)

at System.Windows.Forms.Button.OnClick(EventArgs e)

at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)

at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)

at System.Windows.Forms.Control.WndProc(Message& m)

at System.Windows.Forms.ButtonBase.WndProc(Message& m)

at System.Windows.Forms.Button.WndProc(Message& m)

at System.Windows.Forms.ControlNativeWindow.OnMessage(Message& m)

at System.Windows.Forms.ControlNativeWindow.WndProc(Message& m)

at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)

at System.Windows.Forms.ComponentManager.System.Windows.Forms.UnsafeNativeMethods+IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)

at System.Windows.Forms.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)

at System.Windows.Forms.Application.Run(Form mainForm)

at Semester_Project.frmNewUser.Main() in C:\Old D Drive\Kevin\SchoolWork\2002-2003 VT Second Semester\Visual Basic\Semester Project\frmNewUser.vb:line 3The program '[1336] Semester Project.exe' has exited with code 0 (0x0).

[jfackler]

   Private Sub frmNewUser_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing
       Dim daUsers As OleDb.OleDbDataAdapter
       Dim commandBuilder As OleDb.OleDbCommandBuilder

       daUsers = New OleDb.OleDbDataAdapter(sqlStr, connStr)
       commandBuilder = New OleDb.OleDbCommandBuilder(daUsers)
       'daUsers.InsertCommand = commandBuilder.GetInsertCommand()
       daUsers.Update(dsUsers, "Users")
       'dsUsers.AcceptChanges()
       'daUsers.Dispose()
   End Sub

   Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
       Me.Close()
   End Sub
End Class

 

When you create a commandbuilder, it defines your insert statement.

Try commenting out the line as above and see if it corrects your problem.

 

 

Also: The following adds the row to the dataset which is what the dataadapter is actually using to update your source.

(I commented out the code that I changed)

 

Dim daUsers As New OleDb.OleDbDataAdapter(sqlStr, connStr)
        Dim NewRow As DataRow = dsusers.Tables("Users").NewRow

       'dsUsers = New DataSet()


       'dtUsers = dsUsers.Tables("Users")

       'newRow = dtUsers.NewRow()

      ' newRow.BeginEdit()

       'newRow = DsUsers1.Users.NewRow

       newRow("Username") = txtUserName.Text
       newRow("Password") = txtPW.Text
       newRow("Bank_Name") = txtBankName.Text
       newRow("Starting_Balance") = txtStartingBalance.Text
       newRow("Minimum_Balance") = txtMinBalance.Text
       newRow("Service_Charge") = txtSVCChg.Text
       newRow("ATM_Fee") = txtATMFee.Text

      ' newRow.EndEdit()

      ' dtUsers.Rows.Add(newRow)

dsusers.Tables("Users").Rows.Add(NewRow)

 

You're not editing a row, you're adding a row. The BeginEdit and EndEdit are not used here.

 

 

One more thought.... you have constructed a new instance of your dataadpater on form load.

Then, every time you click a button, you construct a new object.

 

Option Strict On

Public Class frmNewUser
   Inherits System.Windows.Forms.Form

   Private dsUsers As DataSet


#Region " Windows Form Designer generated code "


   Dim dtUsers As New DataTable()
   Dim connStr As String = "Provider=Microsoft.Jet.OleDb.4.0;" & _
       "Data Source = Users.MDB"
   Dim sqlStr As String = "SELECT * From Users"
   Dim daUsers As OleDb.OleDbDataAdapter

 

Dim that daUser as above and then only call the constructor (i.e.

Dim daUsers As New OleDb.OleDbDataAdapter(sqlStr, connStr))

once on the form load.

Since you've defined the object you don't then need to keep constructing it. Wasteful of those valuable resources.

 

 

 

 

Jon

Edited April 16, 2003 by jfackler

[Disasterpiece]

Ok, I did all of the above, same error message. Any other suggestions?

 

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll

[jfackler]

Do you have a single table in the db?

Do you have a primary key defined?

[Disasterpiece]

Yes, and yes

 

edit: The primary key is set to an ID Field which is setup as Autonumber. Could this be faulty?

[jfackler]

Hey,

I've got to go deliver a baby....really.

I'll be back in about an hour.

If your still on I'll talk to you then.

 

Jon

[Disasterpiece]

Good luck with that!

 

I'll probably be in bed in an hour, but will check this thread in the morning.

 

Thanks for your help.

[jfackler]

Back from my day/(night) job: supports this nasty coding habit I've developed.

 

9 lb. 3 oz. boy child.

Mom and baby both doing well.

 

I'll catch up later....when you find the answer, post it back here so I know you've found the solution.

 

Jon

[Disasterpiece]

Just to clarify a bit:

 

I based my syntax for this from an example using ADO.NET found on MSDN. The only difference between mine and what was on MSDN is that they were using a SQL database and SQL connections, while i'm using OLEDB/Access.

 

Could this be what's wrong? Something like this shouldn't be rocket science, it's just a matter of me not knowing the syntax.

[Nerseus]

Can you show us the value of "daUsers.InsertCommand.CommandText"? It should be something like "INSERT INTO table (col, col, ...)". I'm guessing there's still an issue with the column names or table name...

 

-Nerseus

[Disasterpiece]

How would I go about that?

 

If I remember correctly the Table name is "Usernames" (I've changed this from "Users")

 

The Column names are as follows:

"ID" Primary Key

"Username"

"Password"

"Bank_Name"

"Starting_Balance"

"Minimum_Balance"

"Service_Charge"

"ATM_Fee"

[Disasterpiece]

Oops - Correction I tried taking out the ID Field because I thought that might be messing it up and set the Primary Key to Username but I'm still having the problem.

 

And like I said, I still don't understand why this is so difficult. I mean people write things into databases all the time right? Why won't it work for me?!!!

[jfackler]

I think Ners right, but I think the problem player is the column named password.

I created the database and let the IDE wizard create the insert into statement.

Password is bracketed.

Any thoughts about this Ner?

 

I've got another baby on the way....work keeps getting in the way of the fun stuff. Check back in a little while.

 

Jon

[Disasterpiece]

Well, my teacher sent me an email saying it was okay to use the IDE wizard to setup the connection, so I think I'll just use that because everything works fine when I do it that way.

[Disasterpiece]

Wait, wait wait wait!!

 

Newsflash: IT WORKS

:)

 

It did end up being the Password Field, I simply changed it to "PW" and it works like a charm.

 

Thanks for the help everyone, I may be back with more questions :)

[Nerseus]

Ah, whew.

 

Well, the alternative is to NOT use the CommandBuilder (it's kind of a beginner's helper class more than anything). You can use the CommandBuilder while in development, but look at the SQL string it builds for you (the CommandText property as I mentioned above). Once you have the INSERT string that the CommandBuilder built for you, you can do away with the CommandBuilder and hard-code the string for yourself.

 

It will mean manually updating the string as your database changes. But, it also gives you more controls over the SQL you're using. For example, the default SQL might look like:

INSERT INTO TABLE1 (ID, Username, Password) VALUES (?, ?, ?)

 

As you've seen, this won't work since Password is a reserved word. Instead, replace the string with:

INSERT INTO TABLE1 ([iD], [username], [Password]) VALUES (?, ?, ?)

 

Notice the square brackets. You can *always* use square brackets on column names. If you ever forget and your column is a reserved word, you just might get this exception again. For example, "id" is a reserved word in SQL Server... Better safe than sorry and use the square brackets and NOT use the CommandBuilder.

 

-Nerseus

[jfackler]

Yahoo....

Hope we get an A from the teach.

[Nerseus]

Here's something that you should check out:

Weaning Developers from the CommandBuilder.

 

It's an article provided by Microsoft.

 

-Nerseus

[jfackler]

Thanks Nerseus.