OleDbDataAdapter appears to use incorrect path to DB

[id]

Hi

I am using .NET framework 1.0. I am having problems with the following
VB.NET code:

----

..
..
..
..

Dim da As New OleDbDataAdapter(selectQuery, connection)
Dim ds As New DataSet()
Try
da.Fill(ds)
Catch eDB As OleDbException
showException("Database exception while filling dataset.", eDB)
Return Nothing
End Try

..
..
..
-----
The connection object i pass into the OleDbDataAdapter constructor has been
initialised with the connection string:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=x:\projects\TestData
\tracking.mdb;User ID=Admin;Password=;Mode=Share Deny None"

I checked that the data adapter's SelectCommand's connection was set to the
correct connection string using the debugger.

On the line: da.Fill(ds), i get an exception with the message:
"Could not find file 'x:\projects\MyAppName\bin\tracking.mdb'."

Now, obviously this occurs because the database does not exist at this
location. But what I can't understand is why the path it's using to look
for the database is the executable path.

Has anyone seen this issue?

Any help/comments greatly appreciated!

Thanks

id

 

[Scott M.]

Since you are using Access, it makes sense to import the database into your
project. Then you can just use Server.MapPath(relative path to file).

I don't understand what you mean by the path to the db being the 'exeutable'
path. You told the connection where the database was and it is not at that
path, so it won't open.

Did you establish a valid SelectCommand for the DataAdapter?

No need to have your Try...Catch return nothing.

Also, your connection string is an ADO connection string and not an ADO.NET
connection string. In ADO.NET, you will get a copy of the data, not the
actual data itself, so the "share deny none" bit is irrelevant since you are
not sharing the data with anyone else.

Also, your Try...Catch is a bit mangled. What is showException()?

The code should look like this:

Imports System.OleDB

Dim conStr as String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.MapPath(relative path to "tracking.mdb")

Dim con as New OleDBConnection(conStr)
Dim da As New OleDbDataAdapter("Select something from something", con)

Dim ds As New DataSet()

Try
con.open()
da.Fill(ds)
Catch eDB As OleDbException
'Assuming you have a label on the form named "lblError"
lblError.Text = eDB.Message
Catch e As Exception
lblError.Text = e.getType.ToString() & " " & e.Message
Finally
con.Close()
End Try

 

[id]

Hi Scott,

Thanks for your quick reply. I've included my responses below.

Since you are using Access, it makes sense to import the database into
your project. Then you can just use Server.MapPath(relative path to
file).

I can't really import the database as it is one that is used by another
product. My intention is to just use it for reading and comparing records
to another source.

I don't understand what you mean by the path to the db being the
'exeutable' path. You told the connection where the database was and
it is not at that path, so it won't open.

By 'executable path' I mean the path in which the main application exe is
in. In this case it is: x:\projects\MyAppName\bin\. It is just strange
that the path i used in my connection string is changed to this path.

Did you establish a valid SelectCommand for the DataAdapter?

I believe so. The String variable selectQuery, contains the string:
"SELECT * FROM PerfTrack.JobLog". I passed this into the DataAdapter's
constructor.

No need to have your Try...Catch return nothing.

Interesting. The reason i do this is so I exit the function and I return
Nothing because this will signify that the function failed. By the way, I
am returning a DataTable from this function.

Also, your connection string is an ADO connection string and not an
ADO.NET connection string. In ADO.NET, you will get a copy of the
data, not the actual data itself, so the "share deny none" bit is
irrelevant since you are not sharing the data with anyone else.

Ah I see. That's a good tip, I didn't know that. I will change that.

Also, your Try...Catch is a bit mangled. What is showException()?

showException looks like this:

Public Sub showException(ByVal msg As String, ByRef e As Exception)
MessageBox.Show(msg + vbCrLf + "Error message: " + _
e.Message.ToString(), "Exception", MessageBoxButtons.OK,
MessageBoxIcon.Error)
End Sub

The code should look like this:

Imports System.OleDB

Dim conStr as String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
& _
Server.MapPath(relative path to "tracking.mdb")

Dim con as New OleDBConnection(conStr)
Dim da As New OleDbDataAdapter("Select something from something", con)

Dim ds As New DataSet()

Try
con.open()
da.Fill(ds)
Catch eDB As OleDbException
'Assuming you have a label on the form named "lblError"
lblError.Text = eDB.Message
Catch e As Exception
lblError.Text = e.getType.ToString() & " " & e.Message
Finally
con.Close()
End Try

Yes this code looks neater. Thanks for the example.

I will try these things and post my results.

Thanks again.

 

[Scott M.]

No need to have your Try...Catch return nothing.

Interesting. The reason i do this is so I exit the function and I return
Nothing because this will signify that the function failed. By the way, I
am returning a DataTable from this function.

Ok, you didn't show that you had this code in a function, so I didn't
understand why you were using Return. If your function's return type is
seet to a DataTable, won't you error out if your return Nothing (which is
not a DataTable)?

 

[id]

Ok, you didn't show that you had this code in a function, so I didn't
understand why you were using Return. If your function's return type
is seet to a DataTable, won't you error out if your return Nothing
(which is not a DataTable)?

Yep sorry i didn't include that function.
Returning Nothing works ok. From my understanding it's like passing a
reference to nothing back.

Also, I sorted out my original problem! It was an error in the SQL select
query. However, I still can't understand why this would change the path to
the database within the Data Adapter's Select Command's connection string.

Thanks for you help.