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 intASSet Nocount onINSERT 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.fldPostCodeFROM tblSite FULL OUTER JOIN tblStaff ON tblSite.fldSiteID = tblStaff.fldSiteIDWHERE (tblStaff.fldStaffID = @StaffID)SELECT @@Identity from tblIndividualINSERT INTO tblEnquiry (fldIndividualID, fldMethodID, fldCandidateName, fldCandidateSurname, fldInformationOfficerID, fldDateRecieved,fldStaffID,fldAssignedOfficer)VALUES (@@Identity, 2, @CandidateName, @CandidateSurname, @NaricOfficer, @Date, @StaffID,@AssignedOfficer)select enquiryID=@@identity from tblEnquiryINSERT INTO tblEnquiryCorrespondenceReceived (fldEnquiryID, fldMethodID, fldCorrespondenceType, fldDate, fldReceivedBy)VALUES (@@Identity, @MethodID, @Purpose , @date, @NaricOfficer)Set Nocount offand 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 = nothingbut ican't get the identity out of the store procedure i want the idenity of tblEnquiryI 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.aspthe rest of the procedure does as designed just no return value" |
|