PDA

View Full Version : Connecting to SQL DB



dude9er
02-19-2007, 06:29 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!!!

matthew_qwconsulting
10-10-2007, 07:11 PM
I had this same problem and finally found the fix. So, in case you are still looking for it what you have to do is add .Value after your Fields deal. So, you would have something like:
<tr>
<td width="150"><%= oRs.Fields("cheese").Value %></td>
<td width="150"><%= oRs.Fields("fname").Value %></td>
<td width="150"><%= oRs.Fields("lname").Value %></td>
<td width="150"><%= oRs.Fields("email").Value %></td>
<td width="150"><%= oRs.Fields("phone").Value %></td>
<td width="200"><%= oRs.Fields("txtSource").Value %></td>
</tr>

and then that should work. Or at least it worked for me. Hopefully that helps somebody out there Google-ing this very topic! :)