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.Fill Schema(dsShenDa ta, SchemaType.Sour ce, "SHENHrsTab le")
.... the error then says "The ConnectionStrin g property has not been
initialized".

************** CODE *************** *************** *************** **
Imports System.IO
Imports System.Data.Ole Db

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

Dim OleDbConnection 1 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 OleDbDataAdapte r(strSelect1, OleDbConnection 1)

' Dataset 1
Dim dsShenData As New DataSet

' Fill Dataset 1
daShenData.Fill (dsShenData.Tab les("SHENHrsTab le"))
'''daShenData.F illSchema(dsShe nData, SchemaType.Sour ce, "SHENHrsTab le")
*************** *************** *************** *************** ********

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]

Mr. B wrote:

Imports System.IO
Imports System.Data.Ole Db

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

Shouldn't that be
Dim connString1 As String = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source="
& Path.Combine(TS dbPath, 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:-
<configuratio n>
<appSettings>
<add key="sqlConnect ionString" value =
"Server=WHERE\E VER;Database=db ;Trusted_Connec tion=true" />
</appSettings>

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

and to retrieve the value:-
Private sqlConnectionSt ring As String =
System.Configur ation.Configura tionSettings.Ap pSettings("sqlC onnectionString ")

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
connectionStrin g argument when instantiating the OleDbConnection object or
assigned to the ConnectionStrin g property of the object.

--
Brendan Reynolds

"Mr. B" <Us**@NoWhere.c om> wrote in message
news:ll******** *************** *********@4ax.c om...

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.Fill Schema(dsShenDa ta, SchemaType.Sour ce, "SHENHrsTab le")
... the error then says "The ConnectionStrin g property has not been
initialized".

************** CODE *************** *************** *************** **
Imports System.IO
Imports System.Data.Ole Db

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

Dim OleDbConnection 1 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 OleDbDataAdapte r(strSelect1, OleDbConnection 1)

' Dataset 1
Dim dsShenData As New DataSet

' Fill Dataset 1
daShenData.Fill (dsShenData.Tab les("SHENHrsTab le"))
'''daShenData.F illSchema(dsShe nData, SchemaType.Sour ce, "SHENHrsTab le")
*************** *************** *************** *************** ********

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

[Paul Clement]

On Tue, 06 Dec 2005 20:38:29 GMT, Mr. B <Us**@NoWhere.c om> wrote:

¤ 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.Fill Schema(dsShenDa ta, SchemaType.Sour ce, "SHENHrsTab le")
¤ ... the error then says "The ConnectionStrin g property has not been
¤ initialized".
¤
¤ ************** CODE *************** *************** *************** **
¤ Imports System.IO
¤ Imports System.Data.Ole Db
¤
¤ ' Connection String1 to SHENTSdata.mdb
¤ Dim connString1 As String = "Provider=Micro soft.Jet.OLEDB. 4.0;Data
¤ Source=TSdbPath & SHENTSdata.mdb"
¤
¤ Dim OleDbConnection 1 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 OleDbDataAdapte r(strSelect1, OleDbConnection 1)
¤
¤ ' Dataset 1
¤ Dim dsShenData As New DataSet
¤
¤ ' Fill Dataset 1
¤ daShenData.Fill (dsShenData.Tab les("SHENHrsTab le"))
¤ '''daShenData.F illSchema(dsShe nData, SchemaType.Sour ce, "SHENHrsTab le")
¤ *************** *************** *************** *************** ********
¤
¤ 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 OleDbConnection 1 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 <Us************ ***********@sws pectrum.com>
wrote:

Where is the line of code where you Open the OleDbConnection 1 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]

With Deft Fingers, "Andrew Morton" <ak*@in-press.co.uk.inv alid> wrote:

' Connection String1 to SHENTSdata.mdb
Dim connString1 As String = "Provider=Micro soft.Jet.OLEDB. 4.0;Data
Source=TSdbPath & SHENTSdata.mdb"
Shouldn't that be
Dim connString1 As String = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source="
& Path.Combine(TS dbPath, 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.Fil lSchema" 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
have thought that I missed that <grin>).
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]

With Deft Fingers, "Stephen Howe" <stephenPOINTho weATtns-globalPOINTcom>
wrote:

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?

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]

With Deft Fingers, "Brendan Reynolds" <br******@discu ssions.microsof t.com>
wrote:

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
connectionStri ng argument when instantiating the OleDbConnection object or
assigned to the ConnectionStrin g 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 ConnectionStrin g 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 ConnectionStrin g argument?

Have a look at the code you posted.

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

Stephen Howe