CODEWITHSUNDEEP
c#sqlwinforms
Resu
Resu

Reputation: 33

How to update excel sheet using c#

I have two Excel sheets.

This is the first sheet. enter image description here

This is the second sheet enter image description here

In the first sheet there is a column called Language and there is a similar column in the second sheet. So now I want Language data from the first sheet to the second sheet according to their ID.

Below is my Code.

System.Data.OleDb.OleDbConnection MyConnection;
System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand();
string sql = null;

MyConnection = new System.Data.OleDb.OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\consolidated.xls;Extended Properties=Excel 8.0;");


MyConnection.Open();
myCommand.Connection = MyConnection;
              
sql = "Update [second$]  set  [second$].[Language] =[first$].[Language]  where [first$].[ID]= [second$].[ID]  ";
myCommand.CommandText = sql;
myCommand.ExecuteNonQuery();

MyConnection.Close();

Getting below error :

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

Additional information: No value given for one or more required parameters.

I'm not very skilled with SQL already, so my SQL syntax could be wrong too...but I'm not sure.

Upvotes: 2

Views: 6306

Answers (1)

Maxim Balaganskiy
Maxim Balaganskiy

Reputation: 1574

This is the best I could come up with :)

        var connectionStringBuilder = new System.Data.OleDb.OleDbConnectionStringBuilder();
        connectionStringBuilder.DataSource = @"c:\dev\tmp\consolidated.xlsx";
        connectionStringBuilder.Provider = "Microsoft.ACE.OLEDB.12.0";
        connectionStringBuilder.Add("Extended Properties", "Excel 12.0;");

        using (var connection = new System.Data.OleDb.OleDbConnection(connectionStringBuilder.ToString()))
        {
            connection.Open();
            var updateCommand = connection.CreateCommand();
            updateCommand.CommandText = "update [second$] S inner join [first$] F on S.ID = F.ID set S.Language = F.Language";
            updateCommand.ExecuteNonQuery();
        }

Upvotes: 3

Related Questions