Solved

How to revise the SQL statement from the attached script?

Posted on 2012-03-29
3
247 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
  • 2
3 Comments
 
LVL 28

Expert Comment

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

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Boost your ability to deliver ambitious and competitive web apps by choosing the right JavaScript framework to best suit your project’s needs.
This article explains how to prepare an HTML email signature template file containing dynamic placeholders for users' Azure AD data. Furthermore, it explains how to use this file to remotely set up a department-wide email signature policy in Office …
In this tutorial viewers will learn how to embed Flash content in a webpage using HTML5. Ensure your DOCTYPE declaration is set to HTML5: "<!DOCTYPE html>": Use the <object> tag to embed Flash content.: To specify that the object is Flash content, d…
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now