Home arrow ASP arrow Page 3 - 101 Forms and 1 Database Script in ASP
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
Rating: 4 stars4 stars4 stars4 stars4 stars / 26
February 23, 2003
TABLE OF CONTENTS:
  1. · 101 Forms and 1 Database Script in ASP
  2. · How To Do It
  3. · The Code
  4. · Conclusion

print this article
SEARCH DEVARTICLES

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
%>

blog comments powered by Disqus
ASP ARTICLES

- Central Scoreboard with Flash and ASP
- Calorie Counter Using WAP and ASP
- Creating PGP-Encrypted E-Mails Using ASP
- Be My Guest in ASP
- Session Replacement in ASP
- Securing ASP Data Access Credentials Using t...
- The Not So Ordinary Address Book
- Adding and Displaying Data Easily via ASP an...
- Sending Email From a Form in ASP
- Adding Member Services in ASP
- Removing Unconfirmed Members
- Trapping HTTP 500.100 - Internal Server Error
- So Many Rows, So Little Time! - Case Study
- XDO: An XML Engine Class for Classic ASP
- Credit Card Fraud Prevention Using ASP and C...

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials