Inserting data - Works but....

The code below is something I am working on. The data is requested directly into variables - data1-69 etc...

The problem is if the field in the form contains no data, at the moment I am using loads of little If statements to check if it is empty and if this is true then insert the Words FALSE into the variable, and the insert the data to db but infact what I wanna do is if the field is blank then do not insert it..... but with 69 odd fields how is this acheiveable???

(I am guessing I am just overlooking this!!!)

<%
data1 = Request.Form("FirstName")
If data1 = "" Then
data1 = "FALSE"
else
end if
data2 = Request.Form("LastNAme")
If data2 = "" Then
data2 = "FALSE"
else
end if
data3 = Request.Form("EmailAddress")
If data3 = "" Then
data3 = "FALSE"
else
end if
data4 = Request.Form("Homephone")
If data4 = "" Then
data4 = "FALSE"
else
end if
data5 = Request.Form("Workphone")
If data5 = "" Then
data5 = "FALSE"
else
end if
data6 = Request.Form("Mobilenumber")
If data6 = "" Then
data6 = "FALSE"
else
end if
data7 = Request.Form("SalaryMIN")
If data7 = "" Then
data7 = "FALSE"
else
end if
data8 = Request.Form("AGE")
If data8 = "" Then
data8 = "FALSE"
else
end if
data9 = Request.Form("Sex")
If data9 = "1" Then
data9 = "FEMALE"
elseif data9 = "2" Then
data9 = "MALE"
else
end if
data10 = Request.Form("Address1")
data11 = Request.Form("Address2")
If data11 = "" Then
data11 = "FALSE"
else
end if
data12 = Request.Form("Town")
If data12 = "" Then
data12 = "FALSE"
else
end if
data13 = Request.Form("County")
If data13 = "" Then
data13 = "FALSE"
else
end if
data14 = Request.Form("Postcode")
If data14 = "" Then
data14 = "FALSE"
else
end if
data15 = Request.Form("TempWork")
If data15 = "" Then
data15 = "FALSE"
else
end if
data16 = Request.Form("PermWork")
If data16 = "" Then
data16 = "FALSE"
else
end if
data17 = Request.Form("FullTime22")
If data17 = "" Then
data17 = "FALSE"
else
end if
data18 = Request.Form("PartTime22")
If data18 = "" Then
data18 = "FALSE"
else
end if
data19 = Request.Form("NoticePeriod")
data20 = Request.Form("Thame")
If data20 = "" Then
data20 = "FALSE"
else
end if
data21 = Request.Form("Haddenham")
If data21 = "" Then
data21 = "FALSE"
else
end if
data22 = Request.Form("Oxford")
If data22 = "" Then
data22 = "FALSE"
else
end if
data23 = Request.Form("Aylesbury")
If data23 = "" Then
data23 = "FALSE"
else
end if
data24 = Request.Form("Chinnor")
If data24 = "" Then
data24 = "FALSE"
else
end if
data25 = Request.Form("PrincesRis")
If data25 = "" Then
data25 = "FALSE"
else
end if
data26 = Request.Form("LongCrendon")
If data26 = "" Then
data26 = "FALSE"
else
end if
data27 = Request.Form("HighWycombe")
If data27 = "" Then
data27 = "FALSE"
else
end if
data28 = Request.Form("OwnTransport")
If data28 = "" Then
data28 = "FALSE"
else
end if
data29 = Request.Form("ContactPreference")
data30 = Request.Form("AvailNights")
If data30 = "" Then
data30 = "FALSE"
else
end if
data31 = Request.Form("secretary")
If data31 = "" Then
data31 = "FALSE"
else
end if
data32 = Request.Form("OfficeManager")
If data32 = "" Then
data32 = "FALSE"
else
end if
data33 = Request.Form("Administration")
If data33 = "" Then
data33 = "FALSE"
else
end if
data34 = Request.Form("Receptionist")
If data34 = "" Then
data34 = "FALSE"
else
end if
data35 = Request.Form("CustomerServices")
If data35 = "" Then
data35 = "FALSE"
else
end if
data36 = Request.Form("SupervisoryExperience")
If data36 = "" Then
data36 = "FALSE"
else
end if
data37 = Request.Form("Marketing")
If data37 = "" Then
data37 = "FALSE"
else
end if
data38 = Request.Form("Clerical")
If data38 = "" Then
data38 = "FALSE"
else
end if
data39 = Request.Form("OfficeJunior")
If data39 = "" Then
data39 = "FALSE"
else
end if
data40 = Request.Form("DataEntry")
If data40 = "" Then
data40 = "FALSE"
else
end if
data41 = Request.Form("TeleSales")
If data41 = "" Then
data41 = "FALSE"
else
end if
data42 = Request.Form("StockControl")
If data42 = "" Then
data42 = "FALSE"
else
end if
data43 = Request.Form("HRTraining")
If data43 = "" Then
data43 = "FALSE"
else
end if
data44 = Request.Form("FieldSales")
If data44 = "" Then
data44 = "FALSE"
else
end if
data45 = Request.Form("Warehouse")
If data45 = "" Then
data45 = "FALSE"
else
end if
data46 = Request.Form("driving")
If data46 = "" Then
data46 = "FALSE"
else
end if
data47 = Request.Form("ForkliftLicence")
If data47 = "" Then
data47 = "FALSE"
else
end if
data48 = Request.Form("Catering")
If data48 = "" Then
data48 = "FALSE"
else
end if
data49 = Request.Form("Packing")
If data49 = "" Then
data49 = "FALSE"
else
end if
data50 = Request.Form("Accounts")
If data50 = "" Then
data50 = "FALSE"
else
end if
data51 = Request.Form("PurchLedger")
If data51 = "" Then
data51 = "FALSE"
else
end if
data52 = Request.Form("SalesLedger")
If data52 = "" Then
data52 = "FALSE"
else
end if
data53 = Request.Form("TrialBalance")
If data53 = "" Then
data53 = "FALSE"
else
end if
data54 = Request.Form("PAYE")
If data54 = "" Then
data54 = "FALSE"
else
end if
data55 = Request.Form("CreditControl")
If data55 = "" Then
data55 = "FALSE"
else
end if
data56 = Request.Form("IT")
If data56 = "" Then
data56 = "FALSE"
else
end if
data57 = Request.Form("Purchasing")
If data57 = "" Then
data57 = "FALSE"
else
end if
data58 = Request.Form("Engineering")
If data58 = "" Then
data58 = "FALSE"
else
end if
data59 = Request.Form("CADDraftsman")
If data59 = "" Then
data59 = "FALSE"
else
end if
data60 = Request.Form("WebDesigner")
If data60 = "" Then
data60 = "FALSE"
else
end if
data61 = Request.Form("FieldServiceEngineer")
If data61 = "" Then
data61 = "FALSE"
else
end if
data62 = Request.Form("DesignMAC")
If data62 = "" Then
data62 = "FALSE"
else
end if
data63 = Request.Form("Word")
If data63 = "" Then
data63 = "FALSE"
else
end if
data64 = Request.Form("Excel")
If data64 = "" Then
data64 = "FALSE"
else
end if
data65 = Request.Form("LotusSuite")
If data65 = "" Then
data65 = "FALSE"
else
end if
data66 = Request.Form("Powerpoint")
If data66 = "" Then
data66 = "FALSE"
else
end if
data67 = Request.Form("SAGE")
If data67 = "" Then
data67 = "FALSE"
else
end if
data68 = Request.Form("InternetEmail")
If data68 = "" Then
data68 = "FALSE"
else
end if

' capture comments from the page before - else =""
data69 = Request.Form("comments")
If data69 = "" Then
data69 = "FALSE"
else
end if

' capture the users PC details - Don't know why you need these but oh well (as your stats catch them all anyway!
'Remote_computer_name = Request.ServerVariables("REMOTE_HOST")
'User_name = Request.ServerVariables("REMOTE_USER")
'Browser_type = Request.ServerVariables("HTTP_USER_AGENT")

' Add the jobRef from Querystring
txtJobRef = Request.QueryString("JobRef")
optDataProtection = Request.Form("optDataProtection")
Register = "YES"



Set Con = Server.CreateObject("ADODB.Connection")
conString = "DRIVER={Microsoft Access Driver (*.mdb)};" & _
"DBQ=" & server.mappath("candidate_reg.mdb") & ";"
Con.Open = conString

sqlString = "INSERT INTO Results (FirstName,LastName,HomePhone,WorkPhone,MobileNumb er,EmailAddress,SalaryMin, AGE, Location, TempWork, PermWork, NoticePeriod, Thame, Chinnor, Haddenham, PrincesRis, Aylesbury, Oxford, HighWycombe, LongCrendon, OwnTransport, ContactPreference, AvailNights, Address1, Address2, Address3, Address4, Postcode, Secretary, Administration, Receptionist, CustomerServices, SupervisoryExperience, Marketing, Clerical, OfficeJunior, DataEntry, TeleSales, StockControl, HRTraining, FieldSales, Warehouse, driving, ForkliftLicence, Catering, Packing, IT, Purchasing, Engineering, CADDraftsman, WebDesigner, FieldServiceEngineer, DesignMAC, Accounts, PurchLedger, SalesLedger, TrialBalance, PAYE, CreditControl, Word, Excel, LotusSuite, Powerpoint, SAGE, InternetEmail, Sex, FullTime, PartTime, OfficeManager, comments, optDataProtection, txtjobref, register) " &_
"VALUES ('" & data1 & "','" & data2 & "','" & data4 & "','" & data5 & "','" & data6 & "','" & data3 & "','" & data7 & "','" & data8 & "','" & data12 & "','" & data15 & "','" & data16 & "','" & data19 & "','" & data20 & "','" & data24 & "','" & data21 & "','" & data25 & "','" & data23 & "','" & data22 & "','" & data27 & "','" & data26 & "','" & data28 & "','" & data29 & "','" & data30 & "','" & data10 & "','" & data11 & "','" & data12 & "','" & data13 & "','" & data14 & "','" & data31 & "','" & data33 & "','" & data34 & "','" & data35 & "','" & data36 & "','" & data37 & "','" & data38 & "','" & data39 & "','" & data40 & "','" & data41 & "','" & data42 & "','" & data43 & "','" & data44 & "','" & data45 & "','" & data46 & "','" & data47 & "','" & data48 & "','" & data49 & "','" & data56 & "','" & data57 & "','" & data58 & "','" & data59 & "','" & data60 & "','" & data61 & "','" & data62 & "','" & data50 & "','" & data51 & "','" & data52 & "','" & data53 & "','" & data54 & "','" & data55 & "','" & data63 & "','" & data64 & "','" & data65 & "','" & data66 & "','" & data67 & "','" & data68 & "','" & data9 & "','" & data17 & "','" & data18 & "','" & data32 & "','" & data69 & "','" & optDataProtection & "','" & txtJobRef & "','" & Register & "')"
Con.Execute sqlString

Response.Redirect "default.asp?PageID=1"

%>

Thanks for any help!

Allan

If your field name correspondens with your formelement name, you can just for each loop through the request.form and create the sql:

with the test If Not i = "cmdSubmit" and Len(Request.Form(i)) > 0 you do two things:
1. you ommit the submit button in the sql quey. Note: if you have more fields that need to be ommitted, you can add them here in the test
2. you filter out the fields which are left empty and do not add them to the sql statement.

After the loop you need to remove the last commas and quotes from the sql statement, and then you are good to go. I encourage you to first do a response.write sqlString before you do a database insert to see whether everything came out right, or that you need to tweak the program a little bit.

Not tested, i hope this will work

thanks rdoekes! Never thought of using a loop!

With a bit of modiifcation it works perfectly now!