Add new record to databased based on textbox input

CarolMorgan · January 7th, 2005, 03:36 PM

I have an on line annual update training that has a certificate that is printed for supervisor signature. I currently have 4 text boxes and one combo box that adds a new record to Access database. I want to change the fill in page to one textbox (SSN) that will get the info from database based on textbox input, add the new record to database and then display the users name on certificate.

nashnash · January 10th, 2005, 03:16 AM

can you plz send me the exact requirement more clearly or web page you have .so that it will be easy to fix..

Thanks
Nash

CarolMorgan · January 10th, 2005, 01:45 PM

The pages work but do not add a new record to the database. I did not create the database so the field names are not user friendly. Thank you for your help:
"textboxsignin.asp"

<html>
<head>

<title>textbox SSN</title>
</head>
<body>
<%

SQL = "SELECT * FROM TRAINING"

Set RecordSet = Server.CreateObject("ADODB.Recordset")

RecordSet.Open SQL, conn, cmdDC

Response.Write("<h2>Please include dashes (123-12-1234)</h2>")

Response.Write("<form method='post' action='textboxview.asp'>")

'displays box for SSN input

Response.Write("<input type='text' name='strSSN' size='46'>")

' Display command button
Response.Write("<br><br>")
Response.Write("<input type='submit' value='Submit SSN'>")
Response.Write("<input type='hidden' name='[CLASS]' value='Online'>")
Response.Write("<input type='hidden' name='DATE' value='NOW()'>")
Response.Write("<input type='hidden' name='Hours' value='1'>")
Response.Write("<input type='hidden' name='HRDIS' value='1234'>")

Response.Write("</form>")

%>
</body></html>

"textboxview.asp"

<head>
<title>Database Example</title>
</head>
<body bgColor="#FFFFFF">
<center>

<%

Set rst = Server.CreateObject("ADODB.Recordset")
SSN = Request("strSSN")

Select Case trim(SSN)
    Case ""
        strSql = "select * From TRAINING WHERE SSN = '" & SSN & "' "
    Case Else
        'strSql = "select * From TRAINING WHERE SSN like '" & SSN & "' & INSERT INTO TRAINING (NAMElast,NAMEfirst,SSN) VALUES('" & request.Form("NAMElast") & "','" & request.Form("NAMElast") & "','" & request.Form("SSN") & "')"
    strSql = "select * From TRAINING WHERE SSN = '" & SSN & "' "
End Select

'requesting info from signin form for database
NAMElast = request.form("NAMElast")
NAMEfirst = request.form("NAMEfirst")
MI = request.form("MI")
SSN = request.form("SSN")
[CLASS] = request.form("CLASS")
'DATE = request.form("DATE")
Hours = request.form("Hours")
HRDIS= replace(HRDIS,"'","''")

'adding info to database
        rst = "INSERT INTO TRAINING (NAMElast,NAMEfirst,MI,SSN,CLASS,DATE,Hours,HRDIS) "
        rst = strSql & " VALUES ("
        rst = strSql & "'" & NAMElast & "'"
        rst = strSql & "'" & NAMEfirst & "'"
        rst = strSql & "'" & MI & "'"
        rst = strSql & "'" & SSN & "'"
        rst = strSql & "'" & [CLASS] & "'"
        rst = strSql & "'" & DATE & "'"
        rst = strSql & "'" & Hours & "'"
        rst = strSql & "'" & HRDIS & "'"
        rst = strSql & ")"

NAMElast = request.form("NAMElast")
NAMEfirst = request.form("NAMEfirst")
SSN = request.form("SSN")
SSN= replace(SSN,"'","''")

rst = conn.execute (strSql)
'rst.Open strProvider, strSql, adOpenStatic

'adding a response
If err.number>0 then
   response.write "VBScript Errors Occured:" & "<P>"
    response.write "Error Number=" & err.number & "<P>"
    response.write "Error Descr.=" & err.description & "<P>"
    response.write "Help Context=" & err.helpcontext & "<P>"
    response.write "Help Path=" & err.helppath & "<P>"
    response.write "Native Error=" & err.nativeerror & "<P>"
    response.write "Source=" & err.source & "<P>"
    response.write "SQLState=" & err.sqlstate & "<P>"
   end if
   IF conn.errors.count> 0 then
    response.write "Database Errors Occured" & "<P>"
    response.write strSql & "<P>"
   for counter= 0 to conn.errors.count
    response.write "Error #" & conn.errors(counter).number & "<P>"
    response.write "Error desc. -> " & conn.errors(counter).description & "<P>"
   next
   else
    response.write "<center><br><br><br><br>"
    response.write ""
    response.write "Thank you"
    response.write "<br>"
    response.write ""
    response.write "Your name has been added to the BBP Database<p></center>"
   end if

%>

<align="center"><%=rst("NAMEfirst") %><%response.write(" ")%><%=rst("NAMElast") %></I> </b></p><br>

  <p align="center"><b>has successfully
    completed the online</b></p>
  <p align="center">Lakeland
  Village</p>
  <p align="center"> Annual BBP\TB
  Update </p>
  <p align="center"><b>Date Completed:<%response.write(date())%>

    </b></p>
  <p align="center"><b>Supervisor Signature:
    ________________________________________</b></p>
  <p align="left"> 
  <input type="button" name="printWhole" value="Print Certificate" onclick="parent.print()"> 
  <input type="button" value="Finish" name="exitwindow" onclick="window.close();"response.write("Thank You")><p align="center">

    <input type=hidden name="NAMEfirst" value="">

  <strong><span style="text-transform: uppercase">A copy
  of this document must be filed in the employee's personnel file</span></strong></form>
<p> </p>

  </HTML>

  </BODY>