Click here to Skip to main content
14,691,771 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


@

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.



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 9: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.
   
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.
tnewt 17-Jan-16 22:11pm
   
yes IDNUM is an int.
I tried all of the below and did not get an error but it also did not update the mdb file.

cmd.Parameters.AddWithValue("@IFIELD7", dataTable.Rows[i][7]);
cmd.Parameters.AddWithValue("@IDNUM", dataTable.Rows[i][0]);

cmd.Parameters.AddWithValue("@IFIELD7", dataTable.Rows[i].ItemArray.GetValue(7));
cmd.Parameters.AddWithValue("@IDNUM", dataTable.Rows[i].ItemArray.GetValue(0));

cmd.Parameters.AddWithValue("@IFIELD7", dataTable.Rows[i].ItemArray.GetValue(7).ToString());
cmd.Parameters.AddWithValue("@IDNUM", dataTable.Rows[i].ItemArray.GetValue(0).ToString());

and I checked I am getting the correct values with dataTable.Rows[i].ItemArray.GetValue(0).ToString()

learning a lot... thanks so much for your help
PIEBALDconsult 17-Jan-16 23:51pm
   
So, it's working now?
tnewt 18-Jan-16 9:46am
   
Yes it is working. i had to move some of the code within the for each row loop because it was only updating the first line. I will post the finished code when it is complete. I have one last issue if you can guide me.

cmd.Parameters.AddWithValue("@IFIELD7", dataTable.Rows[i][7]);
cmd.Parameters.AddWithValue("@IFIELD7", dataTable.Rows[i].ItemArray.GetValue(7));
both return the value(in the mdb)in the format of "1994-06-08 00:00:00"

cmd.Parameters.AddWithValue("@IFIELD7", dataTable.Rows[i].ItemArray.GetValue(7).ToString());
returns the format "6/8/1994 12:00:00 AM"

the value in the csv file and the desired value in the mdb should be "06/08/1994"
the field format in the mdb file is set to date mm/dd/yyyy only.

is there a way to achive the correct format?
tnewt 18-Jan-16 10:28am
   
the csv file value is "06/08/1994"
string test1 = dataTable.Rows[i].ItemArray.GetValue(4).ToString();
string test2 = String.Format("{0:MM/dd/yyyy}", dataTable.Rows[i].ItemArray.GetValue(4).ToString());

returns "6/8/1994 12:00:00 AM" before sending the value to the mdb file
tnewt 18-Jan-16 11:38am
   
Got it!
cmd.Parameters.AddWithValue("@IFIELD4", String.Format("{0:MM/dd/yyyy}", dataTable.Rows[i][4]));

You rock PIEBALDconsult! Thank you!
you made me do it and i learned alot from it.
Cleaning up the code and i will post it later today.
Thank you!
posting the final code for anyone that can use it.

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

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