Collection | Statements other considerations
Close to the top of the function, you'll notice that we used the Replace function to replace each single quote in the data with two single quotes. This is because the SQL translator uses single quotes as the delimiter for the string, and if there are single quotes in the data, it can cause a SQL interpretation error. Converting a single quote to two single quotes ensures that the SQL translator correctly adds single quotes to the text.
By now, there are only two questions left to resolve: null value nulls and omit fields from the final SQL statement. The code for these two issues is included in list D.
List D:buildsqlinsert The final version of the function.
<%
function Buildsqlinsert (targettable, Omitfields)
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 = uCase (x)
' Judge if the field is omitted?
If InStr (UCase (Omitfields), x) = 0 Then
Fielddata = replace (Request.Form (fieldName), _
"'", "''")
' If there is no data, insert NULL
If trim (fielddata) = "" Then
Fielddata = "NULL"
Vstr = vstr & Fielddata & ","
Nstr = nstr & FieldName & ","
Else
Typedelimpos = InStr (FieldName, "_")
If Typedelimpos = 0 Then
' is a text field
' Create a list of field names
Nstr = nstr & FieldName & ","
Vstr = vstr & "'" & Fielddata & "',"
Else
' field is another type
FieldType = Left (FieldName, typeDelimPos-1)
FieldName = Mid (FieldName, Typedelimpos + 1)
' Add the field name to the name list
Nstr = nstr & FieldName & ","
' Make the field type uppercase to ensure matching
Select Case UCase (FieldType)
Case "NUM"
Vstr = vstr & Fielddata & ","
' Treat the unknown type as text
Case Else
Vstr = vstr & "'" & Fielddata & "',"
End Select
End If
End If
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) & ")"
' Integrate the SQL statements
Buildsqlinsert = ISTR & Nstr & Vstr
End Function
If Trim (Request ("FName") &request ("lname") &request ("Age") <> "" Then
Response.Write (Buildsqlinsert ("") & "<BR<")
Response.Write (Buildsqlinsert ("Num_age") & "<BR>")
Response.Write (Buildsqlinsert ("Lname,fname") & "<BR>")
Response.Write (Buildsqlinsert ("Mycheckbox,fname") &
=< "<BR<")
Else
%>
<body>
<form name=f method=post action= "List 4.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= "checkbox" value= "Y" name= "MyCheckBox" <do you want this checked?<br>
<input type= "Submit" value= "Submit" >
</form>
</body>
<%
End If
%>
Null processing of NULL values is fairly straightforward. If the field we receive is empty, we will vent the value in the final SQL field. This is simple, and it is important to note that the use of this method presupposes that the form data has been validated before the operation is done. For example, if your database does not allow fname to use null values, you must make sure that the user fills in the field before submitting the data to the ASP code. (client JavaScript does this in a very good case.) )
Another problem is that sometimes you might not want to include a field in an SQL statement. List D solves this problem by adding omitfields arguments (omitting fields) to the function. Any field names passed in this parameter will be excluded from the final SQL statement. If you want to omit more than one field, just separate it with a comma. If you do not want to omit any fields, simply pass an empty string ("") to the function. Note that you want to pass the field name by the name of the field in the HTML file, rather than by the name of the field in the database (for example, passing num_age instead of age.) )
