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
 SQL Server 2000 Forums
 SQL Server Development (2000)
 @@idenity fron stroed procedure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 10/30/2003 :  08:36:51  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Ian writes "i have stored procedure thus:

CREATE PROCEDURE AddSiteEnquiry
@Candidatename varchar(50),
@Candidatesurname varchar(50),
@NaricOfficer int,
@Date datetime,
@StaffID int,
@MethodID int,
@AssignedOfficer int,
@Purpose int
AS
Set Nocount on
INSERT INTO tblIndividual
(fldTitle, fldName, fldSurname, fldFax, fldEMail, fldPhone,
fldAddress1, fldAddress2, fldAddress3, fldAddress4,
fldCountryID, fldPostCode)
SELECT tblStaff.fldTitle, tblStaff.fldName, tblStaff.fldSurname,
tblStaff.fldFax, tblStaff.fldEMail, tblStaff.fldPhone,
tblSite.fldAddress1, tblSite.fldAddress2,
tblSite.fldAddress3, tblSite.fldAddress4, tblSite.fldCountryID,
tblSite.fldPostCode
FROM tblSite FULL OUTER JOIN
tblStaff ON tblSite.fldSiteID = tblStaff.fldSiteID
WHERE (tblStaff.fldStaffID = @StaffID)

SELECT @@Identity from tblIndividual

INSERT INTO tblEnquiry
(fldIndividualID, fldMethodID, fldCandidateName,
fldCandidateSurname, fldInformationOfficerID,
fldDateRecieved,fldStaffID,fldAssignedOfficer)
VALUES (@@Identity, 2, @CandidateName, @CandidateSurname, @NaricOfficer, @Date, @StaffID,@AssignedOfficer)

select enquiryID=@@identity from tblEnquiry

INSERT INTO tblEnquiryCorrespondenceReceived
(fldEnquiryID, fldMethodID, fldCorrespondenceType, fldDate,
fldReceivedBy)
VALUES (@@Identity, @MethodID, @Purpose , @date,
@NaricOfficer)
Set Nocount off

and asp calling it thus:

if not zerocheck(Request.Form("country"))=0 and zerocheck(request.form("Staff"))=0 then
'individual written
Set rsEnquiry = objconn.execute("addWrittenEnquiry '" & request.form("Title") &"', '" & request.form("name") &"', '" & request.form("surname") & "'," &request.form("Country") & ", " &request.querystring("NaricOfficer") & ", '" & makedate(now()) &"', " & zerocheck(request.form("Method")) &", " & zerocheck(request.form("AssignedOfficer")) & ", " &zerocheck(request.form("Purpose")) &",'" & request.form("address1") &"','" & request.form("address2") &"','" & request.form("address3")&"','" & request.form("address4")&"','" & request.form("postcode")&"','" & request.form("phone")&"','" & request.form("fax")&"','" & request.form("email") &"','"& strCandidateName &"','"& strCandidateSurName &"'")
else
'site written
Set rsEnquiry = objconn.execute("AddSiteEnquiry '" & request.form("name") &"', '" & request.form("surname") & "'," & request.querystring("NaricOfficer") & ", '" & makedate(now()) &"', " & zerocheck(request.form("Staff")) &", " & zerocheck(request.form("Method")) &", " & zerocheck(request.form("AssignedOfficer")) &", " &zerocheck(request.form("Purpose")))
end if
'Set rsEnquiry = objconn.execute("SELECT @@Identity from tblEnquiry")
response.redirect "enquiryDetails.asp?NaricOfficer="&request.querystring("NaricOfficer")&"&EnquiryID="&rsEnquiry("enquiryID")
rsEnquiry.Close
set rsEnquiry = nothing

but ican't get the identity out of the store procedure i want the idenity of tblEnquiry
I get Error Type:
ADODB.Field (0x80020009)
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.
/naric/admin/enquiryDetails.asp

the rest of the procedure does as designed just no return value"

Stoad
Freaky Yak Linguist

*
1983 Posts

Posted - 10/30/2003 :  08:51:37  Show Profile  Visit Stoad's Homepage  Reply with Quote
Maybe better IDENT_CURRENT('myTable') instead?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.28 seconds. Powered By: Snitz Forums 2000