读取csv文件来更新acess mdb [英] reading csv file to update acess mdb

问题描述

我被卡住了，我认为问题出在我的更新命令中。我收到错误

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

我正在尝试读取csv文件并更新访问mdb文件中的INDEXDB1表。

这是我到目前为止所做的事情。

这个错误并没有帮助我研究我的问题，所以我不得不寻求帮助。

我真的很感谢你的时间，如果你能指出我正确的方向。

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

推荐答案

你需要阅读UPDATE语句的正确语法。



SQL UPDATE语句 [ ^ ]





您还需要学习使用参数化语句。

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.

谢谢！

为可以使用它的任何人发布最终代码。

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

这篇关于读取csv文件来更新acess mdb的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！