help with insert database script (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

sgreen0 -> help with insert database script (1/5/2005 22:49:40)

On a page that has already successfully inserted data into a table, I've added a second routine (edited from a copy of the earlier one) for a different table. It isn't working. It's not even posting an empty record.

Here is the code: 

<% 
If Session("AccessLevel") <>"" Then 

	Dim myConnString2
	Dim myConnection2
	Dim mySQL2


MyConnString2 = Application("online_ConnectionString")
	
 
Set myConnection2 = Server.CreateObject("ADODB.Connection")
	
myConnection2.Open myConnString2
	
mySQL2= "INSERT INTO Login_values"
mySQL2= mySQL2 & "(AccessLevel,CourseID,CourseName) "
mySQL2= mySQL2 & "VALUES ('" & Session("uAccessLevel") & "','" 
mySQL2= mySQL2 & Session("uCourseID") & "','"
mySQL2= mySQL2 & Session("uCourseName") & "')"


myConnection2.Execute mySQL2
	
myConnection2.Close
			
Set myConnection2 = Nothing
	
End If
 
%>


Can you see anything suspicious?

I've got <% session("AccessLevel") %> displayed on the same page and on the page displayed next, so I know it's good. I do notice that the timestamp on the database changes with each insert attempt.

Any ideas?

Thanks.

Stephen

PS If posting the complete page would help, I will...



ou812 -> RE: help with insert database script (1/5/2005 23:31:11)

I'm not seeing anything stick out as being incorrect.

Can you place these right above your "myConnection2.Execute mySQL2" statement?
response.write mySQL2
response.end

This way you can insure your Insert looks as you expect, and that it is indeed getting there?



sgreen0 -> RE: help with insert database script (1/5/2005 23:48:29)

Brian!

I did as you suggested, but nothing appeared on the screen. I'm posting the complete page. You will see that I 've displayed four session variables in the middle of the page for testing. They display as they should.

Thanks for taking the time to look at this.

Stephen 

<html>

<head>
<LINK REL=STYLESHEET TYPE="text/css" HREF="../../anmt_stylesheet.css">

<meta http-equiv="Content-Language" content="en-us">

<%
If Request.Form("FirstName")<>"" Then 
Session("uFirstName") = Request.Form("FirstName")
If Request.Form("LastName")<>"" Then 
Session("uLastName") = Request.Form("LastName")
Else 
Session("uFirstName") = Session("uFirstName") 
Session("uLastName") = Session("uLastName") 
End If 
End If


	Function ParseBody(strText)
		'=================================================
		' This function replaces the Chr(13) with a <br>
		' tag in whatever string is passed to it.
		'=================================================
		strText = Replace(strText, Chr(13), "<br>")
		ParseBody = strText
	End Function

If Request.Form("FirstName")<>"" Then 

	Dim myConnString
	Dim myConnection
	Dim mySQL


MyConnString = Application("online_ConnectionString")
	
 
Set myConnection = Server.CreateObject("ADODB.Connection")
	
myConnection.Open myConnString
	
mySQL= "INSERT INTO OnLineContacts "
mySQL= mySQL & "(FirstName,LastName,Address,City,State,Zip,HomePhone,Cellphone,Email,Source,Notes) "
mySQL= mySQL & "VALUES ('" & Request.Form("FirstName") & "','" 
mySQL= mySQL & Request.Form("LastName") & "','"
mySQL= mySQL & Request.Form("Address") & "','"
mySQL= mySQL & Request.Form("City") & "','" 
mySQL= mySQL & Request.Form("State") & "','"
mySQL= mySQL & Request.Form("Zip") & "','"
mySQL= mySQL & Request.Form("HomePhone") & "','"
mySQL= mySQL & Request.Form("CellPhone") & "','"
mySQL= mySQL & Request.Form("Email") & "','" 
mySQL= mySQL & Request.Form("Source") & "','"
mySQL= mySQL & Request.Form("Background") & "')"



myConnection.Execute mySQL
	
myConnection.Close
			
Set myConnection = Nothing
	
End If
 

'===================================================================
' Send the results to e-mail.
'===================================================================

If Request.Form("FirstName")<>"" Then 

Function die(strInput)
	response.write strInput
	response.end
End Function

'change to address of your own SMTP server
strHost = "mail.anmt.org"
strReturn = Request.Form("Return")

strBody = ""

strFirstName = trim(Request.Form("FirstName"))
if not strFirstName = "" then
	strBody = strBody & "First Name: " & strFirstName & vbcrlf & vbcrlf
end if

strLastName = trim(Request.Form("LastName"))
if not strLastName = "" then
	strBody = strBody & "Last Name: " & strLastName & vbcrlf & vbcrlf
end if

strAddress = trim(Request.Form("Address"))
if not strAddress = "" then
	strBody = strBody & "Address: " & strAddress & vbcrlf & vbcrlf
end if

strCity = trim(Request.Form("City"))
if not strCity = "" then
	strBody = strBody & "City: " & strCity & vbcrlf & vbcrlf
end if

strState = trim(Request.Form("State"))
if not strState = "" then
	strBody = strBody & "State: " & strState & vbcrlf & vbcrlf
end if

strZip = trim(Request.Form("Zip"))
if not strZip = "" then
	strBody = strBody & "Zip: " & strZip & vbcrlf & vbcrlf
end if

strHomePhone = Cstr(trim(Request.Form("HomePhone")))
if not strHomePhone = "" then
	strBody = strBody & "Home Phone: " & strHomePhone & vbcrlf & vbcrlf
end if

strCellPhone = Cstr(trim(Request.Form("CellPhone")))
if not strCellPhone = "" then
	strBody = strBody & "Cell Phone: " & strCellPhone & vbcrlf & vbcrlf
end if

strEmail = trim(Request.Form("email"))
if not stremail = "" then
	strBody = strBody & "E-Mail: " & strEmail & vbcrlf & vbcrlf
end if

strEmailConfirm = trim(Request.Form("EmailConfirm"))
if not strEmailConfirm = "" then
	strBody = strBody & "E-Mail Confirmation: " & strEmailConfirm & vbcrlf & vbcrlf
end if

strSource = trim(Request.Form("Source"))
if not strSource = "" then
	strBody = strBody & "Source: " & vbcrlf & strSource & vbcrlf
end if

strBackground = trim(Request.Form("Background"))
if not strBackground = "" then
	strBody = strBody & "Background: " & vbcrlf & strBackground & vbcrlf
end if

If Request.Form("FirstName")<>"" Then 
Session("uFirstName") = Request.Form("FirstName") 
Session("uLastName") = Request.Form("LastName") 
Else 
Session("uFirstName") = Session("uFirstName") 
Session("uLastName") = Session("uLastName") 
End If 

'////////////////////////
'die strBody
'////////////////////////

Set Mail = Server.CreateObject("Persits.MailSender")
' enter valid SMTP host
Mail.Host = strHost

Mail.From 		= trim(Request.Form("Email")) ' From address
Mail.FromName 	= trim(Request.Form("FirstName" & "LastName")) ' optional
Mail.AddAddress  "academyonline@anmt.org"

' message subject
Mail.Subject 	= "Online Registration"

' message body
Mail.Body 		= strBody

strErr = ""

bSuccess = False

On Error Resume Next ' catch errors

Mail.Send ' send message

If Err <> 0 Then ' error occurred
  strErr 		= Err.Description
  Response.Write "halted : " & strErr
else
  bSuccess 		= True
  Response.Redirect(strReturn)
End If

End If
%>

</head>

<body>

<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="97%" height="234">
  <tr>
    <td width="26%" rowspan="2" valign="top" height="234">
    
<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="23%" height="495">
<tr>
 <td width="25%" height="108">
 <!--webbot bot="Include" U-Include="online_nav_bar.htm" TAG="BODY" --></td>
 </tr>
</table>
</td>
    <td width="75%" height="1">
    <p align="center" style="margin-top: 0; margin-bottom: 0">
    <img border="0" src="images/register.gif" align="center"></td>
  </tr>
  <tr>
    <td width="75%" height="352" valign="top">
      <table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="100%" height="91">
        <tr>
          <td colspan="2" height="24">
    <p style="margin-left: 20; margin-right: 20; margin-top: 6; margin-bottom: 0">
    <font face="Arial Rounded MT Bold">
<% response.write Session("uFirstName")%>, now tell us about the course you wish to 
    register for:</font></p>
          </td>
        </tr>
        <tr>
          <td height="1" valign="top">
          <form method="POST" action="--WEBBOT-SELF--">
            <input TYPE="hidden" NAME="VTI-GROUP" VALUE="0"><p style="margin-left: 20; margin-right: 20; margin-top: 12; margin-bottom: 0">
    <font face="Arial Rounded MT Bold">Course Names:</font></td>
          <td height="1" valign="top">
          <p style="margin-top: 12">
          <nobr>
 <!--#include file="../_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
          </SCRIPT>
<% end if %>
<%
fp_sQry="SELECT CourseID, CourseName FROM OnLineCourses ORDER BY CourseName ASC"
fp_sDefault=""
fp_sNoRecords="No records returned."
fp_sDataConn="online"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=True
fp_sMenuChoice="CourseName"
fp_sMenuValue="CourseName"
fp_sColTypes="&CourseID=3&CourseName=202&CourseFee=6&"
fp_iDisplayCols=1
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>

<select NAME="CourseName" SIZE="1">
        
<!--#include file="../_fpclass/fpdbrgn1.inc"-->
<option><%=FP_FieldHTML(fp_rs,"CourseName")%></option>
          
<!--#include file="../_fpclass/fpdbrgn2.inc"-->
</select>
<% Session("uCourseName") = Request.Form("CourseName") %>
<%
If Request.Form("CourseName") = "Advanced Curriculum" Then
Session("uAccessLevel") = "Advanced"
End If

If Request.Form("CourseName") = "Book Lab" Then
Session("uAccessLevel") = "Book"
End If
dim vAccessLevel

If Request.Form("CourseName") = "Songwriting Lab" Then
Session("uAccessLevel") = "Song"
End If

If Request.Form("CourseName") = "Core Curriculum" Then
Session("uAccessLevel") = "Core"
End If

If Request.Form("CourseName") = "Lyric Lab" Then
Session("uAccessLevel") = "Lyric"
End If

If Request.Form("CourseName") = "Music Lab" Then
Session("uAccessLevel") = "Music"
End If
%>
          </nobr>
   <input type="submit" value="Select" name="B1">
   
   
<p style="margin-top: 12">
           </td>
        </tr>
        <tr>
          <td height="1" valign="top">
    <p style="margin-left: 20; margin-right: 20; margin-top: 0; margin-bottom: 12">
    <font face="Arial Rounded MT Bold">Tuition Amount:</font></p>
          </td>
          <td height="1" valign="top">
          <!--#include file="../_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
          </SCRIPT>
<% end if %>
<%
fp_sQry="SELECT CourseID, Format(CourseFee, 'Currency') AS Amount FROM OnLineCourses WHERE (CourseName =  '::CourseName::')"
fp_sDefault="CourseName=0"
fp_sNoRecords="No records returned."
fp_sDataConn="online"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice="Amount"
fp_sMenuValue="Amount"
fp_sColTypes="&Amount=202&"
fp_iDisplayCols=1
fp_fCustomQuery=True
BOTID=1
fp_iRegion=BOTID
%>
<!--#include file="../_fpclass/fpdbrgn1.inc"-->

<p style="margin-top: 0; margin-bottom: 12">
          <%=FP_FieldVal(fp_rs,"Amount")%></p>

<% Session("uAmount") = FP_FieldVal(fp_rs,"Amount") %>
<% Session("uCourseID") = FP_FieldHTML(fp_rs,"CourseID") %>
          <!--#include file="../_fpclass/fpdbrgn2.inc"-->
</td>
        </tr>
        <tr>
          <td height="1" valign="top" colspan="2">
<% response.write Session("uFirstName")%>
<% response.write Session("uCourseName")%>
<% response.write Session("uCourseID")%>
<% response.write Session("uAmount")%>
<% response.write Session("uAccessLevel")%>

<% 
If Session("AccessLevel") <>"" Then 

	Dim myConnString2
	Dim myConnection2
	Dim mySQL2


MyConnString2 = Application("online_ConnectionString")
	
 
Set myConnection2 = Server.CreateObject("ADODB.Connection")
	
myConnection2.Open myConnString2
	
mySQL2= "INSERT INTO Login_values"
mySQL2= mySQL2 & "(AccessLevel,CourseID,CourseName) "
mySQL2= mySQL2 & "VALUES ('" & Session("uAccessLevel") & "','" 
mySQL2= mySQL2 & Session("uCourseID") & "','"
mySQL2= mySQL2 & Session("uCourseName") & "')"

response.write mySQL2 
response.end 
myConnection2.Execute mySQL2
	
myConnection2.Close
			
Set myConnection2 = Nothing
	
End If
 
%>


<p align="center" style="margin-top: 12"><font face="Arial"><b>When you click 
the PayPal logo you will be 
    directed to PayPal to make your payment. Once you submit your payment, you will be 
    directed the Login Registration Page so you can choose a Login User Name 
    and Password.  Then you may begin your curriculum.  
    Enjoy!</b></font>
    <p align="center">
    <a href="Paypal.asp?Amount=<%=Session("uAmount")%>&CourseName=<%=Session("uCourseName") %>&CourseID=<%=Session("uCourseID") %>&AccessLevel=<%=Session("uAccessLevel")%>">
    <img border="0" src="images/icon_mastercard.gif"><img border="0" src="images/icon_visa.gif"><img border="0" src="images/icon_paypal.gif"><img border="0" src="images/icon_echeck.gif"><img border="0" src="images/icon_discover.gif"><img border="0" src="images/icon_amex.gif"></a><p align="center">
    <img border="0" src="images/logoPaypal.gif"><p align="center">
    <font face="Verdana, Arial, sans-serif" size="3">
        </tr>
      </table>
      </td>
  </tr>
  </table>

</body>

</html>



ou812 -> RE: help with insert database script (1/6/2005 0:02:13)

If I'm reading your code correctly, and from what you just stated, you are leaving your page before you get to your insert.

This is where you are leaving.
If Err <> 0 Then ' error occurred
strErr = Err.Description
Response.Write "halted : " & strErr
else
bSuccess = True
Response.Redirect(strReturn)
End If

After you send email successfully you redirect to somewhere else and never reach your mySQL2 section. What is it you want to do if your email fails? Currently it just displays it has a problem and continues on with the remainder of the page, which should go down to the mySQL2 section. But, if the email succeeds it redirects to another page, never reaching mySQL2 area.






sgreen0 -> RE: help with insert database script (1/6/2005 1:14:32)

Good call.

So I commented out these three lines:

else
bSuccess = True
Response.Redirect(strReturn)

And still nothing happens.

Hmmm...

Stephen



Spooky -> RE: help with insert database script (1/6/2005 1:53:15)

Also remove "On Error Resume Next" to see if an error is created



sgreen0 -> RE: help with insert database script (1/6/2005 2:18:28)

Removing that line showed this error:

Microsoft VBScript compilation error '800a03f6'

Expected 'End'

/online_curriculum/online_register_email.asp, line 498


Line 498 doesn't exist...

Stephen



sgreen0 -> RE: help with insert database script (1/6/2005 2:57:57)

I found it - a missing End If at the very end of the send e-mail code. The only problem is that the HTML on the page doesn't completely display.

The two DRW areas show. Then the results of the code Brian suggested (with blank entries).

As soon as I select a CourseName from the drop down, the Amount is displayed and Biran's code fills in. It looks like this:

INSERT INTO LoginValues(AccessLevel,CourseID,CourseName) VALUES ('Advanced','5','Advanced Curriculum')

However, the rest of the page, some text and a link to PayPal (just a dummy page at the moment) don't show up.

More to the point, nothing is added to the second database.

Thanks for your time.

Stephen



ou812 -> RE: help with insert database script (1/6/2005 11:51:52)

Can you post your code how it looks right now. It is sounding like you might have an ASP stop or start marker(<% %>) misplaced or missing now, if it is displaying some ASP code and not some HTML code.



sgreen0 -> RE: help with insert database script (1/6/2005 12:44:52)

Thanks, Brian!

Here is the code as it is: 

<html>

<head>
<LINK REL=STYLESHEET TYPE="text/css" HREF="../../anmt_stylesheet.css">

<meta http-equiv="Content-Language" content="en-us">

<%
If Request.Form("FirstName")<>"" Then 
Session("uFirstName") = Request.Form("FirstName")
If Request.Form("LastName")<>"" Then 
Session("uLastName") = Request.Form("LastName")
Else 
Session("uFirstName") = Session("uFirstName") 
Session("uLastName") = Session("uLastName") 
End If 
End If


	Function ParseBody(strText)
		'=================================================
		' This function replaces the Chr(13) with a <br>
		' tag in whatever string is passed to it.
		'=================================================
		strText = Replace(strText, Chr(13), "<br>")
		ParseBody = strText
	End Function

If Request.Form("FirstName")<>"" Then 

	Dim myConnString
	Dim myConnection
	Dim mySQL


MyConnString = Application("online_ConnectionString")
	
 
Set myConnection = Server.CreateObject("ADODB.Connection")
	
myConnection.Open myConnString
	
mySQL= "INSERT INTO OnLineContacts "
mySQL= mySQL & "(FirstName,LastName,Address,City,State,Zip,HomePhone,Cellphone,Email,Source,Notes) "
mySQL= mySQL & "VALUES ('" & Request.Form("FirstName") & "','" 
mySQL= mySQL & Request.Form("LastName") & "','"
mySQL= mySQL & Request.Form("Address") & "','"
mySQL= mySQL & Request.Form("City") & "','" 
mySQL= mySQL & Request.Form("State") & "','"
mySQL= mySQL & Request.Form("Zip") & "','"
mySQL= mySQL & Request.Form("HomePhone") & "','"
mySQL= mySQL & Request.Form("CellPhone") & "','"
mySQL= mySQL & Request.Form("Email") & "','" 
mySQL= mySQL & Request.Form("Source") & "','"
mySQL= mySQL & Request.Form("Background") & "')"



myConnection.Execute mySQL
	
myConnection.Close
			
Set myConnection = Nothing
	
End If
 

'===================================================================
' Send the results to e-mail.
'===================================================================

If Request.Form("FirstName")<>"" Then 

Function die(strInput)
	response.write strInput
	response.end
End Function

'change to address of your own SMTP server
strHost = "mail.anmt.org"
strReturn = Request.Form("Return")

strBody = ""

strFirstName = trim(Request.Form("FirstName"))
if not strFirstName = "" then
	strBody = strBody & "First Name: " & strFirstName & vbcrlf & vbcrlf
end if

strLastName = trim(Request.Form("LastName"))
if not strLastName = "" then
	strBody = strBody & "Last Name: " & strLastName & vbcrlf & vbcrlf
end if

strAddress = trim(Request.Form("Address"))
if not strAddress = "" then
	strBody = strBody & "Address: " & strAddress & vbcrlf & vbcrlf
end if

strCity = trim(Request.Form("City"))
if not strCity = "" then
	strBody = strBody & "City: " & strCity & vbcrlf & vbcrlf
end if

strState = trim(Request.Form("State"))
if not strState = "" then
	strBody = strBody & "State: " & strState & vbcrlf & vbcrlf
end if

strZip = trim(Request.Form("Zip"))
if not strZip = "" then
	strBody = strBody & "Zip: " & strZip & vbcrlf & vbcrlf
end if

strHomePhone = Cstr(trim(Request.Form("HomePhone")))
if not strHomePhone = "" then
	strBody = strBody & "Home Phone: " & strHomePhone & vbcrlf & vbcrlf
end if

strCellPhone = Cstr(trim(Request.Form("CellPhone")))
if not strCellPhone = "" then
	strBody = strBody & "Cell Phone: " & strCellPhone & vbcrlf & vbcrlf
end if

strEmail = trim(Request.Form("email"))
if not stremail = "" then
	strBody = strBody & "E-Mail: " & strEmail & vbcrlf & vbcrlf
end if

strEmailConfirm = trim(Request.Form("EmailConfirm"))
if not strEmailConfirm = "" then
	strBody = strBody & "E-Mail Confirmation: " & strEmailConfirm & vbcrlf & vbcrlf
end if

strSource = trim(Request.Form("Source"))
if not strSource = "" then
	strBody = strBody & "Source: " & vbcrlf & strSource & vbcrlf
end if

strBackground = trim(Request.Form("Background"))
if not strBackground = "" then
	strBody = strBody & "Background: " & vbcrlf & strBackground & vbcrlf
end if

If Request.Form("FirstName")<>"" Then 
Session("uFirstName") = Request.Form("FirstName") 
Session("uLastName") = Request.Form("LastName") 
Else 
Session("uFirstName") = Session("uFirstName") 
Session("uLastName") = Session("uLastName") 
End If 


'////////////////////////
'die strBody
'////////////////////////

Set Mail = Server.CreateObject("Persits.MailSender")
' enter valid SMTP host
Mail.Host = strHost

Mail.From 		= trim(Request.Form("Email")) ' From address
Mail.FromName 	= trim(Request.Form("FirstName" & "LastName")) ' optional
Mail.AddAddress  "academyonline@anmt.org"

' message subject
Mail.Subject 	= "Online Registration"

' message body
Mail.Body 		= strBody

strErr = ""

bSuccess = False

'On Error Resume Next ' catch errors

Mail.Send ' send message

If Err <> 0 Then ' error occurred
  strErr 		= Err.Description
  Response.Write "halted : " & strErr
'else
'  bSuccess 		= True
'  Response.Redirect(strReturn)End If
End If
End If
%>

</head>

<body>

<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="97%" height="234">
  <tr>
    <td width="26%" rowspan="2" valign="top" height="234">
    
<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="23%" height="495">
<tr>
 <td width="25%" height="108">
 <!--webbot bot="Include" U-Include="online_nav_bar.htm" TAG="BODY" --></td>
 </tr>
</table>
</td>
    <td width="75%" height="1">
    <p align="center" style="margin-top: 0; margin-bottom: 0">
    <img border="0" src="images/register.gif" align="center"></td>
  </tr>
  <tr>
    <td width="75%" height="352" valign="top">
      <table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="100%" height="91">
        <tr>
          <td colspan="2" height="24">
    <p style="margin-left: 20; margin-right: 20; margin-top: 6; margin-bottom: 0">
    <font face="Arial Rounded MT Bold">
<% response.write Session("uFirstName")%>, now tell us about the course you wish to 
    register for:</font></p>
          </td>
        </tr>
        <tr>
          <td height="1" valign="top">
          <form method="POST" action="--WEBBOT-SELF--">
            <input TYPE="hidden" NAME="VTI-GROUP" VALUE="0"><p style="margin-left: 20; margin-right: 20; margin-top: 12; margin-bottom: 0">
    <font face="Arial Rounded MT Bold">Course Names:</font></td>
          <td height="1" valign="top">
          <p style="margin-top: 12">
          <nobr>
 <!--#include file="../_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
          </SCRIPT>
<% end if %>
<%
fp_sQry="SELECT CourseID, CourseName FROM OnLineCourses ORDER BY CourseName ASC"
fp_sDefault=""
fp_sNoRecords="No records returned."
fp_sDataConn="online"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=True
fp_sMenuChoice="CourseName"
fp_sMenuValue="CourseName"
fp_sColTypes="&CourseID=3&CourseName=202&CourseFee=6&"
fp_iDisplayCols=1
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>

<select NAME="CourseName" SIZE="1">
        
<!--#include file="../_fpclass/fpdbrgn1.inc"-->
<option><%=FP_FieldHTML(fp_rs,"CourseName")%></option>
          
<!--#include file="../_fpclass/fpdbrgn2.inc"-->
</select>
<% Session("uCourseName") = Request.Form("CourseName") %>
<%
If Request.Form("CourseName") = "Advanced Curriculum" Then
Session("uAccessLevel") = "Advanced"
End If

If Request.Form("CourseName") = "Book Lab" Then
Session("uAccessLevel") = "Book"
End If
dim vAccessLevel

If Request.Form("CourseName") = "Songwriting Lab" Then
Session("uAccessLevel") = "Song"
End If

If Request.Form("CourseName") = "Core Curriculum" Then
Session("uAccessLevel") = "Core"
End If

If Request.Form("CourseName") = "Lyric Lab" Then
Session("uAccessLevel") = "Lyric"
End If

If Request.Form("CourseName") = "Music Lab" Then
Session("uAccessLevel") = "Music"
End If
%>
          </nobr>
   <input type="submit" value="Select" name="B1">
   
   
<p style="margin-top: 12">
           </td>
        </tr>
        <tr>
          <td height="1" valign="top">
    <p style="margin-left: 20; margin-right: 20; margin-top: 0; margin-bottom: 12">
    <font face="Arial Rounded MT Bold">Tuition Amount:</font></p>
          </td>
          <td height="1" valign="top">
          <!--#include file="../_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
          </SCRIPT>
<% end if %>
<%
fp_sQry="SELECT CourseID, Format(CourseFee, 'Currency') AS Amount FROM OnLineCourses WHERE (CourseName =  '::CourseName::')"
fp_sDefault="CourseName=0"
fp_sNoRecords="No records returned."
fp_sDataConn="online"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice="Amount"
fp_sMenuValue="Amount"
fp_sColTypes="&Amount=202&"
fp_iDisplayCols=1
fp_fCustomQuery=True
BOTID=1
fp_iRegion=BOTID
%>
<!--#include file="../_fpclass/fpdbrgn1.inc"-->

<p style="margin-top: 0; margin-bottom: 12">
          <%=FP_FieldVal(fp_rs,"Amount")%></p>

<% Session("uAmount") = FP_FieldVal(fp_rs,"Amount") %>
<% Session("uCourseID") = FP_FieldHTML(fp_rs,"CourseID") %>
          <!--#include file="../_fpclass/fpdbrgn2.inc"-->
</td>
        </tr>
        <tr>
          <td height="1" valign="top" colspan="2">
<% response.write Session("uFirstName")%>
<% response.write Session("uCourseName")%>
<% response.write Session("uCourseID")%>
<% response.write Session("uAmount")%>
<% response.write Session("uAccessLevel")%>

<% 
If Session("AccessLevel") <>"" Then 

	Dim myConnString2
	Dim myConnection2
	Dim mySQL2


MyConnString2 = Application("online_ConnectionString")
	
 
Set myConnection2 = Server.CreateObject("ADODB.Connection")
	
myConnection2.Open myConnString2
	
mySQL2= "INSERT INTO LoginValues"
mySQL2= mySQL2 & "(AccessLevel,CourseID,CourseName) "
mySQL2= mySQL2 & "VALUES ('" & Session("uAccessLevel") & "','" 
mySQL2= mySQL2 & Session("uCourseID") & "','"
mySQL2= mySQL2 & Session("uCourseName") & "')"

response.write mySQL2 
response.end 
myConnection2.Execute mySQL2
	
myConnection2.Close
			
Set myConnection2 = Nothing
	
End If
 
%>


<p align="center" style="margin-top: 12"><font face="Arial"><b>When you click 
the PayPal logo you will be 
    directed to PayPal to make your payment. Once you submit your payment, you will be 
    directed the Login Registration Page so you can choose a Login User Name 
    and Password.  Then you may begin your curriculum.  
    Enjoy!</b></font>
    <p align="center">
    <a href="Paypal.asp?Amount=<%=Session("uAmount")%>&CourseName=<%=Session("uCourseName") %>&CourseID=<%=Session("uCourseID") %>&AccessLevel=<%=Session("uAccessLevel")%>">
    <img border="0" src="images/icon_mastercard.gif"><img border="0" src="images/icon_visa.gif"><img border="0" src="images/icon_paypal.gif"><img border="0" src="images/icon_echeck.gif"><img border="0" src="images/icon_discover.gif"><img border="0" src="images/icon_amex.gif"></a><p align="center">
    <img border="0" src="images/logoPaypal.gif"><p align="center">
    <font face="Verdana, Arial, sans-serif" size="3">
        </tr>
      </table>
      </td>
  </tr>
  </table>

</body>

</html>


As I said, I understand why the session variables aren't filled until the selection is amde from the drop down (except for FirstName, which is already displayed at the dop of the page). But why the HTML isn't showing...

Stephen



sgreen0 -> RE: help with insert database script (1/6/2005 13:01:57)

Actually, as I look at it now the page runs UNTIL the new mySQL2 code, then stops displaying HTML. I tried removing the IF statement before that code, but it still doesn't work. The IF might be stopping the mySQL2 from running until the drop down is selelcted because Session("AccessLevel") is blank until then...

Stephen



ou812 -> RE: help with insert database script (1/6/2005 15:50:46)

Stephen, I'm not sure where it is stopping to display or working for you. The "response.end" is still in your code after where it should display the SQL2 statement. Is it displaying your sql statement? I'm having a hard time walking through the code trying to determine what might be wrong because I'm not clear what is or isn't working. Can you describe, or perhaps a screen shot, of what is working, or displaying?



sgreen0 -> RE: help with insert database script (1/6/2005 17:14:50)

Brian!

I removed the response.end from the code and the rest of the HTML DID display. However, the SQL code didn't. The SQL2 entry was still NOT entered in the database.

Let me try to describe what should be happening.

This page follows a standard form that collects personal info (name, address, etc.). The first thing it does is post that information to a table in our database (OnLineContacts). This works.

Then it takes the same info and sends a notification e-mail. This works.

Along the way I create sesson variables for FirstName and LastName.

Next a DRW created drop down display a choice of Course Names (CourseName). This is where I create session variable for CourseName and AccessLevel. This works.

Following this another DRW displays the tuition amount (Amount) based on the drop down selection. Here I create session variables for CourseID and Amount. This is working.

Right after that, I display some session variables - for testing only, they will be removed.

Now we come to the troublesome area. This is another database insert meant to write some of the session variables to a table. (The reason for this is that the next step in the registration process after this page is to leave our domain and go to PayPal, and I don't know of any way to carry all those variables to PayPal and back to Spooky Login for login registration.)

Then the HTML should appear - just a message to the registrant about where they are going next followed by a link to PayPal passing (hopefully) the amount to be charged. (Currently the link goes to a dummy PayPal page until I figure out how to link to PayPal. I've never worked with PayPal, so I'm not sure what is and is not possible as far as variables go.)

Does that paint a good enough picture. I don't know how to attach a picture otherwise I would...

Thanks for all your patient help.

Stephen



sgreen0 -> RE: help with insert database script (1/6/2005 17:23:30)

Brian!

I usually use Fast Reply that doesn't show the Embed Picture in Post option.

So here is a screenshot of how the page looks now. You'll notice that the SQL statement that I said appeared earlier, doesn't now.

Take care.

Stephen

[image]local://upfiles/2926/EE1ADB8A604E44B8B031C081EA271EF6.jpg[/image]



BeTheBall -> RE: help with insert database script (1/6/2005 18:49:39)

Personally, I think you are trying to do too much on one page. Generally, the inserting of data requires some sort of form submission to accomplish the task. I understand that the user completes one form to get to this page. I would suggest that you then have a second form with the additional data you need and have that form submit to a third page whereon you insert the data and then from there send the user to PayPal.

Just my 2 cents.



sgreen0 -> RE: help with insert database script (1/6/2005 19:10:28)

Duane!

You may be right. If I leave the original form page as is, posting to a second page that contains the course and fee DRW's (stripped off the database and e-mail code), how can I then post all the info to the third page for database posting and e-mailing?

BTW, the question still remains - why isn't the SQL2 section inserting into the database??

Thanks for your help.

Stephen



ou812 -> RE: help with insert database script (1/6/2005 19:40:35)

Okay, if you go to more pages you will need to pass the data by session, querystring, or hidden fields, and then response.reidrect. Then grab them on the recieving page by session, request.querystring, or request.form, respectively. Each way has positives and negatives. Let us know if you need more assistance with one of these ways if you choose to go to more pages.

But, with your current setup I'm still thinking your query2 may have something not quite right, since that is all that really isn't working. When you did the response.write of mySQL2(with the response.end, for viewing/testing only) did the query look okay? Are you able to just run this query against your database to verify?



sgreen0 -> RE: help with insert database script (1/6/2005 21:22:24)

Brian!

I copied the code to a page with one line of text. You can see that I created session variables to match. 

<html>
<head>
<meta name="GENERATOR" content="Microsoft FrontPage 5.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>New Page 1</title>
</head>
<% 
session("uAccessLevel")="Advanced"
session("uCourseName")="Advanced Curriculum"
session("uCourseID")="5"
	Dim myConnString2
	Dim myConnection2
	Dim mySQL2
MyConnString2 = Application("online_ConnectionString")
Set myConnection2 = Server.CreateObject("ADODB.Connection")
myConnection2.Open myConnString2
mySQL2= "INSERT INTO LoginValues"
mySQL2= mySQL2 & "(AccessLevel,CourseID,CourseName) "
mySQL2= mySQL2 & "VALUES ('" & Session("uAccessLevel") & "','" 
mySQL2= mySQL2 & Session("uCourseID") & "','"
mySQL2= mySQL2 & Session("uCourseName") & "')"
'response.write mySQL2 
'response.end 
myConnection2.Execute mySQL2
myConnection2.Close
Set myConnection2 = Nothing
%>
<body>
This page should insert a record into the LoginVales table of online.mdb.
</body>
</html>


It worked exactly as it should.

Stephen



sgreen0 -> RE: help with insert database script (1/6/2005 22:19:53)

I'm not sure what changed, but the SQL2 insert is working now.

In fact, as soon as the page opens, a record is inserted in the table with leftover session variables. Then another record was inserted after the drop down choice (CourseName).

Since session("uCourseID") is only created AFTER the selection is made from the drop down, I changed the If statement before the SQL2 code to:

If Sesssion("uCourseID") <> "" Then (run SQL2)

That stopped the page from inserting in the database immediately.

I also set those session variables to "" at the top of the original form page, just in case.

I wish I could tell you what magic I wrought to make this work. I hope it sticks.

I'm still going to work on Duane's suggestion.

Thanks so much guys.

Stephen



ou812 -> RE: help with insert database script (1/6/2005 23:15:33)

Congrats, Stephen! Glad to hear it is working. It is nice when the magic gets it going but always wish I knew why just in case it happens again. [:D]



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.140625