4
Answers

Error on Importing Excel to SQL database table

Seven Balderrama

Seven Balderrama
4y
1.1k
1
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:
  1. using System;  
  2. using System.Data;  
  3. using System.Data.SqlClient;  
  4. using System.Data.OleDb;  
  5. using System.Configuration;  
  6. using System.Runtime.CompilerServices;  
  7. namespace ImportExcel  
  8. {  
  9. class Program  
  10. {  
  11.   
  12. static void Main()  
  13. {  
  14.   
  15. string excelfilepath = "C:\\test\\Test_Import.xlsx";  
  16. //declare variables - edit these based on your particular situation  
  17. string ssqltable = "ImportMultipleSheets";  
  18. // make sure your sheet name is correct, here sheet name is sheet1, so you can change your sheet name if have different  
  19. string myexceldataquery = "select [Emp_ID],[Sheet],[Employee_Name] from [sheet1$]";  
  20. try  
  21. {  
  22. //create our connection strings  
  23. string sexcelconnectionstring = @"provider=Microsoft.ACE.OLEDB.12.0;data source=" + excelfilepath + ";Extended Properties=" + "\"excel 8.0;hdr=yes;\"";  
  24. string ssqlconnectionstring = "server=SQLServer\\SERVER01;Trusted_Connection=True; database = TESTTHIS;";  
  25. //execute a query to erase any previous data from our destination table  
  26. string sclearsql = "delete from " + ssqltable;  
  27. SqlConnection sqlconn = new SqlConnection(ssqlconnectionstring);  
  28. SqlCommand sqlcmd = new SqlCommand(sclearsql, sqlconn);  
  29. sqlconn.Open();  
  30. sqlcmd.ExecuteNonQuery();  
  31. sqlconn.Close();  
  32. //series of commands to bulk copy data from the excel file into our sql table  
  33. OleDbConnection oledbconn = new OleDbConnection(sexcelconnectionstring);  
  34. OleDbCommand oledbcmd = new OleDbCommand(myexceldataquery, oledbconn);  
  35. oledbconn.Open();  
  36. OleDbDataReader dr = oledbcmd.ExecuteReader();  
  37. SqlBulkCopy bulkcopy = new SqlBulkCopy(ssqlconnectionstring);  
  38. bulkcopy.DestinationTableName = ssqltable;  
  39. while (dr.Read())  
  40. {  
  41. bulkcopy.WriteToServer(dr);  
  42. }  
  43. oledbconn.Close();  
  44. }  
  45. catch (Exception ex)  
  46. {  
  47. Console.WriteLine(ex);  
  48. Console.ReadLine();  
  49. }  
  50. }  
  51. }  
  52. }  
Sorry in advance if i didnt insert the code right... first time posting. 
Answers (4)
Next Recommended Forum