A simpler way to set up a statement
This shows that we need an easy way to create SQL statements based on any form, regardless of how many fields there are in the form, regardless of the field type. To do this, we take advantage of the Request.Form collection built into the ASP. After looping through this set, we can extract the name of each form field and its value together. List B is a simple version of this way of working. When you view the code, be aware that our HTML field name must be the same as the field name in the database table for this technology to take effect.
List B: Use Request.Form to easily build SQL strings.
<%
ISTR = "INSERT INTO UData"
Vstr = "VALUES ("
Nstr = "("
' Looping through a form collection and building up parts of the SQL statement
For each x in Request.Form
' Create a list of field names
Nstr = nstr & x & ","
' Create a list of field values
If UCase (x) = "Age" Then
Vstr = vstr & Request.Form (x) & ","
Else
Vstr = vstr & "'" & Request.Form (x) & "',"
End If
Next
"The end of the", "removed from the string we established
Vstr = Left (Vstr, Len (VSTR)-2) & ")"
Nstr = Left (Nstr, Len (NSTR)-2) & ")"
' Assemble the SQL statements.
ISTR = ISTR & Nstr & Vstr
If Trim (Request ("FName")) >> "Then
Response.Write (ISTR & ">BR>")
Else
%>
<body>
<form name=f method=post action= "List 2.asp" >
Gimme your:<br>
Name: <input type=text name= "FName" ><br>
Last Name: <input type=text name= "LName" ><br>
Age: <input type=text name= ' age ' ><br>
<input type= "Submit" value= "Submit" >
</form>
</body>
<%
End If
%>
The first thing you might notice is that this code is a little longer than the code in the first paragraph. However, keep in mind that this code can handle any number of fields. Therefore, it is very effective to use this technique for large forms.
The second thing you should be aware of is that we still have to know the name and type of a field-age field. Therefore, we need a way to tell the field type to the ASP code in order to create the appropriate SQL statements.
The solution we use here is to embed the data type into each HTML field name. For example, the name of a numeric field starts with a string num_. In this way, change the age field name to Num_age. (Character and text fields do not need to be renamed, so they are placed in parentheses by default.) List C shows the actual application of the solution.
List C: Embed the field type in the HTML field name.
<%function Buildsqlinsert (targettable)
ISTR = "INSERT INTO" & Targettable & ""
Vstr = "Values" ("NSTR =" ("
' Looping through a form collection and building up parts of the SQL statement
For each x in Request.Form
FieldName = X
Fielddata = replace (Request.Form (fieldName), "'", "" ")
Typedelimpos = InStr (FieldName, "_")
If Typedelimpos = 0 Then
' Its a text field
' Create a list of field names
Nstr = nstr & FieldName & ","
Vstr = vstr & "'" & Fielddata & "',"
Else
' is another kind of data type
FieldType = Left (FieldName, typeDelimPos-1)
FieldName = Mid (FieldName, Typedelimpos + 1)
' Add the field name to the list of field names
Nstr = nstr & FieldName & ","
' Turn the field type to uppercase to ensure that the match
Select Case UCase (FieldType)
Case "NUM"
Vstr = vstr & Fielddata & ","
' Treat the unknown type as text
Case Else
Vstr = vstr & "'" & Fielddata & "',"
End Select
End If
Next
"The end of the", "removed from the string we established
Vstr = Left (Vstr, Len (VSTR)-2) & ")"
Nstr = Left (Nstr, Len (NSTR)-2) & ")"
' Assemble the SQL statements.
Buildsqlinsert = ISTR & Nstr & Vstr
End Function
If Trim (Request ("FName")) >< "Then
Response.Write (Buildsqlinsert & ">BR<")
Else
%>
<body>
<form name=f method=post action= "listing3.asp" >
Gimme your:<br>
Name: <input type=text name= "FName" ><br>
Last Name: <input type=text name= "LName" ><br>
Age: <input type=text name= "Num_age" ><br>
<input type= "Submit" value= "Submit" >
</form>
</body>
<%
End If
%>
Here, we use part of the code from list B and place them in a function called Buildsqlinsert. After setting the temporary variable, we go into the loop and handle as much as we did before. The main difference is that each time we go into the loop, we look for the underscore (_) in the field name. If you find the next row
