ok, so I'm sure this is posted somewhere else, but I don't have the time to scroll through all 43,252 posts.

I have a form that a user inputs info and they hit submit and then the info it supposed to be inserted into a table in SQL Server. (the insert statement isn't the problem...maybe...i tried it directly in sql server and it works) The problem is trying to find out why the values are not being inserted into the table from my form.

Here's a sample of my code, perhaps one of you coding geniuses (pure compliment) can take a glance and tell me if I have an obviously huge glaring error.

<form method="post" action="default.asp" id="frmEventPanel" name="frmEventPanel">
<div style="position: absolute;top: 170px;left: 230px;" class="body">
 <table border="0" cellpadding="4" cellspacing="0" width="500">
  <tr>
   <td colspan="4" style="padding-bottom: 10px;" class="h1" align="center">
    Sign up,&nbsp;complete online surveys, and you could win cash.    
   </td>
  </tr>
  <tr>
   <td class="h2" align="right">First Name:</td>
   <td colspan="3">
    <input name="txtFirst" type="text" class="monospace" size="12" maxlength="50" />
    &nbsp;&nbsp;Last Name:&nbsp;&nbsp;<input name="txtLast" type="text" class="monospace" size="16" maxlength="50" />
   </td>
  </tr>
  <tr>
   <td class="h2" align="right">E-mail Address:</td>
   <td colspan="3"><input name="txtEmail" type="text" class="monospace" size="39" maxlength="100" /></td>
  </tr>
  <tr>
   <td class="h2" align="right">Year of Birth:</td>
   <td colspan="3">
    <input name="txtYear" type="text" class="monospace" size="4" maxlength="4" />
   </td>
  </tr> 
  <tr valign="baseline">
   <td class="h2" align="right">Gender:</td>
   <td colspan="3" class="body"><input name="optGender" type="radio" value="F" />Female&nbsp;&nbsp;<input name="optGender" type="radio" value="M" />Male&nbsp;&nbsp;</td>
  </tr>  
  <tr valign="baseline">
   <td class="h2" align="right">Living Status:</td>
   <td colspan="3" class="body">
    <input name="optLiving" type="radio" value="Campus" />On/Near Campus&nbsp;&nbsp;
    <input name="optLiving" type="radio" value="Campus" />Commute From Home&nbsp;&nbsp;
   </td>
  </tr> 
  <tr valign="baseline">
   <td class="h2" align="right">Student Type:</td>
   <td colspan="3" class="body">
    <input name="optStudentType" type="radio" value="STDType" />Full Time&nbsp;&nbsp;
    <input name="optStudentType" type="radio" value="STDType" />Part Time&nbsp;&nbsp;
   </td>
  </tr> 
  <tr valign="baseline">
   <td class="h2" colspan="4" style="padding-top: 12px;padding-left: 20px;">
    <table border="0" cellpadding="3" cellspacing="0" align="center" width="500">
     <tr>
      <td colspan="4" class="h2">How did you learn about this Event?</td>
     </tr>
     <tr>
      <td><input name="optLearn" type="radio" value="WebSite" />Bookstore</td>
      <td><input name="optLearn" type="radio" value="Facebook" />Facebook&nbsp;&nbsp;</td>
      <td><input name="optLearn" type="radio" value="Flyer" />Campus Flyer&nbsp;&nbsp;</td>
      <td><input name="optLearn" type="radio" value="Friend" />Referred by a Friend&nbsp;&nbsp;</td>
     </tr>
     <tr>
      <td colspan="4">
       <input name="optLearn" type="radio" value="Other" />Other:&nbsp;&nbsp;<input type="text" name="txtLearnOther" size="43" maxlength="100" class="monospace">
      </td>
     </tr>
    </table>
   </td>
  </tr> 
  <!--</form>
  <form method="post" action="default.asp" id="frmInstitution" name="frmInstitution"> -->
  <tr valign="baseline">
   <td class="h2" colspan="4" style="padding-top: 5px;padding-left: 20px;">
    <table border="0" cellpadding="3" cellspacing="0" align="center" width="500">
     <tr>
      <td class="h2">What college/institution do you attend?</td>
     </tr>
     <tr>
      <td>
       <%
       Dim DataConn
       Dim CmdPopulateInstitutions
       Dim SQL
        
       Set DataConn = Server.CreateObject("ADODB.Connection")
       Set CmdPopulateInstitutions = Server.CreateObject("ADODB.Recordset")
        
       DataConn.Open  "DSN=example_con; UID=sampleID; PWD=examplepwd"
       SQL = "SELECT *"
       SQL = SQL & " FROM vw123_ID_Institution"
       CmdPopulateInstitutions.Open SQL, DataConn
       %>
       &nbsp;
       <select name="intCategoryID" class="body">
        <option selected="true"></option>
        <%While Not CmdPopulateInstitutions.EOF%>
        <option value="<%=CmdPopulateInstitutions("ID")%>"><%=CmdPopulateInstitutions("Name")%></option>
        <%
        CmdPopulateInstitutions.MoveNext
        Wend
            
        CmdPopulateInstitutions.Close
        Set CmdPopulateInstitutions = Nothing
        DataConn.Close
        Set DataConn = Nothing
        %>    
       </select>
      </td>
     </tr>  
    </table>
   </td>
  </tr>  
 </table>
</div>
  <div align="center" style="padding-top: 10px;">
  <table border="0" bgcolor="#ffffff" cellpadding="1" cellspacing="1">
   <tr>
    <td align="center" style="padding-top: 0px;">
     <%session("checktext") = RandomText(5)%><img alt="Captcha" src="captcha/generate-captcha.asp" border="0" />
    </td>
   </tr>
   <%if instr(Request.Form("formError"),"imagecheck") <> 0 then%>  
   <tr>
    <td align="center"><font color="red"><b>** The image check did not pass. **</b></font></td>
   </tr>
   <%end if%>
   <tr>
    <td align="center" style="padding-top: 4px;"><b>Enter the code shown above:</b>&nbsp;<input type="text" name="imagecheck" size="5" value="<%=Request.Form("imagecheck")%>"><div class="footer">(This helps Money4Textbooks.com prevent automated registrations.)</div></td>
    <!--By completing this enrollment form, you consent to receive transactional and informational emails and faxes from Money4Texbooks.com.  <br> myURL.com will provide independent options to opt out of communications.-->
   </tr>
   <tr>
    <td align="center" style="padding: 20px;">
     <input type="image" src="/images/buttonSignUp.gif" name="submit" value="Sign Me Up!" onclick="return checkSubmit()" onmouseover="javascript:this.src='/images/buttonSignUpOn.gif'" onmouseout="javascript:this.src='/images/buttonSignUp.gif'">
    </td>
   </tr>
  </table>
 </div>
 </form>
<!--#include virtual="scriptlib/footer.inc"-->
<%
FirstN = request.form("txtFirst")
LastN = request.form("txtLast")
Email = request.form("txtEmail")
BYear = request.form("txtYear")
oGender = request.form("optGender")
oStudentType = request.form("optStudentType")
oLiving = request.form("optLiving")
iCategoryID = request.form("intCategoryID", CmdPopulateInstitutions("ID"))
oLearn = request.form("optLearn")
LearnO = request.form("txtLearnOther")
 
 
'Dim DataConn
Dim sql1
'Set DataConn = Server.CreateObject("ADODB.Connection")
DataConn.Open  "DSN=example_con; UID=sampleID; PWD=examplepwd" 
 
  sql1 = sql1 & "INSERT INTO tbllisting (FirstName, LastName, Email, BirthYear, Gender, StudentType, Housing, InstitutionID, ReferredByTypeID, RefByOtherDescription) VALUES ("
  sql1 = sql1 & "'" & FirstN & "'"
  sql1 = sql1 & ", "
  sql1 = sql1 & "'" & LastN & "'"
  sql1 = sql1 & ", "
  sql1 = sql1 & "'" & Email & "'"
  sql1 = sql1 & ", "
  sql1 = sql1 & "'" & BYear & "'"
  sql1 = sql1 & ", "
  sql1 = sql1 & "'" & oGender & "'"
  sql1 = sql1 & ", "
  sql1 = sql1 & "'" & oStudentType & "'"
  sql1 = sql1 & ", "
  sql1 = sql1 & "'" & oLiving & "'"
  sql1 = sql1 & ", "
  sql1 = sql1 & "'" & iCategoryID & "'"
  sql1 = sql1 & ", "
  sql1 = sql1 & "'" & oLearn & "'"
  sql1 = sql1 & ", "  
  sql1 = sql1 & "'" & LearnO & "'" & ")"
 
 DataConn.Execute(sql1)
 response.redirect "faq.asp"
%>

If the insert works from Query Analyzer, it sounds like your code isn't connecting properly. Run a trace ( SQL Profiler ) and then run your page code, and particularly watch for failed logins.

Or, what if my last name, learning, gender, or whatever has an apostrophy in it? Not likely, but still, rather than using Connection.Execute, you might consider building an ADODB.Command with parameters and execute that instead.

Finally, what happens if the record is already in the table? Will SQL fail a duplicate insert, ignore it, or...?

Also, do away with the nested html tables!

ok.....how? I'm not an ASP person, in fact, I know virtually nothing about classic ASP. I'm muddling my way through this by finding articles online and trying to modify the codes.

I can do it in .NET but my bosses refuse to put the .NET framework on this particular server for whatever reason. (bad move, if you ask me)

So, how would I go about

building an ADODB.Command with parameters and execute that instead

?

As for the tables, you're correct (the tables need to go)...but since I didn't do the initial design, it's not mine to modify.(I'll suggest it to the HTML person, here.)

(btw, thanks for the quick reply... )

I don't know, someone who actually understands old-school asp will have to come along and answer this one. Hirst and Adam are the reigning champs around here.

But why on earth would they limit themselves like that? Something to do with worries about Microsoft security? I hate when managers prevent real work from getting done over something they heard.

response.write the query just before the .execute runs

take out the redirect so you can see what the query looks like and any error that may occur.

If you don't spot the problem, post the query back here.

You've got two errors that I can see toward the bottom:

--> 'Dim DataConn
Dim sql1
--> 'Set DataConn = Server.CreateObject("ADODB.Connection")
DataConn.Open  "DSN=example_con; UID=sampleID; PWD=examplepwd"

When you add ' to a line in ASP (except in a string), you comment out the remainder of the line. When you add ' to the beginning of a line, you comment out the entire line.

At the very least, uncomment the third line.

Now I've had a look in daytime;

another problem is that the insert code is going to run immediately after the form has been populated rather than being submitted, (it will run then as well of course) and this is happening before the user even sees the form.

APS VbScript does not wait for user actions, the server does not "know" if it is the first time the page has opened or it has been submitted.
You need to wrap your insert code in an IF statement, to check if the form has been submitted.

if request.form("submit") = "" then 
' run your insert here
end if

Originally Posted by ADAM Web Design

You've got two errors that I can see toward the bottom:

--> 'Dim DataConn
Dim sql1
--> 'Set DataConn = Server.CreateObject("ADODB.Connection")
DataConn.Open  "DSN=example_con; UID=sampleID; PWD=examplepwd"

When you add ' to a line in ASP (except in a string), you comment out the remainder of the line. When you add ' to the beginning of a line, you comment out the entire line.

At the very least, uncomment the third line.

I was running into errors with those two lines... since they were already dimmed in another function I just utilized them...again...I wasn't sure about how classic ASP would work with a "Re-Dim"... so I pretty much just got rid of those two.

As of lately this is now what I'm getting:

Microsoft VBScript runtime error '800a000d'
Type mismatch
/default.asp, line 150

What does this mean?

Originally Posted by chrishirst

Now I've had a look in daytime;

another problem is that the insert code is going to run immediately after the form has been populated rather than being submitted, (it will run then as well of course) and this is happening before the user even sees the form.

APS VbScript does not wait for user actions, the server does not "know" if it is the first time the page has opened or it has been submitted.
You need to wrap your insert code in an IF statement, to check if the form has been submitted.

if request.form("submit") = "" then 
' run your insert here
end if

I will also give that a shot! Thanks!

So far you've all been a great help to me!


mb

Originally Posted by mb2000inc

What does this mean?

What's 4 + "santa"? The answer is a type conversion error, because 4 is an integer and "santa" is a string. You could do "4" + "santa" = "4santa" but you get the idea?

Also if it says line 150, show us that.

Ok, cool! Sorry, I haven't been back in a while. I implemented your ideas, and I must say... they worked swimingly! You guys ALL rock!