Matt_Kennedy
asked on
HTML table linked to Access Base
I am trying to build an html page that displays fields from an ACCESS 2003 database in a table. For some reason the fields do not come up when the html page is executed. The SQL query looks at the database and is suppose to return all rows that begin with the letter R, Instead it returns a blank table. Can someone please look at the code and see where the mistakes lay? The code is below:
<html>
<head>
<title>Brand Select</title>
</head>
<body>
<%
' Database connection - remember to specify the path to your database
'DIM Conn
Set Conn = Server.CreateObject("ADODB .Connectio n")
DIM ConnectString
Set ConnectString="Provider=Mi crosoft.Je t.OLEDB.4. 0;" & "Data Source=\\anderson\company\ BrandSourc e\Brands.m db"
Conn.Open ConnectString
Set RSTitleList = Server.CreateObject("ADODB .Recordset ")
' SQL Query
'DIM strSQL
SET strSQL = "SELECT * FROM Brands WHERE (((Brands.BrandName) Like 'R*'))"
Set RSTitleList = Conn.Execute(strSQL)
' Execute the query (the recordset rs contains the result)
%>
<TABLE align=center COLSPAN=8 CELLPADDING=5 BORDER=0 WIDTH=200>
<!-- Begin our column header row -->
<TR>
<TD VALIGN=TOP BGCOLOR="#800000">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=2> Brand Name</FONT>
</TD>
<TD ALIGN=CENTER BGCOLOR="#800000">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=2> SubBrand</FONT>
</TD>
<TD ALIGN=CENTER BGCOLOR="#800000">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=2> Manufacture</FONT>
</TD>
<TD ALIGN=CENTER BGCOLOR="#800000">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=2> Handle</FONT>
</TD>
<TD ALIGN=CENTER BGCOLOR="#800000">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=2> Website</FONT>
</TD>
</TR>
<!-- Ok, let's get our data now -->
<% do while not RStitleList.EOF %>
<TR>
<TD BGcolor ="f7efde" align=center><font style ="arial narrow" size=2>
<%=RStitleList("BrandName" )%></font> </TD>
<TD BGcolor ="f7efde" align=center><font style ="arial narrow" size=2>
<%=RSTitleList("SubBrand") %> </font> </TD>
<TD BGcolor ="f7efde" align=center><font style ="arial narrow" size=2>
<%=RSTitleList("Manufactur e")%> </font> </TD>
<TD BGcolor ="f7efde" align=center><font style ="arial narrow" size=2>
<%=RSTitleList("Handle")%> </font> </TD>
<TD BGcolor ="f7efde" align=center><font style ="arial narrow" size=2>
<%=RSTitleList("Website") %> </font> </TD>
</TR>
<% RSTitleList.MoveNext%>
<%loop %>
<% Conn.Close
Set Conn = Nothing%>
</body>
</html>
thank you in advance!
<html>
<head>
<title>Brand Select</title>
</head>
<body>
<%
' Database connection - remember to specify the path to your database
'DIM Conn
Set Conn = Server.CreateObject("ADODB
DIM ConnectString
Set ConnectString="Provider=Mi
Conn.Open ConnectString
Set RSTitleList = Server.CreateObject("ADODB
' SQL Query
'DIM strSQL
SET strSQL = "SELECT * FROM Brands WHERE (((Brands.BrandName) Like 'R*'))"
Set RSTitleList = Conn.Execute(strSQL)
' Execute the query (the recordset rs contains the result)
%>
<TABLE align=center COLSPAN=8 CELLPADDING=5 BORDER=0 WIDTH=200>
<!-- Begin our column header row -->
<TR>
<TD VALIGN=TOP BGCOLOR="#800000">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=2> Brand Name</FONT>
</TD>
<TD ALIGN=CENTER BGCOLOR="#800000">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=2> SubBrand</FONT>
</TD>
<TD ALIGN=CENTER BGCOLOR="#800000">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=2> Manufacture</FONT>
</TD>
<TD ALIGN=CENTER BGCOLOR="#800000">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=2> Handle</FONT>
</TD>
<TD ALIGN=CENTER BGCOLOR="#800000">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=2> Website</FONT>
</TD>
</TR>
<!-- Ok, let's get our data now -->
<% do while not RStitleList.EOF %>
<TR>
<TD BGcolor ="f7efde" align=center><font style ="arial narrow" size=2>
<%=RStitleList("BrandName"
<TD BGcolor ="f7efde" align=center><font style ="arial narrow" size=2>
<%=RSTitleList("SubBrand")
<TD BGcolor ="f7efde" align=center><font style ="arial narrow" size=2>
<%=RSTitleList("Manufactur
<TD BGcolor ="f7efde" align=center><font style ="arial narrow" size=2>
<%=RSTitleList("Handle")%>
<TD BGcolor ="f7efde" align=center><font style ="arial narrow" size=2>
<%=RSTitleList("Website") %> </font> </TD>
</TR>
<% RSTitleList.MoveNext%>
<%loop %>
<% Conn.Close
Set Conn = Nothing%>
</body>
</html>
thank you in advance!
ASKER
Thank you for the response, however, the question was, where is the problem with the code I have presented to you?
thank you
thank you
OK,
Fair enough, ...
;-)
I just thought that the greater issue was that you simply needed to generate HTML for a table/query...
I am sure an HTML Expert will be along shortly and see the issue more clearly...
;-)
Jeff
Fair enough, ...
;-)
I just thought that the greater issue was that you simply needed to generate HTML for a table/query...
I am sure an HTML Expert will be along shortly and see the issue more clearly...
;-)
Jeff
First of all, make sure that you have some records in the Access table that begin with R , otherwise it is normal you get emplty HTML table.
Second, the SQL query phrase should be a little cleaner:
Finally, even your SQL is absolutely correct, I would use:
If your table contains a huge number of records, check that the BrandName column is indexed.
My solution is (read my important comments that begin with ============):
Second, the SQL query phrase should be a little cleaner:
SELECT * FROM Brands WHERE BrandName Like 'R*'
Finally, even your SQL is absolutely correct, I would use:
SELECT * FROM Brands WHERE UCase(Left(BrandName,1))='R'
If your table contains a huge number of records, check that the BrandName column is indexed.
My solution is (read my important comments that begin with ============):
<html>
<head>
<title>Brand Select</title>
</head>
<body>
<%
' Database connection - remember to specify the path to your database
'DIM Conn
Set Conn = Server.CreateObject("ADODB.Connection")
DIM ConnectString
'Set ConnectString="Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=\\anderson\company\BrandSource\Brands.mdb"
ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\anderson\company\BrandSource\Brands.mdb"
Conn.Open ConnectString
Set RSTitleList = Server.CreateObject("ADODB.Recordset")
' SQL Query
'DIM strSQL
'strSQL = "SELECT * FROM Brands WHERE BrandName Like 'R*'"
' =============================== I CHOOSED TO USE A DIFFERENT SQL
strSQL = "SELECT * FROM Brands WHERE UCase(Left(BrandName,1)) = 'R'"
' =============================== PAY ATTENTION TO THE FOLLOWING LINE
RSTitleList.Open strSQL, Conn
' Execute the query (the recordset rs contains the result)
%>
<TABLE align=center CELLPADDING=5 BORDER=0 WIDTH=200>
<!-- Begin our column header row -->
<TR>
<TD VALIGN=TOP BGCOLOR="#800000">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=2> Brand Name</FONT>
</TD>
<TD ALIGN=CENTER BGCOLOR="#800000">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=2> SubBrand</FONT>
</TD>
<TD ALIGN=CENTER BGCOLOR="#800000">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=2> Manufacture</FONT>
</TD>
<TD ALIGN=CENTER BGCOLOR="#800000">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=2> Handle</FONT>
</TD>
<TD ALIGN=CENTER BGCOLOR="#800000">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=2> Website</FONT>
</TD>
</TR>
<!-- Ok, let's get our data now -->
<%
' =============================== FIRST CHECK THAT WE REALLY HAVE SOME RECORDS
if RSTitleList.BOF And RSTitleList.EOF Then
%>
<TR>
<TD colspan="5" align="center">No records found!</TD>
</TR>
<%
Else
' =============================== NOW WE CAN CYCLE THROUGH ALL RESULT RECORDS
' =============================== I USED WHILE - WEND CYCLE
while not RStitleList.EOF
%>
<TR>
<TD BGcolor ="f7efde" align=center><font style ="arial narrow" size=2>
<%=RStitleList("BrandName")%></font> </TD>
<TD BGcolor ="f7efde" align=center><font style ="arial narrow" size=2>
<%=RSTitleList("SubBrand")%> </font> </TD>
<TD BGcolor ="f7efde" align=center><font style ="arial narrow" size=2>
<%=RSTitleList("Manufacture")%> </font> </TD>
<TD BGcolor ="f7efde" align=center><font style ="arial narrow" size=2>
<%=RSTitleList("Handle")%> </font> </TD>
<TD BGcolor ="f7efde" align=center><font style ="arial narrow" size=2>
<%=RSTitleList("Website") %> </font> </TD>
</TR>
<%
RSTitleList.MoveNext
Wend
End If
' =============================== NOW I CLOSE THE RECORDSET AND DISPOSE IT
RStitleList.Close
Set RStitleList = Nothing
Conn.Close
Set Conn = Nothing
%>
</body>
</html>
ASKER
Aurelianco
Thank you for the reply. With your code, however, I receive No Records found, even though there are two records in the table that begin with R. Are there any requirements to use the ADODB.connection ?
What could i be missing? the database is 2003/2007 and it is shared. Can you send me the database you are using?
thank you again for the help
Thank you for the reply. With your code, however, I receive No Records found, even though there are two records in the table that begin with R. Are there any requirements to use the ADODB.connection ?
What could i be missing? the database is 2003/2007 and it is shared. Can you send me the database you are using?
thank you again for the help
ASKER
Aurelianco,
Attached is the database and I trying to access along with the code page.
Brands.mdb
retrieve.htm
Attached is the database and I trying to access along with the code page.
Brands.mdb
retrieve.htm
ASKER
Can anyone provide any feedback from the above attached files?
For some reason I receive 'No Results Found' everytime I load the page.
Any insight would be greatly appreciated.
thank you
For some reason I receive 'No Results Found' everytime I load the page.
Any insight would be greatly appreciated.
thank you
This is the Brands.mdb database I used. It is Access 2000.
Brands.mdb
Brands.mdb
Apparently the code and database are all OK, the problem comes from the retrieve.htm file name.
HTM files are NOT processed on the server. So, you have to rename it as retrieve.asp and the IIS will process the file on the server as an ASP script.
HTM files are NOT processed on the server. So, you have to rename it as retrieve.asp and the IIS will process the file on the server as an ASP script.
ASKER
Aurelianco,
Thank you for the solution! I am almost there, but I am not sure how to put the file into a directory that will allow me to serve the retreive.asp file via a url. When I click on the file directory it opens it in sharepoint designer. If i open it with IE it just displays the code. Can you help me with that?
thank you again!
Thank you for the solution! I am almost there, but I am not sure how to put the file into a directory that will allow me to serve the retreive.asp file via a url. When I click on the file directory it opens it in sharepoint designer. If i open it with IE it just displays the code. Can you help me with that?
thank you again!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
DoCmd.OutputTo acOutputTable, "YourTableName", acFormatHTML, "C:\YourFolder\YourTableNa