Search code examples
c#sqlexceloledboledbcommand

Create Table with Parameters


I would like to create an Excel table, using OleDb:

OleDbCommand oleDbCommand = new OleDbCommand();
oleDbCommand.Connection = oleDbConnection;

String commandText = "CREATE TABLE" + " [" + sheetModel.Sheet.Name + "] ";
commandText += "(";

for (int index = 0; index < spalten; index++)
{
    String _header = sheetModel.DT1.Rows[heaader].ItemArray[index].ToString();

    oleDbCommand.Parameters.Add(new OleDbParameter("@var" + (index + 1).ToString(), _header));

    if (index > 0)
    {
        commandText += ", ";
    }

    commandText += "@var" + index.ToString() + " VARCHAR";
}
commandText += ");";


try
{
    oleDbCommand.CommandText = commandText;
    oleDbCommand.ExecuteNonQuery();
    oleDbCommand.Parameters.Clear();
}
catch (Exception exception)
{
    MessageBox.Show(exception.Message);
    return;
}  

The result for the Excel table is,

@var0 @var1 @var2 @var3 @var4 @var5

but I should look like this:

"PREIS/Stk. EURO" "Stk." "" "Produkt" "Artikelmerkmale" "Sonstige"

Where is my mistake?
The code is working fine for inserting values.


Solution

  • 1) I created a table with the columns Name: F1, F2, ...

    2) I Updated the columns Name with a dynamic Name (F1, F2, ... also created dynamicly)

    String _Header = "PREIS/Stk. EURO"
    oleDbCommand.CommandText = "UPDATE[MySheet] SET F1 = @var";
    oleDbCommand.Parameters.Add(new OleDbParameter("@var", _header));
    

    That's all!