<-noob = help with form data

 
' primary code taken from http://www.asp101.com
 
'Create an ADO connection to a database
<%@ LANGUAGE="VBSCRIPT" %>
<% 
Dim DB_CONNECTIONSTRING
DB_CONNECTIONSTRING = "driver={MySQL};server=_______;uid=_______;pwd=____  ___;database=_______;option=NUM"
Set adoDataConn = Server.CreateObject("ADODB.Connection") 
%>
'load the includes file for the conn's used
<!-- #include file="adovbs.inc" -->
<%
Dim I			' Standard looping var
Dim strSQL	 ' String variable for building our query
Dim iRecordAdded ' Id of added record
 
'Step 1:
Dim objRecordset
Set objRecordset = Server.CreateObject("ADODB.Recordset")
 
'Step 2:
strSQL = "SELECT * FROM constitutions WHERE 0=1;"
objRecordset.Open strSQL, DB_CONNECTIONSTRING, adOpenKeyset, adLockPessimistic, adCmdText 
'objRecordset.Source		 = "constitution"
'objRecordset.ActiveConnection = DB_CONNECTIONSTRING
'objRecordset.CursorType	 = adOpenKeyset
'objRecordset.LockType		 = adLockPessimistic
'objRecordset.Open
 
'Step 3: 
' To add a new record to the current recordset we naturally call the AddNew Method.
objRecordset.AddNew
 
'Step 4:
' String / Text Data Type
objRecordset.Fields("Name") = CStr(UserName(WeekDay(Date())))
' Integer Data Type
objRecordset.Fields("integer_field") = CInt(Day(Now()))
' Date / Time Data Type
objRecordset.Fields("date_time_field") = Now()
 
'Step 5: update the table
objRecordset.Update
'Show the user something:
' Get the DB assigned ID of the record we just added.
iRecordAdded = objRecordset.Fields("id").Value
 
' Tell people which record we added.
Response.Write "<p>Record id " & iRecordAdded & " added!</p>" & vbCrLf
'Step 6:
' Finally we close the recordset and release the memory used by the
' object variable by setting it to Nothing (a VBScript keyword)
objRecordset.Close
Set objRecordset = Nothing
%>

 
<%
' Include the VBScript ADO constants file
%>
<!-- #include file="adovbs.inc" -->
<%
' *** Begin DB Setup ***
Dim strConnString
' Sample access OLEDB CONN String.
'strConnString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
' Server.MapPath("db_scratch.mdb") & ";"
 
strConnString = "driver={MySQL ODBC 3.51 Driver};server=_______;uid=______;pwd=_____;databa  se=_______"
Set adoDataConn = Server.CreateObject("ADODB.Connection") 
adoDataConn.Open "DSN=______"
' *** End DB Setup *** 
Dim cnnFormToDB	   ' CONN object
Dim strSQL			' String in which to build our SQL command
Dim lngRecsAffected   ' # of records affected... just informational
' Vars for the fields read in from the form.  All fields are read
' in as strings so I need to covert them to the appropriate data
' types to be sure they're appropriate for the DB fields.  These
' variables give me some working space to do this easily.
Dim name	  ' Submitter Name
Dim group   ' Student Group
Dim email  ' Submittor Email
Dim status ' Constitution Status
Dim date_time ' Submission Date & Time
Dim strErrorMsg	   ' Holds error message if we catch any problems.

' See if we have any info to process.
' If we don't (ie. the first time through) we just show
' the form.  If we do we proceed with the insert.
If Request.Form("action") <> "Save Form Data" Then
 ' Show the form
 %>
 <form action="<%= Request.ServerVariables("SCRIPT_NAME") %>" method="post">
 <input type="hidden" name="action" value="Save Form Data" />
 <table border="0">
 <tr>
  <td align="right"><strong>Name:</strong></td>
  <td align="left"><input type="text" name="name" maxlength="10" /></td>
 </tr>
 <tr>
  <td align="right"><strong>Group Name:</strong></td>
  <td align="left"><input type="text" name="group" /></td>
 </tr>
 <tr>
  <td align="right"><strong>Email:</strong></td>
  <td align="left"><input type="text" name="email" /></td>
 </tr>
 <tr>
  <td>&nbsp;</td>
  <td>
   <input type="reset" value="Clear" />
   <input type="submit" value="Save" />
  </td>
 </tr>
 </table>
 </form>
 <%
Else
 ' Do our DB insert!
 ' Retrieve the 3 strings to be entered into the DB
 name	 = Request.Form("name")
 group  = Request.Form("group")
 email = Request.Form("email")
 status = 0
 date_time = Now()

 On Error Resume Next
 strErrorMsg = ""
 
 ' String (text) field:

 name = Trim(name)
 If Len(name) = 0 Or Len(name) > 10 Then Err.Raise 1
 group = Trim(group)
 If Len(group) = 0 Or Len(group) > 10 Then Err.Raise 1
 email = Trim(email)
 If Len(email) = 0 Or Len(email) > 10 Then Err.Raise 1
 
 name	 = Replace(name, "'", "''")
 group  = Replace(group, "'", "''")
 email = Replace(email, "'", "''")
 
 If Err.number <> 0 Then
  strErrorMsg = strErrorMsg & "Your entry for string_field is " & _
   "inappropriate!<br />" & vbCrLf
  Err.Clear
 End If
 ' I don't know if this is really documented or a hack,
 ' but it turns error trapping back off!
 On Error Goto 0
 ' If we have an error in our error string then we show
 ' the error message o/w we proceed with the insert.
 If strErrorMsg <> "" Then
  ' Show the error message that got us here!
  Response.Write strErrorMsg
 Else
  ' Open connection to the DB
  Set cnnFormToDB = Server.CreateObject("ADODB.Connection")
  cnnFormToDB.Open strConnString '"DSN=groups_dsn" 
  ' Build our SQL String
  strSQL = ""
  strSQL = strSQL & "INSERT INTO constitutions "
  strSQL = strSQL & "(name, GroupName, email, status, submitted) " & vbCrLf
  strSQL = strSQL & "VALUES ("
  strSQL = strSQL & "'" & name & "'"
  strSQL = strSQL & ", "
  strSQL = strSQL & "'" & group & "'"
  strSQL = strSQL & ", "
  strSQL = strSQL & "'" & email & "'"
  strSQL = strSQL & ", "
  strSQL = strSQL & status
  strSQL = strSQL & ", "
  strSQL = strSQL & "'" & NOW() & "'"
  strSQL = strSQL & ");"
 
  cnnFormToDB.Execute strSQL, lngRecsAffected, adCmdText Or adExecuteNoRecords
  ' Dispose of the CONN object
  cnnFormToDB.Close
  Set cnnFormToDB = Nothing
  
  ' Display a verification message and we're done!
  
  If lngRecsAffected <> 1 Then
  %>
  <h2> sorry there was an error! no information recorded. </h2>
  <% 
  Else
  %>
  
  <h2>Thanks for submitting your information to us!</h2>
  
  <p>
  <strong>The resulting SQL statement was:</strong>
  <pre><%= strSQL %></pre>
  </p>
  
  <p>
  <strong>Number of records affected:</strong> <%= lngRecsAffected %>
  </p>
  <%
  End If
 End If
End If
%>