locked
dynamic Access connection string from Net code-behind RRS feed

  • Question

  • User-1679916991 posted

    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\2000eventdb388.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.

    Wednesday, October 5, 2011 12:17 PM

Answers

  • User3866881 posted

    Hello:)

    I think you are using Windows 7, am I right?

    If I'm right, I think because Users is a very special folder that needs permission.. Once you cannot reach to that, that problem will be raised. So that's what you've seen.

    The solution is that you can copy your mdb to D:\ or other folder except "Users", but you must be sure that it's allowing permission.

    Thx anyway.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 6, 2011 10:21 PM