Null values in excel

R

rfazendeiro

Well-known member
Joined
Mar 8, 2004
Messages
110
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.

Code: 
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

Code: 
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. Im looking for somethink like this

Code: 
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
 
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]
 
Back
Top