SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Select Auto Id
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

danasegarane76
Posting Yak Master

India
240 Posts

Posted - 08/22/2006 :  07:25:45  Show Profile  Send danasegarane76 an ICQ Message
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
Flowing Fount of Yak Knowledge

India
1907 Posts

Posted - 08/22/2006 :  07:31:18  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message
Are you using SQL Server 2000?

Chirag
Go to Top of Page

danasegarane76
Posting Yak Master

India
240 Posts

Posted - 08/22/2006 :  07:32:22  Show Profile  Send danasegarane76 an ICQ Message
Yes I am using SQL Server 2000 with VB as Front End
Go to Top of Page

chiragkhabaria
Flowing Fount of Yak Knowledge

India
1907 Posts

Posted - 08/22/2006 :  07:43:19  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message
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
Go to Top of Page

danasegarane76
Posting Yak Master

India
240 Posts

Posted - 08/22/2006 :  07:46:16  Show Profile  Send danasegarane76 an ICQ Message
Please Help me in creating the procedure with 4 arguments
Go to Top of Page

chiragkhabaria
Flowing Fount of Yak Knowledge

India
1907 Posts

Posted - 08/22/2006 :  07:59:52  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message

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 


Chirag
Go to Top of Page

danasegarane76
Posting Yak Master

India
240 Posts

Posted - 08/22/2006 :  08:01:10  Show Profile  Send danasegarane76 an ICQ Message
Thanks Chi,
I will and check and return
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 08/22/2006 :  08:04:57  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message
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"
Go to Top of Page

DonAtWork
Flowing Fount of Yak Knowledge

2165 Posts

Posted - 08/23/2006 :  08:57:30  Show Profile
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
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 08/23/2006 :  11:40:22  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message
or is it ident_current()?

Harsh Athalye
India.
"Nothing is Impossible"
Go to Top of Page

chiragkhabaria
Flowing Fount of Yak Knowledge

India
1907 Posts

Posted - 08/24/2006 :  02:15:58  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message
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
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 08/24/2006 :  04:19:59  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message
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"
Go to Top of Page

chiragkhabaria
Flowing Fount of Yak Knowledge

India
1907 Posts

Posted - 08/24/2006 :  04:42:44  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message
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
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 08/24/2006 :  07:01:55  Show Profile
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000