Can I insert the data to a table which columns are unknown in mdb file from c#?

Question

I think I have a weird doubt!!

I have created a table using C#[with a tool not programatically ] in mdb file, then I am inserting the values to that table, what the issue is I don't know how many columns are available in that table, but I wanna insert value from the datagridview..

Spire.DataExport.Access.AccessExport accessExport = new Spire.DataExport.Access.AccessExport();

                    accessExport.DataSource = Spire.DataExport.Common.ExportSource.DataTable;
                    accessExport.DataTable = this.dataGridView2.DataSource as System.Data.DataTable;
                    accessExport.DatabaseName = saveFileDialog1.FileName;
                    accessExport.TableName = "ExtractedTable";
                    accessExport.SaveToFile();
                    //OleDbCommand cmdt = new OleDbCommand("Create Table "+profiletablegrid. ", con);
                     string strDirectory = saveFileDialog1.FileName;
                     OleDbConnection conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strDirectory);
                     conn.Open();
                     for (int i = 41; i < dataGridView2.Rows.Count; i++)
                     {
                         for (int j = 0; j < dataGridView2.Rows[i].Cells.Count; j++)
                         {
                             OleDbCommand cmdd = new OleDbCommand("Insert into sample values(@a,@b,@c,@d)", conn);
                             cmdd.Parameters.Add("@a", OleDbType.VarChar).Value = dataGridView2.Rows[i].Cells[j].Value.ToString();
                             cmdd.Parameters.Add("@b", OleDbType.VarChar).Value = dataGridView2.Rows[i].Cells[j].Value.ToString();
                             cmdd.Parameters.Add("@c", OleDbType.VarChar).Value = dataGridView2.Rows[i].Cells[j].Value.ToString();
                             cmdd.Parameters.Add("@d", OleDbType.VarChar).Value = dataGridView2.Rows[i].Cells[j].Value.ToString();
                             cmdd.ExecuteNonQuery();
                         }
                     }

So Since I know the columns I am inserting 4 values, but if I don't know how many columns are there, then how can i insert the value... I can count the datagridview total columns, but how can I insert according to that?

Aftab Ahmed · Accepted Answer · 2014-03-21T11:50:24.490

1

Without knowing column Names or Number of Columns of a table in my experience it's not possible to insert data in to it. How ever you can use this work around to get column names of particular table then insert data into those columns. The first thing you would do is make sure that no data gets returned:

SELECT TOP 0 your_table.* FROM your_table WHERE 1 = 2;

Now assuming you know how to set up a DataReader you would do the following:

using(var reader = command.ExecuteReader())
{
    // This will return false - we don't care, we just want to make sure the schema table is there.
    reader.Read();

    var table = reader.GetSchemaTable();
    foreach (DataColumn column in table.Columns)
    {
        Console.WriteLine(column.ColumnName);
    }
}

Now you have column names so build up your insert statement.

Ok Consider you have n number of columns then your code will look like this.

List<string> colArr=new List<string>();
foreach (DataColumn column in table.Columns)
{
    colArr.Add(column.ColumnName);
}

now build your sql in this way.

string colNames="";
string val="";
for (int i = 0; i < colArr.Count; i++)
{
   if(i!=colArr.Count-1)
   {
     colNames+=col+",";
     val+="Some Value,";
   }
   else
   {
     colNames+=col;
     val+="Some Value";
   }
}
string sqlQuery="Insert Into your_Table "+colNames+" ("+val+")";

edited Mar 21 '14 at 11:50

answered Mar 21 '14 at 06:26

Aftab Ahmed

1,727
11
15

This will help for one user... Actually I am developing a product, so many will use, i don't know how many columns each will have.. some may have 5 or 6, after developing a product we can't go and change the code, so as an in-built I need a query which reads the number of columns and inserts the value according to the number of column, i don't want the column name it's enough to know the number of columns – Jeswin Rebil Mar 21 '14 at 07:49
Above code inserted values to 4 columns, but how can we insert if they have 5 or 6 columns.... – Jeswin Rebil Mar 21 '14 at 07:51
Is there any way to export the datagridview directly to the mdb file? I have searched many sites all they did is exporting to excel and then importing to mdb... – Jeswin Rebil Mar 21 '14 at 07:52
you can try to save your returned column names in an array. then loop through this array and build up your sql insert statement dynamically. in this way you don't need to worry about number of columns if they are increasing or decreasing. – Aftab Ahmed Mar 21 '14 at 07:56
k consider I have 6 columns and I m getting in array like, a[0]="p",a[1]="p1",a[2]="p2",a[3]="p3",a[4]="p4",a[5]="p5".. Then how can give insert statement for this, Insert into table values("......."); How can I Pass all the values in one row?? – Jeswin Rebil Mar 21 '14 at 08:04
i have edited my code to address this issue hope you will get some idea – Aftab Ahmed Mar 21 '14 at 08:13
The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect. it shows some error in select statement – Jeswin Rebil Mar 21 '14 at 08:37
Thanks a lot mate I got more ideas from this awesome :) – Jeswin Rebil Mar 21 '14 at 09:08
OMG!! How to remove the last comma[,] ? it's showing insert syntax error because of this – Jeswin Rebil Mar 21 '14 at 09:52
Yeah done, but still I get error for only one record, I think there no way to clear that, because at the particular record,the name is o'conner so it throws syntax error, can we avoid that record and continue with the next records? – Jeswin Rebil Mar 21 '14 at 10:29
have a look at similar issue http://stackoverflow.com/questions/7747370/how-to-replace-apostrophe-with-double-apostrophe-in-string – Aftab Ahmed Mar 21 '14 at 10:33
you can use `if(val.Contains("'")){string nVal=val.Replace("'","''")}` – Aftab Ahmed Mar 21 '14 at 11:33
oh!! I am using string values, so i used ' ' for all the values.. If i add this then it will replace everything – Jeswin Rebil Mar 21 '14 at 11:48
do this before this line `val+="Some Value,";` – Aftab Ahmed Mar 21 '14 at 11:49

score 0 · Answer 2 · answered Mar 21 '14 at 06:31

assuming you are using OleDbConnection you can call

DataTable schema = connection.GetSchema("Columns");

to get the schema data of your Database ... in that table you will find each column of each table in the db ...

use that to build you SQL statement at runtime

Can I insert the data to a table which columns are unknown in mdb file from c#?

2 Answers2