Every time in the operation of the database is the most annoying is based on the content of the form submitted to write SQL statements, especially when the field is more cumbersome, easy to write wrong. So I wrote the following function to generate the SQL statement, and I think it's OK to use it.
Program code:
<%
' ======== all of the following functions do not add fault-tolerant mechanism, so we must ensure the correctness of the parameters ========
' ================ parameter Description ================
' This function returns the SQL statement that inserts the data
' Note: Use this function to make the field name in the form consistent with the field name in the database
' and the button cannot be submitted, to be a button, to submit a form through JavaScript's Submit method
' Objform must be Request.Form
' Tbname is the table name for inserting data
' Numstr for fields that do not need to be enclosed in single quotes, the fields are separated by commas (,), no, please replace
'=======================================
Function Insertstr (OBJFORM,TBNAME,NUMSTR)
Dim Tbfield ()
Dim Tbfieldval ()
I=0
For each objitem in Objform
ReDim Preserve Tbfield (i)
Tbfield (i) =objitem
ReDim Preserve Tbfieldval (i)
Numpos=instr (Numstr,objitem)
If Numpos<>0 Then
Tbfieldval (i) =trim (Objform (objitem))
Else
Tbfieldval (i) = "'" &trim (Objform (objitem)) & "'"
End If
I=i+1
Next
insertstr= "INSERT INTO" &tbName& "(" &join (Tbfield, ",") & ") VALUES (" &join (Tbfieldval, ",") & ") "
End Function
' =================== parameter Description =========================
' SQL statement of the updated data returned by this function
' Note: Use this function to make the field name in the form consistent with the field name in the database
' and the button cannot be submitted, to be a button, to submit a form through JavaScript's Submit method
' Objform must be Request.Form
' Tbname is the name of the table that updates the data
' Wherefield for the updated condition field, there are multiple (,) split
' Wherejoin is set to and or or when there are more than one conditional field for the update
' Numstr for fields that do not need to be enclosed in single quotes, the fields are separated by commas (,), no, please replace
'===================================================
Function Updatestr (OBJFORM,TBNAME,WHEREFIELD,WHEREJOIN,NUMSTR)
Dim Setfieldval ()
Dim Wherestrarr ()
I=0
T=0
For each objitem in Objform
Wherepos=instr (Wherefield,objitem)
If Wherepos=0 Then
ReDim Preserve Setfieldval (i)
Numpos=instr (Numstr,objitem)
If Numpos<>0 Then
Setfieldval (i) =objitem& "=" &trim (objform (objitem))
Else
Setfieldval (i) =objitem& "= '" &trim (Objform (objitem)) & ""
End If
I=i+1
Else
ReDim Preserve Wherestrarr (t)
Numpos2=instr (Numstr,objitem)
If Numpos2<>0 Then
Wherestrarr (t) =objitem& "=" &trim (objform (objitem))
Else
Wherestrarr (t) =objitem& "= '" &trim (Objform (objitem)) & "'"
End If
T=t+1
End If
Next
Wherepos2=instr (Wherefield, ",")
If Wherepos2<>0 Then
Wherestr= "where" &join (Wherestrarr, "&whereJoin&")
Else
Wherestrarray=wherestrarr
Wherestr= "where" &wherestrarray (0)
End If
updatestr= "Update" &tbName& "set" &join (Setfieldval, ",") &wherestr
End Function
' ============== parameter Description =================
' This function returns the SQL statement that updates the data
' Only the update condition is not submitted through form, such as session.
' Conditions is the condition of the update
' Numstr for fields that do not need to be enclosed in single quotes, the fields are separated by commas (,), no, please replace
'======================================
Function UPDATESTR2 (OBJFORM,TBNAME,CONDITIONS,NUMSTR)
Dim Setfieldval ()
I=0
For each objitem in Objform
ReDim Preserve Setfieldval (i)
Numpos=instr (Numstr,objitem)
If Numpos<>0 Then
Setfieldval (i) =objitem& "=" &trim (replace (Objform (objitem), "'", "))
Else
Setfieldval (i) =objitem& "= '" &trim (replace (Objform (objitem), "'", ")) &" "
End If
I=i+1
Next
updatestr2= "Update" &tbName& "set" &join (Setfieldval, ",") & "where" &conditions
End Function