Microsoft Text Driver "NO" ? (csv, datatable)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Microsoft Text Driver "NO" ? (csv, datatable)

Author

Topic

Sitka
Aged Yak Warrior

571 Posts

Posted - 2005-12-07 : 13:59:01

I'm not sure this is exactly what is going on but I figured I'd show an example.

Lots of times there may be a small physical file that you
would want to process in a .NET application, transform it
into some kind of data context then send it on over to SQL.
CSV files are easy to understand as a datatable so basically
it can be expeditious to transform some input to a csv and
then use the text Driver to fill a datatable.


strConn = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + strImportFolder + ";";
Conn = new System.Data.Odbc.OdbcConnection(strConn);

normally you may procedeed something like this


da = new System.Data.Odbc.OdbcDataAdapter("select * from [" + strFileName + "]", Conn);
da.Fill(dt);
Conn.Close();

this caused me some real trouble when I wanted to Clear the
datatable (dt) and refill the adapter (like someone might push a
button twice to go get the csv again.) once the datatable had gone
through some manipulation and styling


if (!dt.Columns.Contains("MATCH"))
{
	dt.Columns.Add("MATCH");
}
//foreach (DataRow dr in dt.Rows)
//{
//	DisplayRow(dr);					
//}

dataGrid1.SetDataBinding(dt,"");				

// use below two lines to remove the Append row from DataGrids
CurrencyManager cm1 = (CurrencyManager)this.BindingContext[dataGrid1.DataSource, dataGrid1.DataMember];      
((DataView)cm1.List).AllowNew = false;

// Get the width of Longest Field to make the grid column easier to read
int newwidth = LongestField(dt, "DESCRIPTION_NAME");

dt.TableName = "BOM";
// Create new Table Style
DataGridTableStyle ts = new DataGridTableStyle();
ts.MappingName = "BOM";
dataGrid1.TableStyles.Clear();
dataGrid1.TableStyles.Add(ts);
dataGrid1.TableStyles["BOM"].RowHeadersVisible = false;

// Assign New Width to DataGrid column
dataGrid1.TableStyles["BOM"].GridColumnStyles["DESCRIPTION_NAME"].Width = newwidth;
newwidth = LongestField(dt, "SIZE_CAT");
dataGrid1.TableStyles["BOM"].GridColumnStyles["SIZE_CAT"].Width = newwidth;
dataGrid1.Refresh();

or something like that,
But on consecutive times this pseudo code was run and da.Fill(dt)
was called, I was getting extra columns created through the da.
I guessed this was because the dt had a legacy schema binding in
it which I was unsuccesful at getting out. Columns.Clear() didn't
seem to work. Maybe the scope of dt was wrong but I tried a lot of
things and it wasn't going well.

Turns out the failure was happening here


da = new System.Data.Odbc.OdbcDataAdapter("select * from [" + strFileName + "]", Conn);
da.Fill(dt);
Conn.Close();

so I tried


da = new System.Data.Odbc.OdbcDataAdapter("select NO,SOURCE,QTY,DESCRIPTION_NAME,SIZE_CAT,DATE_REQD,Sht,O_M,CUST_STD from [" + strFileName + "]", Conn);
da.Fill(dt);
Conn.Close();

which is calling the Header columns from the .csv specifically

"frig", still something weird going on.


da = new System.Data.Odbc.OdbcDataAdapter("select [NO],SOURCE,QTY,DESCRIPTION_NAME,SIZE_CAT,DATE_REQD,Sht,O_M,CUST_STD from [" + strFileName + "]", Conn);
da.Fill(dt);
Conn.Close();

That works! NO meant Number to me in the .csv but it meant something
else to the provider (something more sinister?, a reserved word?).
So just to be safe....


da = new System.Data.Odbc.OdbcDataAdapter("select [NO],[SOURCE],[QTY],[DESCRIPTION_NAME],[SIZE_CAT],[DATE_REQD],[Sht],[O_M],[CUST_STD] from [" + strFileName + "]", Conn);
da.Fill(dt);
Conn.Close();

If I hadn't been in the know about "don't use * in select"
even in this trivial case I would still be debugging.
Kind of think this may not be an issue in .NET 2 because I
believe the DataAdapter would not be needed, just a TableAdapter
but I'm not writing in .Net version 2 yet.
Hope the idea behind this is clear enough to be helpful.

"it's definitely useless and maybe harmful".

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources