ASP

101 Forms and 1 Database Script in ASP

In this article, Matt shows us how to use just one database script to handle all the ASP forms on your site.

Author Info:

By: Matt Burnett

Poor Best

Rating:

/ 14

February 23, 2003

TABLE OF CONTENTS:

· 101 Forms and 1 Database Script in ASP
· How To Do It
· The Code
· Conclusion

101 Forms and 1 Database Script in ASP - The Code
(Page 3 of 4 )

<%
'This is an advanced part and when implemented you might need to modify it some.
'We've got to get into the database with the table and select one row.
'We will use this row to move through the fields and get field types.
'The field types are used in construction of the fields and values string satements.
xSqlCommand     = Request.Form("xSqlCommand")
xTable       = Request.Form("xTable")
xPrimaryKeyFieldName = Request.Form("xPrimaryKeyFieldName") 'Used with update and delete
xPrimaryKey     = Request.Form("xPrimaryKey") 'Used with update and delete

Set rs = Conn.execute "SELECT TOP 1 * FROM " & xTable
aryRsFieldTypes = getRsFieldTypes(rs)
'This guy builds name:value pairs for use later.
For each oRequest in Request.Form
  'Notice, this if removes any 'x' guys from our SQL, as only db fields are used.
  ’x - is a little wierd, but putting it in give us a lot of future flexability.
  'non 'x' are used in the sql.
  If (Left(oRequest,1) <> "x") then
    If (Request.Form(oRequest) <> "") Then
    'Customize this, as integers may cause errors, if you want them to be nothing (‘’).
      strNames = strNames & oRequest & ","
      If cBool(getAryValue(oRequest, aryRsFieldTypes)) Then
  strValues = strValues & Request.Form(oRequest) & ","
      Else
  strValues = strValues & "'" & Replace(Request.Form(oRequest),"'","''") & "',"
      End If
    End If
  End If
Next

strNames = Left(strNames,Len(strNames)-1) 'cuts off the extra comma
strValues = Left(strValues,Len(strValues)-1)
'Now, we have two strings of matched name/values
REM Update the database
If xSqlCommand = "INSERT" then
  sql = insSQL(xTable,strNames,strValues)

ElseIf xSqlCommand = "UPDATE" then
  sql = updSQL(xTable,xPrimaryKeyFieldName,xPrimaryKey,strNames,strValues)

ElseIf xSqlCommand = "DELETE" then
  sql = delSql(xTable,xPrimaryKeyFieldName,xPrimaryKey)

End If
Conn.execute sql
'END PROGRAM

Function insSQL(table,fields,values)
  REM Written by M.Burnett
  REM This function requires a comma separated list of fields with matching
  'comma delimited list of values.
  Sql = "INSERT into " & table & " ("& fields &") Values ("& values &")"
  insSQL = Sql
End Function
Function updSQL(table,PrimaryKeyFieldName,ID,fields,values)
  REM Written by M.Burnett
  REM This function requires a comma separated list of fields with matching
  'comma delimited list of values.
  Dim arryFields, arryValues, x, y, LastRow
  arryFields = Split(fields,",")
  arryValues = Split(values,",")
  LastRow = Ubound(arryFields)
  Sql = "UPDATE " & table & " Set "
  For x = 0 To Ubound(arryFields)
    If x = LastRow Then
      Sql = Sql & arryFields(x) & " = " & arryValues(x) & " "
    Else
      Sql = Sql & arryFields(x) & " = " & arryValues(x) & ", "
    End If
  Next
  Sql = Sql & "Where " & PrimaryKeyFieldName & "=" & ID
  updSQL = Sql
End Function
Function delSql(table,PrimaryKeyFieldName,primaryID)
  Sql = "Delete from "& table &" where " & PrimaryKeyFieldName & " = " & primaryID
  delSql = Sql
End Function

Function getRsFieldTypes(rs)
  'Returns array, EVEN index is name, ODD index is value
  'In this case, value is '0' for string, and '1' for integer.
  Dim y, blnFieldtype
  If rs.EOF then exit function
  On error resume next
  For y = 0 to 500
    If (rs.Fields(y).type = "3") Then
      blnFieldtype = 1
    Else
      blnFieldtype = 0
    End If
    getRsFieldTypes = getRsFieldTypes & rs.Fields(y).Name & "," & blnFieldtype & ","
  Next
  getRsFieldTypes = Split(getRsFieldTypes,",")
  on error goto 0
End Function

Function getAryValue(ByVal strItemName, ByVal aryItems)
  'Assumes a single column array where EVEN index is name, ODD index is value
  'We've only to split into string or int.
  'Returns the value.
  Dim q As Integer
  getAryValue = ""
  For q = 0 To UBound(aryItems) Step 2
    If (UCase(aryItems(q)) = UCase(strItemName)) Then
      getAryValue = aryItems(q + 1)
      Exit For
    End If
  Next
End Function
%>

Next: Conclusion >>