[RESOLVED] DbaseIII file on a network share ODBC Exception search key was not found

[phezo]

Hi All
Does anyone know why I get {"ERROR [HY000] [Microsoft][ODBC dBase Driver] The search key was not found in any record."} when accessing a DbaseIII file that resides on a network share, I have attempted to validate credentials using the following,

Shell("Net Use \\IPADDRESS\Share /user:UsernameHere PasswordHere", AppWinStyle.Hide, True, 10000)

The error does not occur when I have the database file on my local PC...

BR
Phezo

[kevininstructor]

Have you considered using OleDb to read the dBase file into a DataTable

[phezo]

Thanks for the response Kevin,
Please have a look at the code so far, this may give you a better understanding of what I am trying todo. On my local C drive, and a local Share drive the adapter fills the dataset no problem. The error occurs when I move the file to a network share.

Many thanks for your assistance...

Dim MyServTagData As New LabelDbaseData
Dim LabelDbaseIIIPath As String = "\\131071\MyNas" 'Change this path to when moving to network
Try
'The shell net use was to ensure I had the correct credentials for my connection
Shell("Net Use \\131071\MyNas /user:UUUUUU PPPPPP", AppWinStyle.Hide, True, 10000)
'Changed username and Password above
Catch ex As Exception
End Try


Try
Dim MyConnectionString As String
Dim DbPoNumberToSearchFor As String = Strings.Mid(PurchaseOrder, 2, 12)
'Dim oledbAdapter As OleDbDataAdapter
MyConnectionString = "Driver={Microsoft dBASE Driver (*.dbf)}; DBQ=" & LabelDbaseIIIPath & "; DriverID=21"
'MyConnectionString = "Driver={Microsoft dBASE Driver (*.dbf)}; DBQ=" & LabelDbaseIIIPath & "; DriverID=277"
Dim connODBC = New OdbcConnection(MyConnectionString)
connODBC.Open()
'Dim commODBC = New OdbcCommand("Select * from NEW_DATABASE.DBF WHERE PO_Number ='" & Strings.Mid(PurchaseOrder, 2, 12) & "'", connODBC)
'Dim commODBC = New OdbcCommand("Select * from NEW_DATABASE.DBF WHERE PO_Number ='" & DbPoNumberToSearchFor & "'", connODBC)
Dim commODBC = New OdbcCommand("Select * from NEW_DATABASE.DBF", connODBC)
Dim daODBC = New OdbcDataAdapter(commODBC)
Dim cbODBC = New OdbcCommandBuilder(daODBC)
Dim dsODBC = New DataSet("X")
daODBC.Fill(dsODBC) <<<<<<<<<<<<<<<<<<<<<<<<<<<<<Error Occurs here >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Debug.Print(dsODBC.Tables(0).Rows.Count.ToString)
If dsODBC.Tables(0).Rows.Count > 0 Then
For i = 0 To dsODBC.Tables(0).Rows.Count - 1
Debug.Print(dsODBC.Tables(0).Rows(i).Item(0).ToString)
If dsODBC.Tables(0).Rows(i).Item(0).ToString = Strings.Mid(PurchaseOrder, 2, 12) Then
MyServTagData.PurchaseOrder = PurchaseOrder
MyServTagData.Model = dsODBC.Tables(0).Rows(i).Item(5).ToString
Debug.Print(dsODBC.Tables(0).Rows(i).Item(0).GetType.ToString)
MyServTagData.Po_Quantity = dsODBC.Tables(0).Rows(i).Item(2).ToString
MyServTagData.PartNumberLong = dsODBC.Tables(0).Rows(i).Item(6).ToString
Debug.Print(dsODBC.Tables(0).Rows(i).Item(0).ToString)
Debug.Print(dsODBC.Tables(0).Rows(i).Item(2).ToString)
Debug.Print(dsODBC.Tables(0).Rows(i).Item(5).ToString)
Debug.Print(dsODBC.Tables(0).Rows(i).Item(6).ToString)
End If
Next
Else 'if there is no record then the data does not exist and we cannot retrieve more from the DbaseIII
MyServTagData.PurchaseOrder = PurchaseOrder
MyServTagData.Model = "NoDataIn Label DB"
MyServTagData.Po_Quantity = "0"
MyServTagData.PartNumberLong = "NoDataIn Label DB"
End If
PartNoLong = MyServTagData.PartNumberLong
daODBC.Dispose()
connODBC.Close()

Catch ex As Exception
MsgBox("Can not open connection to app.path NEW_DATA.DBF ! " & vbCrLf & ex.Message)

End Try
Return MyServTagData

[kevininstructor]

But you are still using ODBC, I wanted to know if you tried using OleDb. If you are unsure how to do this with OleDb let me know, if not willing that is your choice.

[phezo]

I have solved the problem, you have to use the UNC path either in the connection string or in the SQL statement "\\131071\MyNas\"

MyConnectionString = "Driver={Microsoft dBASE Driver (*.dbf)}; DBQ=\\131071\MyNas\"; DriverID=21" Solves the problem

"if not willing that is your choice"

I wouldnt be here Kevin if I wasnt "Willing"...


To answer your question I had issues with OleDB, I redone this section with ODBC, another uturn was not an option as I would rather solve the problem I had, also OleDB returned 25% of the records in the Dbase III file, OLEDB returns 100%.

OleDB with DbaseIII also has a retriction with 8.3 file as discussed here http://social.msdn.microsoft.com/For...-8bcaa9c11929/

Hopefully this solution will help someone else in a similar situation...

BR
Phezo