New row auto increment question (ASP)

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.

All Forums

Development Tools

Other Development Tools

New row auto increment question (ASP)

Author

Topic

BBrian
Starting Member

11 Posts

Posted - 2005-08-03 : 10:17:38

Hi,

This is my first time writing asp or sql. I covered them a little in college.

I'm writing some asp with forms to fill my database. After I use "INSERT INTO main..." I need to find the value of the primary key of the new row so it can be used when submitting the next few forms.

Is there an easier way or should I be just using something like
sql = "SELECT ci_number FROM main WHERE name='" & Request.Form("name") & "' and version='" & Request.Form("version") & "'

name and version will be passed from the form on the previous page.

Thanks..

B

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-03 : 10:40:38

SELECT isnull(max(ci_number),0)+1 FROM main WHERE name='" & Request.Form("name") & "' and version='" & Request.Form("version") & "'

will have new value for next record

Madhivanan

Failing to plan is Planning to fail

BBrian
Starting Member

11 Posts

Posted - 2005-08-03 : 10:50:26

I was asking for the record just entered. Was the code I suggested the way people normally find it out. It just seems like there should be a nicer way.

Brian

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2005-08-03 : 10:52:44

Is your primary key an identity column?

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-03 : 10:56:01

Select max(ci_number)?

Madhivanan

Failing to plan is Planning to fail

BBrian
Starting Member

11 Posts

Posted - 2005-08-03 : 12:05:41

I haven't yet made the database. I'm waiting for someone to sort out space etc on one of the servers.

I was going to start the table with

CREATE TABLE main
(
ci_number int(6) NOT NULL AUTO_INCREMENT,

which I guess makes it an identity column??????

Select max(ci_number) makes sense. Is this how you guys normally get it?

Brian.

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2005-08-03 : 13:49:26

Is this Microsoft SQL Server? That looks like a MySQL statement.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

BBrian
Starting Member

11 Posts

Posted - 2005-08-04 : 04:37:30

eh, I didn't know there was any difference. I thought it was standard across databases.
We use sybase here but I've been writing my code by just taking bits from the internet and putting them together. Someone else working here will probably look over everything before it gets used. I'm just a student working here for the summer.

B

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-04 : 04:51:52

If ci_number is auto_increment number then no need to insert it in the table
When the new row is inserted it will be updated with next value
If you want to know what the generated value is then use

Select max(ci_number) from main

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources