New to C# - don't quite "get" the errors..

[conipto]

OK, so I have a very simple project I'm working on, one of many to teach myself C#, as I've gotten a good bit of experience with VB.NET, and used to know C when I was an infant and blah blah.. you know the drill.

Here's my sub.. err.. "void" that's populating a dataGridView with a simple select command taken out of a textbox. I know the SQL is fine, and I know the DB Connection is fine, but When I try to set the datagridview.datasource property to the datatable, I get some error saying I need to have columns in the datagridview first. So, I am trying to extract the column names from the datareader using the getschematable method. I get an exception saying "Column 'ColumnName' already belongs to another DataTable"" I don't get it.

private void button1_Click(object sender, EventArgs e)
        {


            OleDbConnection myOLEConn = new OleDbConnection();
            myOLEConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Z:\\db1.mdb;User Id=admin;Password=;";
            myOLEConn.Open();
            OleDbCommand myOLEcmd = new OleDbCommand();
            myOLEcmd.CommandText = textBox1.Text;
            myOLEcmd.Connection = myOLEConn;
            OleDbDataReader myReader = myOLEcmd.ExecuteReader();
            DataTable myDt = new DataTable();
            dataGridView1.Columns.Clear();
            int i;
            for (i = 0; i < myReader.GetSchemaTable().Columns.Count-1; i++) 
            {
                myDt.Columns.Add(myReader.GetSchemaTable().Columns[i]);
            }
            while (myReader.Read() == true)
            {
                myDt.Rows.Add(myReader);
            }
            dataGridView1.DataSource = myDt;
            myOLEConn.Close();
        }

Any pointers would be appreciated.

Bill

[jmcilhinney]

You should be using:

DataTable myDT = myReader.GetSchematable();

There's no point getting a DataTable and then trying transfer all it's columns to another table. Also, you can't transfer the actual columns anyway. Just like DataRows, DataColumns can only belong to a single DataTable. You would actually have to create a copy of the column to do what you're trying to, but there is no need anyway.

[popskie]

or u can use oledbdataadapter and fill the dataset.

[conipto]

or u can use oledbdataadapter and fill the dataset.

Well, I would use a dataadapter if I were reading and writing, but with read only, I like to normally just use the reader.. it's faster, and uses less resources. (atleast, in VB.NET)

I did manage with some coersion to get the stupid thing to work, it ended up looking a bit superfluous to me though, I'm sure there has to be an easier way to transfer the data to the datagridview..

private void button1_Click(object sender, EventArgs e)
        {


            OleDbConnection myOLEConn = new OleDbConnection();
            myOLEConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Z:\\db1.mdb;User Id=admin;Password=;";
            myOLEConn.Open();
            OleDbCommand myOLEcmd = new OleDbCommand();
            myOLEcmd.CommandText = textBox1.Text;
            myOLEcmd.Connection = myOLEConn;
            OleDbDataReader myReader = myOLEcmd.ExecuteReader();
            dataGridView1.Columns.Clear();
            DataTable myDt = myReader.GetSchemaTable();
            DataTable myNewDT = new DataTable();
            int i;
            for (i = 0; i < myDt.Rows.Count - 1; i++)
            {
                myNewDT.Columns.Add(myDt.Rows[i][0].ToString());
            }

            while (myReader.Read() == true)
            {
                Object[] newRow = new Object[myNewDT.Columns.Count];
                for (i = 0; i < myNewDT.Columns.Count; i++)
                {
                    newRow[i] = myReader[i];
                }                    
                myNewDT.Rows.Add(newRow);

            }
            dataGridView1.DataSource = myNewDT;
            myOLEConn.Close();
        }

Bill

[jmcilhinney]

Here's how you read data into a DataTable using a DataReader:

DataTable myTable = myReader.GetSchemaTable();
DataRow myRow = null;

while (myReader.Read())
{
    myRow = myTable.NewRow();

    for (int i = 0; i < myTable.Columns.Count; i++)
    {
        myRow[i] = myReader[i];
    }
}

Then you just assign the DataTable to the DataSource of the DataGridView. You only need one DataTable.

[conipto]

That's basically what I had originally, but I run into a problem with the layout of the returned GetSchemaTable. When you call GetSchemaTable, the table returned is a detailed table containing alot more than just column headings, and in fact each column is a row with detailed information. (see attatched picture) I'm gonna dig into my old VB projects and see how I did this before.

Bill

[jmcilhinney]

Ooh... maybe I spoke too soon. I'll do some research myself and make sure I'm not under any misconceptions about what GetSchemaTable does, which it seems I may well be. Sorry for the misdirection.