INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Data Connectivity

An easy way to submit a LOT of fields from a form to a database. by gbaughma
Posted: 13 Apr 07

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.  winky smile

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.

Back to Microsoft: ASP (Active Server Pages) FAQ Index
Back to Microsoft: ASP (Active Server Pages) Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close