Dataset won't filll?!

[sundog1]

Hi Guys...

Putting together a Tiny little app which shows VPN Details for use 'In-House' but for some strange reason my DataSet won't fill?

Can anyone spot the mistake? :S

        private void Form1_Load(object sender, EventArgs e)
        {

       //XML Load of Document. This loads the XML Document and the value of a Single Node then inputs this to a text box//
            XmlDocument doc = new XmlDocument();
            doc.Load("C:\\vpn.xml");
            XmlNode node = doc.SelectSingleNode("/DataBases/VPN_Access/vpn_DB");
            this.txtBoxDebug.Text = node.InnerText;

            //Creates new versions of the Connection string and Data Set//
            con = new System.Data.OleDb.OleDbConnection();
            ds = new DataSet();

            //The actual connection to the database//
            con.ConnectionString = "PROVIDER=Microsoft.ACE.OLEDB.12.0;Data Source =" + txtBoxDebug.Text.ToString();

            //The SQL String you need to pass into the Data Adapter to collect the information//
            string vpnDetails = "SELECT * FROM VPN";
            da = new System.Data.OleDb.OleDbDataAdapter(vpnDetails, con);

            //The Data Adapater (da) is told to fill the DataSet (ds) with the information pulled from the SQL Query and call this fill "cashCustomers"//
            da.Fill(ds, "VPN");

            //Opens the connection//
            con.Open();

            //Closes The Connection//
            con.Dispose();

            //Tells the dataGridView to load with the information stored in table called 'cashCustomers' in the DataSet (DS)//
            dataGridView1.DataSource = ds.Tables["VPN"];

Many thanks for your help.
Mark.

[Ketsuekiame]

Shouldn't you open the connection before you try and fill the dataset?

[sundog1]

Hi Ketsuekiame, Thanks for your reply.

Do'h.. Yes I've done that but still same problem, :S

[Ketsuekiame]

Have you run a SQL profiler to check you're pulling any data back?

[sundog1]

Is there a profiler built in to VS2010?

[Ketsuekiame]

It's built into SQL Management Studio. You can download it for free from Microsoft. Works with SQL Express as well.

[Ketsuekiame]

Admittedly this might be the wrong path to go down. It is only a simple select statement after all...
Have you tried not passing a table name to the data adapter? Instead using index lookup on the following ds.Table statement. This will check whether or not something is coming through.

You could also try setting the command and connection seperately rather than in the constructor?

conn.Open();
da = new OleDbDataAdapter();
da.SelectCommand = new OleDbCommand("select * from VPN", conn);
da.Fill(ds);
conn.Close();

[sundog1]

Hi Ketsue,

I've trouble shot this a bit and I think it's something to do with the connection string for some reason.

Won't even let me access it if I hard-code the location of the accdb.

[Ketsuekiame]

Remove the space from after Data Source.
Data Source=DSName not Data Source =DSName

[sundog1]

Nope... Still nothing..

When debugging it gets to con.open();

and then just opens without completing the rest of the code.

           //Creates new versions of the Connection string and Data Set//
            con = new System.Data.OleDb.OleDbConnection();
            ds = new DataSet();

            //Opens the connection//


            //The actual connection to the database//
          con.ConnectionString = "PROVIDER=Microsoft.ACE.OLEDB.12.0;Data Source=C:\vpn_db.accdb";

            con.Open();
            da = new OleDbDataAdapter();
            da.SelectCommand = new OleDbCommand("select * from VPN", con);
            da.Fill(ds);
            con.Close();


        } 
    }
}

[Ketsuekiame]

If you're going to hardcode a string like that, you need to use the @ sign in front of it:
con.ConnectionString = @"PROVIDER=Microsoft.ACE.OLEDB.12.0;Data Source=C:\vpn_db.accdb";

Otherwise it will treat \v as a special character (vertical tab iirc)

If you don't want to use the @ sign you should use \\ instead.

[sundog1]

God.. I'm an idiot.

I had the DB passworded. Slaps self

Sorry for wasting your time guys.. This has resolved itself now.

Just out of curiosty is it possible to put a 'password' into the connection string of a ACCDB?