none
Using Windows Forms, am I able to browse to an MS Access DB and use the path for the db connection? RRS feed

  • Question

  • I basically want to take a 10 step procedure and turn it into 3....  I'm trying to write a query to select various records from multiple db's one at a time and write records to a txt file. Browse to DB, select records, write selected records to new txt file, and then start over using a new DB. My question is, in my application,

    //here is how I'm opening DB public void GetPath() { DialogResult dr = openFileDialog1.ShowDialog(); if (dr == DialogResult.OK) txtDatabasePath.Text = openFileDialog1.FileName;

    }


    when browsing to DB, can I use path each time for my DB connection? I only want to write code for this once and use it multiple times. FYI: Code here works, I just haven't figured out how to plug in the paths to the DB's variably.

    Thanks in advanced

    Justin

    private string _path = @"Q:\JIP Workflows\204320National\Data\";
    private string _outputSpoilTextFileName = "OutputSpoils.txt";
    
            private void OutputSpoils(DataTable dtSpoils)
            {
                using (var sw = new StreamWriter(_path + _outputSpoilTextFileName))
                {
                    sw.WriteLine("Key|Sequence|Barcode_2d|BallotType");
                    foreach (DataRow row in dtSpoils.Rows)
                    {
                        sw.WriteLine(row[0].ToString() + "|" + row[1].ToString() + "|" + row[2].ToString() + "|" + row[3].ToString());
                    }
                }
            }
    
    private DataTable Retrieve(string range1, string range2)
            {
                string tableName = FindAllTables(_path);
    
                OleDbConnection accessConn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + _path + "204320-National.MDB");
                DataTable userTables = null;
                //we want user tables only, not system tables
                string[] restrinctions = new string[4];
                restrinctions[3] = "Table";
                OleDbCommand cmd = new OleDbCommand(@"SELECT id,sequence,barcode_2d,ballottype FROM Table Name_File where barcode_2d>=@range1 and barcode_2d<=@range2", accessConn);
                OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                da.SelectCommand.Parameters.Add("range1", SqlDbType.NVarChar).Value = range1;
                da.SelectCommand.Parameters.Add("range2", SqlDbType.NVarChar).Value = range2;
                DataTable dt = new DataTable();
                da.Fill(dt);
    
                return dt;
            }
    
     public static string FindAllTables(string path)
            {
                OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + path + "204320-National.MDB");
    
                DataTable userTables = null;
                //we want user tables only, not system tables
                string[] restrinctions = new string[4];
                restrinctions[3] = "Table";
                conn.Open();
                userTables = conn.GetSchema("Tables", restrinctions);
                conn.Close();
    
                return userTables.Rows[0]["Table_name"].ToString();


    • Edited by Justin_E Sunday, July 19, 2015 9:22 AM
    • Moved by Kristin Xie Monday, July 20, 2015 7:18 AM more related to Ado.net
    Sunday, July 19, 2015 9:17 AM

Answers

  • What was the problem with my suggested approach?

    .

    Define a string variable.

    private string DatabaseFullPath;

    Obtain the path to the database

    DatabaseFullPath = openFileDialog1.FileName;

    Use that to open the database

    using (OleDbConnection conn = new OleDbConnection(@"Provider=...Data Source=" 
    + DatabaseFullPath
    )


    Sunday, July 19, 2015 7:12 PM

All replies

  • Hey Justin.

    If I understand your question correctly, you're pretty much there. Which makes me wonder whether I've misunderstood. But anyhow.

    You already have this:

    OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" 
    + path 
    + "204320-National.MDB");
    

    And you have

    txtDatabasePath.Text = openFileDialog1.FileName;

    If you declare a string variable with scope to your window and stash the path away  in there.

    Isn't that going to be what you want for your DataSource?

    Call that DatabaseFullPath.

    Can't you just do:

    using (OleDbConnection conn = new OleDbConnection(@"Provider=...Data Source=" 
    + DatabaseFullPath
    )



    Sunday, July 19, 2015 9:45 AM
  • What is the relationship between your text file and mdb? I think your question is related to your text file as well. Probably people here don't really understand what are you trying to do.

    chanmm


    chanmm

    Sunday, July 19, 2015 10:55 AM
  • Dang, I'm not sure, I'm trying to use someone else's code, it seems pretty easy to do, but I'm having trouble with it maybe I'll just start from scratch. I haven't been coding C# for very long. I appreciate your help.
    Sunday, July 19, 2015 4:07 PM
  • the text file is being exported(pipe delimited) from mdb to be processed in another application. The issue is that the mdb's location, file name and fields are never the same. 

    What I'm trying to do is this:

    here is an example of what the txt file looks like.

    KEY|Sequence|StCode|City|County
    1|1|IA|Craig|Plymouth
    2|2|TN|Reagan|Henderson
    3|3|IL|Saint Joseph|Champaign
    4|4|NE|Harvard|Clay
    5|5|MO|Bell City|Stoddard
    6|6|MO|Osborn|Dekalb
    7|7|LA|Big Bend|Avoyelles
    8|8|WI|Medina|Outagamie
    9|9|NC|Pollocksville|Jones
    10|10|WA|Cunningham|Adams

    Run Application(in question)

    this is an example of the what the txt file(new copy) should look like after running my application. These records are selected by the user either in a range i.e. 1-5, or singly 1, 4, 10

    KEY|Sequence|StCode|City|County
    1|1|IA|Craig|Plymouth
    4|4|NE|Harvard|Clay
    10|10|WA|Cunningham|Adams

    So what I would like to know how can I use the openFileDialog to use for my DB connection, or am I going about this all wrong??? I'm sure there is a better way, but the output result has to be accurate


    • Edited by Justin_E Sunday, July 19, 2015 4:26 PM
    Sunday, July 19, 2015 4:24 PM
  • Are the database locations known beforehand? If so, you could put the Connection string for each database in the config file, like this:

    <connectionStrings>
     <add name="DB1" connectionString="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\somedirectory1\204320-National.MDB"/>
     <add name="DB2" connectionString="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\somedirectory2\Two-National.MDB"/>
     <add name="DB3" connectionString="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\somedirectory3\Three-National.MDB"/>
    </connectionStrings>

    And in your code, loop through the connection strings to process each database:

    foreach (var ConnString in ConfigurationManager.ConnectionStrings)
    {
        // process each database using its ConnString
    }
    


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Sunday, July 19, 2015 5:02 PM
  • Unfortunately, they're not. That's my issue. That's why I want to navigate to the mdb with explorer and use that path as the connection string.
    Sunday, July 19, 2015 5:24 PM
  • What was the problem with my suggested approach?

    .

    Define a string variable.

    private string DatabaseFullPath;

    Obtain the path to the database

    DatabaseFullPath = openFileDialog1.FileName;

    Use that to open the database

    using (OleDbConnection conn = new OleDbConnection(@"Provider=...Data Source=" 
    + DatabaseFullPath
    )


    Sunday, July 19, 2015 7:12 PM
  • I'll give it a shot...

    Thanks very much all, for your help.

    Sunday, July 19, 2015 8:15 PM