PDA

View Full Version : display the records...

madg3rr
06-11-2003, 01:28 AM
I cant seem to display the records frm the database. how do i do dis? how do i define the recordset? i tried every method but it will end up an error... juz dunno wad else to do.

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "CPAR"
strSQL="Select * FROM Search"
set rsSearch=Conn.Execute(strSQL)

dis is wad i haf to open up the particular sql but displaying them is a tough nut. so wad shud i add & how? i understand muz use the movefirst & movenext. But i m a beginner & all dis has been givin me a headache. so pls can sumone help me?
jsawkang
06-11-2003, 01:43 AM
Hi..

here how u can display the record from database.

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "CPAR"
strSQL="Select * FROM Search"
set rsSearch=Conn.Execute(strSQL)

<table>
<tr>
<td>column 1</td>
<td>column 2</td>
</tr>
<%While not rsSearch.EOF%>
<tr>
<td><%=rsSearch("column1")%></td>
<td><%=rsSearch("column2")%></td>
</tr>
<%rs.movenext
wend%>
</table>

* Note: replace the "column1, column2" with the fields in ur search table.
madg3rr
06-11-2003, 02:49 AM
how abt dis? i attached my file in dis post. izit possible if u could take a look n help me see wad is wrong? i've added the "while" but still i cant display.... it gives me dis error...

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause.

/cpas/Hazlin/SEARCH.asp, line 21

thanx ur help is greatly appreciated...
jsawkang
06-11-2003, 03:40 AM
ur section of code not same as given by angelika!!!

this is angelika's code!
follow her code as u said solved already in the previous topic.

<%
Dim strSQL,Category,Division

Category=Request.Form("Category")
Division=Request.Form("Division")

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "CPAR"

strSQL="Select * FROM Search "

If not Category = "All Categories" Then
strSQL = strSQL + "WHERE Category = '"&Category&"' "
End If

If Not Division = "All Divisions" Then
strSQL = strSQL + "And Division = '"&Division&"' "
End If

set rsSearch=Conn.Execute(strSQL)
%>
jsawkang
06-11-2003, 03:42 AM
here a good tip!

just copy the code to notepad then copy from notepad and paste the code.
madg3rr
06-11-2003, 03:46 AM
oh kk now i noe but den y cant i still display everything? i feel like givin up oredi... y cant the system read in & display wad i wan? wad do i hafta do?
jsawkang
06-11-2003, 07:50 PM
hi...

why don't u separate that page into 2 page?

page1
<HTML>
<HEAD>
<TITLE>Database Search Engine</TITLE>
</head>
<body background="Images/clouds.jpg">
<h1><font size="+3"><i><b><u><font color="#0000CC">CPAR Search Engine </font></u></b></i></font>
</h1>
<form name="form1" method="post" action="search.asp">
<p align="center">&nbsp;</p>
<p align="center"><font color="#3333FF"><b>Searching For</b></font>
<select name="Category">
<option>All Categories
</option>
<option>Internal
Quality Audit NC</option>
<option>Customer
Complaints</option>
<option>Supplier/Subcontract
Followup</option>
<option>Trend
Analysis</option>
<option>Others</option>
</select>
<b><font color="#3333FF">In</font></b>
<select name="Department">
<option>All Divisions</option>
<option>BCTD</option>
<option>CTD</option>
<option>CSD</option>
<option>MTD</option>
</select>
<input type="submit" name="Search" value="Search">
</body>
</HTML>


page2/search.asp

<html>
<body>
<%
Dim strSQL,Category,Division

Category=Request.Form("Category")
Division=Request.Form("Division")

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "CPAR"

strSQL="Select * FROM Search"

If not Category = "All Categories" Then
strSQL = strSQL + "WHERE Category = Category "
End If

If Not Division = "All Divisions" Then
strSQL = strSQL + "And Division = Division "
End If

set rsSearch=Conn.Execute(strSQL)

%>
<table width="100%" border="0">
<tr bgcolor="#800000">
<td width="10%">
<div align="center"><b><font color="#FFFFFF">CPAR No</font></b></div>
</td>
<td width="9%">
<div align="center"><b><font color="#FFFFFF">Division</font></b></div>
</td>
<td width="27%">
<div align="center"><b><font color="#FFFFFF">Department</font></b></div>
</td>
<td width="26%">
<div align="center"><b><font color="#FFFFFF">Category</font></b></div>
</td>
<td width="14%">
<div align="center"><b><font color="#FFFFFF">Issuing Officer</font></b></div>
</td>
<td width="14%">
<div align="center"><b><font color="#FFFFFF">Date Of Issue</font></b></div>
</td>
</tr>
<%While not rsSearch.EOF%>
<tr>
<td width="10%">_<%=rsSearch("CPAR_No")%> </td>
<td width="9%">_<%=rsSearch("Division")%> </td>
<td width="27%">_<%=rsSearch("Department")%> </td>
<td width="26%">_<%=rsSearch("Category")%> </td>
<td width="14%">_<%=rsSearch("Issuing_Officer")%></td>
<td width="14%">_<%=rsSearch("DateOfIssue")%></td>
</tr>
<%rsSearch.movenext
wend%>
</table>
</body>
</html>
madg3rr
06-11-2003, 08:57 PM
i did as wad u told me to separate the codes. It can work but why is tad wen i choose lets say "div1" & "category1" it does nt display wad i wan instead it shows me no records can be found wen der is such a record in my database.
wad is wrong wif the if statement? i used Angelika's code but it nvr display anything so i modified it to like dis...

If not Category = "All Categories" Then
strSQL = strSQL + "WHERE Category = '"&Category&"' "
If Not Division = "All Divisions" Then
strSQL = strSQL + "And Division = '"&Division&"' "
End If
End If

dis code show me all the records i haf in my database wen i choose all categories n divisions but it doesnt show the specific record wen i specify the criterias. wads wrong wif it?
jsawkang
06-11-2003, 09:38 PM
Hi...

i've tested angelika's code. the code work fine for me.
so, this come to the conclusion the maybe ur form field name not same with the request.form in ur search.asp page.

i've out that ur second drop down menu is called "department". but in ur request.form in asp page. u put request.form("Division")

check it out
madg3rr
06-11-2003, 10:01 PM
thanx i've done it... thanx realli realli alot... thanx fer ur patience & trying solve my problem... real thanx... didnt noe such a small diff can gif me headache fer 2days... thanx man...