Binding and adding rows with SQL identity column

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a problem with an application that I have been porting from VB6 to
..NET. In short, I cannot figure out the proper way to display a new record
after the ds.newrow method has been called. The unique PK on this table is
generated by SQL Server as an identity, incrementing by 1 on every row
insert. The particular form uses bound fields and so forth instead of a grid,
and buttons that call the currency manager to move about the ds. All standard
stuff.

I was sure I screwed something up, so I started a new project and used the
data wizard to model the problem. It created the form, fields, action and
navigation buttons fine. Everything loads fine...except that it exhibits the
same issue. Namely, the user cannot seem to see anything new after he
attempts to add a new record. In any case, I could not help but notice that
the wizard-generated form uses a method to insert a row that looks like this:

Me.BindingContext(msDs, "MyTable").EndCurrentEdit()
Me.BindingContext(myDs, "MyTable").AddNew()

This appears to be discouraged based on articles I have read on MSDN and
from others on this forum. This is interesting enough in itself as this was
generated by the built-in wizard in VS2003. In the case of data coming from a
datatable, you are supposed to use something along the lines of

oRow = myDs.Tables(0).NewRow
oRow.Tables(0).Rows.Add(oRow)

Then call endcurrentedit on the currency manager. I would then assume
checking the changes, and then calling the acceptchanges method on the ds
would be the next steps.

the NewRow method works, but similar to the previous case, it looks like to
the user that he is still sitting on the same record. In order to continue to
work on the new row, the dataset must tbe reloaded from scratch. These data
are extensive and this strikes me as a workaround. If there was not a
identity column on the sql table, perhaps this would all the fine and I could
code around the visual defects by clearing text boxes, etc. at the time of
the row add.

Am I somehow missing the beauty of disconnected recordsets? This cannot be
the way the world works now in .NET is it? How does one grab that new Pk from
the Sql table identity insert beforehand so that new row "knows" who it is? I
realize I am disconnected so the server does not know what I am doing on the
client at the time the row is added to the datatable, but there must be
elegant method of doing something that I am sure many others have to do as
well.

Appreciate all help!
Jon
 
Hi,

jon f kaminsky said:
I have a problem with an application that I have been porting from VB6 to
.NET. In short, I cannot figure out the proper way to display a new record
after the ds.newrow method has been called. The unique PK on this table is
generated by SQL Server as an identity, incrementing by 1 on every row
insert. The particular form uses bound fields and so forth instead of a
grid,
and buttons that call the currency manager to move about the ds. All
standard
stuff.

I was sure I screwed something up, so I started a new project and used the
data wizard to model the problem. It created the form, fields, action and
navigation buttons fine. Everything loads fine...except that it exhibits
the
same issue. Namely, the user cannot seem to see anything new after he
attempts to add a new record. In any case, I could not help but notice
that
the wizard-generated form uses a method to insert a row that looks like
this:

Me.BindingContext(msDs, "MyTable").EndCurrentEdit()
Me.BindingContext(myDs, "MyTable").AddNew()
Click to expand...

The commonly used strategy goes like this.

Each time you call AddNew then the DataTable generates a new (temporarily)
primary key.

Each time an update is done (dataAdapter.Update) then the real primary key
is fetched from the database and put back into the DataTable. This will
only work if you have an insert query that contains two queries, one to
insert and one to fetch the new primary key (or the entire row). Google for
"SCOPE_IDENTITY" and ado.net.

The thing that most concerns me is why the TextBox's don't clear after
calling AddNew(), they really should clear. That must be fixed first...
Are there CheckBox's on the Form ?

HTH,
greetings
 
Thanks, Bart. yes the clearing thing is a bit odd. In fact if you try to
clear some of the text boxes manually, the value cdomes back after you click
into another text box.

yes I have a bit field that is bound to a checkbox.
--
thx,
jf kaminsky


:
 
Hi,

jon f kaminsky said:
Thanks, Bart. yes the clearing thing is a bit odd. In fact if you try to
clear some of the text boxes manually, the value cdomes back after you
click
into another text box.

yes I have a bit field that is bound to a checkbox.
Click to expand...

The CheckBox's are most likely causing the problem, see :

http://support.microsoft.com/default.aspx?scid=kb;en-us;326440

Basicly, after calling someDataAdapter.Fill you must set a default value for
all boolean columns:

someDataSet.Tables["tableName"].Columns["columnName"].DefaultValue = True;
// or False


HTH,
greetings
 
Back
Top