Solved

How to revise the SQL statement from the attached script?

Posted on 2012-03-29
3
257 Views
Last Modified: 2012-03-29
<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<%
Set connectString = Server.CreateObject("ADODB.Connection")
connectString.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.MapPath ("..\asia2\login\css_users.mdb") & ";"
connectString.Open

ID = request.QueryString("ID")

Set sqlContent = CreateObject("ADODB.Command")
sqlContent.ActiveConnection=connectString
sqlContent.Prepared = true

sqlContent.commandtext = "SELECT Users.ID, Users.FName, Users.LName, Users.Email, Users.Password, Users.Country, Users.Telephone, Users.Mobile, Users.Company, Users.Address, Users.Status FROM Users INNER JOIN  WHERE Users.ID=?"


'"SELECT Users.ID, Users.FName, Users.LName, Users.Email, Users.Password, Users.Country, Users.Telephone, Users.Mobile, Users.Company, Users.Address, Users.Status FROM Users INNER JOIN series ON products.seriesid = series.seriesid WHERE products.seriesid=?"

sqlContent.Parameters.Append sqlContent.CreateParameter("@seriesid", 3, 1, , SeriesID)
Set rsProducts = sqlContent.Execute
if not rsProducts.eof then
series_name = rsProducts("series_name")
end if
%>

<!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>
-------------------
<table width="100%" border="1" bordercolor="#cccccc" cellpadding="0" bgcolor="#999999">
    		<!--<form name="form" id="form" method="post" action="signup_result2.asp" onSubmit="return checkform(this)">-->

    		<tr>
        		<td class="td" width="23%" align="right">First Name:</td>
           		<td width="77%"><b><font class="b"><%=rsUsers("FName")%></font></b>
                </td>
        	</tr>
    		<tr>
        		<td class="td" width="23%" align="right">Last Name:</td>
           		<td width="77%"><font class="b"><%=rsUsers("LName")%></font>
                </b>
                </td>
        	</tr>
           	<tr>
        		<td class="td" width="23%" align="right">Email:</td>
           		<td width="77%"><font class="b"><%=rsUsers("Email")%></font></b>
            	</td>
        	</tr>
			<tr>
        		<td class="td" width="23%" align="right">Password:</td>
           		<td width="77%"><font class="b"><%=rsUsers("Password")%></font></b>
                </td>
        	</tr>
        	<tr>
        		<td class="td" width="23%" align="right">Country:</td>
           		<td width="77%"><font class="b"><%=rsUsers("Country")%></font></b>	
         	   </td>
        	</tr>
	        <tr>
    	    	<td class="td" width="23%" align="right">Telephone:</td>
        	   	<td width="77%"><font class="b"><%=rsUsers("Telephone")%></font></b>
                </td>
        	</tr>
        	<tr>
        		<td class="td" width="23%" align="right">Mobile:</td>
           		<td width="77%"><font class="b"><%=rsUsers("Mobile")%></font></b>
                </td>
        	</tr>
	    	<tr>
    	    	<td class="td" width="23%" align="right">Company:</td>
        	   	<td width="77%"><font class="b"><%=rsUsers("Company")%></font></b>
                </td>
        	</tr>
    		<tr valign="top">
        		<td class="td" width="23%" align="right">Address:</td>
           		<td width="77%"><font class="b"><%=rsUsers("Address")%></font>
                </b>
                </td>
        	</tr>
    		<tr valign="top">
        		<td class="td" width="23%" align="right">Status:</td>
           		<td width="77%"><font class="b"><%=rsUsers("Status")%></font>
                </b>
                </td>
        	</tr>

	        <tr>
        		<td class="td" width="23%" align="right">Activate:</td>
           		<td width="77%">
                	<input name="activate" type="radio" value="Activate" checked />&nbsp;
                    <input name="notactivate" type="radio" value="Not Activate" />
                </td>
        	</tr>
    </table>

Open in new window


Dear EE experts,

We would like to ask for tech support on how to revise the attached code (=snippet 8250412)
We're stuck on the SQL statement, we would like to select ID, which is from the database (css-users.mdb). But before that, there is one page that list all users, then the first name would be link, link to the attached script (=snippet 8250412). We would like to show on the page of the attached script each data from each row...

Thank you & hope to hear soon...
css-users.mdb
0
Comment
Question by:Stiebel Eltron
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 29

Expert Comment

by:sammySeltzer
ID: 37781541
Please explain a bit more clearer what you are trying to do.
0
 
LVL 29

Accepted Solution

by:
sammySeltzer earned 500 total points
ID: 37781915
If this is what you want, then here they are.

sumamry.asp:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<%
Set connectString = Server.CreateObject("ADODB.Connection")
connectString.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.MapPath ("css-users.mdb") & ";"
connectString.Open

ID = request.QueryString("ID")

Set sqlContent = CreateObject("ADODB.Command")
sqlContent.ActiveConnection=connectString
sqlContent.Prepared = true

sqlContent.commandtext = "SELECT Users.ID, Users.FName, Users.LName, Users.Email, Users.Password, Users.Country, Users.Telephone, Users.Mobile, Users.Company, Users.Address, Users.Status FROM Users "
sqlContent.Parameters.Append sqlContent.CreateParameter("@seriesid", 3, 1, , ID)
Set rsUsers=sqlContent.Execute(SQL)
If rsUsers.EOF Then
%>
	<span  class=middletitle style="font-size: small; font-weight: bold;">
	&nbsp;View - Court Hearing Results: NO RECORDS FOUND
	</span>
<%
Else
%>


<table width="100%" border=1 bordercolor=gainsboro cellspacing=0;">
<tr>
</TR>
<TR style="background-color:cornsilk;font-weight:bold">
    <TD>First Name</TD>
	<TD>Last Name</TD>
	<TD>Email</TD>
	<TD>Password</TD>
	<TD>Country</TD>
	<TD>Telephone</TD>
	<TD>Mobile</TD>
	<TD>Company</TD>
	<TD>Address</TD>
	<TD>Status</TD>
	<TD>Activate</TD>

</TR>
<%
	Do Until rsUsers.EOF
		id    =   rsUsers("id")
		fname    =   rsUsers("fname")
		lname    =   rsUsers("lname")
		email		=   rsUsers("email")
		password	=   rsUsers("password")
		country	=   rsUsers("country")
		telephone	=   rsUsers("telephone")
		mobile	=   rsUsers("mobile")
		company	=   rsUsers("company")
		address	=   rsUsers("address")
		status		=   rsUsers("status")
%>


   		<tr>

           		<td><b><font class="b"><a href="details.asp?id=<%=rsUsers("id")%>"><%=rsUsers("fname")%></a></font></b>
                </td>

           		<td><font class="b"><%=LName%></font>
                </b>
                </td>

           		<td><font class="b"><%=Email%></font></b>
            	</td>

           		<td><font class="b"><%=Password%></font></b>
                </td>

           		<td><font class="b"><%=Country%></font></b>
         	   </td>

        	   	<td><font class="b"><%=Telephone%></font></b>
                </td>

           		<td><font class="b"><%=Mobile%></font></b>
                </td>


        	   	<td><font class="b"><%=Company%></font></b>
                </td>

           		<td><font class="b"><%=Address%></font>
                </b>
                </td>

           		<td><font class="b"><%=Status%></font>
                </b>
                </td>
           		<td>
                	<input name="activate" type="radio" value="Activate" checked />&nbsp;
                    <input name="activate" type="radio" value="Not Activate" />
                </td>
        	  </tr>
            <%
		   rsUsers.MoveNext
	     loop
        end if
       rsUsers.Close
      %>
     </table>

Open in new window


Details.asp:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!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>

<%

ID = request.QueryString("ID")

set Conn = server.CreateObject("ADODB.Connection")
set objRS =server.CreateObject("ADODB.Recordset")
Conn.ConnectionString= "Provider=microsoft.jet.oledb.4.0;Data source=" & server.mappath("css-users.mdb")
objRs.CursorLocation = 3
Conn.Open

sql = "SELECT Users.FName, Users.LName, Users.Email, Users.Password, Users.Country, Users.Telephone, Users.Mobile, Users.Company, Users.Address, Users.Status FROM Users where id =" & id & " "
'response.write sql
'response.end
Set rsUsers = Conn.Execute(sql)
If not rsUsers.eof then
 fname = rsUsers("FName")
 LName = rsUsers("LName")
 Email = rsUsers("Email")
 Password = rsUsers("Password")
 Country = rsUsers("Country")
 Telephone = rsUsers("Telephone")
 Mobile = rsUsers("Mobile")
 Company = rsUsers("Company")
 Address = rsUsers("Address")
 Status = rsUsers("Status")
end if
%>
<br>
<table width="100%" border="1" bordercolor="#cccccc" cellpadding="0" bgcolor="#999999">

    		<tr>
        		<td class="td" width="23%" align="right">First Name:</td>
           		<td width="77%"><b><font class="b"><%= FName %></font></b>
                </td>
        	</tr>
    		<tr>
        		<td class="td" width="23%" align="right">Last Name:</td>
           		<td width="77%"><font class="b"><%=LName%></font>
                </b>
                </td>
        	</tr>
           	<tr>
        		<td class="td" width="23%" align="right">Email:</td>
           		<td width="77%"><font class="b"><%= Email %></font></b>
            	</td>
        	</tr>
			<tr>
        		<td class="td" width="23%" align="right">Password:</td>
           		<td width="77%"><font class="b"><%=Password%></font></b>
                </td>
        	</tr>
        	<tr>
        		<td class="td" width="23%" align="right">Country:</td>
           		<td width="77%"><font class="b"><%=Country%></font></b>
         	   </td>
        	</tr>
	        <tr>
    	    	<td class="td" width="23%" align="right">Telephone:</td>
        	   	<td width="77%"><font class="b"><%=Telephone%></font></b>
                </td>
        	</tr>
        	<tr>
        		<td class="td" width="23%" align="right">Mobile:</td>
           		<td width="77%"><font class="b"><%=Mobile%></font></b>
                </td>
        	</tr>
	    	<tr>
    	    	<td class="td" width="23%" align="right">Company:</td>
        	   	<td width="77%"><font class="b"><%=Company%></font></b>
                </td>
        	</tr>
    		<tr valign="top">
        		<td class="td" width="23%" align="right">Address:</td>
           		<td width="77%"><font class="b"><%=Address%></font>
                </b>
                </td>
        	</tr>
    		<tr valign="top">
        		<td class="td" width="23%" align="right">Status:</td>
           		<td width="77%"><font class="b"><%=Status%></font>
                </b>
                </td>
        	</tr>

	        <tr>
        		<td class="td" width="23%" align="right">Activate:</td>
           		<td width="77%">
                	<input name="activate" type="radio" value="Activate" checked />&nbsp;
                    <input name="notactivate" type="radio" value="Not Activate" />
                </td>
        	</tr>
    </table>

Open in new window


If this is not what you want., then explain yourself better.
0
 

Author Closing Comment

by:Stiebel Eltron
ID: 37785456
What we need is found in Details.asp
Thank you very much for great support!
0

Featured Post

Why Off-Site Backups Are The Only Way To Go

You are probably backing up your data—but how and where? Ransomware is on the rise and there are variants that specifically target backups. Read on to discover why off-site is the way to go.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
form isn't working is it missing a document 21 64
Div not showing up 6 33
center text in div with CSS3 2 31
htaccess file to redirect site to www. 3 16
An enjoyable and seamless user experience can go a long way on an eCommerce site. While a cohesive layout and engaging copy play roles in creating a positive user experience, some sites neglect aspects that seem marginal but in actuality prove very …
FAQ pages provide a simple way for you to supply and for customers to find answers to the most common questions about your company. Here are six reasons why your company website should have a FAQ page
The viewer will the learn the benefit of plain text editors and code an HTML5 based template for use in further tutorials.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question