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
 SQL Server 2000 Forums
 SQL Server Development (2000)
 @@idenity fron stroed procedure

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-10-30 : 08:36:51
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 - 2003-10-30 : 08:51:37
Maybe better IDENT_CURRENT('myTable') instead?
Go to Top of Page
   

- Advertisement -