A function that ASP automatically generates SQL statements based on the form

Source: Internet
Author: User
Tags table name trim

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.