CODEWITHSUNDEEP
c#c#-4.0ms-accessc#-3.0
Coolenough
Coolenough

Reputation: 533

Update MS Access database using Oledb connection

When I was trying to update an MS Access database (.mdb file) using OleDbConnection, I got this error:

Microsoft JET Database Engine

at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
at CostEstimate.Form1.processdbBtn_Click(Object sender, EventArgs e) in C:\Users\sy\Visual Studio 2008\Projects\demo\demo\Form1.cs :line 137

Line 137 refers to command.ExecuteNonQuery(); in the below code

Please find the code below and help me in figuring out the problem

  OleDbConnection conn = new OleDbConnection();
  conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + @"data source=C:\Users\sy\Visual Studio 2008\Projects\demo\demo\CE_Database.mdb;";

  conn.Open();

  OleDbCommand command = new OleDbCommand();
  string[][] allData = [[1,'a'],[2,'b'],[3,'c']]

  foreach (string[] individualData in allData)
  {
    command.CommandText = @"insert into CETable(JobCode,JobName) Values(individualData[0],individualData[1])";
    command.Connection = conn;
    command.ExecuteNonQuery();
  }

  conn.Close();

Upvotes: 0

Views: 3538

Answers (1)

roybalderama
roybalderama

Reputation: 1640

Try this:

OleDbConnection conn = new OleDbConnection();
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + @"data source=C:\Users\sy\Visual Studio 2008\Projects\demo\demo\CE_Database.mdb;";
conn.Open();
string[][] allData = [[1,'a'],[2,'b'],[3,'c']]

foreach (string[] individualData in allData)
{
    OleDbCommand command = new OleDbCommand()
    {
        Connection = conn,
        CommandText = string.Format(@"insert into CETable(JobCode,JobName) Values({0}, {1});", individualData[0], individualData[1])";
    };
    command.ExecuteNonQuery();
}
conn.Close();

But I'll also suggest that you set each field value through a parameter. This is a better practice to assure of not having injections

Upvotes: 0

Related Questions