The filling of the array cellValue went ok.
The data went in.
Putting the array into an Sql MDF database is the problem.
The connection string works and the insert into command is correct.
I believe the error is in the putting of the array directly into the @col1:
cmd.Parameters.AddWithValue("@col1", cellValue[1, h]);
Here is my code:
protected void Button1_Click(Object sender, EventArgs e)
{
DataSet ds = new DataSet();
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;
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.Parameters.AddWithValue("@col1", cellValue[1, h]);
cmd.Parameters.AddWithValue("@col2", cellValue[2, h]);
cmd.Parameters.AddWithValue("@col3", cellValue[3, h]);
cmd.ExecuteNonQuery();
conn.Close();
cmd.Dispose();
conn.Dispose();
}
}
}
catch (Exception ex)
{
Label2.Text = ex.ToString();
}
}
}
Error code:
System.Data.SqlClient.SqlException (0x80131904): String or binary data would be truncated. The statement has been terminated. 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.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, 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\Optiplex760\Documents\path\Default.aspx.cs:line 61 ClientConnectionId:d64c1f53-c093-4f81-ad47-38bf1a86a111