Click here to Skip to main content
15,885,365 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am stuck and I think the problem is in my update command. I get the error

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


@

C#
cmd.ExecuteNonQuery();


I am trying to read a csv file and update the INDEXDB1 table in an access mdb file.
Here is what I have done so far.
The error is not helping me to research what my problem is so I have to ask for some help.
I really appreciate your time if you can point me in the right direction.



C#
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
//-------------------------
using System.Data.OleDb;
using System.IO;
using System.Globalization;



namespace testupdate
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string strCvsFileName = @"c:\09760005.csv";

            string pathOnly = Path.GetDirectoryName(strCvsFileName);
            string fileName = Path.GetFileName(strCvsFileName);

            string sql = @"SELECT * FROM [" + fileName + "]";

            using (OleDbConnection connection = new OleDbConnection(
                      @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathOnly +
                      ";Extended Properties=\"Text;HDR=YES\""))
            using (OleDbCommand command = new OleDbCommand(sql, connection))
            using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
            {
                DataTable dataTable = new DataTable();
                dataTable.Locale = CultureInfo.CurrentCulture;
                adapter.Fill(dataTable);

                OleDbConnection DBconn = new OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=DATAGRP.MDB;");
                OleDbCommand cmd = new OleDbCommand();

                cmd.Connection = DBconn;
                cmd.CommandType = CommandType.Text;
                DBconn.Open();
                //read each row in the Datatable and insert that record into the DB
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    cmd.CommandText = "UPDATE INDEXDB1 SET =(IDNUM,IFIELD1,IFIELD2,IFIELD3,IFIELD4,IFIELD5,IFIELD6,IFIELD7)" +
                                     " VALUES ('" + dataTable.Rows[i].ItemArray.GetValue(0) + "','" + dataTable.Rows[i].ItemArray.GetValue(1) + "','" + dataTable.Rows[i].ItemArray.GetValue(2) +
                                     "','" + dataTable.Rows[i].ItemArray.GetValue(3) + "','" + dataTable.Rows[i].ItemArray.GetValue(4) + "','" + dataTable.Rows[i].ItemArray.GetValue(5) +
                                     "','" + dataTable.Rows[i].ItemArray.GetValue(6) + "','" + "')";

                    cmd.ExecuteNonQuery();
                }
                //close DB.connection
                DBconn.Close();
            }
        }
        
    }
}
Posted
Updated 17-Jan-16 8:17am
v2

You need to read up on the proper syntax of the UPDATE statement.

SQL UPDATE Statement[^]


You also need to learn to use parameterized statements.
 
Share this answer
 
v3
Comments
tnewt 17-Jan-16 14:55pm    
Thank you for pointing me in the right direction. I need to do more reading up on the link. not working but I tried:

cmd.CommandText = "UPDATE INDEXDB1 SET IFIELD1='dataTable.Rows[i].ItemArray.GetValue(1)',IFIELD2='dataTable.Rows[i].ItemArray.GetValue(2)',IFIELD3='dataTable.Rows[i].ItemArray.GetValue(3)',IFIELD4='dataTable.Rows[i].ItemArray.GetValue(4)',IFIELD5='dataTable.Rows[i].ItemArray.GetValue(5)',IFIELD6='dataTable.Rows[i].ItemArray.GetValue(6)',IFIELD7='dataTable.Rows[i].ItemArray.GetValue(7) WHERE IDNUM='dataTable.Rows[i].ItemArray.GetValue(0)'";
PIEBALDconsult 17-Jan-16 15:10pm    
I added: You also need to learn to use parameterized statements.
tnewt 17-Jan-16 16:47pm    
cool. taking some time but reading up and trying to understand parameterized statements.
tnewt 17-Jan-16 21:29pm    
Got this far but now I get a 'System.InvalidCastException'
do I need to match the type I am reading or the type in the mdb file?

cmd.CommandText = "UPDATE INDEXDB1 SET IFIELD1= @IFIELD1, IFIELD2 = @IFIELD2, IFIELD3 = @IFIELD3, IFIELD4= @IFIELD4, IFIELD5 = @IFIELD5, IFIELD6 = @IFIELD6, IFIELD7 = @IFIELD7 WHERE IDNUM= @IDNUM";

cmd.Parameters.Add(new SqlParameter("@IDNUM", CommandType.Text).Value = dataTable.Rows[i].ItemArray.GetValue(0).ToString());
cmd.Parameters.Add(new SqlParameter("@IFIELD1", CommandType.Text).Value = dataTable.Rows[i].ItemArray.GetValue(1).ToString());
cmd.Parameters.Add(new SqlParameter("@IFIELD2", SqlDbType.VarChar).Value = dataTable.Rows[i].ItemArray.GetValue(2).ToString());
cmd.Parameters.Add(new SqlParameter("@IFIELD3", SqlDbType.VarChar).Value = dataTable.Rows[i].ItemArray.GetValue(3).ToString());
cmd.Parameters.Add(new SqlParameter("@IFIELD4", SqlDbType.VarChar).Value = dataTable.Rows[i].ItemArray.GetValue(4).ToString());
cmd.Parameters.Add(new SqlParameter("@IFIELD5", SqlDbType.VarChar).Value = dataTable.Rows[i].ItemArray.GetValue(5).ToString());
cmd.Parameters.Add(new SqlParameter("@IFIELD6", SqlDbType.VarChar).Value = dataTable.Rows[i].ItemArray.GetValue(6).ToString());
cmd.Parameters.Add(new SqlParameter("@IFIELD7", SqlDbType.VarChar).Value = dataTable.Rows[i].ItemArray.GetValue(7).ToString());
PIEBALDconsult 17-Jan-16 21:36pm    
Is IDNUM a number?
Don't use SqlParameters with an OleDbCommand.
How about trying cmd.Parameters.AddWithValue("@IFIELD7",dataTable.Rows[i][7])

But, most of all, the OleDb provider doesn't truly support named parameters, so put the @IDNUM parameter at the end, as it is in the UPDATE statement.
Thank you!
posting the final code for anyone that can use it.

C#
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
//-------------------------
using System.Data.OleDb;
using System.IO;
using System.Globalization;
using System.Data.SqlClient;



namespace testupdate
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string strCvsFileName = "09760005.csv";
            string pathOnly = Directory.GetCurrentDirectory();
            string sql = @"SELECT * FROM [" + strCvsFileName + "]";

            using (OleDbConnection connection = new OleDbConnection(
                      @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathOnly + ";Extended Properties=\"Text;HDR=YES\""))
            using (OleDbCommand command = new OleDbCommand(sql, connection))
            using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
            {
                DataTable dataTable = new DataTable();
                dataTable.Locale = CultureInfo.CurrentCulture;
                adapter.Fill(dataTable);
                dataGridView1.DataSource = dataTable;

                OleDbConnection DBconn = new OleDbConnection(@"Provider=Microsoft.Jet.OleDb.4.0;Data Source=DATAGRP.MDB;");
                DBconn.Open();

                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    OleDbCommand cmd = new OleDbCommand();
                    cmd.Connection = DBconn;
                    cmd.CommandText = "UPDATE INDEXDB1 SET IFIELD1= @IFIELD1, IFIELD2 = @IFIELD2, IFIELD3 = @IFIELD3, IFIELD4= @IFIELD4, IFIELD5 = @IFIELD5, IFIELD6 = @IFIELD6, IFIELD7 = @IFIELD7 WHERE IDNUM= @IDNUM";

                    cmd.Parameters.AddWithValue("@IFIELD1", dataTable.Rows[i].ItemArray.GetValue(1).ToString());
                    cmd.Parameters.AddWithValue("@IFIELD2", dataTable.Rows[i].ItemArray.GetValue(2).ToString());
                    cmd.Parameters.AddWithValue("@IFIELD3", dataTable.Rows[i].ItemArray.GetValue(3).ToString());
                    cmd.Parameters.AddWithValue("@IFIELD4", String.Format("{0:MM/dd/yyyy}", dataTable.Rows[i][4]));
                    cmd.Parameters.AddWithValue("@IFIELD5", String.Format("{0:MM/dd/yyyy}", dataTable.Rows[i][5]));
                    cmd.Parameters.AddWithValue("@IFIELD6", String.Format("{0:MM/dd/yyyy}", dataTable.Rows[i][6]));
                    cmd.Parameters.AddWithValue("@IFIELD7", dataTable.Rows[i].ItemArray.GetValue(7).ToString());
                    cmd.Parameters.AddWithValue("@IDNUM", dataTable.Rows[i].ItemArray.GetValue(0).ToString());
                  
                    cmd.ExecuteNonQuery();
                }
                DBconn.Close(); 
            }
        }
    }
}
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900