Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    New Coder
    Join Date
    Aug 2004
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Connecting to SQL DB

    I have a form that submits the information to a SQL database. The form has a two radiobuttons name="Cheese" with value="yes" OR value="no" and then collects contact info.

    The form submits to the SQL DB and I want to create a display page that will show the form and sort the form based on the name="Cheese" yes or no.

    All yes values will display form info under the yes column.

    All no values will display form info under the no column.

    So there are two columns displaying the information based the results of the SQL row name=Cheese. How do I connect to the DB and "sort" the info based on this value and dynamically show this information on my site?

    Here is my ASP connect to DB code, I can't figure out how to display results based on SQL info.


    Code:
    '========This information==============
    
    <% 
    'connect to SQL Server
    Dim oConn, oRs
    Dim qry, connectstr, fieldname, tablename
    Dim db_name, db_username, db_userpassword
    Dim db_server
    db_server = "somesite.net"
    db_name = "DB_CHEESE"
    db_username = "username"
    db_userpassword = "xxxxxxx"
    fieldname = "cheese,fname,lname,email,phone"
    tablename = "tablename"
    connectstr = "Driver={SQL Server};SERVER=" & db_server & ";DATABASE=" & db_name & ";UID=" & db_username & ";PWD=" & db_userpassword
    Set oConn = Server.CreateObject("ADODB.Connection")
    oConn.Open connectstr
    
    qry = "SELECT * FROM " & tablename
    Set oRS = oConn.Execute(qry)
    
    %>
    
    '===========END THIS INFORMATION============
    
    
    '====HERE IS THE DISPLAYED SQL INFO THAT I WANT DISPLAYED BY VALUE CHEESE========
    
    '===Value CHEESE=yes ======
    <table width = 600 border="1">
    	<tr>
               <td width="300"><strong>Cheese:</strong></td>
    	   <td width="300"><strong>First Name:</strong></td>
     	   <td width="300"><strong>Last Name:</strong></td>
     	   <td width="300"><strong>E-Mail:</strong></td>
     	   <td width="300"><strong>Phone:</strong></td>
     	   <td width="300"><strong>Source:</strong></td>
    	</tr>
    	<%
    		Set oRS = oConn.Execute(qry)
    	if not oRS.EOF then
    		while not oRS.EOF
    			%>
    			<tr>
                           <td width="150"><%= oRs.Fields("cheese") %></td>
    			<td width="150"><%= oRs.Fields("fname") %></td>
    			<td width="150"><%= oRs.Fields("lname") %></td>
    			<td width="150"><%= oRs.Fields("email") %></td>
    			<td width="150"><%= oRs.Fields("phone") %></td>
    			<td width="200"><%= oRs.Fields("txtSource") %></td>
    			</tr>
    			<%
    			oRS.movenext
    			wend
    			oRS.close
    	end if
    
    	Set oRs = nothing
    	Set oConn = nothing
    	%>
    
    
    '===Value CHEESE=no ======
    <table width = 600 border="1">
    	<tr>
               <td width="300"><strong>Cheese:</strong></td>
    	   <td width="300"><strong>First Name:</strong></td>
     	   <td width="300"><strong>Last Name:</strong></td>
     	   <td width="300"><strong>E-Mail:</strong></td>
     	   <td width="300"><strong>Phone:</strong></td>
     	   <td width="300"><strong>Source:</strong></td>
    	</tr>
    	<%
    		Set oRS = oConn.Execute(qry)
    	if not oRS.EOF then
    		while not oRS.EOF
    			%>
    			<tr>
                           <td width="150"><%= oRs.Fields("cheese") %></td>
    			<td width="150"><%= oRs.Fields("fname") %></td>
    			<td width="150"><%= oRs.Fields("lname") %></td>
    			<td width="150"><%= oRs.Fields("email") %></td>
    			<td width="150"><%= oRs.Fields("phone") %></td>
    			<td width="200"><%= oRs.Fields("txtSource") %></td>
    			</tr>
    			<%
    			oRS.movenext
    			wend
    			oRS.close
    	end if
    
    	Set oRs = nothing
    	Set oConn = nothing
    	%>
    Thanks is advance!!!

  • #2
    Senior Coder angst's Avatar
    Join Date
    Apr 2004
    Location
    Toronto, Ontario
    Posts
    2,114
    Thanks
    15
    Thanked 122 Times in 122 Posts
    use "ORDER BY"
    as below:
    <%
    'connect to SQL Server
    Dim oConn, oRs
    Dim qry, connectstr, fieldname, tablename
    Dim db_name, db_username, db_userpassword
    Dim db_server
    db_server = "somesite.net"
    db_name = "DB_CHEESE"
    db_username = "username"
    db_userpassword = "xxxxxxx"
    fieldname = "cheese,fname,lname,email,phone"
    tablename = "tablename"
    connectstr = "Driver={SQL Server};SERVER=" & db_server & ";DATABASE=" & db_name & ";UID=" & db_username & ";PWD=" & db_userpassword
    Set oConn = Server.CreateObject("ADODB.Connection")
    oConn.Open connectstr

    qry = "SELECT * FROM " & tablename & " ORDER BY cheese ASC"
    Set oRS = oConn.Execute(qry)

    %>

  • #3
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,072
    Thanks
    4
    Thanked 8 Times in 8 Posts
    Are There 2 columns with the info put into the tables like so?

    Code:
    <table>
       <tr>
           <td>table for yes</td>
           <td> table for no</td>
       </tr>
    </table>


    Either way the extra call to the database is not needed

    step 1) Use ORDER BY Cheese in your SQL statement

    Step 2) remove the extra call to database

    step 3) add if statement check for yes or no to populate the appropriate table
    Last edited by miranda; 02-19-2007 at 06:51 PM.

  • #4
    New Coder
    Join Date
    Aug 2004
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts
    thanks for the info, I'll give "ORDER BY cheese" a try.

    Does this allow me to create two tables, one that includes the YES values and another that includes the NO values? I want to be able to create two tables titled YES and titled NO so the user can scroll down and find the table associated with the values.

    thanks for the help. As you can tell I have limited ASP knowledge.

  • #5
    Senior Coder angst's Avatar
    Join Date
    Apr 2004
    Location
    Toronto, Ontario
    Posts
    2,114
    Thanks
    15
    Thanked 122 Times in 122 Posts
    with the order by notice: "ASC" ( a-z ) , or u could use "DESC" ( z-a )

  • #6
    New Coder
    Join Date
    Aug 2004
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts
    miranda, what would the if statement be to set up the yes or no tables. nto sure how to do this.

  • #7
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,072
    Thanks
    4
    Thanked 8 Times in 8 Posts
    Depending on how you have your database set up.
    if it is set up where cheese is a bit field (1 or 0) {equivalant to Access yes/no}
    If oRs.Fields("cheese") = 1 Then 'display the yes records

    Else 'display the no records

    End If

    or a varchar storing "yes" and "no" respectively
    If oRs.Fields("cheese") = "yes" Then 'display the yes records

    Else 'display the no records

    End If


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •