Want to build the ChatGPT based Apps? Start here
Become a member
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
C# Corner Home
Technologies
Monthly Leaders
ASK A QUESTION
Forum guidelines
Seven Balderrama
0
3
777
Error on Importing Excel to SQL database table
May 15 2020 5:20 PM
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.
Reply
Answers (
4
)
Parameter inside <%= %>
search string not work if i pass it arrounded single quotes