Add new record then get unique id of new record

Hi:

I have a .net 2.0 application that inserts a record into a database.  What I would like to do is:

(1) Confirm the success or failure of the insert.

(2) On success, get the unique ID of that new record so I can show it in a popup to confirm to the user as a Tracking Number.

(3) On fail, note why and inform user.

I am using VB code behind and a module call for all my database calls. 

 

'Add new request to database

Dim oSQL As New SQLDB("SERVER", "DATABASE", "USERID", "PASSWORD")

oSQL.RunSQLAction(

"INSERT INTO DATABASE(PermitType, PM, PMPhone, RequestDate)Values('" & strOptChoice & "', '" & cboProjMgr.SelectedItem.Value & "', '" & lstProjMgrPhone.Items(0).Value & "', '" & Now() & "')")

'CODE WOULD GO HER TO CONFIRM SAVE BEFORE I RUN CODE TO GET NEW UNIQUE ID

 

'Get ID of last record entered.

Dim oSQL2 As New SQLDB("SERVER", "DATABASE", "USERID", "PASSWORD")

Dim intMaxID As Int64 = oSQL.GetValue("Select MAX(ID) FROM DATABASE")

'Give user confirmation# equal to the records unique id.

'If intMaxID <> "" Then     'THIS LINE OF CODE IS NOT WORKING

Response.Write(

"<script>alert('Your request has been submitted. Your Confirmation# is: ' & intMaxID & ')</script>")

'Else

 --- Can anyone help with code?  Thank you in advance.
0
ckcasp
2/14/2007 2:59:51 PM
asp.net.object-datasource 16182 articles. 0 followers. Follow

5 Replies
1088 Views

Similar Articles

[PageSpeed] 52
Get it on Google Play
Get it on Apple App Store

One idea would be to get the unique id first and store it in a local variable... Then on your insert use that number instead of MAX(ID)+1.

So

//Get id
int id = Execute("SELECT MAX(ID) + 1 FROM SOME TABLE")

//Insert record
InsertRecord( id );

//Validate
GetRecord( id );

Another option may be to use a stored proc... You could call a stored proc and have it return either the ID of the record entered or the entire record (letting you skip step 3).

Hope that helps!

 

0
shayd
2/14/2007 11:17:27 PM

(1) Take a look here: http://msdn2.microsoft.com/en-us/library/ms171921(VS.80).aspx 

(2) You can retrieve the last unique ID with the SQL statement:

SELECT @@SCOPE_IDENTITY

http://msdn2.microsoft.com/en-us/library/aa259185(SQL.80).aspx

(3) use Try.. Catch.. Finally

0
hans_v
2/15/2007 1:36:30 AM

hi,

though your code looks like to do what it is supposed to, but i suggest you to use parameters instead of inline queries and pass values using parameters following way

sqlcommand sc = new sqlcommand("insert table(fiedl1, fiedl2) values(@field1, @field2)");

sc.parameters.addwithvalue("@field1", "its_value"); and likewise.

and for getting last id of the record you could use @@SCOPE_IDENTITY. but that in your case will cause 2 db hits so i give you still better solution to use stored procedure in that check if record was inserted or not and accordingly return @@SCOPE_IDENTITY using either output parameter or just write

Select @@SCOPE_IDENTITY, so in your .net code you can use ExecuteScalar.

hope its not too much complicated.

regards,

satish.


Kind Attn: If a reply to your post helped you, kindly mark it as Answered.
__________________________________________________
Please save Animals Help World Society For Protection Of Animals,
Protect these speechless creatures of GOD
0
satish_nagdev
2/15/2007 1:54:57 AM

Hi Everyone:

Thank you so much for your help!

 I was able to return the value by using:

 SELECT @@IDENTITY AS 'Identity'

 

0
ckcasp
2/15/2007 3:12:42 PM
@@SCOPE_IDENTITY is safer than @@IDENTITY.
Kind Attn: If a reply to your post helped you, kindly mark it as Answered.
__________________________________________________
Please save Animals Help World Society For Protection Of Animals,
Protect these speechless creatures of GOD
0
satish_nagdev
2/16/2007 2:07:27 AM
Reply:

Similar Artilces:

Add new record then get unique id of new record?
Hi all I’m inserting a new record into my database but i want to get the unique ID for the row I’ve just inserted. Is there an easy way to do this after i have just added the new row, or will i have to create a separate query to get the last record entered into the database. The value i need to get out the database is the comID which is auto generated when the record is added. I have provided my insert statement below and would appreciate any advice on the best way to do this public void newcompany() {SqlCommand addcompany = new SqlCommand("insert into company (com_nam...

Getting new record ID when inserting new in formview
As the subject says, I'm adding a new record in a FormView control.  I need the new records ID.  Is it possible to get this?  I'm assuming I would have to be in the FormView1_ItemInserted, not the FormView1_ItemInserting event.  I've tried e.values("ID") but it comes back as nothing.In case it makes a difference, I'm using a LinqDataSource.   Diane It should be some thing like this..  <InsertParameters> <asp:Parameter Name="FieldName" Type="String" /> <asp:Parameter...

SqlDataSource with Access database: how to get back the latest inserted record Id after Inserting a new record
Hi all, Y use an Access database with a password. That´s the reason why I use the SqlDataSource. Works very good!But, I have bad luck in getting back the latest inserted record Id after Inserting a new record. I use the DetailsView. An SqlDataSource is bound to a TextBox that should represent the IdCliente.So, after inserting the Client details I want to have back the IdCliente to show the Client details inserted.I have read many of your greatfull help about getting back the latest inserted record Id after Inserting a new record.I used your good ideas: INSERT INTO [T_Clientes...

Add a button to add a new row and insert the new record to gridview
Hello, I have a gridview1 on a page attached to a SQLDataSource.  The gridvioew page is displayed when a selection from a drop down box on the master page matches the FK field (partID) on the gridview page via querystring.  I would like to add a buttton that allows me to add a row to gridview1 without losing the information already displayed, typed in, or updated.  I have the PK and 8 check box feilds set as bound field (DetailID) and all of the remaining 10 text fields converted to template fields.  What is the best way to add this functio...

Updating a new record and reading the new ID...
Hi, Using 'regular' ADO, if I want to add a new record to a table and return the new 'AutoNumbered' ID I'd do something like that: (Assuming VB 6) Public Function AddName(NewName as String) As Long Dim objRS As New Recordset Dim intNewID As Long objRS.Open "SELECT * FROM Names", objConnection, ' Options... objRS.AddNew objRS!Name = NewName objRS.Update intNewID = objRS!ID objRS.Close AddName = intNewName End Function Now, this code is simple enough and it returns the new ID without a fuss....

redirect to new page after insert record and display new record?
I'm using 3.5 framework and VB.I have a GridView with a button that when it is clicked will insert the selected record into a differnet table.  What I want to do is modify this so that the when the record is inserted into the table, the browser is redirected to a details.aspx page with the newly added record displayed. I think somehow, the details.aspx page needs to show the last record generated or something?So, if someone could show me how to:1. Modify the code below to redirect to details.aspx after the record is inserted.2. What the code-behind would look like on the details.asp...

add new items then clicking on refresh button will add new duplicate records to database.
hi,  i have used commandfield for updating and inserting but after i insert one record and click on refresh duplicate record is added to database. thanks in advance Refresh will repeat the previous postback; so if you added a new record in the previous postback (on Inert click, may be?) then Refresh will add another record (duplicate).Mark replies as answers if they helped you solve the problem. sultani.khalid.af: i have used commandfield for updating and inserting but after i insert one record and click on refresh duplicate record is added to database. Check this http://blo...

Reading ID for new inserted record and Use ID to Add into another table
Hi   I have a FormView.   My requirements are   o       To retrieve the ID for the newly inserted record into First table. I am going to use SELECT SCOPE_IDENTITY().   o       Use the ID of the newly inserted record into First table and write 4 rows of information into the second table.   My query is   1.    When using SELECT SCOPE_IDENTITY() do I have to convert into (Cint).mycommand.ExecuteScalar to use into second table. 2.    How can I add 4 ro...

When i add a new record i get an error.
hi in a page i use DataListView to insert records (Lan=VB) in my SQL database i creat some colums and once is DateStamp to save the Date of the record is created and not alowed NULL but in Default mode (getdate()) when i creat a record and don't insert annything for it page getting an error and tel me the DateStamp is not alow NULL. please tell me what to do to insert the record whithout getting error? Thanks Zed   If you set the coloum not alowedn NULL, the GetDate() will not work when the input value is NULL. You can select: 1) Alow the column NULL value , or 2). In yuor ...

How to Get the Id of New Autonumber Record
Help please, anyone.. How can I get the id of a new inserted record? I tried to use 'select @@identity' but it always gave me '0'. I used Ms Access and ASP.NET 2.0. r1c To bad you used Access .... SQL server has something called SCOPE_IDENTITY to return id value of the last created record.... Try the proposed solution on this link --> http://objectsharp.com/blogs/bruce/articles/210.aspx Regards, Narayana Rao Surapaneni Architect | Author | MCSD for .NET | MCSD | SCJP www.msdeveloper.info  r1c wrote:Help please, anyone..How can I get the id of a new inserted record?I ...

Get int ID of new Record
Hi:I'm trying to write a class that inserts a new record and retrieves the ID number.   My stored procedure looks like this:ALTER PROCEDURE dbo.InsertTask(    @Task_ID int OUTPUT,    @Task nvarchar(50),)AS    SET NOCOUNT ON;INSERT INTO Tasks                      (Task)VALUES     (@Task);SET @Task_ID = @@IDENTITYSELECT Scope_Identity()RETURN @Task_ID   My class looks like this:public int InsertTask(Tas...

No Way To Add A New Record in DetailsView After Last Record is Deleted
I have a DetailsView which I would prefer to default to 'Readonly' mode.It has the usual edit, insert and delete buttonsProblem is, if it's a new user who will have no records then they won't see the 'insert' button and thus cannot add a new record.Same goes if the user does has a record and deletes it, then they too will not see the 'insert' button nad won't be able to add a new record.Not sure what the best way round this other than defaulting to 'insert' mode which I would rather not do.Maybe if I could get a record count and change teh DetailsV...

How to reuse the primary key of a deleted record when add a new record?
I have a database table to record all request from customer, base on primary key column to delete/update record. my question is if one record is deleted, primary key of the deleted record can not be used when add in a new record, for example, I have total 10 records in my table, if record which primary key is 6 is deleted, when I add in a new record, I'd like use 6 instead of 11 (auto-generated by the system) as the primary key for the new add in record. we may perform delete records frequently, and the deleted records are saved in another table called deleted_record. if the ...

How to Add a new records to Master and corresponding records to detail form
Description of the issue:  The idea is for an easy way for a user to enter new records to both master and corresponding records to via detail page.  The user will perform data entry / insert via the web forms Master and Detail pages (underlying tables 1:M).  The idea is that I want the user to be able to click a button and the corresponding record ID number (PK) from the master form is passed as foreign key (FK) inserted to the detail table or detail form so the user can generate the detail record without manually entering the Foreign Key (PK form the master form). &n...

Web resources about - Add new record then get unique id of new record - asp.net.object-datasource

Récord - Wikipedia, the free encyclopedia
Text is available under the Creative Commons Attribution-ShareAlike License ;additional terms may apply. By using this site, you agree to the ...

The Record
Great new podcast co-hosted by Chris Parrish and Brent Simmons: The Record brings you the stories you should know about the Mac and Cocoa development ...

Lompoc Record - Lompoc, California News
The first time Jerry Brown was governor of California, he earned the nickname “Gov. Moonbeam.” It was, after all, the tail-end of the Hippie ...

Sydney Thunder knock off Sixers by 46 runs in front of record NSW crowd
... years, the Sydney Thunder have done everything they can to secure themselves a finals spot with a 46-run victory in front of a domestic record ...

Martin Guptill and Kane Williamson break world Twenty20 record
MARTIN Guptill made 87 and Kane Williamson 72 in an unbroken record partnership of 171 that steered New Zealand to a 10-wicket win over Pakistan ...

David Bowie cover music in high demand at Winnipeg record store, owner says
A Winnipeg record store owner says he was inundated with inquiries about what classic David Bowie material he had in stock following the death ...

Intel Reports Q4 FY 2015 Results: Record Revenue For Q4
Today Intel announced their earnings for the fourth quarter of fiscal year 2015. The year ended strong, with record revenues of $14.9 billion. ...

Watch: The Mountain from ‘Game of Thrones’ carries two fridges on his back and sets a new world record ...
You may not be familiar with the name Hafthór Júlíus Björnsson, but if you're a seasoned Game of Thrones viewer, you've undoubtedly seen his ...

WE MUST GIVE PROPS TO DONALD TRUMP: But We Must Also Discount Double-Check His Record
By the Editors of the Conservative Review It is hard not to feel protective over Donald Trump while the political classes are seething and scornful. ...

The only record that matters to Tom Brady is the one involving wins and losses - Patriots - Boston.com ...
FOXBOROUGH – It is not as if gaudy statistics and shattered records are required to define Tom Brady’s career. Citing rank on career leaderboards ...

Resources last updated: 1/18/2016 12:37:39 AM