Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rtutus
Aged Yak Warrior
522 Posts |
 Posted - 2006-02-05 : 19:15:36
|
| Hi folks,I run a sql command that executes an Insert request to a database like this:str = "insert into TblAccount(Name) values(@Name)"pm = cm.Parameters.Add(New SqlParameter("Name", SqlDbType.BigInt))pm.Value = "whatever"cm.sqlcommand=strcm.ExecuteNonQuery()In the database, I have the primary key column "AccountNumber" having "identity seed= yes". So my accountNumber column is geneated automatically by SQL server.The problem is when I insert my record using the sqlCommand above, I need to get back the value of my account number that was generated by SQL server. The reason why I need that value is to update the other columns of that same record of my TblAccount table later on within the aplication, other columns like: date of birth, Address....I don t know how to get the value of the AccountNumber that sql server ll have generated for me.Thanks a lot for your help. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-02-05 : 20:43:55
|
| the value is returned by scope_identity() Have a look in bol==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
rtutus
Aged Yak Warrior
522 Posts |
Posted - 2006-02-05 : 20:53:54
|
| Can u give me the code how I implement that function in my .NET code pleaseThanks my friend |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-02-05 : 21:14:31
|
| str = "set nocount on insert into TblAccount(Name) values(@Name) select id = scope_identity()"Should return the value in a resultset.Better to make this into a stored proc and use an output parameter for the value.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2006-02-06 : 03:05:21
|
| I would also advice you use a stored proceedure in your page, and use the above code, instead of a command object. |
|
|
|
|
|
|
Getting back the value of an Identity-seed-column