Inserting a new row into SQL Server using ADO.NET+ 1

[TimRegester]

I have written a Winforms app in C# on Visual Studio Express 2005 with a SQL Server 2005 database behind. If I can prove it works I will get VS 2005/8 Pro plus MSDN and lots of other useful dev apps.

Most of the application deals with XML and I have dealt with that ok. But the base of the record comes from a SQL Server DB.

Using .Net 2.O and ADO.NET I need to insert a row at the end of a table on a form with no datagrid, a binding source, dataset and table adaptor. I cannot for the life of me find a working method to do it.

I have used (where bs is binding source) bs.addnew() to create a blank record and this can then be completed and bs.endedit() but cannot find the method to write the row back to the table. I tried bs.insert and although I can get the row position ok cannot find the definition of the "object value" required in the syntax. Everything i try gives a run time exception of "Cannot insert external objects to this list"

So 1) What is this object and what value should it be?
or 2) Suggestions for an up to date book as reference.
or 3)A tutorial for this scenario not using a datagrid.
or 4) a link to sample code.

 

[chrissie1]

It is probably your tableadapter1.update

Christiaan Baes
Belgium

My Blog
"In a system where you can define a factor as part of a third factor, you need another layer to check the main layer in case the second layer is not the base unit." - jrbarnett

 

[TimRegester]

Please can you elaborate?

Do you mean that the value of the object is tableadaptor.update (which does not sound right unless I am missing something) or do you mean I should be using that method in preference to the bindingsource.insert?

 

[chrissie1]

As far as I know your bindingsource uses the dataset, right. and the dataset was filled with the adapter, right? Well then you use the adapter to update the dataset to the database

something like this adapter.update(dataset)

But if you don't believe me...

Christiaan Baes
Belgium

My Blog
"In a system where you can define a factor as part of a third factor, you need another layer to check the main layer in case the second layer is not the base unit." - jrbarnett

 

[TimRegester]

rewrote the code to ignore the bindingsource and use the datatable update method as you suggested. Hey Presto it works. But ignores the bindingsource.

Wondering if I can use both as Binding source allows data validation out of box whereas I will now have to write validation code. So although textboxes bound the update method ignores bindingsource.

code used (table and field names changed) as follows
// create a new datatable
DataTable dtbl = new DataTable();
// set it to reference dataset datatable being updated
dtbl = this.vDataSet.DATATABLE;
//Count the rows
int icount = dtbl.Rows.Count;
// tracecode msgbox to check count increments
MessageBox.Show("Number of recs before code = " + icount);
// create a new row
DataRow dr = dtbl.NewRow();
// update row columns
dr["REF"] = txtRef.Text;
dr["ADDRESS"] = txtAddress.Text;
dr["SOMMAT"] = txtSommat.Text;
dr["SOMMATELSE"] = txtSommatElse.Text;
dr["MONEY1"] = txtMoney1.Text;
dr["MONEY2"] = txtMoney2.Text;
dr["INSTR"] = txtInstr.Text;
dr["PHOTOS"] = txtPhotos.Text;
dr["COMPLETED"] = false;
// add the row to the datable
dtbl.Rows.Add(dr);
// update the tableadaptor
vTableAdapter.Update(vDataSet.DATATABLE);
icount = dtbl.Rows.Count;
// tracecode msgbox to check count increments
MessageBox.Show("Number of recs after code = " + icount);