Problem using OdbcDataAdapter

[sbreytberg]

Hi all,

I am a newbie at dotnet. Been going through someone else's code and
here's a problem:

I am loading a csv file into the database. When there's more than one
record, it works fine, but when there's only one, it fails.

file is:
usrName usrFirstName usrLastName usrTitle usrDepartment usrCostCode
jdoe1 John Doe GA R&D
1234

and so on.

the code to load:
----------start----------------

Private Function getCSVDataset(ByVal strCSVFileName As String) As
System.Data.DataSet
Dim oOdbcConnection As OdbcConnection
Dim oDataAdapter As OdbcDataAdapter
Dim oCommand As OdbcCommand
Dim oDataset As DataSet

Try
Dim sConnString As String = "Driver={Microsoft Text Driver
(*.txt; *.csv)};DBQ=" &
Replace(System.AppDomain.CurrentDomain.BaseDirectory & "Log/", "/",
"\\") & ";Extensions=asc,csv,tab,txt;"
oOdbcConnection = New OdbcConnection(sConnString)
oOdbcConnection.Open()

oCommand = New OdbcCommand
With oCommand
.Connection = oOdbcConnection
.CommandType = CommandType.Text
.CommandText = "SELECT * FROM " & strCSVFileName
End With

oDataAdapter = New OdbcDataAdapter
oDataset = New DataSet
oDataAdapter.SelectCommand = New
OdbcCommand(oCommand.CommandText, oOdbcConnection)
oDataAdapter.Fill(oDataset)
Return oDataset
Catch Ex As Exception
Dim objErrorHandler As New Common.ErrorHandler(Ex)
objErrorHandler = Nothing
Return Nothing
Finally
oOdbcConnection.Close()
oOdbcConnection.Dispose()
oDataAdapter.Dispose()

oOdbcConnection = Nothing
oDataAdapter = Nothing
oCommand = Nothing
End Try
End Function
---------end---------------

The code to read:
----------start--------------
Private Function getUserDetails(ByVal strRawUserDetails As String) As
Boolean

Dim objCommon As BusinessLayer.clsCommon
Dim oDtRawUD As DataSet
Dim oRowUD As DataRow
Dim oDtRawUDRows As DataRow()
Dim oDtRawUDRow As DataRow
Dim objErrorHandler As Common.ErrorHandler
Dim iCounter As Integer = 0
Dim bReturn As Boolean = True

Try
objCommon = New BusinessLayer.clsCommon
objErrorHandler = New Common.ErrorHandler

If oDtRawUD Is Nothing Then
bReturn = False
Else

For Each oRowUD In oDtRawUD.Tables.Item(0).Rows
------------End Snippet----------------

(it goes on )

When I look at the data in the immediate window, it shows this if there
are more than one record in the file (excluding column names)

?oDtRawUD.Tables.Item(0).Rows.Item(0).ItemArray
{Length=11}
(0): "jdoe1"
(1): "John"
(2): "Doe"
(3): "GA"
(4): "R&D"
(5): 1234 {Integer}

When there's only one, this happens:

(0): {System.DBNull}

Why? How? Any ideas?

thanks for any help
Sanna

 

[Mark Rae]

Why? How? Any ideas?

Is there a problem with the schema.ini file...?
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp

Does the problem still happen if you use OleDb rather than ODBC...?
http://www.connectionstrings.com/?carrier=textfile