CODEWITHSUNDEEP
c#databasems-accessoledboledbdataadapter
oakleg
oakleg

Reputation: 3

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

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;
}

Upvotes: 0

Views: 550

Answers (1)

Steve
Steve

Reputation: 216243

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

Upvotes: 1

Related Questions