Hello Lister,



I am working on a form, which has text boxes, around 50 check boxes and 

number of dates, which are optional. Before I would handle such form in a 

very laborious code, which is shown here. I am looking for ideas or some 

written functions for avoiding error in executing sql insert statement 

when a form is submitted to an asp script for inserting data into the 

database with optional date and checkboxes fields. 





<%

	Property = Trim(Request.Form("Property"))

	First_name = Trim(Request.Form("First_Name"))

	Last_name = Trim(Request.Form("Last_name"))

	Email = Trim(Request.Form("Email"))

	Address = Trim(Request.Form("Address"))

	City = Trim(Request.Form("City"))

	State = Trim(Request.Form("State"))

	Zip = Trim(Request.Form("Zip"))

	Ssr = Request.Form("Ssr")

	Tci = Request.Form("Tci")

	Rbi = Request.Form("Rbi")

	Si = Request.Form("Si")

	Dinners = Request.Form("Dinners")

	Golf = Request.Form("Golf")

	Spa = Request.Form("Spa")

	Tennis = Request.Form("Tennis")

	Gift_cert = Request.Form("Gift_cert")

	Special_occasion = Request.Form("Special_occasion")

	Birthday = Request.Form("Birthday")

	Spouse_birthday = Request.Form("Spouse_birthday")

	Ip_address = Request.ServerVariables("REMOTE_HOST")

		

' Boolean Fields in the database can accept 0 and -1 for No and Yes

	

	If Ssr = "" Then		Ssr	 = 0 End If 

	If Tci = "" Then		Tci = 0 End If 

	If Rbi = "" Then		Rbi = 0 End If 

	If Si = "" Then			Si = 0 End If

	If Dinners = "" Then		Dinners = 0 End If 

	If Golf = "" Then		Golf = 0 End If

	If Spa = "" Then		Spa = 0 End If 

	If Tennis = "" Then		Tennis = 0 End If

	If Gift_cert = "" Then		Gift_cert = 0 End If



' for avoiding error due to '(quotes) in the text

Function SQLQuote(var)

	If InStr(var, "'") <> 0 Then

		var = Replace(var, "'", "''")

	End If

	SQLQuote = var

End Function



sDBName = "Provider=Microsoft.Jet.OLEDB.4.0; Data 

Source=D:\database\jcr.mdb"

Set objDB = Server.CreateObject("ADODB.Connection")

objDB.Open sDBName



'Code to add a new record...

		sql = "Insert Into elist ("

		sql = sql & "Property,"

		sql = sql & "First_name,"

		sql = sql & "Last_name,"

		sql = sql & "Email,"

		sql = sql & "Address,"

		sql = sql & "City,"

		sql = sql & "State,"

		sql = sql & "Zip,"

		sql = sql & "Ssr,"

		sql = sql & "Tci,"

		sql = sql & "Rbi,"

		sql = sql & "Si,"

		sql = sql & "Dinners,"

		sql = sql & "Golf,"

		sql = sql & "Spa,"

		sql = sql & "Tennis,"

		sql = sql & "Gift_cert,"

	If Special_occasion <> "" Then	

		sql = sql & "Special_occasion,"

	End if

	If Birthday <> "" Then

		sql = sql & "Birthday,"

	End if

	If Spouse_birthday <> "" Then

		sql = sql & "Spouse_birthday,"

	End if

		sql = sql & "Ip_address"

		sql = sql & ") "

		sql = sql & "Values ("

		sql = sql & "'" & Property & "',"

		sql = sql & "'" & SqlQuote(First_name) & "',"

		sql = sql & "'" & SqlQuote(Last_name) & "',"

		sql = sql & "'" & SqlQuote(Email) & "',"

		sql = sql & "'" & SqlQuote(Address) & "',"

		sql = sql & "'" & SqlQuote(City) & "',"

		sql = sql & "'" & SqlQuote(State) & "',"

		sql = sql & "'" & SqlQuote(Zip) & "',"

		sql = sql & "" & Ssr & ","

		sql = sql & "" & Tci & ","

		sql = sql & "" & Rbi & ","

		sql = sql & "" & Si & ","

		sql = sql & "" & Dinners & ","

		sql = sql & "" & Golf & ","

		sql = sql & "" & Spa & ","

		sql = sql & "" & Tennis & ","

		sql = sql & "" & Gift_cert & ","

	If Special_occasion <> "" Then	

		sql = sql & "#" & Special_occasion & "#,"

	End If

	If Birthday <> "" Then

		sql = sql & "#" & Birthday & "#,"

	End If

	If Spouse_birthday <> "" Then

		sql = sql & "#" & Spouse_birthday & "#,"

	End if

		sql = sql & "'" & Ip_address & "'"

		sql = sql & ");"

		

' Response.Write sql

ObjDB.Execute(sql)



objDB.Close

Set objDB = Nothing



%>





I am looking for ideas/code of how to write functions similar as SQLQuote 

above, which would handle Null values in Date and Boolean field. I am 

tired of writing If..then..End if statements. There has to be some other 

efficient way of handling this.



Any idea to make the above code efficient is appreciated.



Thanks in advance for your help and ideas.



NP

It's easy to write your own functions

You might want to write a very generic function to test for all data types,

but suppose you wanted one just for your boolean values:



Function fncTestForBoolean( _

    ByVal blnToTest _

    )



    ' Accepts a variable to test

    ' Returns 0 if the variable is not boolean

    ' Returns the boolean value otherwise



    If Len(blnToTest & "") = 0 then



        fncTestForBoolean = 0



    Else



        If blnToTest <> 0 and blnToTest <> 1 then



            fncTestForBoolean = 0



        Else



            fncTestForBoolean = blnToTest



        End if



    End if



End Function ' fncTestForBoolean



HTh



Cheers

Ken





----- Original Message -----

From: "Nagendra Pandey" <npandey@h...>

To: "ASP Databases" <asp_databases@p...>

Sent: Tuesday, February 27, 2001 3:33 AM

Subject: [asp_databases] Insert error due to optional date and checkbox

field in a form?





> Hello Lister,

>

> I am working on a form, which has text boxes, around 50 check boxes and

> number of dates, which are optional. Before I would handle such form in a

> very laborious code, which is shown here. I am looking for ideas or some

> written functions for avoiding error in executing sql insert statement

> when a form is submitted to an asp script for inserting data into the

> database with optional date and checkboxes fields.



<snipped>



> I am looking for ideas/code of how to write functions similar as SQLQuote

> above, which would handle Null values in Date and Boolean field. I am

> tired of writing If..then..End if statements. There has to be some other

> efficient way of handling this.

>

> Any idea to make the above code efficient is appreciated.

>

> Thanks in advance for your help and ideas.

>

> NP

Thanks Ken for giving me idea of how to write functions.



Based on your idea I wrote a functions for date data types which is a 

follow:





Function fncTestForDate(ByVal dtmToTest)

	If Len(dtmToTest & "") = 0 then

		fncTestForDate = NULL

	Else

		If Not IsDate(dtmToTest) Then

		 Response.Write dtmToTest & " is not a valid date."

		 Response.End

		Else		

		 fncTestForDate = dtmToTest

		End If

	End if

End Function





My question how to do you handle NULL values in SQL insert statement 

(database:Access 2000) for numeric and date data types.



Regards

NP

Why not build the SQLString on the fly, then you can only add the bits 

that you need to to it and ignore the rest.

Hi Greg,



Thanks for your response.



How do you build the SQLString on the fly. My form is big with many 

optional fields. Can you post any sample code. I am new to this. 



Thanks again



NP



> Why not build the SQLString on the fly, then you can only add the bits 

> that you need to to it and ignore the rest.