asked on 10/7/2011

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

ASP Web Development Microsoft Access

Last Comment

Stiebel Eltron

10/10/2011

pritamdutt

10/7/2011

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.

Stiebel Eltron

10/7/2011

ASKER

@pritamdutt: The database location is same with the registration form & result form. That's why it's just the filename...

Scott McDaniel (EE MVE )

10/7/2011

I believe that your code should look like:

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

pritamdutt

10/7/2011

I agree with @LSMConsulting.. I was just giving an reference on how to display values along with the message.

Stiebel Eltron

10/7/2011

ASKER

@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!

Scott McDaniel (EE MVE )

10/7/2011

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

Stiebel Eltron

10/7/2011

ASKER

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."

pritamdutt

10/7/2011

@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.

Stiebel Eltron

10/7/2011

ASKER

@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...

ASKER CERTIFIED SOLUTION

Scott McDaniel (EE MVE )

10/7/2011

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Stiebel Eltron

10/7/2011

ASKER

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

SOLUTION

pritamdutt

10/7/2011

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Stiebel Eltron

10/7/2011

ASKER

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

Scott McDaniel (EE MVE )

10/7/2011

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.

Stiebel Eltron

10/8/2011

ASKER

I'll get back to you on Monday, asap....

Stiebel Eltron

10/10/2011

ASKER

@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(...)'

pritamdutt

10/10/2011

Is it possible for your upload copy of calling Form as well for further investigation.

Stiebel Eltron

10/10/2011

ASKER

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

Stiebel Eltron

10/10/2011

ASKER

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!
:-)