Visual C#.NET ERROR: An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

Question

Good day, everyone. I am new with visual c#.net. I am using OleDb which is for MS Access database. I am having trouble with my Inserting data int MS Access database, I don't know which one is wrong with here. In my theory is how my algorithm is badly constructed. I kept receiving this OleDb exception error which is "Additional information: Syntax error in INSERT INTO statement."

here are my codes:

I have 3 fields in my database table, which is called "usersTable". Which are: user_name ,password ,and user_type.

adduser.cs

note: I kept receiving the error in this line of code global.da.Update(global.ds1, "usersTable");

using System;
using System.Data;
using System.Data.OleDb;

private void dbConnect()
    {
        global.sconn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source = D:/c# programs/soer_final/User.accdb";
        global.dbConn = new OleDbConnection(global.sconn);
        global.dbCmd.Connection = global.dbConn;
        global.dbCmd.CommandText = "Select * from usersTable";
        global.da.SelectCommand = global.dbCmd;
        global.dbConn.Open();
        global.ds1.Clear();
        global.da.Fill(global.ds1, "usersTable");
    }

 private void SaveButton_Click(object sender, EventArgs e)
        {
            try { 
                dbConnect();
                DataRow dRow = global.ds1.Tables["usersTable"].NewRow();
                dRow[0] = user_name.Text;
                dRow[1] = password.Text;
                dRow[2] = user_type.Text;


                global.ds1.Tables["usersTable"].Rows.Add(dRow);
                global.da.Update(global.ds1, "usersTable");
                global.dbConn.Close();
                MessageBox.Show("User added");
            }
           catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }

Program.cs

using System.Data;
using System.Data.OleDb;

public class global
{
    public static string sconn;
    public static OleDbConnection dbConn;
    public static OleDbDataAdapter da = new OleDbDataAdapter();
    public static OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
    public static OleDbCommand dbCmd = new OleDbCommand();
    public static DataSet ds1 = new DataSet();
    public static DataRow[] foundrow;
}

Steve · Accepted Answer · 2017-03-23 11:20:07Z

1

You need to change the line where you build the OleDbCommandBuilder.

OleDbCommandBuilder cb = new OleDbCommandBuilder(da) 
{ QuotePrefix = "[", QuoteSuffix = "]"};

This will force the OleDbCommandBuilder to enclose the field names of the generated commands (for update, insert and delete operations) with square brackets. In turn this avoids the Syntax error caused by the use of a reserved keyword in your column names (PASSWORD).

Of course, you could also change that column name and remove once and for all the source of your problem.

By the way, I strongly advise to not have a global class to keep all those variable alive for the lifetime of your program. They will be a persistent source of troubles for your program. Connection not closed and disposed, command with previous use parameters and text, adapter that changes its configuration and so on. Better to instantiate these objects when you need them and destroy them afterwards. Not a big cost in terms of perfomances

edited Mar 23, 2017 at 11:20

answered Mar 23, 2017 at 11:14

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

oakleg Over a year ago

well, I felt dumb haha your answer solved my issue. Sorry, I didn't know that the password is a reserved keyword. Thanks a lot for helping with my issue, man. You're awesome! Have an awesome day!

Collectives™ on Stack Overflow

Visual C#.NET ERROR: An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related