[RESOLVED] help connecting to DB

[Crash893]

Hi all,


Im not sure where im going wrong but here is my source code

its a single access2003 DB with one table called WinAudi2

BindingSource CA_bindingsource = new BindingSource();
        

        private void button1_Click(object sender, EventArgs e)
        {
        string MyString;    
        System.Data.Odbc.OdbcConnection cn;
        System.Data.Odbc.OdbcCommand cmd;
        MyString = "Select * from WinAudi2"; 
        cn = new System.Data.Odbc.OdbcConnection(@"Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\Sweep.mdb;Uid=Admin;Pwd=;");
        cmd = new System.Data.Odbc.OdbcCommand(MyString, cn);

        try
        {
            cn.Open();


            CA_bindingsource.DataSource = cmd;
            dataGridView1.DataSource = CA_bindingsource;
            MessageBox.Show("Connected");
        }

        catch (Exception ex)
        {
            //MessageBox.Show("Error with CSV backup" + Environment.NewLine + ex.Message);
            Console.WriteLine(ex.ToString());
            MessageBox.Show("Problem" + Environment.NewLine + ex.Message);
        }


        finally
        {
            cn.Close();
        }

and when i go to run the code i get this



Am i not structuring something right?

[jmcilhinney]

You don't bind your BindingSource to the command. You execute the command, store the result set in an object and then bind that to the BindingSource. Given the fact that you want to bind the data to a grid you would execute the command using a OleDbDataAdapter to Fill a DataTable. It's the DataTable you'd bind to the BindingSource.

[Crash893]

Im not sure exactly what you mean?

i should be storing cmd in a datatable?, then move it to the binding source?



Also i thought that the odbc adapter was better for some reason?

[jmcilhinney]

If you're using Access then OleDb is preferable to Odbc.

As for the mechanics, a Command doesn't have any data. A Command basically contains some SQL code and a reference to a Connection. The Connection is obviously used to connect to the database. The SQL code is what gets executed to perform some action. When the Command is executed with a query then a result set is created, which you can then read with a DataReader. If you only want read-only, forward-only access to the data then you should use a DataReader explicitly.

In your case you random, read/write access to the result set so you should use a DataAdapter. It will wrap your Command and hide the DataReader to make your life easier. You just use its Fill method to populate a DataTable.

Firstly, while Odbc will work I suggest switching to OleDb. Either way, read about the DataAdapter class for the namespace you're using. Pay special attention to the constructors, the SelectCommand property and the Fill method. Also, there are numerous ADO.NET tutorials available on the Web because ADO.NET is a pivotal technology. Here's just one.

[Crash893]

I'll give it a shot

http://www.vbforums.com/showthread.php?t=478920