Select Auto Id - SQL Server Forums

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

Old Forums

CLOSED - General SQL Server

Select Auto Id

Author

Topic

danasegarane76
Posting Yak Master

242 Posts

Posted - 2006-08-22 : 07:25:45

Hi,
I am using the following querey to insert values into a table row.
DB.execute("update logintime="12/12/2006 08.30 pm" where fpname="test")

That Table has a auto field id.And Now my aim is that how to retrive the autoid column with this query.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-22 : 07:31:18

Are you using SQL Server 2000?

Chirag

danasegarane76
Posting Yak Master

242 Posts

Posted - 2006-08-22 : 07:32:22

Yes I am using SQL Server 2000 with VB as Front End

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-22 : 07:43:19

First of all your syntax for the update statment is in correct, it should be somthing like this


Update <TableName> Set  logintime="12/12/2006 08.30 pm" where fpname="test"

Now, you can not retrive the value from the update statment you need to pass the select statement to get the value.


Select IDCol From <TableName> Where fpName = "test"

so it will be better if you create on procedure and set output parameter as the identity column.

Chirag

danasegarane76
Posting Yak Master

242 Posts

Posted - 2006-08-22 : 07:46:16

Please Help me in creating the procedure with 4 arguments

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-22 : 07:59:52

[code]
Create Proc Proc_Name
(
@Arg1 <YourDateType>,
@Arg2 <YourDateType>,
@Arg3 <YourDateType>,
@AutoID BigInt Out
)
As
Begin
-- Here your update Statement..

Select @AutoID = [ID] From <YourTable> Where ...

Return @AutoID
End
[/code]

Chirag

danasegarane76
Posting Yak Master

242 Posts

Posted - 2006-08-22 : 08:01:10

Thanks Chi,
I will and check and return

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-08-22 : 08:04:57

What if the update statement causes multiple rows to be updated? What if multiple users simultaneously execute same SP with same parameters?

Harsh Athalye
India.
"Nothing is Impossible"

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-08-23 : 08:57:30

SCOPE_IDENTITY() is your FRIEND

For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-08-23 : 11:40:22

or is it ident_current()?

Harsh Athalye
India.
"Nothing is Impossible"

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-24 : 02:15:58

FROM BOL

quote:

SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions in that they return values inserted into IDENTITY columns.

IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the value generated for a specific table in any session and any scope

Chirag

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-08-24 : 04:19:59

quote:
Originally posted by chiragkhabaria

FROM BOL
quote:

SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions in that they return values inserted into IDENTITY columns.

IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the value generated for a specific table in any session and any scope

That's what dana wants, right? Last inserted value in the autoid column?
Chirag

Harsh Athalye
India.
"Nothing is Impossible"

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-24 : 04:42:44

quote:

That's what dana wants, right? Last inserted value in the autoid column?

I just posted that scope_identity and DENT_CURRENT or some what similar.

About orginal post, what i analyses was that he wanna update the a particular column based on some condition and wants the value of the identity column.

Chirag

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-24 : 07:01:55

My impression was that he actually wanted to insert a new row into the table and didn't realize that an UPDATE would not do that.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources