|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
 Posted - 10/30/2003 : 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" |
|
@@idenity fron stroed procedure
