SQL阻止将数组添加到col1中 [英] The SQL is blocking the addition of the Array into col1

问题描述

Microsoft.Office.Interop工作正常并给出一个数组：cellValue [j，k]。



col1，col2和col3定义为nchar（10 ）在mdf文件中。



SQL阻止将数组添加到col1中：

The Microsoft.Office.Interop works fine and gives an Array: cellValue[j,k].

col1, col2 and col3 are defined as nchar(10) in the mdf file.

The SQL is blocking the addition of the Array into col1:

cmd.Parameters.AddWithValue("@col1", cellValue[1,h]);

如何格式化接受SQL col1，col2和col3的数组

How do I format the Array to be accepted into SQL col1,col2 and col3

protected void Button1_Click(Object sender, EventArgs e)
 {
     //From Excel
     Microsoft.Office.Interop.Excel.Application exlApp = new Microsoft.Office.Interop.Excel.Application();
     Microsoft.Office.Interop.Excel.Workbook exlWb = exlApp.Workbooks.Open(@"C:\Users\Optiplex760\Documents\a  Excel\ExcelToMDF.xls");
     Microsoft.Office.Interop.Excel.Worksheet exlWs= exlWb.Sheets["Sheet1"];
     Microsoft.Office.Interop.Excel.Range usedRange = exlWs.UsedRange;
     int col = Convert.ToInt32(usedRange.Columns.Count);
     int row = Convert.ToInt32(usedRange.Rows.Count);
     exlApp.Visible = true;
     string[,] cellValue = new string[row + 1, col + 1];
     for (int j = 1; j <= row-1; j++)
     {
         for (int k = 1; k <= col-1; k++)
         {
             cellValue[j, k] = exlWs.Cells[j+1,k+1].ToString();
         }
     }
     exlWb.Close();
     exlWs = null;
     exlWb = null;
     exlApp.Quit();
     exlApp = null;
     //To MSSQL
     String connStr, cmdStr;
     connStr = ConfigurationManager.ConnectionStrings["MDFExceldb"].ConnectionString;
     for (int h = 1; h<row+1; h++)
     {
     cmdStr = "INSERT INTO [Table1] (col1,col2,col3) VALUES (@col1,@col2,@col3);";
         try
         {
             using (SqlConnection conn = new SqlConnection(connStr))
             {
                 using (SqlCommand cmd = new SqlCommand(cmdStr, conn))
                 {
                     conn.Open();
                     cmd.ExecuteNonQuery();
                     cmd.Parameters.AddWithValue("@col1", cellValue[1,h]);
                     cmd.Parameters.AddWithValue("@col2", cellValue[2,h]);
                     cmd.Parameters.AddWithValue("@col3", cellValue[3,h]);
                     conn.Close();
                     cmd.Dispose();
                     conn.Dispose();
                 }
             }
         }catch (Exception ex){
             Label2.Text = ex.ToString();
         }
     }
 }

错误代码：

Error Code:

System.Data.SqlClient.SqlException (0x80131904): Must declare the scalar variable "@col1". at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at ExceltoMDF_ApplicationWorkbookMethod._Default.Button1_Click(Object sender, EventArgs e) in C:\Users\User\path\Default.aspx.cs:line 56 ClientConnectionId:1ff3a55d-0e67-4c19-8e68-1a64d6260be8 

推荐答案

将代码更改为......

Change the code to...

conn.Open();

cmd.Parameters.AddWithValue("@col1", cellValue[1,h]);
cmd.Parameters.AddWithValue("@col2", cellValue[2,h]);
cmd.Parameters.AddWithValue("@col3", cellValue[3,h]);

cmd.ExecuteNonQuery();

这篇关于SQL阻止将数组添加到col1中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！