OleDbConnection syntax error

[msch-prv]

I'm having a format error with OleDbConnection (Format of the
initialization string does not conform to specification starting at
index 0) with the following. (The line referred to by the compiler isI
flagged with: <-- problem) . I use ASP.NET 2.0.

TIA for any hints, Mark

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>

Dim objDataSet As New DataSet()
Dim objDataAdapter As New OleDbDataAdapter()
Dim strConnection As String =
ConfigurationManager.ConnectionStrings("DbConn").ConnectionString

Dim objConnection As New OleDbConnection(strConnection) <-- format
error

objConnection.Open()
Dim objCommand As New OleDbCommand(strSQL, objConnection)
objDataAdapter.SelectCommand = objCommand
objDataAdapter.Fill(objDataSet)

grd2.DataSource = objDataAdapter.SelectCommand
grd2.DataBind()

where (web.config):
<connectionStrings>
<add name="DbConn"
connectionString="~/App_Data/CRM2K_be.mdb"
providerName="System.Data.OleDb"/>
</connectionStrings>

 

[msch-prv]

I found a workaround, but this is contrary to the samples I have seen
so far.

The following works:

Dim objConnection As New
OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;" _
& "DATA SOURCE=" &
Server.MapPath("App_Data/CRM2K_be.mdb;"))

However, with DbConn defined in web.config as above yields an error::

Dim strConn As String =
ConfigurationManager.ConnectionStrings("DbConn").ConnectionString
Dim objConnection As New OleDbConnection(strConn)

fails presumably because it returns a fraction of the connection
string, since:

Response.Write(strConn) = '~/App_Data/CRM2K_be.mdb'

What is the correct syntax for web.config declarations? TIA, Mark

 

[Christopher Reed]

I believe the correct connection string should be:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;"

Additionally, the "~" is not understood by OleDB because it's a server
construct for ASP.NET.

Hope this works!

 

[msch-prv]

Thanks Christopher,

It works now after editing the connection strings in web.config:

<connectionStrings>
<add name="DbConn"
connectionString="PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA
SOURCE=|DataDirectory|\CRM2K_be.md"
providerName="System.Data.OleDb"/>
</connectionStrings>