Create dynamic connection string to Access

[janwane]

I have posted this question in asp.net forums and not gotten a single response. It regards creating a dynamic connection string to a uniquely-named Access db in my Net program which I was able to do in classic ASP:

I am converting classic asp app to Net. Unique Access database is copied from empty shell & given unique name based on EventID (example 2000eventdb388.mdb. "388" is the EventID). Data for same EventID is pulled from SQL db and appended to same tables in the new Access db. I am having terrible time making dynamic connection string work. In classic asp this is code:

csEventID=cstr(request("EventID"))
csDB="d:\hosting\natrc5\EventDB\2000eventdb.mdb"
csDBcopy="d:\hosting\natrc5\EventDB\2000eventdb" & csEventID & ".mdb"
If objFSO.FileExists(csDBcopy)=True then
objFSO.DeleteFile "d:\hosting\natrc5\EventDB\2000eventdb" & csEventID & ".mdb", False
End If
objFSO.CopyFile csDB, csDBcopy
Set aConn = Server.CreateObject("ADODB.Connection")
aConn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & csDBcopy & ";"
(read from SQL table, insert to Access table until all tables for this event are copied)

In Net I have tried ADO & OleDb. It works fine when I go in thru "front door" and build an SQLDataSource in the aspx program which builds a connection string in my web.config file BUT it's to a hard-coded Access db name. I need to build the connection string in my .cs program. So I have carefully copied the syntax into this code in my "rowcommand" module:

string csPath2 = Server.MapPath("eventdb\\");
if (e.CommandName == "Build2000")
{
csDBempty = "2000eventdb.mdb";
csDBmdb = "2000eventdb" + Session["SelEventID"].ToString() + ".mdb";
csDBzip = "2000eventdb" + Session["SelEventID"].ToString() + ".zip";
DirectoryInfo dirInfo2000 = new DirectoryInfo(@csPath);
System.IO.FileInfo[] fileNamesZip2000 = dirInfo2000.GetFiles(csDBzip);
if (fileNamesZip2000.GetLength(0) > 0)
{
File.Delete(@csPath2 + csDBzip);
}
System.IO.FileInfo[] fileNamesDB2000 = dirInfo2000.GetFiles(csDBmdb);
if (fileNamesDB2000.GetLength(0) > 0)
{
File.Delete(@csPath2 + csDBmdb);
}
File.Copy(@csPath2 + csDBempty, @csPath2 + csDBmdb);
//build new Access db from SQL db
OleDbConnection aConn = new OleDbConnection();
aConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + @csPath2 + csDBmdb;



//BreedAbbrLookup
strSQL=" SELECT * FROM BreedAbbrLookup";
DataView dv = (DataView)SDS_BreedLookup.Select(DataSourceSelectArguments.Empty);
if (!Convert.IsDBNull(dv) && dv.Count > 0)
{
aConn.Open();
foreach (DataRowView drv in dv)
{
string csBRID = drv["BreedAbbrID"].ToString();
string csBR = TickedString.ts(drv["BreedAbbr"].ToString());
string csBD = TickedString.ts(drv["BreedDef"].ToString());
strSQL = " INSERT INTO NATRC5.BreedAbbrLookup (" +
" BreedAbbrID, BreedAbbr, BreedDef)" +
" VALUES (" + csBRID + ", " + csBR + ", " + csBD + ")";
cmd = new OleDbCommand(strSQL, aConn);
cmd.ExecuteNonQuery();
}
aConn.Close();
}

The "@csPath2" works just fine for the File.Delete and File.Copy commands. In debug it shows the correct path for the connection string: Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Users\AACL_IT\Documents\Visual Studio 2008\WebSites\NATRC5net\rmsnet\eventdb\2000eventdb 388.mdb. The aConn.Open() executes without error.

However when it gets to the cmd.ExecuteNonQuery() it bombs out saying "Could not find file 'C:\Program Files\Common Files\Microsoft Shared\DevServer\10.0\NATRC5.mdb'. Can ANYONE help me with this? Thank you.

[June7]

Don't see how you will have better luck getting answer in an Access forum. Have you tried this one http://forums.aspfree.com/net-development-11/

[janwane]

I had not found that particular forum. Thanks for pointing it out.