HLP: Opening Access DB issues

[Mr. B]

VB.net 2003; MS Access db; Netframe 1.1

I wrote an Windows app for my last place of employment that opened and
modified a couple of MS Access db files. I did most of my programming at home
on Drive "C". And I used the Drag/Drop Data Items to create the Connection,
DataSet and DataAdapters.

The Access files were located on drives "P" and "S" at work, so I had VB.net
installed on my work PC so I could change the drives in the Connection Icons.

Now I no longer work at that place and they want me to make some changes. Of
course I can't as I don't have a drive P or S on my system. So I'm attempting
to get rid of the Data Icons and do the connection, Data set and adapters with
Code. And of course I'm getting all kinds of errors.

So I'm doing a Test app to find/fix my items. Right off the bat I can't even
establish a proper connection to a DB. The following is that code I'm using.

I get an ERROR at the FILL line (unhandled exception where "Value cannot be
null". But the access file opens just fine if I use the DATA Icons.

And... If I change the Fill line to:
daShenData.FillSchema(dsShenData, SchemaType.Source, "SHENHrsTable")
.... the error then says "The ConnectionString property has not been
initialized".

************** CODE ***********************************************
Imports System.IO
Imports System.Data.OleDb

' Connection String1 to SHENTSdata.mdb
Dim connString1 As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=TSdbPath & SHENTSdata.mdb"

Dim OleDbConnection1 As OleDbConnection = New OleDbConnection

' Data Adapter 1
Dim strSelect1 As String
strSelect1 = "SELECT Bill_Code, Dispersmts, Fri_Hrs, Job_Descrn,
Job_Number, Mileage," & _
"Mon_Hrs, Period_End_Date, PO_Order, Sat_Hrs, Sun_Hrs,
Thr_Hrs, Tue_Hrs," & _
"Wed_Hrs, Work_Descrn FROM SHENHrsTable"
Dim daShenData As New OleDbDataAdapter(strSelect1, OleDbConnection1)

' Dataset 1
Dim dsShenData As New DataSet

' Fill Dataset 1
daShenData.Fill(dsShenData.Tables("SHENHrsTable"))
'''daShenData.FillSchema(dsShenData, SchemaType.Source, "SHENHrsTable")
********************************************************************

QUESTION #1:
How do I fix the No Null value issue (why is it doing this anyways)?

QUESTION #2:
When I get this to work, where is the best place to place this code in my
application? In the Form Load? Between Form Load and "Windows Form Designer
generated code"? In a Module? In a Public Sub? This is because I open and
edit the Data bases elsewhere so I don't really know how to make it more
'globally' available.

QUESTION #3:
(a bit off topic) Or should I forget the whole thing... stick with my DATA
Icons... and use the SUBST command to create Virtual Drives and Fake it that
way?

Thanks muchly

BruceF

 

[Andrew Morton]

Imports System.IO
Imports System.Data.OleDb

' Connection String1 to SHENTSdata.mdb
Dim connString1 As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=TSdbPath & SHENTSdata.mdb"

Shouldn't that be
Dim connString1 As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
& Path.Combine(TSdbPath, SHENTSdata.mdb)

....and where have you defined TSdbPath?


If I'm accessing a resource which is in a different location between the
development and deployment environments, I keep separate web.config files
for each environment and store the appropriate string in each.

So, in web.config I might have:-
<configuration>
<appSettings>
<add key="sqlConnectionString" value =
"Server=WHERE\EVER;Database=db;Trusted_Connection=true" />
</appSettings>

etc, and a different value in the other web.config.

and to retrieve the value:-
Private sqlConnectionString As String =
System.Configuration.ConfigurationSettings.AppSettings("sqlConnectionString")

HTH

Andrew

 

[Stephen Howe]

The Access files were located on drives "P" and "S" at work, so I had
VB.net

installed on my work PC so I could change the drives in the Connection Icons.

Now I no longer work at that place and they want me to make some changes. Of
course I can't as I don't have a drive P or S on my system.

What stops you from right-clicking on "My Network Places" and mapping an
appropriate pathed subdirectory on your hard disk as P or S drive?

QUESTION #3:
(a bit off topic) Or should I forget the whole thing... stick with my DATA
Icons... and use the SUBST command to create Virtual Drives and Fake it that
way?

SUBST will effectively do the same thing

Stephen Howe

 

[Brendan Reynolds]

In addition to the other points that have been made, while you are declaring
the connString1 variable and assigning a value to it, nowhere in the code
that you have posted do you actually use it. It is not passed to the
connectionString argument when instantiating the OleDbConnection object or
assigned to the ConnectionString property of the object.

 

[Paul Clement]

¤ VB.net 2003; MS Access db; Netframe 1.1
¤
¤ I wrote an Windows app for my last place of employment that opened and
¤ modified a couple of MS Access db files. I did most of my programming at home
¤ on Drive "C". And I used the Drag/Drop Data Items to create the Connection,
¤ DataSet and DataAdapters.
¤
¤ The Access files were located on drives "P" and "S" at work, so I had VB.net
¤ installed on my work PC so I could change the drives in the Connection Icons.
¤
¤ Now I no longer work at that place and they want me to make some changes. Of
¤ course I can't as I don't have a drive P or S on my system. So I'm attempting
¤ to get rid of the Data Icons and do the connection, Data set and adapters with
¤ Code. And of course I'm getting all kinds of errors.
¤
¤ So I'm doing a Test app to find/fix my items. Right off the bat I can't even
¤ establish a proper connection to a DB. The following is that code I'm using.
¤
¤ I get an ERROR at the FILL line (unhandled exception where "Value cannot be
¤ null". But the access file opens just fine if I use the DATA Icons.
¤
¤ And... If I change the Fill line to:
¤ daShenData.FillSchema(dsShenData, SchemaType.Source, "SHENHrsTable")
¤ ... the error then says "The ConnectionString property has not been
¤ initialized".
¤
¤ ************** CODE ***********************************************
¤ Imports System.IO
¤ Imports System.Data.OleDb
¤
¤ ' Connection String1 to SHENTSdata.mdb
¤ Dim connString1 As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
¤ Source=TSdbPath & SHENTSdata.mdb"
¤
¤ Dim OleDbConnection1 As OleDbConnection = New OleDbConnection
¤
¤ ' Data Adapter 1
¤ Dim strSelect1 As String
¤ strSelect1 = "SELECT Bill_Code, Dispersmts, Fri_Hrs, Job_Descrn,
¤ Job_Number, Mileage," & _
¤ "Mon_Hrs, Period_End_Date, PO_Order, Sat_Hrs, Sun_Hrs,
¤ Thr_Hrs, Tue_Hrs," & _
¤ "Wed_Hrs, Work_Descrn FROM SHENHrsTable"
¤ Dim daShenData As New OleDbDataAdapter(strSelect1, OleDbConnection1)
¤
¤ ' Dataset 1
¤ Dim dsShenData As New DataSet
¤
¤ ' Fill Dataset 1
¤ daShenData.Fill(dsShenData.Tables("SHENHrsTable"))
¤ '''daShenData.FillSchema(dsShenData, SchemaType.Source, "SHENHrsTable")
¤ ********************************************************************
¤
¤ QUESTION #1:
¤ How do I fix the No Null value issue (why is it doing this anyways)?
¤
¤ QUESTION #2:
¤ When I get this to work, where is the best place to place this code in my
¤ application? In the Form Load? Between Form Load and "Windows Form Designer
¤ generated code"? In a Module? In a Public Sub? This is because I open and
¤ edit the Data bases elsewhere so I don't really know how to make it more
¤ 'globally' available.
¤
¤ QUESTION #3:
¤ (a bit off topic) Or should I forget the whole thing... stick with my DATA
¤ Icons... and use the SUBST command to create Virtual Drives and Fake it that
¤ way?
¤

Where is the line of code where you Open the OleDbConnection1 object?

Typically you open and close connections as you need them instead of maintaining persistent
connections. In this instance you probably want to add a Sub or Function to your Form and call it
from your Form Load event.

You can use the data connection objects (DATA Icons you called them) you created with the wizard,
but you need to change the connection string property at runtime (either in your Sub Main or Form
Load event).


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Mr. B]

With Deft Fingers, Paul Clement <[email protected]>
wrote:

Where is the line of code where you Open the OleDbConnection1 object?

So far it all has been in the Form Load. Just as I posted it.

Typically you open and close connections as you need them instead of maintaining persistent
connections. In this instance you probably want to add a Sub or Function to your Form and call it
from your Form Load event.

Okay. Using the 'Data Icons' I pretty well didn't have to do that as such.

You can use the data connection objects (DATA Icons you called them) you created with the wizard,
but you need to change the connection string property at runtime (either in your Sub Main or Form
Load event).

Thanks Paul!

BruceF

 

[Mr. B]

Shouldn't that be
Dim connString1 As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
& Path.Combine(TSdbPath, SHENTSdata.mdb)

Well... good point. And the answer is I don't know. First off, I never got
any erry at that point, so I guess (to me) that it was okay.

But I just tried that and my "daShenData.FillSchema" line gives an error.

...and where have you defined TSdbPath?

It was a typical DIM statement after the "Inherits System.Windows.Forms.Form".
I just didn't think that was relevant (mind you I can see that you guys might

If I'm accessing a resource which is in a different location between the
development and deployment environments, I keep separate web.config files
for each environment and store the appropriate string in each.

Thanks Andrew... that's probably how I'll go.

Regards,

BruceF

 

[Mr. B]

What stops you from right-clicking on "My Network Places" and mapping an
appropriate pathed subdirectory on your hard disk as P or S drive?

Well you know... I didn't even think about trying that. For the simple reason
that I'm now working ot of my Home (SOHO). And thus I don't have any
'network'. So while I am very familiar with that method, I just didn't even
think about it for Home use. But for sure, a much better way of doing it (I
was raised up on DOS <grin>).

Thanks!

Regards,

BruceF

 

[Mr. B]

In addition to the other points that have been made, while you are declaring
the connString1 variable and assigning a value to it, nowhere in the code
that you have posted do you actually use it. It is not passed to the
connectionString argument when instantiating the OleDbConnection object or
assigned to the ConnectionString property of the object.

Okay Brendan... you kind of lost me here on what you said

What do you mean that it is not pass to the ConnectionString argument?

Regards,

BruceF

 

[Stephen Howe]

Okay Brendan... you kind of lost me here on what you said

What do you mean that it is not pass to the ConnectionString argument?

Have a look at the code you posted.

See where you initialise connString1?
Now where is connString1 used in OleDbConnection1 below? Don't see it.

Stephen Howe

 

[Mr. B]

If I'm accessing a resource which is in a different location between the
development and deployment environments, I keep separate web.config files
for each environment and store the appropriate string in each.

Ah... hehe... do you happen to have the code to do this in a Windows app?
I've never done a Web app. (sigh). Menwhile I'm searching the Web for the
method!

Regards,

BruceF

 

[Stephen Howe]

It was a typical DIM statement after the "Inherits
System.Windows.Forms.Form".

I just didn't think that was relevant (mind you I can see that you guys might
have thought that I missed that <grin>).

You will get better answers from people if the information you first post is
_complete_, nothing missed out, all variables mentioned with declaration,
all intervening lines etc. Miss any lines of code out and the responses back
will on those very lines missed out (could they be the cause of your
problems?).

Stephen Howe

 

[Brendan Reynolds]

The OleDbConnection class has two constructors. One takes no arguments, the
second takes a connectionString argument. The OleDbConnection class also has
a ConnectionString property. In order to tell the object what connection
string you want it to use, you need to either use the second constructor and
pass the connection string in the connectionString argument ...

Dim connString1 As String = "a valid connection string goes here"
Dim OleDbConnection1 As New OleDbConnection(connString1)

.... or, if you use the constructor without arguments, assign the connection
string to the ConnectionString property ...

Dim connString1 As String = "a valid connection string goes here"
Dim OleDbConnection1 As New OleDbConnection
OleDbConnection1.ConnectionString = connString1

Here's a link to the on-line documentation on the OleDbConnection class ...
http://msdn.microsoft.com/library/en-us/cpref/html/frlrfSystemDataOleDbOleDbConnectionClassTopic.asp

 

[Mr. B]

The OleDbConnection class has two constructors. One takes no arguments, the
second takes a connectionString argument. The OleDbConnection class also has
a ConnectionString property. In order to tell the object what connection
string you want it to use, you need to either use the second constructor and
pass the connection string in the connectionString argument ...

Thanks Brendan... but you know... I ended up and revised my code (using an
example of what I found on the Internet which simplified the lines of code).
The 'connection' part is just like you and Stepen said.

Interestingly enough, I STILL had an error at the DataSet Fill part... So I
played around with things for quite a while and then I 'accidently' discovered
the Problem (maybe you can explain this one).

Originally I had the following:

Dim dbPath As String = "C:\SHEN-EDL\Administration\Data\"

Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=dbPath & SHENTSdata.mdb"


What I discovered, was for what ever reason, my dbPath String did NOT work in
the Connection String. So I added the full path in the String...

Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\SHEN-EDL\Administration\Data\SHENTSdata.mdb"

POOF! Success! So no more dbPath strings (unless you can see the fix).

Go figure! You learn something every day (sometimes the hard way and many
hours later).

Regards,

BruceF

 

[Andrew Morton]

Originally I had the following:

Dim dbPath As String = "C:\SHEN-EDL\Administration\Data\"

Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=dbPath &
SHENTSdata.mdb"


What I discovered, was for what ever reason, my dbPath String did NOT
work in the Connection String. So I added the full path in the
String...

Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\SHEN-EDL\Administration\Data\SHENTSdata.mdb"

POOF! Success! So no more dbPath strings (unless you can see the
fix).

The problem is that it doesn't know that the dbPath *inside* the quotes is
meant to refer to a variable, so you have to close the quotes, & the
variable in and then open the quotes again, like this:-

Dim dbPath As String = "C:\SHEN-EDL\Administration\Data\"

Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
dbPath & "SHENTSdata.mdb"

Although for combining paths, it's better to use Path.Combine as it takes
care of adding in the path separator for you:-

Dim dbPath As String = "C:\SHEN-EDL\Administration\Data\"

Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
Path.Combine(dbPath, "SHENTSdata.mdb")

(watch out for line
wrap)

Andrew

 

[Paul Clement]

¤ With Deft Fingers, Paul Clement <[email protected]>
¤ wrote:
¤
¤
¤ >Where is the line of code where you Open the OleDbConnection1 object?
¤
¤ So far it all has been in the Form Load. Just as I posted it.

I looked again and didn't see something that looks like the following:

OleDbConnection1.Open(connString1)

The bottom line is that according to the code you posted the connection is never opened.


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Mr. B]

The problem is that it doesn't know that the dbPath *inside* the quotes is
meant to refer to a variable, so you have to close the quotes, & the
variable in and then open the quotes again, like this:-

Yeah... kinda thought that!

Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
Path.Combine(dbPath, "SHENTSdata.mdb")

Thanks Andrew... Path.Combine is a New one for me (so much still to learn).
Very appreciated!

I was going to try to have one variable (path and mdb file) and throw that
into the Conn string.

Regards,

BruceF