Set value of field in new record

[Steve]

Hi all

I have a form that is bound to a table in my strongly-typed dataset. When I
create a new record, I am using the following code:

With Me.BindingContext(m_oDataSet.SecUser)
.EndCurrentEdit()
.AddNew()
End With

Just before I update the data I use the following line, and on this line I
get an error:

Me.BindingContext(m_oDataSet.SecUser).EndCurrentEdit()

The error is that the UserID column in my table does not allow nulls. So,
After I call the AddNew method, how do I set the UserID column of the newly
created record? I'll set it to some randon figure as the DB is an IDENTITY
type, but I just need a reference to it to set it so my dataset is happy
based on it's own constraints.

Kind Regards,
Steve.

 

[William Ryan eMVP]

Steve:

In general, you can specify a default value for any given datacolumn..so if
this column was column 0 you could use
DataSet.Tables[TableIndex].Columns[ColumnIndex].DefaultValue = whatever.

If this is an identity column, then you set the following properties of the
datacolumn:

dc.AutoIncrement = True

dc.AutoIncrementSeed = 0

dc.AutoIncrementStep = -1

The seed is where you want the counting to begin. You may have instances
where you want it to start at the max value of the column in the db, but in
a multi user environment, you are likely to run into conflicts.

As such, set the AutoIncrement property to True...This will let the column
know it is an autoincrement type. Set the seed at 0 or leave it
unspecified. Set the step to a -1 or other negative number..since Identity
fields in the DB can't be negative, you know that you'll never submit a
value that is already being ussed. WHen you submit a negative value SQL
Server will assign a valid one.

Bill Vaughn has a great example at www.betav.com under Articles/MSDN titled
Managing an Identity Crisis.

HTH,


Bill

 

[Steve]

Hi Bill

That's great, thanks for that. The article is very useful, I'll spend some
more time on it later because it describes what I need to do.

After I make that call to AddNew, I add the information into my form, but
when I call my update procedure, the dataset record doesn't contain any
values, just the value for the ID. My controls are bound to the columns in
the dataset. I am using a strongly-typed dataset, so what is the correct
method for inserting a new record when you want the values to be taken from
the bound controls?

All I want to do is display my form with empty fields, have the user enter
values and then persist those changes back to the database.

Thanks for any help, it's much appreciated!!

Regards,
Steve

Steve:

In general, you can specify a default value for any given datacolumn..so if
this column was column 0 you could use
DataSet.Tables[TableIndex].Columns[ColumnIndex].DefaultValue = whatever.

If this is an identity column, then you set the following properties of the
datacolumn:

dc.AutoIncrement = True

dc.AutoIncrementSeed = 0

dc.AutoIncrementStep = -1

The seed is where you want the counting to begin. You may have instances
where you want it to start at the max value of the column in the db, but in
a multi user environment, you are likely to run into conflicts.

As such, set the AutoIncrement property to True...This will let the column
know it is an autoincrement type. Set the seed at 0 or leave it
unspecified. Set the step to a -1 or other negative number..since Identity
fields in the DB can't be negative, you know that you'll never submit a
value that is already being ussed. WHen you submit a negative value SQL
Server will assign a valid one.

Bill Vaughn has a great example at www.betav.com under Articles/MSDN titled
Managing an Identity Crisis.

HTH,


Bill

Hi all

I have a form that is bound to a table in my strongly-typed dataset.

When

I
create a new record, I am using the following code:

With Me.BindingContext(m_oDataSet.SecUser)
.EndCurrentEdit()
.AddNew()
End With

Just before I update the data I use the following line, and on this line I
get an error:

Me.BindingContext(m_oDataSet.SecUser).EndCurrentEdit()

The error is that the UserID column in my table does not allow nulls. So,
After I call the AddNew method, how do I set the UserID column of the newly
created record? I'll set it to some randon figure as the DB is an IDENTITY
type, but I just need a reference to it to set it so my dataset is happy
based on it's own constraints.

Kind Regards,
Steve.