Discuss Function to build queries in the Code Bank forum on ASP Free. Function to build queries Code Bank forum containing sample code and scripts to help solve common problems. Take a look and use the code to assist in your projects.
Posts: 108
Time spent in forums: 1 Day 9 h 3 m 30 sec
Reputation Power: 8
Function to build queries
I wrote this function to build insert and update queries based on form submissions, and thought it might be useful to others. I've gotten some very solid help on this board, and thought I could give back a little!
So here is the function:
Code:
Function BuildSQL(sqlType, tblName, uID)
If lcase(sqlType) = "insert" Then
sql = "INSERT INTO " & tblName & "("
For Each Field in Request.Form
If lcase(Field) <> "submit" and lcase(Field) <> "action" and lcase(Field) <> uID Then
sql = sql & Field & ","
End If
Next
sql = left(sql,(len(sql)-1)) & ") VALUES ("
For Each Field in Request.Form
If lcase(Field) <> "submit" and lcase(Field) <> "action" and lcase(Field) <> uID Then
If IsNumeric(Request.Form(Field)) Then
sql = sql & Sanitize(Request.Form(Field)) & ","
ElseIf Len(Request.Form(Field)) = 0 Then
sql = sql & "NULL,"
Else
sql = sql & "'" & Sanitize(Request.Form(Field)) & "',"
End If
End If
Next
sql = left(sql,(len(sql)-1)) & ")"
End If
If lcase(sqlType) = "update" Then
sql = "UPDATE " & tblName & " SET "
For Each Field in Request.Form
If lcase(Field) <> "submit" and lcase(Field) <> "action" and Field <> uID Then
If IsNumeric(Request.Form(Field)) Then
sql = sql & Field & "=" & Sanitize(Request.Form(Field)) & ", "
ElseIf Len(Request.Form(Field)) = 0 Then
sql = sql & Field & "= NULL, "
Else
sql = sql & Field & "='" & Sanitize(Request.Form(Field)) & "', "
End If
End If
Next
sql = left(sql,(len(sql)-2))
sql = sql & " WHERE " & uID & " = " & Sanitize(Request.Form(uID))
End If
BuildSQL = sql
End Function
It also uses this function, which sanitizes user input:
Code:
Function Sanitize(input)
Dim regEx
Set regEx = New RegExp
regEx.Pattern = "[^0-9a-zA-Z ()/]"
regEx.IgnoreCase = True
regEx.Global = True
str = regEx.Replace(input, "")
Sanitize = str
End Function
Then you could have your update page, i.e. page-update.asp:
PHP Code:
If auth() Then
If Request.Form("action") = "add" Then
sqlAT = BuildSQL("insert","AuditTypes","aTypeID")
End If
If Request.Form("action") = "edit" Then
sqlAT = BuildSQL("update","AuditTypes","aTypeID")
End If
response.write sqlAT
'strConn.Execute(sqlAT)
'Response.Redirect("atypes.asp")
Else
response.redirect "/igs/error.asp?message=2"
End If
ASP Free Forums Sponsor:
January 23rd, 2007, 03:59 PM
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
