Need help with Saving Info into Database!

Disasterpiece · Apr 14, 2003

Im 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.

Cant figure out whats going on.

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

Code:

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 · Apr 14, 2003

Heres my entire Code in case it helps:

Code:

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 · Apr 15, 2003

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 dont have any reserved words for column names. For example, if you have "First" and "Last" as column names, you wont be able to use the CommandBuilder to generate your SQL for you since it wont properly bracket reserved words.

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

-Ner

Disasterpiece · Apr 15, 2003

Originally posted by 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.

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

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

This isnt an issue in my case.

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

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

Thanks.

Nerseus · Apr 15, 2003

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

I think (I cant 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 · Apr 15, 2003

Hmm...What about "Usernames" ?

As far as an UpdateCommand, would I need that if all Im 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 · Apr 15, 2003

Heres 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 · Apr 15, 2003

Originally posted by Disasterpiece

Code:

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)

Code:

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)

Youre not editing a row, youre 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.

Code:

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 youve defined the object you dont then need to keep constructing it. Wasteful of those valuable resources.

Jon

Disasterpiece · Apr 15, 2003

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 · Apr 15, 2003

Do you have a single table in the db?
Do you have a primary key defined?

Disasterpiece · Apr 15, 2003

Yes, and yes

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

jfackler · Apr 15, 2003

Hey,
Ive got to go deliver a baby....really.
Ill be back in about an hour.
If your still on Ill talk to you then.

Jon

Disasterpiece · Apr 15, 2003

Good luck with that!

Ill probably be in bed in an hour, but will check this thread in the morning.

Thanks for your help.

jfackler · Apr 16, 2003

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

9 lb. 3 oz. boy child.
Mom and baby both doing well.

Ill catch up later....when you find the answer, post it back here so I know youve found the solution.

Jon

Disasterpiece · Apr 16, 2003

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 im using OLEDB/Access.

Could this be whats wrong? Something like this shouldnt be rocket science, its just a matter of me not knowing the syntax.

Nerseus · Apr 16, 2003

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

-Nerseus

Disasterpiece · Apr 16, 2003

How would I go about that?

If I remember correctly the Table name is "Usernames" (Ive 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 · Apr 16, 2003

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 Im still having the problem.

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

jfackler · Apr 16, 2003

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?

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

Jon

Disasterpiece · Apr 16, 2003

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

Need help with Saving Info into Database!

Well-known member

Well-known member

Danner

Well-known member

Danner

Well-known member

Well-known member

Well-known member

Well-known member

Well-known member

Well-known member

Well-known member

Well-known member

Well-known member

Well-known member

Danner

Well-known member

Well-known member

Well-known member

Well-known member