Error trying to use ODBC Connection with .csv File?

[Guest]

I am using following connection string to get data from a .csv file using
vb.net

strConnstr = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" +
System.IO.Path.GetDirectoryName(sFileName) + ";"


It gives me following error on this line

da.Fill(DS, "Test")


Following is the error message and stack trace

=============Error Message ==============
NO_DATA - no error information available"
=============StackTrace ==============
" at System.Data.Odbc.OdbcConnection.HandleError(HandleRef hrHandle,
SQL_HANDLE hType, RETCODE retcode)
at System.Data.Odbc.OdbcDataReader.GetData(Int32 i, SQL_C sqlctype, Int32
cb)
at System.Data.Odbc.OdbcDataReader.internalGetString(Int32 i)
at System.Data.Odbc.OdbcDataReader.GetValue(Int32 i, TypeMap typemap)
at System.Data.Odbc.OdbcDataReader.GetValue(Int32 i)
at System.Data.Odbc.OdbcDataReader.GetValues(Object[] values)
at System.Data.Common.SchemaMapping.LoadDataRow(Boolean clearDataValues,
Boolean acceptChanges)
at System.Data.Common.DbDataAdapter.FillLoadDataRow(SchemaMapping mapping)
at System.Data.Common.DbDataAdapter.FillFromReader(Object data, String
srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords,
DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String
srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
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, String srcTable)
at OpenUrl.Form1.FillCSVData(String sFileName) in C:\VB.Net
Downloads\Saving Web Page\Form1.vb:line 313"

I know its one column which has data and null values for some rows? If i
delete that column than it works fine, i need to read values from that column.

Any suggestion, help, advice would be highly appreciated
Mitesh

 

[Ken Tucker [MVP]]

Hi,

Here is an quick example.


If Not Directory.Exists("C:\CSV Test") Then
Directory.CreateDirectory("C:\CSV Test")

'

' Create a csv file

'

Dim sw As New StreamWriter("C:\CSV Test\Test.csv", False)

sw.WriteLine("Column1,Column2,Column3")

For x As Integer = 0 To 20

sw.WriteLine("{0},{0},{0}", x)

Next

sw.Close()

'

' Open an oledb connection and show it in a datagrid

'

Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\CSV
Test;Extended Properties=""text;HDR=Yes;FMT=Delimited"""

Dim conn As New OleDb.OleDbConnection(strConn)

Dim da As OleDb.OleDbDataAdapter

Dim ds As New DataSet

Dim mycmd As New OleDb.OleDbCommand("Select * from Test.csv", conn)

Try

da = New OleDb.OleDbDataAdapter(mycmd)

da.Fill(ds)

Catch ex As Exception

Trace.WriteLine(ex.ToString)

Return

End Try

DataGrid1.DataSource = ds.Tables(0)

DataGrid1.CaptionText = "CSV Test"





Ken

------------------------------

I am using following connection string to get data from a .csv file using
vb.net

strConnstr = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" +
System.IO.Path.GetDirectoryName(sFileName) + ";"


It gives me following error on this line

da.Fill(DS, "Test")


Following is the error message and stack trace

=============Error Message ==============
NO_DATA - no error information available"
=============StackTrace ==============
" at System.Data.Odbc.OdbcConnection.HandleError(HandleRef hrHandle,
SQL_HANDLE hType, RETCODE retcode)
at System.Data.Odbc.OdbcDataReader.GetData(Int32 i, SQL_C sqlctype, Int32
cb)
at System.Data.Odbc.OdbcDataReader.internalGetString(Int32 i)
at System.Data.Odbc.OdbcDataReader.GetValue(Int32 i, TypeMap typemap)
at System.Data.Odbc.OdbcDataReader.GetValue(Int32 i)
at System.Data.Odbc.OdbcDataReader.GetValues(Object[] values)
at System.Data.Common.SchemaMapping.LoadDataRow(Boolean clearDataValues,
Boolean acceptChanges)
at System.Data.Common.DbDataAdapter.FillLoadDataRow(SchemaMapping
mapping)
at System.Data.Common.DbDataAdapter.FillFromReader(Object data, String
srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords,
DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String
srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
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, String
srcTable)
at OpenUrl.Form1.FillCSVData(String sFileName) in C:\VB.Net
Downloads\Saving Web Page\Form1.vb:line 313"

I know its one column which has data and null values for some rows? If i
delete that column than it works fine, i need to read values from that
column.

Any suggestion, help, advice would be highly appreciated
Mitesh