How to add a new row to a DataList

[Rod]

I'm new to both SQL server and asp.net so please excuse the dumb question.

I have a table in SQL server that has an identity column and also some
columns that are marked as Not Null.

In asp.net I put this table into a dataset then display it in either a
DataList or a DataGrid.

I want to add a new row so I presume I need a button that will add a row to
the dataset. But how do I sort out the identity field.

I could create the blank row in the database first which gives me the id,
but then the Not Null fields will start demanding data. Seems like a Catch
22.

Please advise!

Many thanks in advance.



Rod

 

[Mark Rae]

But how do I sort out the identity field.

Can you explain a bit more what you mean by "sort out" the identity
field...?

When you add a new row to a SQL Server table which contains an identity
field, you don't actually provide a value for that field - SQL Server
generates it for you automatically... If you need to know what the
automatically generated ID of the new record is, use SELECT SCOPE_IDENTIY()
within the same SQL batch...

http://www.google.co.uk/search?hl=en&rls=GGLG,GGLG:2006-28,GGLG:en&q=SELECT+SCOPE_IDENTITY+&meta=

 

[Rod]

Can you explain a bit more what you mean by "sort out" the identity
field...?

When you add a new row to a SQL Server table which contains an identity
field, you don't actually provide a value for that field - SQL Server
generates it for you automatically... If you need to know what the
automatically generated ID of the new record is, use SELECT
SCOPE_IDENTIY() within the same SQL batch...

http://www.google.co.uk/search?hl=en&rls=GGLG,GGLG:2006-28,GGLG:en&q=SELECT+SCOPE_IDENTITY+&meta=

OK I can first create a new row in the SQL table and get the ID, but because
some fields are set to Not Null I have to give them data. But the user has
not yet provided any data.

I could first create the new row in the DataSet without an identity, get the
data for the row (forcing the user to fill in the not null fields) and then
create a new row in the SQL table now I have all the data. I was just
feeling uncomfortable about having a row in my Dataset without an identity
so I was wondering what the proper way of doing it is.

PS thanks for the SELECT SCOPE_IDENTIY tip.

 

[Mark Rae]

OK I can first create a new row in the SQL table and get the ID, but
because some fields are set to Not Null I have to give them data. But the
user has not yet provided any data.

Why do you need to create a row *before* the user has provided any data for
it...???

 

[Rod]

Why do you need to create a row *before* the user has provided any data
for it...???

To get the ID, but maybe I can live without it, Or at least until after the
user has entered the data.

 

[Mark Rae]

To get the ID, but maybe I can live without it, Or at least until after
the user has entered the data.

Apologies - I *really* can't see why you need the ID of a record until it's
been created... What possible use can it be to you...?

 

[Guest]

Howdy,

I suspect he's just switched from MS Access database, in which id is
returned even if you haven't actually inserted anthing.

 

[Mark Rae]

I suspect he's just switched from MS Access database, in which id is
returned even if you haven't actually inserted anthing.

Hmm - you might be right...