Re: Binding and adding rows with SQL identity column

Hi,

"jon f kaminsky" <jonfkaminsky@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:1DD3D2A0-D2AE-40CB-9870-DA8270937697@xxxxxxxxxxxxxxxx
>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()

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

>
> 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
> --
> thx,
> jf kaminsky


.