Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

An easy way to submit a LOT of fields from a form to a database.

Data Connectivity

An easy way to submit a LOT of fields from a form to a database.

by  gbaughma  Posted    (Edited  )
OK... a couple of things first....

1) In your database, the FIELD NAMES must match the names of the form elements.
2) This routine will handle text boxes, text areas, checkboxes, radio buttons, dates, and numbers.

The routine will "loop through" the database, and figure out what type of field it's looking for... so you don't have to worry about converting, say, a formatted date text input box to include the #'s surrounding a date when it's inserted into the database.

Disclaimer: I use this code regularly, for forms containing hundreds of elements, but of course, I don't guarantee that it will work for your application. But it should. ;-)

Code: 
Dim objCommand
Dim objRS
Const adCmdText = &H0001


Set objCommand = Server.CreateObject("ADODB.Command")
Set objRS = Server.CreateObject("ADODB.RecordSet")

With objCommand
	.ActiveConnection = "TableName"
	.CommandType = adCmdText
End With

objCommand.CommandText = "DatabaseName"
objCommand.CommandType = 2

objRS.Open objCommand, , 2, 2

objRS.AddNew ' Add a new record

Response.Write "Got form... Processing...<br>"	

For Each Field In Request.Form

Select Case objRS.Fields(Field).type

Case 200,201,202,203
	' String
	If Request.Form(Field) <> "" Then
	strField = Request.Form(Field)
        (Request.Form(Field),"'","''")
	strField = Replace(Request.Form(Field),chr(34),"&quot;")
        If strField <> "" Then
		objRS.Fields(Field).value = strField
		End If
	End If ' Skip the blank ones
Case 11
	' True or False
	If Request.Form(Field) = "on" Then
		objRS.Fields(Field).value = -1
	Else
		objRS.FIelds(Field).value = 0
	End If
Case 3,2,5
	' integer and number
	' Response.Write "Integer/Number<br>"
	If Request.Form(Field) <> "" Then
		objRS.Fields(Field).value = Request.Form(Field)
	End If ' Skip any blank ones
Case 135,133,134
	' Date/Time Stamp
	strDate = Request.Form(Field)
	If strDate<>"" Then
	objRS.Fields(Field).value = strDate
	End If
Case else
	Response.Write "Field type " & objRS.Fields(Field).type & "<br>"
	Response.Write "Processing stopped.... unknown field type!"
	Response.End
End Select

Next

objRS.Update()

Set objRS = Nothing
set objCommand = Nothing

Response.Write "Submitted!"

If you find any corrections or enhancements to this code, please let me know.
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top