Solved

How to revise the SQL statement from the attached script?

Posted on 2012-03-29
3
1 solution
258 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 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Watch The Video
Question has a verified solution.

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

HipChat Is Much More Than Chat
Article by: xMatters
When the s#!t hits the fan, you don’t have time to look up who’s on call, draft emails, call collaborators, or send text messages. An instant chat window is definitely the way to go, especially one like HipChat. HipChat is a true business app. An…
Website Security Monitoring by Monitis
Article by: Anush
When it comes to security, close monitoring is a must. According to WhiteHat Security annual report, a substantial number of all web applications are vulnerable always. Monitis offers a new product - fully-featured Website security monitoring and pr…
PHP, jQuery, and Form Actions
Video by: Marco
The viewer will learn how to dynamically set the form action using jQuery.
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.
Advertise Here

617 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