桌面应用程序c#中的问题将数据从excell复制到sql [英] problem in desktop application c# copy data from excell to sql

问题描述

我的代码中存在使用桌面应用将数据从excell复制到sql的问题

我发现错误

多步OLE DB操作生成错误.检查每个OLE DB状态值(如果有).没有工作.

我不知道是什么问题

excell文件仅包含一张纸，仅包含4行，其余为空:

乔治1 1 1
威廉姆斯1 1 1
mickel 1 1 1
jinnefr 1 1 1

其中工作表ara常规中的所有列类型

和sql数据库包含表，称为childern包含9列

并且我按顺序使用4列和ara的数据类型(varchar(50)--- smallint ---- tinyint ---- tinyint)不允许为null

但其他颜色允许为空


我背后的代码

i have a problem in my code for copy data from excell to sql using desktop appliction

i find error

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

and i don''t know what is the problem

the excell file include one sheet include only 4 rows and the remain is empty:

george 1 1 1
willum 1 1 1
mickel 1 1 1
jinnefr 1 1 1

where all column types in sheet ara general

and sql database include table called childern include 9 columns

and i use 4 columns and datatypes ara in order (varchar(50)---smallint----tinyint----tinyint) not allow null

but the other coluns allow null


my code behind

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.Data.Common;
using System.Data.SqlClient;

namespace ExcellToSql
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        private void button1_Click(object sender, EventArgs e)
        {
       OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Mode='Write';Integrated Security=SSPI;Data Source=D:\\MyData.xlsx;Extended Properties=" + (char)34 + "Excel 8.0;HDR=Yes;" + (char)34);
                con.Open();
                OleDbCommand cmd = new OleDbCommand("select * from [Sheet1$]", con);
                OleDbDataReader odr = cmd.ExecuteReader();
                string name = " ";
                short country = 0;
                byte status = 0;
                byte type = 0;
                while (odr.Read())
                {
                    name = valid(odr, 0);
                    country = short.Parse(valid(odr, 1));
                    status = byte.Parse(valid(odr, 2));
                    type = byte.Parse(valid(odr, 3));
                    insertdataintosql(name, country, status, type);
                }
                con.Close();
            
        }
        public void insertdataintosql(string name, short country, byte status, byte type)
    {
        SqlConnection con = new SqlConnection("Data Source=.\\sqlexpress;AttachDbFileName=|DataDirectory|exceltosql.mdf;Trusted_Connection=yes");
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "insert into Client(name,country,status,type) values(@name,@country,@status,@type)";
        cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = name;
        cmd.Parameters.Add("@country", SqlDbType.SmallInt).Value = country;
        cmd.Parameters.Add("@status", SqlDbType.TinyInt).Value = status;
        cmd.Parameters.Add("@type", SqlDbType.TinyInt).Value = type;
        
        cmd.CommandType = CommandType.Text;
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
    }
 
        
        protected string valid(OleDbDataReader myreader, int stval)
				
    {
        object val = myreader[stval];
        if (val != DBNull.Value)
            return val.ToString();
        else
            return Convert.ToString(0);
    }
    
        }
    }

因此，我需要任何人告诉我如何解决此错误

so i need any one tell me how i can solve this error

推荐答案

"，等等)； OleDbDataReader odr = cmd.ExecuteReader(); 字符串 name = " ; 短国家/地区= 0 ; 字节状态= 0 ; 字节 type = 0 ; while (odr.Read()) { 名称=有效(odr， 0 )； country = 短 .Parse(valid(odr， 1 )); 状态= 字节 .Parse(valid(odr， 2 )); 类型= byte .Parse(valid(odr， 3 )); insertdataintosql(名称，国家，状态，类型); } con.Close(); } 公用 无效 insertdataintosql(字符串名称，短国家/地区，字节状态，字节类型) { SqlConnection con = 新 SqlConnection(" ))； SqlCommand cmd = 新 SqlCommand(); cmd.Connection = con; cmd.CommandText = " ; cmd.Parameters.Add(" ，SqlDbType.NVarChar).Value =名称； cmd.Parameters.Add(" ，SqlDbType.SmallInt).值=国家/地区； cmd.Parameters.Add(" ，SqlDbType.TinyInt).值=状态； cmd.Parameters.Add(" ，SqlDbType.TinyInt).Value =类型； cmd.CommandType = CommandType.Text; con.Open(); cmd.ExecuteNonQuery(); con.Close(); } 受保护的 字符串有效(OleDbDataReader myreader， int stval) { 对象 val = myreader [stval]; 如果(值！= DBNull.Value) 返回 val.ToString(); 其他 返回 Convert.ToString( 0 ); } } }

", con); OleDbDataReader odr = cmd.ExecuteReader(); string name = " "; short country = 0; byte status = 0; byte type = 0; while (odr.Read()) { name = valid(odr, 0); country = short.Parse(valid(odr, 1)); status = byte.Parse(valid(odr, 2)); type = byte.Parse(valid(odr, 3)); insertdataintosql(name, country, status, type); } con.Close(); } public void insertdataintosql(string name, short country, byte status, byte type) { SqlConnection con = new SqlConnection("Data Source=.\\sqlexpress;AttachDbFileName=|DataDirectory|exceltosql.mdf;Trusted_Connection=yes"); SqlCommand cmd = new SqlCommand(); cmd.Connection = con; cmd.CommandText = "insert into Client(name,country,status,type) values(@name,@country,@status,@type)"; cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = name; cmd.Parameters.Add("@country", SqlDbType.SmallInt).Value = country; cmd.Parameters.Add("@status", SqlDbType.TinyInt).Value = status; cmd.Parameters.Add("@type", SqlDbType.TinyInt).Value = type; cmd.CommandType = CommandType.Text; con.Open(); cmd.ExecuteNonQuery(); con.Close(); } protected string valid(OleDbDataReader myreader, int stval) { object val = myreader[stval]; if (val != DBNull.Value) return val.ToString(); else return Convert.ToString(0); } } }

所以我需要任何人告诉我我该如何解决这个错误

so i need any one tell me how i can solve this error

乍一看...错误的OLEDB连接到MS Excel文件！
参见此处: http://www.connectionstrings.com/ [

For the first look... Wrong OLEDB connection to MS Excel file!
See here: http://www.connectionstrings.com/[^], section Data Files - MS Excel 2007.

报价:

ACE OLEDB 12.0
键入OLE DB提供程序
使用提供程序= Microsoft.ACE.OLEDB.12.0
制造商Microsoft

这是用于连接到具有Xlsx文件扩展名的Excel 2007文件.这是禁用宏的Office Open XML格式.
Provider = Microsoft.ACE.OLEDB.12.0;数据源= c:\ myFolder \ myExcel2007file.xlsx;扩展属性="Excel 12.0 Xml; HDR = YES";

"HDR =是；"表示第一行包含列名，而不是数据. "HDR =否；"表示相反.

ACE OLEDB 12.0
Type OLE DB Provider
Usage Provider=Microsoft.ACE.OLEDB.12.0
Manufacturer Microsoft

This one is for connecting to Excel 2007 files with the Xlsx file extension. That is the Office Open XML format with macros disabled.
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";

"HDR=Yes;" indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite.

这篇关于桌面应用程序c#中的问题将数据从excell复制到sql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！