• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 330
  • Last Modified:

How to change the Thank You page INSERT INTO statement using ASP?

Dear EE experts,

We would like to ask for tech support on how to revise the attached scripts, before, the db that was used is mysql, then we're changing it to MS Access db. Please see the attached script & db as well (css_users.mdb).
The table name is Users.
We also would like to have not just a Thank You result, but to have user's firstname & lastname...

Thank you!

*** This would be related to our Registration Form page, that we'll open for another thread, diffrerent issue.
<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<%  Option Explicit             %>
<%
dim myDb, seriesid, myConnection
Dim connectString, sqlContent, rsGrupo

myDb = "steproducts"

connectString = "Driver={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=" & myDb & ";UID=admin;PWD=stethadmin;"

Set myConnection = Server.CreateObject("ADODB.Connection")
myConnection.Open connectString

sqlContent = "INSERT INTO Grupo (Name,Surname,Username,Password,Email,Telephone,Mobile,Company,Address) VALUES ('"&Request.Form("fname")&"','"&Request.Form("lname")&"','"&Request.Form("Username")&"','"&Request.Form("Password")&"','"&Request.Form("Email")&"','"&Request.Form("Telephone")&"','"&Request.Form("Mobile")&"','"&Request.Form("Company")&"','"&Request.Form("Address")&"')"


Set rsGrupo = Server.CreateObject("ADODB.Recordset") 
Set rsGrupo = myConnection.Execute(sqlContent)
%>
<h1 align="center">Thank You</h1>

Open in new window

css-users.mdb
0
Stiebel Eltron
Asked:
Stiebel Eltron
  • 10
  • 5
  • 4
2 Solutions
 
pritamduttCommented:
You will require following changes
1. Connection string will look like


myDb = "css-users.mdb" ' Should be full path 
connectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & myDB 

Open in new window


and
Thank you would change to something like

<h1 align="center">Thank You <% Response.Write(Request.Form("Username")) %> </h1>

Open in new window


Hope this helps.
0
 
Stiebel EltronAuthor Commented:
@pritamdutt: The database location is same with the registration form & result form. That's why it's just the filename...
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I believe that your code should look like:

 <h1 align="center">Thank You <% Response.Write(Request.Form("fname")) + Response.Write(Request.Form("lname")) %> </h1>
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
pritamduttCommented:
I agree with @LSMConsulting.. I was just giving an reference on how to display values along with the message.
0
 
Stiebel EltronAuthor Commented:
@LSMConsulting: Yes, you're right! But, how about with this part?:
<%
dim myDb, seriesid, myConnection
Dim connectString, sqlContent, rsGrupo

myDb = "steproducts"

connectString = "Driver={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=" & myDb & ";UID=admin;PWD=stethadmin;"

Set myConnection = Server.CreateObject("ADODB.Connection")
myConnection.Open connectString

sqlContent = "INSERT INTO Grupo (Name,Surname,Username,Password,Email,Telephone,Mobile,Company,Address) VALUES ('"&Request.Form("fname")&"','"&Request.Form("lname")&"','"&Request.Form("Username")&"','"&Request.Form("Password")&"','"&Request.Form("Email")&"','"&Request.Form("Telephone")&"','"&Request.Form("Mobile")&"','"&Request.Form("Company")&"','"&Request.Form("Address")&"')"


Set rsGrupo = Server.CreateObject("ADODB.Recordset")
Set rsGrupo = myConnection.Execute(sqlContent)
%>

Kindly advise how to revise it using MS Access db...

Thank you!
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Looks like pritamdutt showed you how to change the connection string to work with your Access database. If you need a little more assistance with that syntax, check this site: http://www.connectionstrings.com/access

In most cases, however, you'll need to use Server.MapPath(myDb) to get the full path to the database on that server:

myDb = Server.MapPath("css-users.mdb") ' Should be full path
connectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & myDB  

MSDB page for Server.MapPath: http://msdn.microsoft.com/en-us/library/ms524632%28v=vs.90%29.aspx

Other than that, everything else should work fine.

Additional:

Personally, I've always opened ADO recordsets like this:

Set rsGrupo = Server.CreateObject("ADODB.Recordset")
rsGrupo.Open sqlContent, myConnection
0
 
Stiebel EltronAuthor Commented:
myDb = Server.MapPath("css-users.mdb") ' Should be full path

How to set full path? As this is located on the server...
I tried to use "http://sitename/.../dbname.mdb, then I tried to input 1 name, but it gives me an error result of "Not a valid name."
0
 
pritamduttCommented:
@stiebel

Once you use Server.MapPath("provide relative path")

e.g.

Your database resides in http://sitename/db/css-users.mdb

then your code should be like

Server.MapPath("db/css-users.mdb")

Hope this helps.
0
 
Stiebel EltronAuthor Commented:
@pritamdutt: I still get a not valid path result...
Eventhough I changed it to:
connectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.MapPath ("..\asia2\login\css_users.mdb") & ";"

Still the same result...
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
How about this:

connectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.MapPath ("css_users.mdb") & ";"



0
 
Stiebel EltronAuthor Commented:
Now I got this error: "Syntax error in INSERT INTO statement"
Error in line 19: Set rsUsers = myConnection.Execute(sqlContent)

Please see the attached codes for the update of my Thank You page...
<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<%  Option Explicit             %>
<%
dim myDb, seriesid, myConnection
Dim connectString, sqlContent, rsUsers

'myDb = "..\asia2\login\css-users.mdb" ' Should be full path 
'connectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & myDB
connectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.MapPath ("css_users.mdb") & ";" 

Set myConnection = Server.CreateObject("ADODB.Connection")
myConnection.Open connectString

sqlContent = "INSERT INTO Users (Name,Surname,Email,Password,RePassword,Country,Telephone,Mobile,Company,Address) VALUES ('"&Request.Form("fname")&"','"&Request.Form("lname")&"','"&Request.Form("Email")&"','"&Request.Form("Password")&"','"&Request.Form("RePassword")&"','"&Request.Form("Country")&"','"&Request.Form("Telephone")&"','"&Request.Form("Mobile")&"','"&Request.Form("Company")&"','"&Request.Form("Address")&"')"


Set rsUsers = Server.CreateObject("ADODB.Recordset") 
Set rsUsers = myConnection.Execute(sqlContent)
%>
<h1 align="center">Thank You <% Response.Write(Request.Form("fname")) + Response.Write(Request.Form("lname")) %> </h1>

Open in new window

0
 
pritamduttCommented:
I'll suggest you to comment out myConnection.Execute and do a Response.Write(sqlContent) to see what query is actually being generated.

May be that will give u the reason.

If all seems fine.. copy the query and run in into MS Access.


Hope this helps
0
 
Stiebel EltronAuthor Commented:
Now I got this message:
INSERT INTO Users (Name,Surname,Email,Password,RePassword,Country,Telephone,Mobile,Company,Address) VALUES ('','','','','','','','','','')
Microsoft VBScript runtime error '800a01a8'

Object required: 'Response.Write(...)'

/iphoto/asia2/login/signup_result.asp, line 19
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
As you can see, you're not supplying anything to the VALUES section of your insert statement. This may or may not succeed, depending on what restrictions you have set on the table and fields.

As pritamdutt said, try running that INSERT statement directly in your Access database. To do that, open the Access database, create a new Query but don't add any tables, then switch over to SQL view. Copy/paste the INSERT Statement into that SQL view, then swith over to Design view. If there is trouble, the Acess query engine will complain.
0
 
Stiebel EltronAuthor Commented:
I'll get back to you on Monday, asap....
0
 
Stiebel EltronAuthor Commented:
@LSMConsulting: As per your response to this thread ID: 36932611, I did what you said, I didn't receive any errors... but I still receive this error, tried to fill up forms and send.
INSERT INTO Users (Name,Surname,Email,Password,RePassword,Country,Telephone,Mobile,Company,Address) VALUES ('da','da','da','da','da','da','da','12','12','da')
Microsoft VBScript runtime error '800a01a8'

Object required: 'Response.Write(...)'
0
 
pritamduttCommented:
Is it possible for your upload copy of calling Form as well for further investigation.
0
 
Stiebel EltronAuthor Commented:
Please see the attached codes.
Snippet ID=8225641 (signup form)
Snippet ID=8225642 (thank you page)
<%@LANGUAGE="VBSCRIPT"%>
<%
function TestCaptcha(byval valSession, byval valCaptcha)
	dim tmpSession
	valSession = Trim(valSession)
	valCaptcha = Trim(valCaptcha)
	if (valSession = vbNullString) or (valCaptcha = vbNullString) then
		TestCaptcha = false
	else
		tmpSession = valSession
		valSession = Trim(Session(valSession))
		Session(tmpSession) = vbNullString
		if valSession = vbNullString then
			TestCaptcha = false
		else
			valCaptcha = Replace(valCaptcha,"i","I")
			if StrComp(valSession,valCaptcha,1) = 0 then
				TestCaptcha = true
			else
				TestCaptcha = false
			end if
		end if		
	end if
end function
%>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">


<html xmlns="http://www.w3.org/1999/xhtml">
<head>


<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<meta http-equiv="Content-Type" content="text/html; charset=windows-874">

<meta name="GENERATOR" content="Mozilla/4.7 [en] (Win98; I) [Netscape]">

<title>..:: Stiebel ::..</title>
<style TYPE="text/css">
<!--
	a
 	{
  		color:#000080;
		font-family:verdana;
		text-decoration:none;
		font-size:12px
	}
	
	
	a:active
 	{
  		color:red;

	}

	a:hover
 	{
  		color:#FF0000;
		text-decoration:none;
		cursor:crosshair;
	}
		
	table
	{
		font-family:verdana;
	}
	
	.td
	{
		font-family:verdana;
		font-size:12px;
	}

	.b
	{
		font-color:#FF0000;
		font-family:verdana;
		font-size:12px;
	}		

-->
</STYLE>

<!--validation-->

<script language="JavaScript" type="text/javascript">
if(!String.prototype.trim) {
  String.prototype.trim = function() {
    return this.replace(/^\s+|\s+$/, '');
  };
}

function isEmail(string) {
  return (string.search(/^\w+((-\w+)|(\.\w+))*\@[A-Za-z0-9]+((\.|-)[A-Za-z0-9]+)*\.[A-Za-z0-9]+$/) != -1)
}
function checkform ( form ) {
  // ** START **
  if (form.username.value.trim() == "") {
    alert( "Please enter your Username." );
    form.username.focus();
    return false ;
  }
  if (form.password.value.trim() == "") {
    alert( "Please enter your Password." );
    form.password.focus();
    return false ;
  }
  if (form.repassword.value.trim() == "") {
    alert( "Please retype your Password." );
    form.repassword.focus();
    return false ;
  }
  if (form.repassword.value.trim() != form.password.value.trim()) {
    alert( "Password didn't match. Please retype." );
    form.repassword.focus();
    return false ;
  }
  if (form.fname.value.trim() == "") {
    alert( "Please enter your name." );
    form.fname.focus();
    return false ;
  }
  if (form.lname.value.trim() == "") {
    alert( "Please enter your surname." );
    form.lname.focus();
    return false ;
  }
  if (form.company.value.trim() == "") {
    alert( "Please enter your company name." );
    form.company.focus();
    return false ;
  }
  if (form.address.value.trim() == "") {
    alert( "Please enter your address." );
    form.address.focus();
    return false ;
  }
  if (form.email.value.trim() == "") {
    alert( "Please enter your email address." );
    form.email.focus();
    return false ;
  }
  
  if (!isEmail(form.email.value.trim())) {
    alert("Please enter correct Email address");
	  form.email.focus();
	  return false;
  }

  if (form.telephone.value.trim() == "") {
    alert( "Please enter your Telephone#." );
    form.telephone.focus();
    return false ;
  }
  if (form.mobile.value.trim() == "") {
    alert( "Please enter your Mobile#." );
    form.mobile.focus();
    return false ;
  }
  if (form.code.value.trim() == "") {
    alert( "Please retype the confirmation code." );
    form.code.focus();
    return false ;
  }
//  if (form.remobile.value.trim() != form.mobile.value.trim()) {
//    alert( "Mobile didn't match. Please retype." );
//    form.remobile.focus();
//    return false ;
//  }
  
    // ** END **
    return true ; // allow submission
}
</script>
<!-- captcha image-->
<script language="javascript">
function RefreshImage(valImageId) {
	var objImage = document.images[valImageId];
	if (objImage == undefined) {
		return;
	}
	var now = new Date();
	objImage.src = objImage.src.split('?')[0] + '?x=' + now.toUTCString();
}
</script>
<!--END-->

</head>

<body bgproperties="fixed" bgcolor="#000000" onLoad="javascript:startmenu()">

<table width="760" border="0" cellspacing="3" cellpadding="1">
  <tr>
    <td width="134"><img src="../IMG/logo_res.jpg" alt="STIEBEL ELTRON" /></td>
    <td width="620" valign="top" bgcolor="#666666"><object classid="clsid:D27CDB6E-AE6D-11cf-96B8-444553540000" codebase="http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=9,0,28,0" width="548" height="197">
      <param name="movie" value="homeintro20.swf" />
      <param name="quality" value="high" />
      <param name="wmode" value="opaque" />
      <embed src="homeintro20.swf" quality="high" wmode="opaque" pluginspage="http://www.adobe.com/shockwave/download/download.cgi?P1_Prod_Version=ShockwaveFlash" type="application/x-shockwave-flash" width="548" height="197"></embed>
    </object></td>
    <td width="134" bgcolor="#FFFFFF"><img src="../IMG/white_square.png" alt="ASIA" border="0" align="top"/></td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    <td align="right" bgcolor="#999999"><font face="Verdana, Geneva, sans-serif" color="#ffffff">
    <%
   '// If user is logged in show name and logout link
   If Session("loggedin") = true Then
      Response.Write "Welcome: " & Session("name")
      Response.Write "<br /><a href=""../asia/login2/logout.asp"">Logout</a>"
   Else
      Response.Write "<a href=""ste-iphoto-login.asp"" title=""CLICK TO LOGIN"">REGISTERED USERS</a>"
   End If
	%>&nbsp;
	</td>
    <td bgcolor="#666666" align="center">&nbsp;</td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    <td align="left" bordercolor="#999999" bgcolor="#000000"><img src="../IMG/member.jpg" width="548" /><br></td>
    <td bgcolor="#999999" valign="top" align="center" rowspan=13>&nbsp;
		
    </td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    <td>
    	<table width="100%" border="1" bordercolor="#ffffff" cellpadding="0" bgcolor="#999999">
    		<form name="form" id="form" method="post" action="signup_result.asp" onSubmit="return checkform(this)">

    		<tr>
        		<td class="td" width="23%" align="right">First Name:</td>
           		<td width="77%"><input id="FName" name="FName" type="text" size="50" maxlength="50" />&nbsp;<b><font class="b">*</font>
                </b>
                </td>
        	</tr>
    		<tr>
        		<td class="td" width="23%" align="right">Last Name:</td>
           		<td width="77%"><input id="LName" name="LName" type="text" size="50" maxlength="50" />&nbsp;<b><font class="b">*</font>
                </b>
                </td>
        	</tr>
           	<tr>
        		<td class="td" width="23%" align="right">Email:</td>
           		<td width="77%"><input id="Email" name="Email" type="text" size="50" maxlength="50" />&nbsp;<b><font class="b">*</font></b>	
            	</td>
        	</tr>

<!--            
	    	<tr>
    	    	<td class="td" width="20%" align="right">Username:</td>
	    	   	<td width="80%"><input id="username" name="username" type="text" size="25" maxlength="15" onkeyup="OnChangedUsername" />&nbsp;<b><font class="b">*</font></b>&nbsp;<input id="btnCheckAvailability" type="button" disabled="disabled" value="Check Availability" onclick="OnCheckAvailability();" />&nbsp;&nbsp;<div id="Available"></div>
                </td>
        	</tr>
-->            
    		<tr>
        		<td class="td" width="23%" align="right">Password:</td>
           		<td width="77%"><input id="Password" name="Password" type="password" size="25" maxlength="15" />&nbsp;<b><font class=
                "b">*</font></b>
                </td>
        	</tr>
    		<tr>
        		<td class="td" width="23%" align="right">Re-type Password:</td>
           		<td width="77%"><input id="RePassword" name="RePassword" type="password" size="25" maxlength="15" />&nbsp;<b><font class="b">*</font></b>
                </td>
        	</tr>
        	<tr>
        		<td class="td" width="23%" align="right">Country:</td>
           		<td width="77%"><input id="Country" name="Country" type="text" size="50" maxlength="50" />&nbsp;<b><font class="b">*</font></b>	
         	   </td>
        	</tr>
	        <tr>
    	    	<td class="td" width="23%" align="right">Telephone:</td>
        	   	<td width="77%"><input id="Telephone" name="Telephone" type="text" size="25" maxlength="15" />&nbsp;<b><font class="b">
                *</font></b>
                </td>
        	</tr>
        	<tr>
        		<td class="td" width="23%" align="right">Mobile:</td>
           		<td width="77%"><input id="Mobile" name="Mobile" type="text" size="25" maxlength="15" />&nbsp;<b><font class="b">*
                </font></b>
                </td>
        	</tr>
	    	<tr>
    	    	<td class="td" width="23%" align="right">Company:</td>
        	   	<td width="77%"><input id="Company" name="Company" type="text" size="50" maxlength="25" />&nbsp;<b><font class="b">*
                </font></b>
                </td>
        	</tr>
    		<tr valign="top">
        		<td class="td" width="23%" align="right">Address:</td>
           		<td width="77%"><textarea name="Address" id="Address" rows="4" cols="39"></textarea>&nbsp;<b><font class="b">*</font>
                </b>
                </td>
        	</tr>
	        <tr>
    	    	<td class="td" width="23%" align="right">Confirm code:&nbsp;
				</td> 
           		<td width="77%">
            		<img id="imgCaptcha" src="captcha.asp" /><br />
                    <a href="javascript:void(0)" onclick="RefreshImage('imgCaptcha')">Change Image</a><br />
		            <input id="code" name="code" type="text" size="25" maxlength="15" />&nbsp;<b><font class="b">*</font></b>
                </td>
        	</tr>
	        <tr>
        		<td class="td" width="23%" align="right">&nbsp;</td>
           		<td width="77%"><input name="Submit" type="submit" value="SUBMIT FORM" onclick="checkform();" />&nbsp;
            		<input name="Reset" type="reset" value="RESET FORM" onClick="return confirm('Are you sure you want to reset the 
                    form?')" />
                </td>
        	</tr>
			<%
			if not IsEmpty(Request.Form("Submit")) then
			Response.Write("<tr><td colspan=""2"" align=""center"">")
			if TestCaptcha("ASPCAPTCHA", Request.Form("captchacode")) then
			Response.Write("<b style=""color:#00CC33"">The code you enter verified.</b>")
			else
			Response.Write("<b style=""color:#FF0000"">You entered the wrong code.</b>")
			end if
			Response.Write("</td></tr>" & vbCrLf)
			end if
			%>


        </form>
        
        <!--javascript-->
        <script language="JavaScript" type="text/javascript">
		//If our user enters data in the username input, then we need to enable our button
		function OnChangedUsername()
		{
		if(document.form.username.value == "")
		{
		document.form.btnCheckAvailability.disabled = true;
		}
		else
		{
		document.form.btnCheckAvailability.disabled = false;
		}
		}
		function OnCheckAvailability()
		{
		if(window.XMLHttpRequest)
		{
		oRequest = new XMLHttpRequest();
		}
		else if(window.ActiveXObject)
		{
		oRequest = new ActiveXObject("Microsoft.XMLHTTP");
		}

		oRequest.open("POST", "AJAX.asp", true);
		oRequest.onreadystatechange = UpdateCheckAvailability;
		oRequest.setRequestHeader("Content-Type", "application/x-www-form-urlencoded");
		oRequest.send("strCmd=availability&strUsername=" + document.form.username.value);
		}
		function UpdateCheckAvailability()
		{
		if(oRequest.readyState == 4)
		{ 
		if(oRequest.status == 200)
		{
		document.getElementById("Available").innerHTML = oRequest.responseText;
		}
		else
		{
		document.getElementById("Available").innerHTML = "Asychronous Error";
		}
		}
		}
		</script>
        <!--END-->
    </table>
    </td>
  </tr>
   <tr>
    <td>&nbsp;</td>
    <td align="center" bgcolor="#ffffff">
    	<a href = "javascript:history.back()"><font size="0.5e">BACK</font></a>
    </td>
  </tr>
</table>

</body>
</html>

Open in new window

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<%Option Explicit%>
<%
dim myDb, seriesid, myConnection
Dim connectString, sqlContent, rsUsers

'myDb = "..\asia2\login\css-users.mdb" ' Should be full path 
'connectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & myDB
connectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.MapPath ("css_users.mdb") & ";" 

Set myConnection = Server.CreateObject("ADODB.Connection")
myConnection.Open connectString

sqlContent = "INSERT INTO Users (Name,Surname,Email,Password,RePassword,Country,Telephone,Mobile,Company,Address) VALUES ('"&Request.Form("fname")&"','"&Request.Form("lname")&"','"&Request.Form("Email")&"','"&Request.Form("Password")&"','"&Request.Form("RePassword")&"','"&Request.Form("Country")&"','"&Request.Form("Telephone")&"','"&Request.Form("Mobile")&"','"&Request.Form("Company")&"','"&Request.Form("Address")&"')"


Set rsUsers = Server.CreateObject("ADODB.Recordset") 
Set rsUsers = Response.Write(sqlContent)'myConnection.Execute(sqlContent)
%>
<h1 align="center">Thank You <% Response.Write(Request.Form("fname")) + Response.Write(Request.Form("lname")) %> </h1>

Open in new window

0
 
Stiebel EltronAuthor Commented:
Though I'm revising my thank you page, I'll give you points first so you won't wait for long...
Then hope to see your support again on my next upcoming thread!
:-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 10
  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now