Multiple inserts
-
Just aiming for that dull old documentation specialist job that noel may have that nobody in their right minds would ever want....
**ASCII stupid question, get a stupid ANSI !!!** -
Now that could be an interesting trio
. -
Hey guys,
Ok, I've decided to stick with the stored procedure. I have written out all the variables and all of the insert commands.
Now, I'm kind of lost. What do I do next? How do I "call" it?
Thanks!
M
------sp_biginsert-----
create procedure sp_biginsert (
@SRrequestDate varchar(50)
@SRschoolNameFirst varchar(150)
@SRschoolNameLast varchar(150)
@SRschoolCompany varchar(150)
@SRschoolBranch varchar(150)
@SRschoolAddress1 varchar(150)
@SRschoolAddress2 varchar(150)
@SRschoolCity varchar(150)
@SRschoolState char(10)
@SRschoolZip varchar(20)
@SRschoolPhone varchar(35)
@SRschoolFax varchar(35)
@SRschoolEmail varchar(50)
@SRschoolRegion varchar(75)
@SRschoolSupervisor varchar(75)
@SRschoolclaimType varchar(50)
@SRschoolDateOfLoss varchar(50)
@SRschoolInsured varchar(50)
@SRschoolClaimNumber varchar(50)
@SRschoolPackaging varchar(50)
@SRschoolContactPrefs varchar(50)
@SRclaimantNameFirst varchar(150)
@SRclaimantNameLast varchar(150)
@SRclaimantOffice varchar(150)
@SRclaimantAddress1 varchar(150)
@SRclaimantAddress2 varchar(150)
@SRclaimantCity varchar(150)
@SRclaimantState char(10)
@SRclaimantZip varchar(20)
@SRclaimantPhone varchar(35)
@SRclaimantDateOfBirth char(50)
@SRclaimantSSN char(10)
@SRclaimantSex char(10)
@SRclaimantMaritalStatus char(10)
@SRclaimantSpouse varchar(150)
@SRclaimantChildrenNum char(15)
@SRclaimantChildrenAges varchar(25)
@SRclaimantRace char(75)
@SRclaimantHeightLow char(75)
@SRclaimantHeightHigh char(75)
@SRclaimantWeightLow char(75)
@SRclaimantWeightHigh char(75)
@SRclaimantSpecialChar varchar(500)
@SRclaimantOccupation varchar(50)
@SRclaimantInjury varchar(500)
@SRclaimantRestrictions varchar(500)
@SRclaimantRepresented char(75)
@SRclaimantVehicle1Make varchar(75)
@SRclaimantVehicle1Model varchar(75)
@SRclaimantVehicle1Tag varchar(75)
@SRclaimantVehicle2Make varchar(75)
@SRclaimantVehicle2Model varchar(75)
@SRclaimantVehicle2Tag varchar(75)
@SRsubjectEmployer varchar(150)
@SREmployerNameFirst varchar(150)
@SREmployerNameLast varchar(150)
@SREmployerAddress1 varchar(150)
@SREmployerAddress2 varchar(150)
@SREmployerCity varchar(150)
@SREmployerState char(10)
@SREmployerZip varchar(20)
@SREmployerPhone varchar(35)
@SREmployerContact char(3)
@SREmployerInfo varchar(500)
@SRcollegeNameFirst varchar(150)
@SRcollegeNameLast varchar(150)
@SRcollegeAddress1 varchar(150)
@SRcollegeAddress2 varchar(150)
@SRcollegeCity varchar(150)
@SRcollegeState char(10)
@SRcollegeZip varchar(20)
@SRcollegePhone varchar(35)
@SRcollegeCopyOnReports char(3)
@SRcollegeContact char(3)
@SRcollegeInfo varchar(500)
@SRrehabCompany varchar(150)
@SRrehabNameFirst varchar(150)
@SRrehabNameLast varchar(150)
@SRrehabAddress1 varchar(150)
@SRrehabAddress2 varchar(150)
@SRrehabCity varchar(150)
@SRrehabState char(10)
@SRrehabZip varchar(20)
@SRrehabPhone varchar(35)
@SRrehabContact char(3)
@SRrehabInfo varchar(500)
@SRbuildingNameFirst varchar(150)
@SRbuildingNameLast varchar(150)
@SRbuildingAddress1 varchar(150)
@SRbuildingAddress2 varchar(150)
@SRbuildingCity varchar(150)
@SRbuildingState char(10)
@SRbuildingZip varchar(20)
@SRbuildingPhone varchar(35)
@SRbuildingContact char(3)
@SRbuildingInfo varchar(500)
@SRreason varchar(500)
@SRobjective1 varchar(500)
@SRobjective2 varchar(500)
@SRobjective3 varchar(500)
@SRbudgetAmount char(10)
@SRsingleDaySurv char(10)
@SRsceneInvestigation char(10)
@SRactivityCheck char(10)
@SRresidencyCheck char(10)
@SRmultidaySurv char(10)
@SRstatement varchar(500)
@SRbackgrounCheck char(10)
@SRwidowCheck char(10)
@SRhospitalCanvass char(10)
@SRdueDate varchar(50)
@SRcomments varchar(500)
INSERT INTO FG_college_Info(SRcollegeNameFirst, SRcollegeNameLast,
SRcollegeAddress1, SRcollegeAddress2, SRcollegeCity, SRcollegeState, SRcollegeZip,
SRcollegePhone, CopyOnReports, SRcollegeContact, SRcollegeInfo) VALUES
(@SRcollegeNameFirst, @SRcollegeNameLast, @SRcollegeAddress1, @SRcollegeAddress2, @SRcollegeCity,
@SRcollegeState, @SRcollegeZip, @SRcollegePhone, @SRcollegeCopyOnReports, @SRcollegeContact, @SRcollegeInfo)
INSERT INTO FG_school_Info(SRrequestDate, SRschoolNameFirst, SRschoolNameLast, SRschoolCompany,
SRschoolBranch, SRschoolAddress1, SRschoolAddress2, SRschoolCity, SRschoolState, SRschoolZip,
SRschoolPhone, SRschoolFax, SRschoolEmail, SRschoolRegion, SRschoolSupervisor,
SRschoolClaimType, SRschoolDateOfLoss, SRschoolInsured, SRschoolPackaging, SRschoolContactPrefs)
VALUES(@SRrequestDate, @SRschoolNameFirst, @SRschoolNameLast, @SRschoolCompany,
@SRschoolBranch, @SRschoolAddress1, @SRschoolAddress2, @SRschoolCity, @SRschoolState, @SRschoolZip,
@SRschoolPhone, @SRschoolFax, @SRschoolEmail, @SRschoolRegion, @SRschoolSupervisor,
@SRschoolClaimType, @SRschoolDateOfLoss, @SRschoolInsured, @SRschoolPackaging, @SRschoolContactPrefs
INSERT INTO FG_Claimant_Info(SRclaimantNameFirst, SRclaimantNameMiddle, SRclaimantNameLast, SRclaimantOffice,
SRclaimantAddress1, SRclaimantAddress2, SRclaimantCity, SRclaimantState, SRclaimantZip, SRclaimantPhone,
SRclaimantDateOfBirth, SRclaimantSSN, SRclaimantSex, SRclaimantMaritalStatus, SRclaimantSpouse,
SRclaimantChildrenNum, SRclaimantChildrenAges, SRclaimantRace, SRclaimantHeightLow, SRclaimantHeightHigh,
SRclaimantWeightLow, SRclaimantWeightHigh, SRclaimantSpecialChar, SRclaimantOccupation, SRclaimantInjury,
SRclaimantRestrictions, SRclaimantRepresented, SRclaimantVehicle1Make, SRclaimantVehicle1Model,
SRclaimantVehicle1Tag, SRclaimantVehicle2Make, SRclaimantVehicle2Model, SRclaimantVehicl2Tag) VALUES(
@SRclaimantNameFirst, @SRclaimantNameMiddle, @SRclaimantNameLast, @SRclaimantOffice,
@SRclaimantAddress1, @SRclaimantAddress2, @SRclaimantCity, @SRclaimantState, @SRclaimantZip, @SRclaimantPhone,
@SRclaimantDateOfBirth, @SRclaimantSSN, @SRclaimantSex, @SRclaimantMaritalStatus, @SRclaimantSpouse,
@SRclaimantChildrenNum, @SRclaimantChildrenAges, @SRclaimantRace, @SRclaimantHeightLow, @SRclaimantHeightHigh,
@SRclaimantWeightLow, @SRclaimantWeightHigh, @SRclaimantSpecialChar, @SRclaimantOccupation, @SRclaimantInjury,
@SRclaimantRestrictions, @SRclaimantRepresented, @SRclaimantVehicle1Make, @SRclaimantVehicle1Model,
@SRclaimantVehicle1Tag, @SRclaimantVehicle2Make, @SRclaimantVehicle2Model, @SRclaimantVehicl2Tag)
INSERT INTO FG_Employer_Info(SRsubjectEmployer, SRemployerNameFirst, SRemployerNameLast,
SRemployerAddress1, SRemployerAddress2, SRemployerCity, SRemployerState, SRemployerZip, SRemployerPhone,
SRemployerContact, SRemployerInfo)VALUES(@SRsubjectEmployer, @SRemployerNameFirst, @SRemployerNameLast,
@SRemployerAddress1, @SRemployerAddress2, @SRemployerCity, @SRemployerState, @SRemployerZip, @SRemployerPhone,
@SRemployerContact, @SRemployerInfo)
INSERT INTO FG_Rehab_Info(SRrehabCompany, SRrehabNameFirst, SRrehabNameLast, SRrehabAddress1,
SRrehabAddress2, SRrehabCity, SRrehabState, SRrehabZip, SRrehabPhone, SRrehabContact, SRrehabInfo)
VALUES(@SRrehabCompany, @SRrehabNameFirst, @SRrehabNameLast, @SRrehabAddress1,
@SRrehabAddress2, @SRrehabCity, @SRrehabState, @SRrehabZip, @SRrehabPhone, @SRrehabContact, @SRrehabInfo)
INSERT INTO FG_building_Info(SRbuildingNameFirst, SRbuildingNameLast, SRbuildingAddress1,
SRbuildingAddress2, SRbuildingCity, SRbuildingState, SRbuildingZip, SRbuildingPhone,
SRbuildingContact, SRbuildingInfo) VALUES(@SRbuildingNameFirst, @SRbuildingNameLast, @SRbuildingAddress1,
@SRbuildingAddress2, @SRbuildingCity, @SRbuildingState, @SRbuildingZip, @SRbuildingPhone,
@SRbuildingContact, @SRbuildingInfo)
INSERT INTO FG_Specific_Instructions(SRreason, SRobjective1, SRobjective2, SRobjective3, SRbudgetAmount,
SRsingleDaySurv, SRsceneInvestigation, SRactivityCheck, SRresidencyCheck, SRmultidaySurv, SRstatement,
SRbackgroundCheck, SRwidowCheck, SRhospitalCanvass, SRdueDate, SRcomments) VALUE(@SRreason, @SRobjective1, @SRobjective2, SRobjective3, SRbudgetAmount,
SRsingleDaySurv, SRsceneInvestigation, SRactivityCheck, SRresidencyCheck, SRmultidaySurv, SRstatement,
@SRbackgroundCheck, @SRwidowCheck, @SRhospitalCanvass, @SRdueDate, @SRcomments)
-
First of all, here's the corrected version (had 25-30 compile errors).
Are you using asp.net or asp on the web pages?
create procedure dbo.sp_biginsert
@SRrequestDate varchar(50),
@SRschoolNameFirst varchar(150),
@SRschoolNameLast varchar(150),
@SRschoolCompany varchar(150),
@SRschoolBranch varchar(150),
@SRschoolAddress1 varchar(150),
@SRschoolAddress2 varchar(150),
@SRschoolCity varchar(150),
@SRschoolState char(10),
@SRschoolZip varchar(20),
@SRschoolPhone varchar(35),
@SRschoolFax varchar(35),
@SRschoolEmail varchar(50),
@SRschoolRegion varchar(75),
@SRschoolSupervisor varchar(75),
@SRschoolclaimType varchar(50),
@SRschoolDateOfLoss varchar(50),
@SRschoolInsured varchar(50),
@SRschoolClaimNumber varchar(50),
@SRschoolPackaging varchar(50),
@SRschoolContactPrefs varchar(50),
@SRclaimantNameFirst varchar(150),
@SRclaimantNameMiddle varchar(150),
@SRclaimantNameLast varchar(150),
@SRclaimantOffice varchar(150),
@SRclaimantAddress1 varchar(150),
@SRclaimantAddress2 varchar(150),
@SRclaimantCity varchar(150),
@SRclaimantState char(10),
@SRclaimantZip varchar(20),
@SRclaimantPhone varchar(35),
@SRclaimantDateOfBirth char(50),
@SRclaimantSSN char(10),
@SRclaimantSex char(10),
@SRclaimantMaritalStatus char(10),
@SRclaimantSpouse varchar(150),
@SRclaimantChildrenNum char(15),
@SRclaimantChildrenAges varchar(25),
@SRclaimantRace char(75),
@SRclaimantHeightLow char(75),
@SRclaimantHeightHigh char(75),
@SRclaimantWeightLow char(75),
@SRclaimantWeightHigh char(75),
@SRclaimantSpecialChar varchar(500),
@SRclaimantOccupation varchar(50),
@SRclaimantInjury varchar(500),
@SRclaimantRestrictions varchar(500),
@SRclaimantRepresented char(75),
@SRclaimantVehicle1Make varchar(75),
@SRclaimantVehicle1Model varchar(75),
@SRclaimantVehicle1Tag varchar(75),
@SRclaimantVehicle2Make varchar(75),
@SRclaimantVehicle2Model varchar(75),
@SRclaimantVehicle2Tag varchar(75),
@SRsubjectEmployer varchar(150),
@SREmployerNameFirst varchar(150),
@SREmployerNameLast varchar(150),
@SREmployerAddress1 varchar(150),
@SREmployerAddress2 varchar(150),
@SREmployerCity varchar(150),
@SREmployerState char(10),
@SREmployerZip varchar(20),
@SREmployerPhone varchar(35),
@SREmployerContact char(3),
@SREmployerInfo varchar(500),
@SRcollegeNameFirst varchar(150),
@SRcollegeNameLast varchar(150),
@SRcollegeAddress1 varchar(150),
@SRcollegeAddress2 varchar(150),
@SRcollegeCity varchar(150),
@SRcollegeState char(10),
@SRcollegeZip varchar(20),
@SRcollegePhone varchar(35),
@SRcollegeCopyOnReports char(3),
@SRcollegeContact char(3),
@SRcollegeInfo varchar(500),
@SRrehabCompany varchar(150),
@SRrehabNameFirst varchar(150),
@SRrehabNameLast varchar(150),
@SRrehabAddress1 varchar(150),
@SRrehabAddress2 varchar(150),
@SRrehabCity varchar(150),
@SRrehabState char(10),
@SRrehabZip varchar(20),
@SRrehabPhone varchar(35),
@SRrehabContact char(3),
@SRrehabInfo varchar(500),
@SRbuildingNameFirst varchar(150),
@SRbuildingNameLast varchar(150),
@SRbuildingAddress1 varchar(150),
@SRbuildingAddress2 varchar(150),
@SRbuildingCity varchar(150),
@SRbuildingState char(10),
@SRbuildingZip varchar(20),
@SRbuildingPhone varchar(35),
@SRbuildingContact char(3),
@SRbuildingInfo varchar(500),
@SRreason varchar(500),
@SRobjective1 varchar(500),
@SRobjective2 varchar(500),
@SRobjective3 varchar(500),
@SRbudgetAmount char(10),
@SRsingleDaySurv char(10),
@SRsceneInvestigation char(10),
@SRactivityCheck char(10),
@SRresidencyCheck char(10),
@SRmultidaySurv char(10),
@SRstatement varchar(500),
@SRbackgroundCheck char(10),
@SRwidowCheck char(10),
@SRhospitalCanvass char(10),
@SRdueDate varchar(50),
@SRcomments varchar(500)
AS
SET NOCOUNT ON
INSERT INTO FG_college_Info(SRcollegeNameFirst, SRcollegeNameLast,
SRcollegeAddress1, SRcollegeAddress2, SRcollegeCity, SRcollegeState, SRcollegeZip,
SRcollegePhone, CopyOnReports, SRcollegeContact, SRcollegeInfo) VALUES
(@SRcollegeNameFirst, @SRcollegeNameLast, @SRcollegeAddress1, @SRcollegeAddress2, @SRcollegeCity,
@SRcollegeState, @SRcollegeZip, @SRcollegePhone, @SRcollegeCopyOnReports, @SRcollegeContact, @SRcollegeInfo)
INSERT INTO FG_school_Info(SRrequestDate, SRschoolNameFirst, SRschoolNameLast, SRschoolCompany,
SRschoolBranch, SRschoolAddress1, SRschoolAddress2, SRschoolCity, SRschoolState, SRschoolZip,
SRschoolPhone, SRschoolFax, SRschoolEmail, SRschoolRegion, SRschoolSupervisor,
SRschoolClaimType, SRschoolDateOfLoss, SRschoolInsured, SRschoolPackaging, SRschoolContactPrefs)
VALUES(@SRrequestDate, @SRschoolNameFirst, @SRschoolNameLast, @SRschoolCompany,
@SRschoolBranch, @SRschoolAddress1, @SRschoolAddress2, @SRschoolCity, @SRschoolState, @SRschoolZip,
@SRschoolPhone, @SRschoolFax, @SRschoolEmail, @SRschoolRegion, @SRschoolSupervisor,
@SRschoolClaimType, @SRschoolDateOfLoss, @SRschoolInsured, @SRschoolPackaging, @SRschoolContactPrefs)
INSERT INTO FG_Claimant_Info(SRclaimantNameFirst, SRclaimantNameMiddle, SRclaimantNameLast, SRclaimantOffice,
SRclaimantAddress1, SRclaimantAddress2, SRclaimantCity, SRclaimantState, SRclaimantZip, SRclaimantPhone,
SRclaimantDateOfBirth, SRclaimantSSN, SRclaimantSex, SRclaimantMaritalStatus, SRclaimantSpouse,
SRclaimantChildrenNum, SRclaimantChildrenAges, SRclaimantRace, SRclaimantHeightLow, SRclaimantHeightHigh,
SRclaimantWeightLow, SRclaimantWeightHigh, SRclaimantSpecialChar, SRclaimantOccupation, SRclaimantInjury,
SRclaimantRestrictions, SRclaimantRepresented, SRclaimantVehicle1Make, SRclaimantVehicle1Model,
SRclaimantVehicle1Tag, SRclaimantVehicle2Make, SRclaimantVehicle2Model, SRclaimantVehicl2Tag) VALUES(
@SRclaimantNameFirst, @SRclaimantNameMiddle, @SRclaimantNameLast, @SRclaimantOffice,
@SRclaimantAddress1, @SRclaimantAddress2, @SRclaimantCity, @SRclaimantState, @SRclaimantZip, @SRclaimantPhone,
@SRclaimantDateOfBirth, @SRclaimantSSN, @SRclaimantSex, @SRclaimantMaritalStatus, @SRclaimantSpouse,
@SRclaimantChildrenNum, @SRclaimantChildrenAges, @SRclaimantRace, @SRclaimantHeightLow, @SRclaimantHeightHigh,
@SRclaimantWeightLow, @SRclaimantWeightHigh, @SRclaimantSpecialChar, @SRclaimantOccupation, @SRclaimantInjury,
@SRclaimantRestrictions, @SRclaimantRepresented, @SRclaimantVehicle1Make, @SRclaimantVehicle1Model,
@SRclaimantVehicle1Tag, @SRclaimantVehicle2Make, @SRclaimantVehicle2Model, @SRclaimantVehicle2Tag)
INSERT INTO FG_Employer_Info(SRsubjectEmployer, SRemployerNameFirst, SRemployerNameLast,
SRemployerAddress1, SRemployerAddress2, SRemployerCity, SRemployerState, SRemployerZip, SRemployerPhone,
SRemployerContact, SRemployerInfo)VALUES(@SRsubjectEmployer, @SRemployerNameFirst, @SRemployerNameLast,
@SRemployerAddress1, @SRemployerAddress2, @SRemployerCity, @SRemployerState, @SRemployerZip, @SRemployerPhone,
@SRemployerContact, @SRemployerInfo)
INSERT INTO FG_Rehab_Info(SRrehabCompany, SRrehabNameFirst, SRrehabNameLast, SRrehabAddress1,
SRrehabAddress2, SRrehabCity, SRrehabState, SRrehabZip, SRrehabPhone, SRrehabContact, SRrehabInfo)
VALUES(@SRrehabCompany, @SRrehabNameFirst, @SRrehabNameLast, @SRrehabAddress1,
@SRrehabAddress2, @SRrehabCity, @SRrehabState, @SRrehabZip, @SRrehabPhone, @SRrehabContact, @SRrehabInfo)
INSERT INTO FG_building_Info(SRbuildingNameFirst, SRbuildingNameLast, SRbuildingAddress1,
SRbuildingAddress2, SRbuildingCity, SRbuildingState, SRbuildingZip, SRbuildingPhone,
SRbuildingContact, SRbuildingInfo) VALUES(@SRbuildingNameFirst, @SRbuildingNameLast, @SRbuildingAddress1,
@SRbuildingAddress2, @SRbuildingCity, @SRbuildingState, @SRbuildingZip, @SRbuildingPhone,
@SRbuildingContact, @SRbuildingInfo)
INSERT INTO FG_Specific_Instructions(SRreason, SRobjective1, SRobjective2, SRobjective3, SRbudgetAmount,
SRsingleDaySurv, SRsceneInvestigation, SRactivityCheck, SRresidencyCheck, SRmultidaySurv, SRstatement,
SRbackgroundCheck, SRwidowCheck, SRhospitalCanvass, SRdueDate, SRcomments) VALUES (@SRreason, @SRobjective1, @SRobjective2, @SRobjective3, @SRbudgetAmount,
@SRsingleDaySurv, @SRsceneInvestigation, @SRactivityCheck, @SRresidencyCheck, @SRmultidaySurv, @SRstatement,
@SRbackgroundCheck, @SRwidowCheck, @SRhospitalCanvass, @SRdueDate, @SRcomments)
SET NOCOUNT OFF
GO
-
i have some vb code samples...hopefully asp should be similar ?!?!?!
magyar..what happened to the uniqueID that you were to have ?! don't see any mention of that...!!!
Dim objCmd As ADODB.Command
Set objCmd = New ADODB.Command
Set objCmd.ActiveConnection = myConnectionString
objCmd.CommandType = adCmdStoredProc
objCmd.CommandText = "sp_biginsert"
objCmd.Parameters(1) = SRrequestDate
objCmd.Parameters(2) = SRschoolNameFirst
objCmd.Parameters(3) = SRschoolNameLast
objCmd.Parameters(4) = SRschoolCompany...and so on
objCmd.Execute
Set objCmd = Nothing
**ASCII stupid question, get a stupid ANSI !!!** -
Ya forgot to mention. You still have to handle the err handling, the new ids to link the tables. Other than that don't forget to tell me what version of asp (or c#) you are using. I'll be able to generate the call code for you.
-
Hi guys,
First off, a big THANK YOU. You guys are awesome. I feel like I should pay you, but I'll pay back the board by trying to help others with my limited knowledge.
I will be using stanard ASP, not .NET.
How did you find all those errors so fast? Is there a "debug" in SQL Server?
Anyway, The UniqueID is automatically generated with the first insert on the first table. But yeah, I forgot to add it to the other four tables. So I guess it would look like this:
create procedure dbo.sp_biginsert
@SRrequestDate varchar(50),
@SRschoolNameFirst varchar(150),
@SRschoolNameLast varchar(150),
@SRschoolCompany varchar(150),
@SRschoolBranch varchar(150),
@SRschoolAddress1 varchar(150),
@SRschoolAddress2 varchar(150),
@SRschoolCity varchar(150),
@SRschoolState char(10),
@SRschoolZip varchar(20),
@SRschoolPhone varchar(35),
@SRschoolFax varchar(35),
@SRschoolEmail varchar(50),
@SRschoolRegion varchar(75),
@SRschoolSupervisor varchar(75),
@SRschoolclaimType varchar(50),
@SRschoolDateOfLoss varchar(50),
@SRschoolInsured varchar(50),
@SRschoolClaimNumber varchar(50),
@SRschoolPackaging varchar(50),
@SRschoolContactPrefs varchar(50),
@SRclaimantNameFirst varchar(150),
@SRclaimantNameMiddle varchar(150),
@SRclaimantNameLast varchar(150),
@SRclaimantOffice varchar(150),
@SRclaimantAddress1 varchar(150),
@SRclaimantAddress2 varchar(150),
@SRclaimantCity varchar(150),
@SRclaimantState char(10),
@SRclaimantZip varchar(20),
@SRclaimantPhone varchar(35),
@SRclaimantDateOfBirth char(50),
@SRclaimantSSN char(10),
@SRclaimantSex char(10),
@SRclaimantMaritalStatus char(10),
@SRclaimantSpouse varchar(150),
@SRclaimantChildrenNum char(15),
@SRclaimantChildrenAges varchar(25),
@SRclaimantRace char(75),
@SRclaimantHeightLow char(75),
@SRclaimantHeightHigh char(75),
@SRclaimantWeightLow char(75),
@SRclaimantWeightHigh char(75),
@SRclaimantSpecialChar varchar(500),
@SRclaimantOccupation varchar(50),
@SRclaimantInjury varchar(500),
@SRclaimantRestrictions varchar(500),
@SRclaimantRepresented char(75),
@SRclaimantVehicle1Make varchar(75),
@SRclaimantVehicle1Model varchar(75),
@SRclaimantVehicle1Tag varchar(75),
@SRclaimantVehicle2Make varchar(75),
@SRclaimantVehicle2Model varchar(75),
@SRclaimantVehicle2Tag varchar(75),
@SRsubjectEmployer varchar(150),
@SREmployerNameFirst varchar(150),
@SREmployerNameLast varchar(150),
@SREmployerAddress1 varchar(150),
@SREmployerAddress2 varchar(150),
@SREmployerCity varchar(150),
@SREmployerState char(10),
@SREmployerZip varchar(20),
@SREmployerPhone varchar(35),
@SREmployerContact char(3),
@SREmployerInfo varchar(500),
@SRcollegeNameFirst varchar(150),
@SRcollegeNameLast varchar(150),
@SRcollegeAddress1 varchar(150),
@SRcollegeAddress2 varchar(150),
@SRcollegeCity varchar(150),
@SRcollegeState char(10),
@SRcollegeZip varchar(20),
@SRcollegePhone varchar(35),
@SRcollegeCopyOnReports char(3),
@SRcollegeContact char(3),
@SRcollegeInfo varchar(500),
@SRrehabCompany varchar(150),
@SRrehabNameFirst varchar(150),
@SRrehabNameLast varchar(150),
@SRrehabAddress1 varchar(150),
@SRrehabAddress2 varchar(150),
@SRrehabCity varchar(150),
@SRrehabState char(10),
@SRrehabZip varchar(20),
@SRrehabPhone varchar(35),
@SRrehabContact char(3),
@SRrehabInfo varchar(500),
@SRbuildingNameFirst varchar(150),
@SRbuildingNameLast varchar(150),
@SRbuildingAddress1 varchar(150),
@SRbuildingAddress2 varchar(150),
@SRbuildingCity varchar(150),
@SRbuildingState char(10),
@SRbuildingZip varchar(20),
@SRbuildingPhone varchar(35),
@SRbuildingContact char(3),
@SRbuildingInfo varchar(500),
@SRreason varchar(500),
@SRobjective1 varchar(500),
@SRobjective2 varchar(500),
@SRobjective3 varchar(500),
@SRbudgetAmount char(10),
@SRsingleDaySurv char(10),
@SRsceneInvestigation char(10),
@SRactivityCheck char(10),
@SRresidencyCheck char(10),
@SRmultidaySurv char(10),
@SRstatement varchar(500),
@SRbackgroundCheck char(10),
@SRwidowCheck char(10),
@SRhospitalCanvass char(10),
@SRdueDate varchar(50),
@SRcomments varchar(500)
AS
SET NOCOUNT ON
INSERT INTO FG_college_Info(SRcollegeNameFirst, SRcollegeNameLast,
SRcollegeAddress1, SRcollegeAddress2, SRcollegeCity, SRcollegeState, SRcollegeZip,
SRcollegePhone, CopyOnReports, SRcollegeContact, SRcollegeInfo) VALUES
(@SRcollegeNameFirst, @SRcollegeNameLast, @SRcollegeAddress1, @SRcollegeAddress2, @SRcollegeCity,
@SRcollegeState, @SRcollegeZip, @SRcollegePhone, @SRcollegeCopyOnReports, @SRcollegeContact, @SRcollegeInfo)
Select @UniqueId=Scope_Identity()
INSERT INTO FG_school_Info(SR_school_Info_ID,SRrequestDate, SRschoolNameFirst, SRschoolNameLast, SRschoolCompany,
SRschoolBranch, SRschoolAddress1, SRschoolAddress2, SRschoolCity, SRschoolState, SRschoolZip,
SRschoolPhone, SRschoolFax, SRschoolEmail, SRschoolRegion, SRschoolSupervisor,
SRschoolClaimType, SRschoolDateOfLoss, SRschoolInsured, SRschoolPackaging, SRschoolContactPrefs)
VALUES(@UniqueId, @SRrequestDate, @SRschoolNameFirst, @SRschoolNameLast, @SRschoolCompany,
@SRschoolBranch, @SRschoolAddress1, @SRschoolAddress2, @SRschoolCity, @SRschoolState, @SRschoolZip,
@SRschoolPhone, @SRschoolFax, @SRschoolEmail, @SRschoolRegion, @SRschoolSupervisor,
@SRschoolClaimType, @SRschoolDateOfLoss, @SRschoolInsured, @SRschoolPackaging, @SRschoolContactPrefs)
INSERT INTO FG_Claimant_Info(SR_Claimant_Info_Id, SRclaimantNameFirst, SRclaimantNameMiddle, SRclaimantNameLast, SRclaimantOffice,
SRclaimantAddress1, SRclaimantAddress2, SRclaimantCity, SRclaimantState, SRclaimantZip, SRclaimantPhone,
SRclaimantDateOfBirth, SRclaimantSSN, SRclaimantSex, SRclaimantMaritalStatus, SRclaimantSpouse,
SRclaimantChildrenNum, SRclaimantChildrenAges, SRclaimantRace, SRclaimantHeightLow, SRclaimantHeightHigh,
SRclaimantWeightLow, SRclaimantWeightHigh, SRclaimantSpecialChar, SRclaimantOccupation, SRclaimantInjury,
SRclaimantRestrictions, SRclaimantRepresented, SRclaimantVehicle1Make, SRclaimantVehicle1Model,
SRclaimantVehicle1Tag, SRclaimantVehicle2Make, SRclaimantVehicle2Model, SRclaimantVehicl2Tag) VALUES(@UniqueId,
@SRclaimantNameFirst, @SRclaimantNameMiddle, @SRclaimantNameLast, @SRclaimantOffice,
@SRclaimantAddress1, @SRclaimantAddress2, @SRclaimantCity, @SRclaimantState, @SRclaimantZip, @SRclaimantPhone,
@SRclaimantDateOfBirth, @SRclaimantSSN, @SRclaimantSex, @SRclaimantMaritalStatus, @SRclaimantSpouse,
@SRclaimantChildrenNum, @SRclaimantChildrenAges, @SRclaimantRace, @SRclaimantHeightLow, @SRclaimantHeightHigh,
@SRclaimantWeightLow, @SRclaimantWeightHigh, @SRclaimantSpecialChar, @SRclaimantOccupation, @SRclaimantInjury,
@SRclaimantRestrictions, @SRclaimantRepresented, @SRclaimantVehicle1Make, @SRclaimantVehicle1Model,
@SRclaimantVehicle1Tag, @SRclaimantVehicle2Make, @SRclaimantVehicle2Model, @SRclaimantVehicle2Tag)
INSERT INTO FG_Employer_Info(SR_Employer_Info_ID, SRsubjectEmployer, SRemployerNameFirst, SRemployerNameLast,
SRemployerAddress1, SRemployerAddress2, SRemployerCity, SRemployerState, SRemployerZip, SRemployerPhone,
SRemployerContact, SRemployerInfo)VALUES(@UniqueId, @SRsubjectEmployer, @SRemployerNameFirst, @SRemployerNameLast,
@SRemployerAddress1, @SRemployerAddress2, @SRemployerCity, @SRemployerState, @SRemployerZip, @SRemployerPhone,
@SRemployerContact, @SRemployerInfo)
INSERT INTO FG_Rehab_Info(SR_Rehab_Inof, SRrehabCompany, SRrehabNameFirst, SRrehabNameLast, SRrehabAddress1,
SRrehabAddress2, SRrehabCity, SRrehabState, SRrehabZip, SRrehabPhone, SRrehabContact, SRrehabInfo)
VALUES(@UniqueId, @SRrehabCompany, @SRrehabNameFirst, @SRrehabNameLast, @SRrehabAddress1,
@SRrehabAddress2, @SRrehabCity, @SRrehabState, @SRrehabZip, @SRrehabPhone, @SRrehabContact, @SRrehabInfo)
INSERT INTO FG_building_Info(SR_building_Info_ID, SRbuildingNameFirst, SRbuildingNameLast, SRbuildingAddress1,
SRbuildingAddress2, SRbuildingCity, SRbuildingState, SRbuildingZip, SRbuildingPhone,
SRbuildingContact, SRbuildingInfo) VALUES(@UniqueId, @SRbuildingNameFirst, @SRbuildingNameLast, @SRbuildingAddress1,
@SRbuildingAddress2, @SRbuildingCity, @SRbuildingState, @SRbuildingZip, @SRbuildingPhone,
@SRbuildingContact, @SRbuildingInfo)
INSERT INTO FG_Specific_Instructions(SR_Specific_Instructions_ID, SRreason, SRobjective1, SRobjective2, SRobjective3, SRbudgetAmount,
SRsingleDaySurv, SRsceneInvestigation, SRactivityCheck, SRresidencyCheck, SRmultidaySurv, SRstatement,
SRbackgroundCheck, SRwidowCheck, SRhospitalCanvass, SRdueDate, SRcomments) VALUES (@UniqueId, @SRreason, @SRobjective1, @SRobjective2, @SRobjective3, @SRbudgetAmount,
@SRsingleDaySurv, @SRsceneInvestigation, @SRactivityCheck, @SRresidencyCheck, @SRmultidaySurv, @SRstatement,
@SRbackgroundCheck, @SRwidowCheck, @SRhospitalCanvass, @SRdueDate, @SRcomments)
SET NOCOUNT OFF
GO
-
I just tried to create it and it popped the errors. You can do that in enterprise manager or in query analyser. Also you need to finish the err handling part and the transaction part. Once this is done, test to see that the sp is doing what it should be doing. Then post the code back and I'll generate the vba call code which you will need to use to call it.
-
Well, I put this all into SQL Query Analyzer, and I got a bunch of messages. I don't understand why it's complaining about QuitWithRollBack, because I do have it declared at the end.
ERROR MESSAGES:
Server: Msg 133, Level 15, State 1, Procedure sp_biginsert, Line 197
A GOTO statement references the label 'QuitWithRollBack' but the label has not been declared.
Server: Msg 133, Level 15, State 1, Procedure sp_biginsert, Line 197
A GOTO statement references the label 'QuitWithRollBack' but the label has not been declared.
Server: Msg 133, Level 15, State 1, Procedure sp_biginsert, Line 197
A GOTO statement references the label 'QuitWithRollBack' but the label has not been declared.
Server: Msg 133, Level 15, State 1, Procedure sp_biginsert, Line 197
A GOTO statement references the label 'QuitWithRollBack' but the label has not been declared.
Server: Msg 133, Level 15, State 1, Procedure sp_biginsert, Line 197
A GOTO statement references the label 'QuitWithRollBack' but the label has not been declared.
Server: Msg 133, Level 15, State 1, Procedure sp_biginsert, Line 197
A GOTO statement references the label 'QuitWithRollBack' but the label has not been declared.
Server: Msg 133, Level 15, State 1, Procedure sp_biginsert, Line 197
A GOTO statement references the label 'QuitWithRollBack' but the label has not been declared.
Server: Msg 178, Level 15, State 1, Line 6
A RETURN statement with a return value cannot be used in this context.
Server: Msg 137, Level 15, State 1, Line 8
Must declare the variable '@Err'.
STORED PROCEDURE:
create procedure dbo.sp_biginsert
@SRrequestDate varchar(50),
@SRschoolNameFirst varchar(150),
@SRschoolNameLast varchar(150),
@SRschoolCompany varchar(150),
@SRschoolBranch varchar(150),
@SRschoolAddress1 varchar(150),
@SRschoolAddress2 varchar(150),
@SRschoolCity varchar(150),
@SRschoolState char(10),
@SRschoolZip varchar(20),
@SRschoolPhone varchar(35),
@SRschoolFax varchar(35),
@SRschoolEmail varchar(50),
@SRschoolRegion varchar(75),
@SRschoolSupervisor varchar(75),
@SRschoolclaimType varchar(50),
@SRschoolDateOfLoss varchar(50),
@SRschoolInsured varchar(50),
@SRschoolClaimNumber varchar(50),
@SRschoolPackaging varchar(50),
@SRschoolContactPrefs varchar(50),
@SRclaimantNameFirst varchar(150),
@SRclaimantNameMiddle varchar(150),
@SRclaimantNameLast varchar(150),
@SRclaimantOffice varchar(150),
@SRclaimantAddress1 varchar(150),
@SRclaimantAddress2 varchar(150),
@SRclaimantCity varchar(150),
@SRclaimantState char(10),
@SRclaimantZip varchar(20),
@SRclaimantPhone varchar(35),
@SRclaimantDateOfBirth char(50),
@SRclaimantSSN char(10),
@SRclaimantSex char(10),
@SRclaimantMaritalStatus char(10),
@SRclaimantSpouse varchar(150),
@SRclaimantChildrenNum char(15),
@SRclaimantChildrenAges varchar(25),
@SRclaimantRace char(75),
@SRclaimantHeightLow char(75),
@SRclaimantHeightHigh char(75),
@SRclaimantWeightLow char(75),
@SRclaimantWeightHigh char(75),
@SRclaimantSpecialChar varchar(500),
@SRclaimantOccupation varchar(50),
@SRclaimantInjury varchar(500),
@SRclaimantRestrictions varchar(500),
@SRclaimantRepresented char(75),
@SRclaimantVehicle1Make varchar(75),
@SRclaimantVehicle1Model varchar(75),
@SRclaimantVehicle1Tag varchar(75),
@SRclaimantVehicle2Make varchar(75),
@SRclaimantVehicle2Model varchar(75),
@SRclaimantVehicle2Tag varchar(75),
@SRsubjectEmployer varchar(150),
@SREmployerNameFirst varchar(150),
@SREmployerNameLast varchar(150),
@SREmployerAddress1 varchar(150),
@SREmployerAddress2 varchar(150),
@SREmployerCity varchar(150),
@SREmployerState char(10),
@SREmployerZip varchar(20),
@SREmployerPhone varchar(35),
@SREmployerContact char(3),
@SREmployerInfo varchar(500),
@SRcollegeNameFirst varchar(150),
@SRcollegeNameLast varchar(150),
@SRcollegeAddress1 varchar(150),
@SRcollegeAddress2 varchar(150),
@SRcollegeCity varchar(150),
@SRcollegeState char(10),
@SRcollegeZip varchar(20),
@SRcollegePhone varchar(35),
@SRcollegeCopyOnReports char(3),
@SRcollegeContact char(3),
@SRcollegeInfo varchar(500),
@SRrehabCompany varchar(150),
@SRrehabNameFirst varchar(150),
@SRrehabNameLast varchar(150),
@SRrehabAddress1 varchar(150),
@SRrehabAddress2 varchar(150),
@SRrehabCity varchar(150),
@SRrehabState char(10),
@SRrehabZip varchar(20),
@SRrehabPhone varchar(35),
@SRrehabContact char(3),
@SRrehabInfo varchar(500),
@SRbuildingNameFirst varchar(150),
@SRbuildingNameLast varchar(150),
@SRbuildingAddress1 varchar(150),
@SRbuildingAddress2 varchar(150),
@SRbuildingCity varchar(150),
@SRbuildingState char(10),
@SRbuildingZip varchar(20),
@SRbuildingPhone varchar(35),
@SRbuildingContact char(3),
@SRbuildingInfo varchar(500),
@SRreason varchar(500),
@SRobjective1 varchar(500),
@SRobjective2 varchar(500),
@SRobjective3 varchar(500),
@SRbudgetAmount char(10),
@SRsingleDaySurv char(10),
@SRsceneInvestigation char(10),
@SRactivityCheck char(10),
@SRresidencyCheck char(10),
@SRmultidaySurv char(10),
@SRstatement varchar(500),
@SRbackgroundCheck char(10),
@SRwidowCheck char(10),
@SRhospitalCanvass char(10),
@SRdueDate varchar(50),
@SRcomments varchar(500)
AS
Declare @UniqueID int, @Err int
BEGIN TRANSACTION
SET NOCOUNT ON
INSERT INTO FG_college_Info(SRcollegeNameFirst, SRcollegeNameLast,
SRcollegeAddress1, SRcollegeAddress2, SRcollegeCity, SRcollegeState, SRcollegeZip,
SRcollegePhone, CopyOnReports, SRcollegeContact, SRcollegeInfo) VALUES
(@SRcollegeNameFirst, @SRcollegeNameLast, @SRcollegeAddress1, @SRcollegeAddress2, @SRcollegeCity,
@SRcollegeState, @SRcollegeZip, @SRcollegePhone, @SRcollegeCopyOnReports, @SRcollegeContact, @SRcollegeInfo)
Select @UniqueId=Scope_Identity()
if @Err <> 0 Goto QuitWithRollBack
INSERT INTO FG_school_Info(SR_school_Info_ID,SRrequestDate, SRschoolNameFirst, SRschoolNameLast, SRschoolCompany,
SRschoolBranch, SRschoolAddress1, SRschoolAddress2, SRschoolCity, SRschoolState, SRschoolZip,
SRschoolPhone, SRschoolFax, SRschoolEmail, SRschoolRegion, SRschoolSupervisor,
SRschoolClaimType, SRschoolDateOfLoss, SRschoolInsured, SRschoolPackaging, SRschoolContactPrefs)
VALUES(@UniqueId, @SRrequestDate, @SRschoolNameFirst, @SRschoolNameLast, @SRschoolCompany,
@SRschoolBranch, @SRschoolAddress1, @SRschoolAddress2, @SRschoolCity, @SRschoolState, @SRschoolZip,
@SRschoolPhone, @SRschoolFax, @SRschoolEmail, @SRschoolRegion, @SRschoolSupervisor,
@SRschoolClaimType, @SRschoolDateOfLoss, @SRschoolInsured, @SRschoolPackaging, @SRschoolContactPrefs)
if @Err <> 0 Goto QuitWithRollBack
INSERT INTO FG_Claimant_Info(SR_Claimant_Info_Id, SRclaimantNameFirst, SRclaimantNameMiddle, SRclaimantNameLast, SRclaimantOffice,
SRclaimantAddress1, SRclaimantAddress2, SRclaimantCity, SRclaimantState, SRclaimantZip, SRclaimantPhone,
SRclaimantDateOfBirth, SRclaimantSSN, SRclaimantSex, SRclaimantMaritalStatus, SRclaimantSpouse,
SRclaimantChildrenNum, SRclaimantChildrenAges, SRclaimantRace, SRclaimantHeightLow, SRclaimantHeightHigh,
SRclaimantWeightLow, SRclaimantWeightHigh, SRclaimantSpecialChar, SRclaimantOccupation, SRclaimantInjury,
SRclaimantRestrictions, SRclaimantRepresented, SRclaimantVehicle1Make, SRclaimantVehicle1Model,
SRclaimantVehicle1Tag, SRclaimantVehicle2Make, SRclaimantVehicle2Model, SRclaimantVehicl2Tag) VALUES(@UniqueId,
@SRclaimantNameFirst, @SRclaimantNameMiddle, @SRclaimantNameLast, @SRclaimantOffice,
@SRclaimantAddress1, @SRclaimantAddress2, @SRclaimantCity, @SRclaimantState, @SRclaimantZip, @SRclaimantPhone,
@SRclaimantDateOfBirth, @SRclaimantSSN, @SRclaimantSex, @SRclaimantMaritalStatus, @SRclaimantSpouse,
@SRclaimantChildrenNum, @SRclaimantChildrenAges, @SRclaimantRace, @SRclaimantHeightLow, @SRclaimantHeightHigh,
@SRclaimantWeightLow, @SRclaimantWeightHigh, @SRclaimantSpecialChar, @SRclaimantOccupation, @SRclaimantInjury,
@SRclaimantRestrictions, @SRclaimantRepresented, @SRclaimantVehicle1Make, @SRclaimantVehicle1Model,
@SRclaimantVehicle1Tag, @SRclaimantVehicle2Make, @SRclaimantVehicle2Model, @SRclaimantVehicle2Tag)
if @Err <> 0 Goto QuitWithRollBack
INSERT INTO FG_Employer_Info(SR_Employer_Info_ID, SRsubjectEmployer, SRemployerNameFirst, SRemployerNameLast,
SRemployerAddress1, SRemployerAddress2, SRemployerCity, SRemployerState, SRemployerZip, SRemployerPhone,
SRemployerContact, SRemployerInfo)VALUES(@UniqueId, @SRsubjectEmployer, @SRemployerNameFirst, @SRemployerNameLast,
@SRemployerAddress1, @SRemployerAddress2, @SRemployerCity, @SRemployerState, @SRemployerZip, @SRemployerPhone,
@SRemployerContact, @SRemployerInfo)
if @Err <> 0 Goto QuitWithRollBack
INSERT INTO FG_Rehab_Info(SR_Rehab_Inof, SRrehabCompany, SRrehabNameFirst, SRrehabNameLast, SRrehabAddress1,
SRrehabAddress2, SRrehabCity, SRrehabState, SRrehabZip, SRrehabPhone, SRrehabContact, SRrehabInfo)
VALUES(@UniqueId, @SRrehabCompany, @SRrehabNameFirst, @SRrehabNameLast, @SRrehabAddress1,
@SRrehabAddress2, @SRrehabCity, @SRrehabState, @SRrehabZip, @SRrehabPhone, @SRrehabContact, @SRrehabInfo)
if @Err <> 0 Goto QuitWithRollBack
INSERT INTO FG_building_Info(SR_building_Info_ID, SRbuildingNameFirst, SRbuildingNameLast, SRbuildingAddress1,
SRbuildingAddress2, SRbuildingCity, SRbuildingState, SRbuildingZip, SRbuildingPhone,
SRbuildingContact, SRbuildingInfo) VALUES(@UniqueId, @SRbuildingNameFirst, @SRbuildingNameLast, @SRbuildingAddress1,
@SRbuildingAddress2, @SRbuildingCity, @SRbuildingState, @SRbuildingZip, @SRbuildingPhone,
@SRbuildingContact, @SRbuildingInfo)
if @Err <> 0 Goto QuitWithRollBack
INSERT INTO FG_Specific_Instructions(SR_Specific_Instructions_ID, SRreason, SRobjective1, SRobjective2, SRobjective3, SRbudgetAmount,
SRsingleDaySurv, SRsceneInvestigation, SRactivityCheck, SRresidencyCheck, SRmultidaySurv, SRstatement,
SRbackgroundCheck, SRwidowCheck, SRhospitalCanvass, SRdueDate, SRcomments) VALUES (@UniqueId, @SRreason, @SRobjective1, @SRobjective2, @SRobjective3, @SRbudgetAmount,
@SRsingleDaySurv, @SRsceneInvestigation, @SRactivityCheck, @SRresidencyCheck, @SRmultidaySurv, @SRstatement,
@SRbackgroundCheck, @SRwidowCheck, @SRhospitalCanvass, @SRdueDate, @SRcomments)
if @Err <> 0 Goto QuitWithRollBack
SET NOCOUNT OFF
GO
COMMIT TRANSACTION
Return 0
QuitWithRollBack:
ROLLBACK TRAN
Return @Err
-
SP :
create procedure dbo.sp_biginsert
@SRrequestDate varchar(50),
@SRschoolNameFirst varchar(150),
@SRschoolNameLast varchar(150),
@SRschoolCompany varchar(150),
@SRschoolBranch varchar(150),
@SRschoolAddress1 varchar(150),
@SRschoolAddress2 varchar(150),
@SRschoolCity varchar(150),
@SRschoolState char(10),
@SRschoolZip varchar(20),
@SRschoolPhone varchar(35),
@SRschoolFax varchar(35),
@SRschoolEmail varchar(50),
@SRschoolRegion varchar(75),
@SRschoolSupervisor varchar(75),
@SRschoolclaimType varchar(50),
@SRschoolDateOfLoss varchar(50),
@SRschoolInsured varchar(50),
@SRschoolClaimNumber varchar(50),
@SRschoolPackaging varchar(50),
@SRschoolContactPrefs varchar(50),
@SRclaimantNameFirst varchar(150),
@SRclaimantNameMiddle varchar(150),
@SRclaimantNameLast varchar(150),
@SRclaimantOffice varchar(150),
@SRclaimantAddress1 varchar(150),
@SRclaimantAddress2 varchar(150),
@SRclaimantCity varchar(150),
@SRclaimantState char(10),
@SRclaimantZip varchar(20),
@SRclaimantPhone varchar(35),
@SRclaimantDateOfBirth char(50),
@SRclaimantSSN char(10),
@SRclaimantSex char(10),
@SRclaimantMaritalStatus char(10),
@SRclaimantSpouse varchar(150),
@SRclaimantChildrenNum char(15),
@SRclaimantChildrenAges varchar(25),
@SRclaimantRace char(75),
@SRclaimantHeightLow char(75),
@SRclaimantHeightHigh char(75),
@SRclaimantWeightLow char(75),
@SRclaimantWeightHigh char(75),
@SRclaimantSpecialChar varchar(500),
@SRclaimantOccupation varchar(50),
@SRclaimantInjury varchar(500),
@SRclaimantRestrictions varchar(500),
@SRclaimantRepresented char(75),
@SRclaimantVehicle1Make varchar(75),
@SRclaimantVehicle1Model varchar(75),
@SRclaimantVehicle1Tag varchar(75),
@SRclaimantVehicle2Make varchar(75),
@SRclaimantVehicle2Model varchar(75),
@SRclaimantVehicle2Tag varchar(75),
@SRsubjectEmployer varchar(150),
@SREmployerNameFirst varchar(150),
@SREmployerNameLast varchar(150),
@SREmployerAddress1 varchar(150),
@SREmployerAddress2 varchar(150),
@SREmployerCity varchar(150),
@SREmployerState char(10),
@SREmployerZip varchar(20),
@SREmployerPhone varchar(35),
@SREmployerContact char(3),
@SREmployerInfo varchar(500),
@SRcollegeNameFirst varchar(150),
@SRcollegeNameLast varchar(150),
@SRcollegeAddress1 varchar(150),
@SRcollegeAddress2 varchar(150),
@SRcollegeCity varchar(150),
@SRcollegeState char(10),
@SRcollegeZip varchar(20),
@SRcollegePhone varchar(35),
@SRcollegeCopyOnReports char(3),
@SRcollegeContact char(3),
@SRcollegeInfo varchar(500),
@SRrehabCompany varchar(150),
@SRrehabNameFirst varchar(150),
@SRrehabNameLast varchar(150),
@SRrehabAddress1 varchar(150),
@SRrehabAddress2 varchar(150),
@SRrehabCity varchar(150),
@SRrehabState char(10),
@SRrehabZip varchar(20),
@SRrehabPhone varchar(35),
@SRrehabContact char(3),
@SRrehabInfo varchar(500),
@SRbuildingNameFirst varchar(150),
@SRbuildingNameLast varchar(150),
@SRbuildingAddress1 varchar(150),
@SRbuildingAddress2 varchar(150),
@SRbuildingCity varchar(150),
@SRbuildingState char(10),
@SRbuildingZip varchar(20),
@SRbuildingPhone varchar(35),
@SRbuildingContact char(3),
@SRbuildingInfo varchar(500),
@SRreason varchar(500),
@SRobjective1 varchar(500),
@SRobjective2 varchar(500),
@SRobjective3 varchar(500),
@SRbudgetAmount char(10),
@SRsingleDaySurv char(10),
@SRsceneInvestigation char(10),
@SRactivityCheck char(10),
@SRresidencyCheck char(10),
@SRmultidaySurv char(10),
@SRstatement varchar(500),
@SRbackgroundCheck char(10),
@SRwidowCheck char(10),
@SRhospitalCanvass char(10),
@SRdueDate varchar(50),
@SRcomments varchar(500)
AS
Declare @UniqueID as int
Declare @Err as int
BEGIN TRANSACTION
SET NOCOUNT ON
INSERT INTO FG_college_Info(SRcollegeNameFirst, SRcollegeNameLast,
SRcollegeAddress1, SRcollegeAddress2, SRcollegeCity, SRcollegeState, SRcollegeZip,
SRcollegePhone, CopyOnReports, SRcollegeContact, SRcollegeInfo) VALUES
(@SRcollegeNameFirst, @SRcollegeNameLast, @SRcollegeAddress1, @SRcollegeAddress2, @SRcollegeCity,
@SRcollegeState, @SRcollegeZip, @SRcollegePhone, @SRcollegeCopyOnReports, @SRcollegeContact, @SRcollegeInfo)
Select @UniqueId=Scope_Identity(), @Err = @@ERROR
if @Err 0 Goto QuitWithRollBack
INSERT INTO FG_school_Info(SR_school_Info_ID,SRrequestDate, SRschoolNameFirst, SRschoolNameLast, SRschoolCompany,
SRschoolBranch, SRschoolAddress1, SRschoolAddress2, SRschoolCity, SRschoolState, SRschoolZip,
SRschoolPhone, SRschoolFax, SRschoolEmail, SRschoolRegion, SRschoolSupervisor,
SRschoolClaimType, SRschoolDateOfLoss, SRschoolInsured, SRschoolPackaging, SRschoolContactPrefs)
VALUES(@UniqueId, @SRrequestDate, @SRschoolNameFirst, @SRschoolNameLast, @SRschoolCompany,
@SRschoolBranch, @SRschoolAddress1, @SRschoolAddress2, @SRschoolCity, @SRschoolState, @SRschoolZip,
@SRschoolPhone, @SRschoolFax, @SRschoolEmail, @SRschoolRegion, @SRschoolSupervisor,
@SRschoolClaimType, @SRschoolDateOfLoss, @SRschoolInsured, @SRschoolPackaging, @SRschoolContactPrefs)
Select @Err = @@ERROR
if @Err 0 Goto QuitWithRollBack
INSERT INTO FG_Claimant_Info(SR_Claimant_Info_Id, SRclaimantNameFirst, SRclaimantNameMiddle, SRclaimantNameLast, SRclaimantOffice,
SRclaimantAddress1, SRclaimantAddress2, SRclaimantCity, SRclaimantState, SRclaimantZip, SRclaimantPhone,
SRclaimantDateOfBirth, SRclaimantSSN, SRclaimantSex, SRclaimantMaritalStatus, SRclaimantSpouse,
SRclaimantChildrenNum, SRclaimantChildrenAges, SRclaimantRace, SRclaimantHeightLow, SRclaimantHeightHigh,
SRclaimantWeightLow, SRclaimantWeightHigh, SRclaimantSpecialChar, SRclaimantOccupation, SRclaimantInjury,
SRclaimantRestrictions, SRclaimantRepresented, SRclaimantVehicle1Make, SRclaimantVehicle1Model,
SRclaimantVehicle1Tag, SRclaimantVehicle2Make, SRclaimantVehicle2Model, SRclaimantVehicl2Tag) VALUES(@UniqueId,
@SRclaimantNameFirst, @SRclaimantNameMiddle, @SRclaimantNameLast, @SRclaimantOffice,
@SRclaimantAddress1, @SRclaimantAddress2, @SRclaimantCity, @SRclaimantState, @SRclaimantZip, @SRclaimantPhone,
@SRclaimantDateOfBirth, @SRclaimantSSN, @SRclaimantSex, @SRclaimantMaritalStatus, @SRclaimantSpouse,
@SRclaimantChildrenNum, @SRclaimantChildrenAges, @SRclaimantRace, @SRclaimantHeightLow, @SRclaimantHeightHigh,
@SRclaimantWeightLow, @SRclaimantWeightHigh, @SRclaimantSpecialChar, @SRclaimantOccupation, @SRclaimantInjury,
@SRclaimantRestrictions, @SRclaimantRepresented, @SRclaimantVehicle1Make, @SRclaimantVehicle1Model,
@SRclaimantVehicle1Tag, @SRclaimantVehicle2Make, @SRclaimantVehicle2Model, @SRclaimantVehicle2Tag)
Select @Err = @@ERROR
if @Err 0 Goto QuitWithRollBack
INSERT INTO FG_Employer_Info(SR_Employer_Info_ID, SRsubjectEmployer, SRemployerNameFirst, SRemployerNameLast,
SRemployerAddress1, SRemployerAddress2, SRemployerCity, SRemployerState, SRemployerZip, SRemployerPhone,
SRemployerContact, SRemployerInfo)VALUES(@UniqueId, @SRsubjectEmployer, @SRemployerNameFirst, @SRemployerNameLast,
@SRemployerAddress1, @SRemployerAddress2, @SRemployerCity, @SRemployerState, @SRemployerZip, @SRemployerPhone,
@SRemployerContact, @SRemployerInfo)
Select @Err = @@ERROR
if @Err 0 Goto QuitWithRollBack
INSERT INTO FG_Rehab_Info(SR_Rehab_Inof, SRrehabCompany, SRrehabNameFirst, SRrehabNameLast, SRrehabAddress1,
SRrehabAddress2, SRrehabCity, SRrehabState, SRrehabZip, SRrehabPhone, SRrehabContact, SRrehabInfo)
VALUES(@UniqueId, @SRrehabCompany, @SRrehabNameFirst, @SRrehabNameLast, @SRrehabAddress1,
@SRrehabAddress2, @SRrehabCity, @SRrehabState, @SRrehabZip, @SRrehabPhone, @SRrehabContact, @SRrehabInfo)
Select @Err = @@ERROR
if @Err 0 Goto QuitWithRollBack
INSERT INTO FG_building_Info(SR_building_Info_ID, SRbuildingNameFirst, SRbuildingNameLast, SRbuildingAddress1,
SRbuildingAddress2, SRbuildingCity, SRbuildingState, SRbuildingZip, SRbuildingPhone,
SRbuildingContact, SRbuildingInfo) VALUES(@UniqueId, @SRbuildingNameFirst, @SRbuildingNameLast, @SRbuildingAddress1,
@SRbuildingAddress2, @SRbuildingCity, @SRbuildingState, @SRbuildingZip, @SRbuildingPhone,
@SRbuildingContact, @SRbuildingInfo)
Select @Err = @@ERROR
if @Err 0 Goto QuitWithRollBack
INSERT INTO FG_Specific_Instructions(SR_Specific_Instructions_ID, SRreason, SRobjective1, SRobjective2, SRobjective3, SRbudgetAmount,
SRsingleDaySurv, SRsceneInvestigation, SRactivityCheck, SRresidencyCheck, SRmultidaySurv, SRstatement,
SRbackgroundCheck, SRwidowCheck, SRhospitalCanvass, SRdueDate, SRcomments) VALUES (@UniqueId, @SRreason, @SRobjective1, @SRobjective2, @SRobjective3, @SRbudgetAmount,
@SRsingleDaySurv, @SRsceneInvestigation, @SRactivityCheck, @SRresidencyCheck, @SRmultidaySurv, @SRstatement,
@SRbackgroundCheck, @SRwidowCheck, @SRhospitalCanvass, @SRdueDate, @SRcomments)
Select @Err = @@ERROR
if @Err 0 Goto QuitWithRollBack
COMMIT TRANSACTION
Return 0
QuitWithRollBack:
ROLLBACK TRAN
Return @Err
SET NOCOUNT OFF
GO
-
Wow! That worked! No errors!
What does the Select @Err=@@ERROR do and NOCOUNT do?
Is my stored procedure all done? Now I just need to add this to my asp script?
THANKS!
M
-
You may change this to not use the function and directly pass the correct values to the parameters.
You will need to change the Declare as ADODB.* to server.createObject("ADODB.*")
you will also need to put this line of code so that the ado constants work in the page (I'm sorry but I don't have time to translate them for you)
(replace the $ with "")
$!--- #include file="..path/adovbs.inc" --$
MyCn is the connection to the database (global to the form in that code so you'll have to declare one).
Private Function exec_sp_biginsert(ByVal SRrequestDate As String, ByVal SRschoolNameFirst As String, ByVal SRschoolNameLast As String, ByVal SRschoolCompany As String, ByVal SRschoolBranch As String, ByVal SRschoolAddress1 As String, ByVal SRschoolAddress2 As String, ByVal SRschoolCity As String, ByVal SRschoolState As String, ByVal SRschoolZip As String, ByVal SRschoolPhone As String, ByVal SRschoolFax As String, ByVal SRschoolEmail As String, ByVal SRschoolRegion As String, ByVal SRschoolSupervisor As String, ByVal SRschoolclaimType As String, ByVal SRschoolDateOfLoss As String, ByVal SRschoolInsured As String, ByVal SRschoolClaimNumber As String, ByVal SRschoolPackaging As String, ByVal SRschoolContactPrefs As String, ByVal SRclaimantNameFirst As String, ByVal SRclaimantNameMiddle As String, ByVal SRclaimantNameLast As String, ByVal SRclaimantOffice As String, ByVal SRclaimantAddress1 As String, ByVal SRclaimantAddress2 As String, ByVal SRclaimantCity As String, ByVal SRclaimantState As String, ByVal SRclaimantZip As String, ByVal SRclaimantPhone As String, ByVal SRclaimantDateOfBirth As String, ByVal SRclaimantSSN As String, ByVal SRclaimantSex As String, ByVal SRclaimantMaritalStatus As String, ByVal SRclaimantSpouse As String, ByVal SRclaimantChildrenNum As String, ByVal SRclaimantChildrenAges As String, ByVal SRclaimantRace As String, ByVal SRclaimantHeightLow As String, ByVal SRclaimantHeightHigh As String, ByVal SRclaimantWeightLow As String, ByVal SRclaimantWeightHigh As String, ByVal SRclaimantSpecialChar As String, ByVal SRclaimantOccupation As String, ByVal SRclaimantInjury As String, ByVal SRclaimantRestrictions As String, ByVal SRclaimantRepresented As String, ByVal SRclaimantVehicle1Make As String, ByVal SRclaimantVehicle1Model As String, ByVal SRclaimantVehicle1Tag As String, ByVal SRclaimantVehicle2Make As String, ByVal SRclaimantVehicle2Model As String, ByVal SRclaimantVehicle2Tag As String, ByVal SRsubjectEmployer As String, ByVal SREmployerNameFirst As String, ByVal SREmployerNameLast As String, ByVal SREmployerAddress1 As String, ByVal SREmployerAddress2 As String, ByVal SREmployerCity As String, ByVal SREmployerState As String, ByVal SREmployerZip As String, ByVal SREmployerPhone As String, ByVal SREmployerContact As String, ByVal SREmployerInfo As String, ByVal SRcollegeNameFirst As String, ByVal SRcollegeNameLast As String, ByVal SRcollegeAddress1 As String, ByVal SRcollegeAddress2 As String, ByVal SRcollegeCity As String, ByVal SRcollegeState As String, ByVal SRcollegeZip As String, ByVal SRcollegePhone As String, ByVal SRcollegeCopyOnReports As String, ByVal SRcollegeContact As String, ByVal SRcollegeInfo As String, ByVal SRrehabCompany As String, ByVal SRrehabNameFirst As String, ByVal SRrehabNameLast As String, ByVal SRrehabAddress1 As String, ByVal SRrehabAddress2 As String, ByVal SRrehabCity As String, ByVal SRrehabState As String, ByVal SRrehabZip As String, ByVal SRrehabPhone As String, ByVal SRrehabContact As String, ByVal SRrehabInfo As String, ByVal SRbuildingNameFirst As String, ByVal SRbuildingNameLast As String, ByVal SRbuildingAddress1 As String, ByVal SRbuildingAddress2 As String, ByVal SRbuildingCity As String, ByVal SRbuildingState As String, ByVal SRbuildingZip As String, ByVal SRbuildingPhone As String, ByVal SRbuildingContact As String, ByVal SRbuildingInfo As String, ByVal SRreason As String, ByVal SRobjective1 As String, ByVal SRobjective2 As String, ByVal SRobjective3 As String, ByVal SRbudgetAmount As String, ByVal SRsingleDaySurv As String, ByVal SRsceneInvestigation As String, ByVal SRactivityCheck As String, ByVal SRresidencyCheck As String, ByVal SRmultidaySurv As String, ByVal SRstatement As String, ByVal SRbackgroundCheck As String, ByVal SRwidowCheck As String, ByVal SRhospitalCanvass As String, ByVal SRdueDate As String, ByVal SRcomments As String, Optional ByRef ReturnValue As Integer) As Integer
On Error GoTo Gestion
Dim MyCmd As ADODB.Command
Set MyCmd = New ADODB.Command
MyCmd.CommandText = "dbo.sp_biginsert"
MyCmd.CommandType = adCmdStoredProc
Dim MyParam As ADODB.Parameter
Set MyParam = New ADODB.Parameter
MyParam.Direction = adParamReturnValue
MyParam.Name = "@Return"
MyParam.Type = adInteger
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRrequestDate"
MyParam.Value = SRrequestDate
MyParam.Size = 50
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRschoolNameFirst"
MyParam.Value = SRschoolNameFirst
MyParam.Size = 150
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRschoolNameLast"
MyParam.Value = SRschoolNameLast
MyParam.Size = 150
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRschoolCompany"
MyParam.Value = SRschoolCompany
MyParam.Size = 150
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRschoolBranch"
MyParam.Value = SRschoolBranch
MyParam.Size = 150
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRschoolAddress1"
MyParam.Value = SRschoolAddress1
MyParam.Size = 150
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRschoolAddress2"
MyParam.Value = SRschoolAddress2
MyParam.Size = 150
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRschoolCity"
MyParam.Value = SRschoolCity
MyParam.Size = 150
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRschoolState"
MyParam.Value = SRschoolState
MyParam.Size = 10
MyParam.Direction = adParamInput
MyParam.Type = adChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRschoolZip"
MyParam.Value = SRschoolZip
MyParam.Size = 20
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRschoolPhone"
MyParam.Value = SRschoolPhone
MyParam.Size = 35
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRschoolFax"
MyParam.Value = SRschoolFax
MyParam.Size = 35
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRschoolEmail"
MyParam.Value = SRschoolEmail
MyParam.Size = 50
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRschoolRegion"
MyParam.Value = SRschoolRegion
MyParam.Size = 75
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRschoolSupervisor"
MyParam.Value = SRschoolSupervisor
MyParam.Size = 75
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRschoolclaimType"
MyParam.Value = SRschoolclaimType
MyParam.Size = 50
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRschoolDateOfLoss"
MyParam.Value = SRschoolDateOfLoss
MyParam.Size = 50
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRschoolInsured"
MyParam.Value = SRschoolInsured
MyParam.Size = 50
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRschoolClaimNumber"
MyParam.Value = SRschoolClaimNumber
MyParam.Size = 50
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRschoolPackaging"
MyParam.Value = SRschoolPackaging
MyParam.Size = 50
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRschoolContactPrefs"
MyParam.Value = SRschoolContactPrefs
MyParam.Size = 50
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRclaimantNameFirst"
MyParam.Value = SRclaimantNameFirst
MyParam.Size = 150
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRclaimantNameMiddle"
MyParam.Value = SRclaimantNameMiddle
MyParam.Size = 150
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRclaimantNameLast"
MyParam.Value = SRclaimantNameLast
MyParam.Size = 150
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRclaimantOffice"
MyParam.Value = SRclaimantOffice
MyParam.Size = 150
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRclaimantAddress1"
MyParam.Value = SRclaimantAddress1
MyParam.Size = 150
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRclaimantAddress2"
MyParam.Value = SRclaimantAddress2
MyParam.Size = 150
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRclaimantCity"
MyParam.Value = SRclaimantCity
MyParam.Size = 150
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRclaimantState"
MyParam.Value = SRclaimantState
MyParam.Size = 10
MyParam.Direction = adParamInput
MyParam.Type = adChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRclaimantZip"
MyParam.Value = SRclaimantZip
MyParam.Size = 20
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRclaimantPhone"
MyParam.Value = SRclaimantPhone
MyParam.Size = 35
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRclaimantDateOfBirth"
MyParam.Value = SRclaimantDateOfBirth
MyParam.Size = 50
MyParam.Direction = adParamInput
MyParam.Type = adChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRclaimantSSN"
MyParam.Value = SRclaimantSSN
MyParam.Size = 10
MyParam.Direction = adParamInput
MyParam.Type = adChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRclaimantSex"
MyParam.Value = SRclaimantSex
MyParam.Size = 10
MyParam.Direction = adParamInput
MyParam.Type = adChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRclaimantMaritalStatus"
MyParam.Value = SRclaimantMaritalStatus
MyParam.Size = 10
MyParam.Direction = adParamInput
MyParam.Type = adChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRclaimantSpouse"
MyParam.Value = SRclaimantSpouse
MyParam.Size = 150
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRclaimantChildrenNum"
MyParam.Value = SRclaimantChildrenNum
MyParam.Size = 15
MyParam.Direction = adParamInput
MyParam.Type = adChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRclaimantChildrenAges"
MyParam.Value = SRclaimantChildrenAges
MyParam.Size = 25
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRclaimantRace"
MyParam.Value = SRclaimantRace
MyParam.Size = 75
MyParam.Direction = adParamInput
MyParam.Type = adChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRclaimantHeightLow"
MyParam.Value = SRclaimantHeightLow
MyParam.Size = 75
MyParam.Direction = adParamInput
MyParam.Type = adChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRclaimantHeightHigh"
MyParam.Value = SRclaimantHeightHigh
MyParam.Size = 75
MyParam.Direction = adParamInput
MyParam.Type = adChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRclaimantWeightLow"
MyParam.Value = SRclaimantWeightLow
MyParam.Size = 75
MyParam.Direction = adParamInput
MyParam.Type = adChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRclaimantWeightHigh"
MyParam.Value = SRclaimantWeightHigh
MyParam.Size = 75
MyParam.Direction = adParamInput
MyParam.Type = adChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRclaimantSpecialChar"
MyParam.Value = SRclaimantSpecialChar
MyParam.Size = 500
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRclaimantOccupation"
MyParam.Value = SRclaimantOccupation
MyParam.Size = 50
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRclaimantInjury"
MyParam.Value = SRclaimantInjury
MyParam.Size = 500
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRclaimantRestrictions"
MyParam.Value = SRclaimantRestrictions
MyParam.Size = 500
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRclaimantRepresented"
MyParam.Value = SRclaimantRepresented
MyParam.Size = 75
MyParam.Direction = adParamInput
MyParam.Type = adChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRclaimantVehicle1Make"
MyParam.Value = SRclaimantVehicle1Make
MyParam.Size = 75
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRclaimantVehicle1Model"
MyParam.Value = SRclaimantVehicle1Model
MyParam.Size = 75
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRclaimantVehicle1Tag"
MyParam.Value = SRclaimantVehicle1Tag
MyParam.Size = 75
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRclaimantVehicle2Make"
MyParam.Value = SRclaimantVehicle2Make
MyParam.Size = 75
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRclaimantVehicle2Model"
MyParam.Value = SRclaimantVehicle2Model
MyParam.Size = 75
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRclaimantVehicle2Tag"
MyParam.Value = SRclaimantVehicle2Tag
MyParam.Size = 75
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRsubjectEmployer"
MyParam.Value = SRsubjectEmployer
MyParam.Size = 150
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SREmployerNameFirst"
MyParam.Value = SREmployerNameFirst
MyParam.Size = 150
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SREmployerNameLast"
MyParam.Value = SREmployerNameLast
MyParam.Size = 150
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SREmployerAddress1"
MyParam.Value = SREmployerAddress1
MyParam.Size = 150
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SREmployerAddress2"
MyParam.Value = SREmployerAddress2
MyParam.Size = 150
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SREmployerCity"
MyParam.Value = SREmployerCity
MyParam.Size = 150
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SREmployerState"
MyParam.Value = SREmployerState
MyParam.Size = 10
MyParam.Direction = adParamInput
MyParam.Type = adChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SREmployerZip"
MyParam.Value = SREmployerZip
MyParam.Size = 20
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SREmployerPhone"
MyParam.Value = SREmployerPhone
MyParam.Size = 35
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SREmployerContact"
MyParam.Value = SREmployerContact
MyParam.Size = 3
MyParam.Direction = adParamInput
MyParam.Type = adChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SREmployerInfo"
MyParam.Value = SREmployerInfo
MyParam.Size = 500
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRcollegeNameFirst"
MyParam.Value = SRcollegeNameFirst
MyParam.Size = 150
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRcollegeNameLast"
MyParam.Value = SRcollegeNameLast
MyParam.Size = 150
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRcollegeAddress1"
MyParam.Value = SRcollegeAddress1
MyParam.Size = 150
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRcollegeAddress2"
MyParam.Value = SRcollegeAddress2
MyParam.Size = 150
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRcollegeCity"
MyParam.Value = SRcollegeCity
MyParam.Size = 150
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRcollegeState"
MyParam.Value = SRcollegeState
MyParam.Size = 10
MyParam.Direction = adParamInput
MyParam.Type = adChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRcollegeZip"
MyParam.Value = SRcollegeZip
MyParam.Size = 20
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRcollegePhone"
MyParam.Value = SRcollegePhone
MyParam.Size = 35
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRcollegeCopyOnReports"
MyParam.Value = SRcollegeCopyOnReports
MyParam.Size = 3
MyParam.Direction = adParamInput
MyParam.Type = adChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRcollegeContact"
MyParam.Value = SRcollegeContact
MyParam.Size = 3
MyParam.Direction = adParamInput
MyParam.Type = adChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRcollegeInfo"
MyParam.Value = SRcollegeInfo
MyParam.Size = 500
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRrehabCompany"
MyParam.Value = SRrehabCompany
MyParam.Size = 150
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRrehabNameFirst"
MyParam.Value = SRrehabNameFirst
MyParam.Size = 150
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRrehabNameLast"
MyParam.Value = SRrehabNameLast
MyParam.Size = 150
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRrehabAddress1"
MyParam.Value = SRrehabAddress1
MyParam.Size = 150
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRrehabAddress2"
MyParam.Value = SRrehabAddress2
MyParam.Size = 150
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRrehabCity"
MyParam.Value = SRrehabCity
MyParam.Size = 150
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRrehabState"
MyParam.Value = SRrehabState
MyParam.Size = 10
MyParam.Direction = adParamInput
MyParam.Type = adChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRrehabZip"
MyParam.Value = SRrehabZip
MyParam.Size = 20
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRrehabPhone"
MyParam.Value = SRrehabPhone
MyParam.Size = 35
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRrehabContact"
MyParam.Value = SRrehabContact
MyParam.Size = 3
MyParam.Direction = adParamInput
MyParam.Type = adChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRrehabInfo"
MyParam.Value = SRrehabInfo
MyParam.Size = 500
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRbuildingNameFirst"
MyParam.Value = SRbuildingNameFirst
MyParam.Size = 150
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRbuildingNameLast"
MyParam.Value = SRbuildingNameLast
MyParam.Size = 150
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRbuildingAddress1"
MyParam.Value = SRbuildingAddress1
MyParam.Size = 150
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRbuildingAddress2"
MyParam.Value = SRbuildingAddress2
MyParam.Size = 150
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRbuildingCity"
MyParam.Value = SRbuildingCity
MyParam.Size = 150
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRbuildingState"
MyParam.Value = SRbuildingState
MyParam.Size = 10
MyParam.Direction = adParamInput
MyParam.Type = adChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRbuildingZip"
MyParam.Value = SRbuildingZip
MyParam.Size = 20
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRbuildingPhone"
MyParam.Value = SRbuildingPhone
MyParam.Size = 35
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRbuildingContact"
MyParam.Value = SRbuildingContact
MyParam.Size = 3
MyParam.Direction = adParamInput
MyParam.Type = adChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRbuildingInfo"
MyParam.Value = SRbuildingInfo
MyParam.Size = 500
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRreason"
MyParam.Value = SRreason
MyParam.Size = 500
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRobjective1"
MyParam.Value = SRobjective1
MyParam.Size = 500
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRobjective2"
MyParam.Value = SRobjective2
MyParam.Size = 500
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRobjective3"
MyParam.Value = SRobjective3
MyParam.Size = 500
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRbudgetAmount"
MyParam.Value = SRbudgetAmount
MyParam.Size = 10
MyParam.Direction = adParamInput
MyParam.Type = adChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRsingleDaySurv"
MyParam.Value = SRsingleDaySurv
MyParam.Size = 10
MyParam.Direction = adParamInput
MyParam.Type = adChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRsceneInvestigation"
MyParam.Value = SRsceneInvestigation
MyParam.Size = 10
MyParam.Direction = adParamInput
MyParam.Type = adChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRactivityCheck"
MyParam.Value = SRactivityCheck
MyParam.Size = 10
MyParam.Direction = adParamInput
MyParam.Type = adChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRresidencyCheck"
MyParam.Value = SRresidencyCheck
MyParam.Size = 10
MyParam.Direction = adParamInput
MyParam.Type = adChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRmultidaySurv"
MyParam.Value = SRmultidaySurv
MyParam.Size = 10
MyParam.Direction = adParamInput
MyParam.Type = adChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRstatement"
MyParam.Value = SRstatement
MyParam.Size = 500
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRbackgroundCheck"
MyParam.Value = SRbackgroundCheck
MyParam.Size = 10
MyParam.Direction = adParamInput
MyParam.Type = adChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRwidowCheck"
MyParam.Value = SRwidowCheck
MyParam.Size = 10
MyParam.Direction = adParamInput
MyParam.Type = adChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRhospitalCanvass"
MyParam.Value = SRhospitalCanvass
MyParam.Size = 10
MyParam.Direction = adParamInput
MyParam.Type = adChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRdueDate"
MyParam.Value = SRdueDate
MyParam.Size = 50
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SRcomments"
MyParam.Value = SRcomments
MyParam.Size = 500
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
MyCn.Open
MyCmd.ActiveConnection = MyCn
MyCmd.Execute exec_sp_biginsert
MyCn.Close
ReturnValue = CInt(MyCmd.Parameters("@Return").Value)
Set MyParam = Nothing
Set MyCmd = Nothing
End Function
-
SET NOCOUNT ON is to tell the server not to send the messages x row(s) affected.
Select @@Error is to save the last error number because it is reset after every operation.
Ex :
Select 1 / 0
if @@ERROR 0 --yes obviously
Print @@ERROR --prints 0 anyways because the if didn't return any errors
MAKE SURE YOU TEST THIS CODE IN DEPTH. You don't want to have to go back in 6 months after an error is found by the users.
-
Holy Cow Remi!
I hope you make at least $250,000/yr!!
But now I have a question...what the heck is all this?? What is MyCmd, MyParam?
What do I need to translate?
I'm totally lost now!
Thanks,
M
-
This is vba code. You need to do small translation to make it work in asp.
If you use the include file I provided, the single modification missing should be this :
Declare MyCmd as ADODB.Command
SET MyCmd = new ADODB.Command
becomes
Declare MyCmd
SET MyCmd = SERVER.CREATEOBJECT ("ADODB.Command")
You'll have to do a search and replace for all the lines where it says set MyParam = new ADODB.Parameter to the create object version.
A command is an object used to send queries on the server, in this case I use is to make a RPC to the server (fasted execution time for a single call).
If you wish you can send the missing balance from 250k to my current salary... which might through the school in the red for quite a few years
.
Viewing 15 posts - 61 through 75 (of 167 total)
You must be logged in to reply to this topic. Login to reply