Sql insertion problem

[Techno]

I've filled a dataset with data from one database. I am now trying to populate it into another database, different table BUT same schema.

thing is everytime I try to executenonquery I always get an error:

Prepared statement '(@p1 int,@p2 datetime,@p3 nvarchar(1024))INSERT INTO Table3 (ID,' expects parameter @p1, which was not supplied.

but I did though....

this.theSQLDataAdapter.InsertCommand = new SqlCommand("INSERT INTO Table3 (ID, dateOfEntry, theNews) VALUES (@p1, @p2, @p3)");
this.theSQLDataAdapter.InsertCommand.Connection = new SqlConnection("Server=.;database=test;Trusted_Connection=true;");

SqlParameter p1 = new SqlParameter("@p1", SqlDbType.Int);
p1.SourceColumn = "ID";

SqlParameter p2 = new SqlParameter("@p2", SqlDbType.DateTime);
p2.SourceColumn = "dateOfEntry";

SqlParameter p3 = new SqlParameter("@p3", SqlDbType.NVarChar, 1024);
p3.SourceColumn = "theNews";


this.theSQLDataAdapter.InsertCommand.Parameters.Add(p1);
this.theSQLDataAdapter.InsertCommand.Parameters.Add(p2);
this.theSQLDataAdapter.InsertCommand.Parameters.Add(p3);

this.theSQLDataAdapter.InsertCommand.Connection.Open();
this.theSQLDataAdapter.InsertCommand.ExecuteNonQuery();
this.theSQLDataAdapter.InsertCommand.Connection.Close();

what am I doing wrong?

[jmcilhinney]

If your data is in a DataTable then you shouldn't be using ExecuteNonQuery. ExecuteNonQuery works on a single record and you have to pass it the data. If you want to get the data from a DataTable you should be calling the Update method of the DataAdapter. Note also that if you want to insert the data then all the rows must have a RowState of Added, which will not be the case by default. You have to set the DataAdapter's AcceptChangesDuringFill property to False so that it doesn't implicitly call AcceptChanges when you call Fill, which will change all the RowStates from Added to Unchanged.

[Techno]

Thanks for that, I figured out a way which was ineffecient - going through each row of data and executing the SPROC giving it the parameters.
the thing is I dont want to do an Update() since I want to COPY the rows from the datatable TO the empty blank table, with the same schema. Update command requires the "WHERE [ID] =" clause and this wont be valid for me...will it?

[jmcilhinney]

No, no ,no. When you call Update on a DataAdapter it executes the DeletCommand, InsertCommand and UpdateCommand of the DataAdapter. If all your rows have a RowState of Added then there are no deleted or updated rows so only the InsertCommand will be executed and all the rows will be inserted into the table that you specify. Having said that, the only reaosn to do it that way is if the two tables are in different databases or you want to edit some of the data before inserting. If it's a direct transfer from one table to another in the same database then you can do it with a single SQL statement without ever bringing the data into your app.

[Techno]

wow did NOT know about that - many thanks!!!