Error on Importing Excel to SQL database table

Hello,

I am not to keen on C#, however i found this example of excel to SQL database C# code, and just edited some stuff, however its not working, giving error message when catching:

System.Data.OleDb.OleDbException (0x80040E10): No value given for one or more required parameters.
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.OleDb.OleDbCommand.ExecuteReader()
at ImportExcel.Program.Main() in C:\Users\adminssbalderrama\source\repos\ImportExcel\ImportExcel\Program.cs:line 40

the code is below, probably something simple for everyone that i am missing, any help would greatly be appreciated:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Configuration;
using System.Runtime.CompilerServices;
namespace ImportExcel
{
class Program
{
static void Main()
{
string excelfilepath = "C:\\test\\Test_Import.xlsx";
//declare variables - edit these based on your particular situation
string ssqltable = "ImportMultipleSheets";
// make sure your sheet name is correct, here sheet name is sheet1, so you can change your sheet name if have different
string myexceldataquery = "select [Emp_ID],[Sheet],[Employee_Name] from [sheet1$]";
try
{
//create our connection strings
string sexcelconnectionstring = @"provider=Microsoft.ACE.OLEDB.12.0;data source=" + excelfilepath + ";Extended Properties=" + "\"excel 8.0;hdr=yes;\"";
string ssqlconnectionstring = "server=SQLServer\\SERVER01;Trusted_Connection=True; database = TESTTHIS;";
//execute a query to erase any previous data from our destination table
string sclearsql = "delete from " + ssqltable;
SqlConnection sqlconn = new SqlConnection(ssqlconnectionstring);
SqlCommand sqlcmd = new SqlCommand(sclearsql, sqlconn);
sqlconn.Open();
sqlcmd.ExecuteNonQuery();
sqlconn.Close();
//series of commands to bulk copy data from the excel file into our sql table
OleDbConnection oledbconn = new OleDbConnection(sexcelconnectionstring);
OleDbCommand oledbcmd = new OleDbCommand(myexceldataquery, oledbconn);
oledbconn.Open();
OleDbDataReader dr = oledbcmd.ExecuteReader();
SqlBulkCopy bulkcopy = new SqlBulkCopy(ssqlconnectionstring);
bulkcopy.DestinationTableName = ssqltable;
while (dr.Read())
{
bulkcopy.WriteToServer(dr);
}
oledbconn.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex);
Console.ReadLine();
}
}
}
}

Sorry in advance if i didnt insert the code right... first time posting.

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Leon D6y ago
Hi,

You can consider using a Excel library like Spire.XLS (https://www.nuget.org/packages/Spire.XLS/) to read excel to datatable, and then use sqlbulkcopy to insert datatable data to database.
Here is the code snippet of read excel to datatable using Spire.XLS.
Workbook workbook = new Workbook();
workbook.LoadFromFile(fileName);
Worksheet sheet = workbook.Worksheets[0];
DataTable dt = sheet.ExportDataTable();
0
Jignesh Kumar6y ago
Hey,

Please take reference from this link,
https://www.aspsnippets.com/Articles/Using-SqlBulkCopy-to-import-Excel-SpreadSheet-data-into-SQL-Server-in-ASPNet-using-C-and-VBNet.aspx
0
Jefferson S. Motta6y ago
I think you are missing ColummnMapping:

look at this:
https://www.c-sharpcorner.com/article/import-excel-data-into-sql-table-using-sqlbulkcopy-in-c-sharp/
Sample:
objbulk.ColumnMappings.Add("[Emp_ID]", "ID???????????????????");
objbulk.ColumnMappings.Add("[Sheet]", "Sheet???????????????????");
objbulk.ColumnMappings.Add("[Employee_Name], "Name???????????????????");
0
Salman Beg6y ago
Hey as per the error it is saying that you are missing some of the required parameter.please do check in line number 40. Also refer this link as an example, https://www.c-sharpcorner.com/blogs/how-to-import-excel-data-in-sql-server-using-asp-net2
0