...

View Full Version : Connecting to SQL DB



dude9er
02-19-2007, 06:30 PM
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.




'========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!!!

angst
02-19-2007, 06:45 PM
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)

%>

miranda
02-19-2007, 06:45 PM
Are There 2 columns with the info put into the tables like so?



<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

dude9er
02-19-2007, 06:50 PM
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.

angst
02-19-2007, 06:51 PM
with the order by notice: "ASC" ( a-z ) , or u could use "DESC" ( z-a )

dude9er
02-19-2007, 07:06 PM
miranda, what would the if statement be to set up the yes or no tables. nto sure how to do this.

miranda
02-19-2007, 07:41 PM
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



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum