Syntax for extracting information from a datagridview and exporting to another table

[shyy]

Hi guys,

I am trying to export the datagridview1 to another table in my database. I see a bunch of SQL but not for OLE. Can someone help me with the syntax. I am basically trying to create a check out form, so when the user checks out. Will take all the columns and rows and export into another table that I have.

I tried both of the following to export into another table called Authors_Table.

Thanks

[COLOR=#000000][FONT=Calibri]        private void button1_Click(object sender, EventArgs e)
        {
            OleDbConnection connect = new OleDbConnection(); //Creates the connection
            connect.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=H:\VB Projects\binding\binding\books.accdb;Persist Security Info=False";
            connect.Open();


            OleDbCommand command = new OleDbCommand();
            command.Connection = connect;
            command.CommandText = "SELECT * FROM Authors";
            OleDbDataAdapter da = new OleDbDataAdapter();
            DataSet ds = new DataSet();
   
            dataGridView1.DataSource = ds;
            dataGridView1.DataMember = "Authors_table";


            da.Fill(ds, "Authors_table");


            connect.Close();        
        }
[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]
[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]I also tried another code and get a different error: Additional information: The SelectCommand property has not been initialized before calling 'Fill'.[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]
[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]        private void button1_Click(object sender, EventArgs e)
        {
[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]            OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=H:\VB Projects\binding\binding\books.accdb;Persist Security Info=False");
            OleDbDataAdapter ad = new OleDbDataAdapter();
            DataSet ds = new DataSet();
            OleDbCommand cmd = new OleDbCommand();
            con.Open();
            ad.SelectCommand = new OleDbCommand("SELECT * FROM Authors");            
//         ad.InsertCommand = new OleDbCommand("INSERT INTO * FROM Authors", con);
            ad.Fill(ds, "Authors_table");
            ad.SelectCommand.ExecuteNonQuery();
            con.Close();
[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]        }

[/FONT][/COLOR]

 

[jmcilhinney]

I see a bunch of SQL but not for OLE.

I assume that what you actually mean is SQL Server and OLE DB. SQL is a programming language for manipulating databases and you are using in the code you posted. All those SELECT and INSERT statements are SQL code. SQL Server is Microsoft's enterprise-grade RDBMS. Not the same thing. Similarly, OLE DB is not OLE.

The thing is, ADO.NET is designed such that data access is pretty much identical no matter the database. If you have code to work with SQL Server then, in the vast majority of cases, it will work for Access with minimal change. Firstly, you simply swap out all the SqlClient members for equivalent OleDb members, e.g. OleDbConnection instead of SqlConnection. You will also need to modify the connection string and, in some cases, there may need to be some changes to the SQL code. the basic pattern of the code will remain unchanged though. So, if you already have examples for SQL Server, you can apply them to Access with those simple changes. Do that and, if it doesn't work, post back with what you've done and what issues you're having and we can help you fix them.