Null values in excel

[rfazendeiro]

hi to all,

 

I have an excel with some information that i want to export to a dataset and then fill a datagrid. This code here works just fine.

 

private void processExcel()
{
System.Data.OleDb.OleDbDataAdapter MyCommand;
System.Data.OleDb.OleDbConnection MyConnection;			
			
dsExcel.Clear();

MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; " + "data source=C:\\book1.xls; " + "Extended Properties=Excel 8.0;");
MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [sheet1$]", MyConnection);

MyCommand.Fill(dsExcel);
MyConnection.Close();

this.dgExcel.DataSource = dsExcel;
}

 

 

my problem is that the excel has lots of empty lines and all that information is put into the datagrid which slows the process. I have this sql Select that returns all lines

 

MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [sheet1$]", MyConnection);

 

I have a column in the excel named CODE. If that cell is empty in the excel i dont want to load it. I have tried to modify the sql select without success. I'm looking for somethink like this

 

MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [sheet1$] [color=Red]WHERE CODE= ' '[/color]", MyConnection);

 

this does not work It gives me this error

System.Data.OleDb.OleDbException: No value given for one or more required parameters.

at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 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.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)

at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)

at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)

at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)

at TransferenciaCargas.TransferenciaBarclays.processExcel() in c:\projectos\portugal\transferencia de cargas\transferenciacargas\transferenciabarclays.cs:line 649

[Nate Bross]

Maybe something like this.

 

[csharp]

MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [sheet1$] WHERE CODE ISNULL", MyConnection);

[/csharp]

 

Aditionally, this will select where CODE is NOT a null value

 

[csharp]

MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [sheet1$] WHERE CODE NOTNULL", MyConnection);

[/csharp]

[robertsams23]

try the following links you will get idea.

 

http://csharp.net-informations.com/excel/csharp-excel-datagridview.htm

 

http://csharp.net-informations.com/excel/csharp-read-excel.htm

 

rob.