Multipart connectionstring question

[Guest]

Hi all,

This is a multipart question.

I’ve got an asp.net site that contains an access database. Previously, I
would debug by making changes and uploading to my host. Now, I’d like to be
able to debug locally and then deploy. I’d really like to use a relative path
for the database file (so I could make no changes between hostide and
debugging client side. When I try to use a relative path, it doesn’t seem to
be referring to the basepoint of my web application as it would for, say, an
href or src attribute. I get an exception stating:

System.Data.OleDb.OleDbException was unhandled by user code
ErrorCode=-2147467259
Message="'C:\Program Files\Microsoft Visual Studio
8\Common7\IDE\App_Data\xc.mdb' is not a valid path. Make sure that the path
name is spelled correctly and that you are connected to the server on which
the file resides."
Source="Microsoft JET Database Engine"

The connection string I used was:

Provider=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=App_Data\xc.mdb

I also tried

Provider=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=\App_Data\xc.mdb

And got the same result.

On the server side, I get a different long path. I figured out how to
construct this log path into what corresponds with the base point of my
application and could hardcode the application setting. This prevents me from
running the same app both on my machine and my host without making changes. I
suppose I could use server.mappath around every DB call, but I was wondering
if there was a more elegant way to handle this situation (to get the “mappedâ€
path into the appsettings value the first time).

How could one even use a server.mappath with a database connection string?
As the path to the database is embedded within the connection string?

Ok, next related question. I wanted to try to take advantage of the new
ConnectionStrings feature in 2.0, so I changed my appsettings to a connection
string. (For now, I’m using a hardcoded path to test.) The connection string
works when I grab it as an appsetting, but when I try to copy it over as a
connection string and grab it with:

conXCDB = New
OleDbConnection(Server.MapPath(System.Configuration.ConfigurationManager.ConnectionStrings("MyMainString").ConnectionString))

I get:

System.Web.HttpException was unhandled by user code
ErrorCode=-2147467259
Message="'Provider=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=C:\xcbackup.mdb' is
not a valid virtual path."
Source="System.Web"

Interestingly, I’m able to response.write the “ConnectionString†property to
the output and it is indeed the exact same string as before. Why now the
complaints? I used:

<add name="MyMainString"
connectionString="Provider=Microsoft.Jet.OLEDB.4.0;DATA
SOURCE=C:\xcbackup.mdb" providerName="System.Data.OleDB" />

I don’t know if I set the providerName properly, but I don’t know why it
would even need this as it should just be grabbing the string so I can create
a connection.

Ok, so I thought maybe I’m constructing the string incorrectly, so I try to
use the server explorer to add the connection. I choose “Microsoft Access DB
file†which chooses .NET framework data provider for OLEDB. Sounds good. I
press next, and instead of different fields for each property (filename,
timeout settings, etc), there is only one property to set:
“ConnectionString.†I try pasting my working string in there, but I get:

“Format of the initialization string does not conform to specification
startingn at index 0.â€

I’ve tried adding single / double quotes, and lots of other things. I’ve
never been able to find ANY input that doesn’t give this error. Whenever I
press “test connection†it immediately reports that it sccedded. It’s when I
press “ok†that it gives me trouble. After googling around, this error seems
to have been resolved by one other by reinstalling the framework, and another
indicated that he only got this on his XP machine.

I tried another route:

I chose “other†for data source and chose “Data provider for OLEDB for data
provider.†in the next screen, I chose “MS Jet 4.0 OLEDB provider†as this is
what I was using in the string. This lets me specify properties graphically.
Under server or file name, I specify an absolute path, and leave everything
else blank. I hit ok and get an error dialog stating:

“Object reference not set to an instance of an object.â€

I’ve tried filling out various values, but no matter what I do in this
dialog I get this error and am unable to proceed.

Well, that’s where I stand. I know there’s a few different questions here,
but any help would be much appreciated. Thanks guys…

-Ben

 

[Kevin Yu [MSFT]]

Hi Ben,

To use a MapPath to map your database file in the connection string, you
have to

1. Map the path
2. Concatenate the path to the connection string

For example:

Dim path as String = Server.MapPath("App_Data\xc.mdb")
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" + path

If you get the connection string directly from an app.config file, you
cannot pass it directly to MapPath as an argument. The argument must be a
valid path. In this case, you have to parse it yourself and re-create the
connection string with the mapped path. Or you can use different app.config
files on debug and deploy.

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Guest]

Thanks for your response, Kevin. Do you have any insight regarding the
difficulty that I encountered when I tried to add the connection via the
Server Explorer? Or why I'm unable to use the connectionstring property but
am able to use an appsettings string?

Thanks Kevin...

-Ben

-Ben

 

[Kevin Yu [MSFT]]

Hi Ben,

What do you mean by adding the connection via the Server Explorer? The
appsetting string is used to get the ConnectionString from the app.config
file. In this case, it's better to put different connection strings in
different app.config files. Then you needn't change your code when
debugging and deploying.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Guest]

Hi Kevin,

What I meant by "adding the connection via the server explorer" is:

in the Server Explorer pane: Right click Data Connections and select "Add
New Connection."

This text from my original post should describe the problem:

Ok, so I thought maybe I’m constructing the string incorrectly, so I try to
use the server explorer to add the connection. I choose “Microsoft Access DB
file†which chooses .NET framework data provider for OLEDB. Sounds good. I
press next, and instead of different fields for each property (filename,
timeout settings, etc), there is only one property to set:
“ConnectionString.†I try pasting my working string in there, but I get:

“Format of the initialization string does not conform to specification
startingn at index 0.â€

I’ve tried adding single / double quotes, and lots of other things. I’ve
never been able to find ANY input that doesn’t give this error. Whenever I
press “test connection†it immediately reports that it sccedded. It’s when I
press “ok†that it gives me trouble. After googling around, this error seems
to have been resolved by one other by reinstalling the framework, and another
indicated that he only got this on his XP machine.

I tried another route:

I chose “other†for data source and chose “Data provider for OLEDB for data
provider.†in the next screen, I chose “MS Jet 4.0 OLEDB provider†as this is
what I was using in the string. This lets me specify properties graphically.
Under server or file name, I specify an absolute path, and leave everything
else blank. I hit ok and get an error dialog stating:

“Object reference not set to an instance of an object.â€

I’ve tried filling out various values, but no matter what I do in this
dialog I get this error and am unable to proceed.


Thanks...

-Ben

 

[Guest]

Update: On my work computer running the same edition of VS 2005
(Professional), I can successfully add the db connection. Interestingly, the
dialog that I'm presented with to do so for an access file is very different
on my work machine. It has individual fields, allows me to browse for the
file, etc. The one on my other computer where it fails, only allows me to
type in a connection string. It's as though a component didn't install
properly. Is there anything I can check?

-Ben

 

[Kevin Yu [MSFT]]

Hi Ben,

This seems to be weired. I can get the dialog for your second way, but
never get the dialog box for you to enter connection string. Maybe you can
show us a screen shot for it.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Guest]

Hi Kevin,

I appreciate the offer, but I don't know of a way to attach a file via the
web news reader. I think I'll just have to be happy generating connection
strings by hand and using them manually.

Thanks again...

-Ben

 

[Kevin Yu [MSFT]]

You're welcome.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."