How do I map INSERT parameters to DataTabler Columns?

[Guest]

I wan to execute an update command that will insert many new rows into a
database table. I loop through my dataset running the update command on each
datable. The problem is that the update commangd fails. I want to map the
parameters used in the insert command to the columns of the datatable.
Does anyone know how to do this???

The code is below:

Thanks
-Taz

private void uploadSummaryToDatabase(DataSet dst, string filePath)
{
OdbcConnection conn = new OdbcConnection();
OdbcDataAdapter adapter = new OdbcDataAdapter();

conn.ConnectionString = "Driver={Microsoft Access Driver (*.mdb)};DBQ=" +
filePath;
conn.Open();

string insComm = "INSERT INTO Summary (Sup_DUNS, Sup_Name, Sup_Street,
Sup_City, Sup_State, Sup_Country, Sup_Zip)" +
"Values (@Sup_DUNS, @Sup_Name, @Sup_Street, @Sup_City, @Sup_State,
@Sup_Country, @Sup_Zip)";


adapter.InsertCommand = new OdbcCommand(insComm, conn);

foreach (DataTable dt in dst.Tables)
{
Try
{
adapter.Update(dt);
}
catch
{
MessageBox.Show("Problem updating to the database.",
"Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}

 

[Jon Skeet [C# MVP]]

I wan to execute an update command that will insert many new rows into a
database table. I loop through my dataset running the update command on each
datable. The problem is that the update commangd fails. I want to map the
parameters used in the insert command to the columns of the datatable.
Does anyone know how to do this???

Well, for one thing, although you construct the command (which is an
insert command, not an update command - they're different) you specify
the parameters in the SQL, but you never add them. You need to
construct an instance of OdbcParameter for each parameter and add it to
the command's Parameters collection.

Either use the constructor which takes four parameters, where the last
one is the source column, or set the source column (i.e. the column in
the DataTable which is used to fill in the command) manually on the
parameter.