Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to revise the SQL statement from the attached script?

Posted on 2012-03-29
3
1 solution
Medium Priority
?
260 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
Join The Community
  • 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 2000 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Access It Now
Question has a verified solution.

If you are experiencing a similar issue, please ask a related question
Most Important Metrics for Your Website Performance
Article by: Anush
This article was originally published on Monitis Blog, you can check it here . Today it’s fairly well known that high-performing websites and applications bring in more visitors, higher SEO, and ultimately more sales. By the same token, downtime…
4 Certifications Experts Exchange Can Help You Earn
Article by: Gene
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using PHP to Count Item Occurrences in Arrays
Video by: Marco
The viewer will learn how to count occurrences of each item in an array.
Introduction to Accessibility (Part 1)
Video by: Jason
Any person in technology especially those working for big companies should at least know about the basics of web accessibility. Believe it or not there are even laws in place that require businesses to provide such means for the disabled and aging p…
Advertise Here

670 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

Advertise Here