Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

output clause sqlsrv

Posted on 2010-09-17
6
Medium Priority
?
685 Views
Last Modified: 2012-05-10
I have a webpage i am working on that tracks absences.  it uses a php page to input records into a mssql 2005 database.  i use the sqlsrv driver as the go between.  

what i need to do is use sqlsrv_query to run an insert statment which uses the output clause to return the id(autonumber) field.  i have been searching for an hour and have not found this.  there are plenty out there about using a stored procedure to return the output but i am not running a stored procedure, i am using sqlsrv_query to run a statment with an output clause.

i need to know if this can be done, and if it can, how would i acess the the returned id field.
0
Comment
Question by:talonsblade
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 13

Expert Comment

by:darren-w-
ID: 33701827
0
 

Author Comment

by:talonsblade
ID: 33702039
i have read this but had questions. first off even microsoft admits there is a bug with the SCOPE IDENTITY() function when it has to do with parallel processing, not sure if that would cause problems for me, dont think it will.
here is the website:
http://connect.microsoft.com/SQLServer/feedback/details/328811/scope-identity-sometimes-returns-incorrect-value 

my second question is that this website is going to be open to al crew leads, what happens if two people try to add a record at the same time, is there a chance that it could return the wrong record id?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 33702045
after your insert, execute:

select scope_identity()

That will give you the identity value of the inserted record.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:talonsblade
ID: 33702089
BraindonGalderisi   you must have been typing your reply while i was typing my quetions.  please refer to my last comment
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 2000 total points
ID: 33702358
That bug is reported in SQL 2005 SP2.  Have you looked at the workarounds?  In particular, the following...


    Declare @InsertedData table (ID int)
    INSERT INTO Orders (VendorOrderID, StoreID, DateCreated)
        OUTPUT Inserted.ID
        INTO @InsertedData
    VALUES (@VendorOrderID, @StoreID, @DateCreated)
    SELECT ID FROM @InsertedData
0
 

Author Comment

by:talonsblade
ID: 33703237
i got what you suggested working.  this is my test code


$myQuery = "INSERT INTO dbo.AbsEvents (Perno, Category, Title, evtStart, evtEnd, sat, sun, defHours, InActive, Remarks) output inserted.id  SELECT '00000000', CatNum, 'test', '1/1/2009', '1/1/2009', 0, 0, 0,0,'test' FROM dbo.Absence_Category WHERE (EventClass = 'clsVac')";

$result = sqlsrv_query($conn,$myQuery);
if( $result === false)
{
	print_r(sqlsrv_errors());
}
while($myData = sqlsrv_fetch_array($result,SQLSRV_FETCH_ASSOC))
{
	print_r($myData);
}

Open in new window

0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question