 |
| Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0. |
Welcome to the p2p.wrox.com Forums.
You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|
|
January 31st, 2005, 12:07 PM
|
|
Authorized User
|
|
Join Date: Jan 2005
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Display Table by Date
Hey all I have a simple coded here which accesses a database and writes a table to display the information based on a field. I would like it to display by order of Newest First but seem to run into trouble.
Code:
<HTML>
<HEAD>
</HEAD>
<Body>
<%@Language = "VbScript"%>
<%
dim objConn,objRec
set objConn=Server.CreateObject("ADODB.Connection")
objConn.Provider="Microsoft.Jet.OLEDB.4.0"
objConn.Open "\\lou1-web01\robohelp\shpsmate\shpsmate v3\asp\hot.mdb"
Set objRec = Server.CreateObject ("ADODB.Recordset")
objRec.Open "Hottopic",objConn, , , adCmdTable
%>
<TABLE BORDER=0 WIDTH=600>
<TR><TD COLSPAN=4 ALIGN=CENTER><B>Hot Topics SOG</B></TD></TR>
<%
While NOT objRec.EOF
If objRec("Control Number") = "10029" THEN
Response.Write "<TR><TD BGCOLOR=""#FFFF66"">" & objRec("Date_") & "</TD>"
Response.Write "<TD>" & objRec("Control Number") & "</TD>"
Response.Write "<TD>" & objRec("Company Name") & "</TD>"
Response.Write "<TD>" & objRec("Comments") & "</TD></TR>"
end if
objRec.MoveNext
Wend
%>
</TABLE>
</Body>
</HTML>
Does anyone now how I can modify this to work appropriately
|
|
January 31st, 2005, 12:37 PM
|
|
Friend of Wrox
|
|
Join Date: Jan 2004
Posts: 303
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Use a SQL query instead of opening the entire table,
instead of this
objRec.Open "Hottopic",objConn, , , adCmdTable
use
objRec.Open "SELECT fields FROM yourTable WHERE <criteria> Order By <fieldname>",objConn,adOpenKeyset,adLockOptimistic , adCmdText
|
|
January 31st, 2005, 12:52 PM
|
|
Authorized User
|
|
Join Date: Jan 2005
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Im sorry Im a newb...What would the <criteria> be? Should I remove If objRec("Control Number") = "10029" THEN
|
|
January 31st, 2005, 01:06 PM
|
|
Friend of Wrox
|
|
Join Date: Jan 2004
Posts: 303
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
if your control number is a numeric field,
SELECT field1,field2.. FROM yourTable WHERE [Control Number] = 10029 Order By <fieldname>
if your control number is a text field,
SELECT field1,field2.. FROM yourTable WHERE [Control Number] = '10029' Order By <fieldname>
Yes, remove your If objRec("Control Number") = "10029" THEN
Please..! I would encourage you to read the book..
|
|
January 31st, 2005, 01:22 PM
|
|
Authorized User
|
|
Join Date: Jan 2005
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Im still getting an error could you take anoyher look for me :)
Microsoft VBScript compilation error '800a0400'
Expected statement
Hot Topic1.asp, line 30
end if
^
Code:
<HTML>
<HEAD>
</HEAD>
<Body>
<%@Language = "VbScript"%>
<%
dim objConn,objRec
set objConn=Server.CreateObject("ADODB.Connection")
objConn.Provider="Microsoft.Jet.OLEDB.4.0"
objConn.Open "\\lou1-web01\robohelp\shpsmate\shpsmate v3\asp\hot.mdb"
Set objRec = Server.CreateObject ("ADODB.Recordset")
objRec.Open "SELECT [Control Number] , [Date_] FROM Hottopic WHERE [Control Number] = '10029' Order By <Date_>",objConn,adOpenKeyset,adLockOptimistic, adCmdText
%>
<TABLE BORDER=0 WIDTH=600>
<TR><TD COLSPAN=4 ALIGN=CENTER><B>Hot Topics SOG</B></TD></TR>
<%
While NOT objRec.EOF
Response.Write "<TR><TD BGCOLOR=""#FFFF66"">" & objRec("Date_") & "</TD>"
Response.Write "<TD>" & objRec("Control Number") & "</TD>"
Response.Write "<TD>" & objRec("Company Name") & "</TD>"
Response.Write "<TD>" & objRec("Comments") & "</TD></TR>"
end if
objRec.MoveNext
Wend
%>
Im sorry to keep bugging you I have just ordered a book.. Maybe in the future I can Help you
|
|
January 31st, 2005, 01:55 PM
|
|
Friend of Wrox
|
|
Join Date: May 2004
Posts: 642
Thanks: 0
Thanked 43 Times in 42 Posts
|
|
[Code]
<HTML>
<HEAD>
</HEAD>
<Body>
<%@Language = "VbScript"%>
<%
dim objConn,objRec
set objConn=Server.CreateObject("ADODB.Connection")
objConn.Provider="Microsoft.Jet.OLEDB.4.0"
objConn.Open "\\lou1-web01\robohelp\shpsmate\shpsmate v3\asp\hot.mdb"
Set objRec = Server.CreateObject ("ADODB.Recordset")
objRec.Open "SELECT [Control Number] , [Date_] FROM Hottopic WHERE [Control Number] = '10029' Order By <Date_>",objConn,adOpenKeyset,adLockOptimistic, adCmdText
%>
<TABLE BORDER=0 WIDTH=600>
<TR><TD COLSPAN=4 ALIGN=CENTER><B>Hot Topics SOG</B></TD></TR>
<%
While NOT objRec.EOF
Response.Write "<TR><TD BGCOLOR=""#FFFF66"">" & objRec("Date_") & "</TD>"
Response.Write "<TD>" & objRec("Control Number") & "</TD>"
Response.Write "<TD>" & objRec("Company Name") & "</TD>"
Response.Write "<TD>" & objRec("Comments") & "</TD></TR>"
end if -- Please Remove this line
objRec.MoveNext
Wend
%>
Om Prakash
|
|
January 31st, 2005, 04:08 PM
|
|
Authorized User
|
|
Join Date: Jan 2005
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Microsoft JET Database Engine error '80040e14'
Thanks for the response but I now receive
Syntax error (missing operator) in query expression '<Date_>'.
Hot Topic1.asp, line 14
As I said before Im a newb so i think I have the whole line mixed up
The field names i would like to Pull are named Control Number, Date_, and Comments from a table called Hot Topics
Thank You again
|
|
January 31st, 2005, 04:11 PM
|
|
Authorized User
|
|
Join Date: Jan 2005
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
sorry the table is called hottopic and Im trying to pull Company Name as well
|
|
January 31st, 2005, 05:00 PM
|
|
Friend of Wrox
|
|
Join Date: May 2004
Posts: 642
Thanks: 0
Thanked 43 Times in 42 Posts
|
|
The error is coming up in the following line:
<%
objRec.Open "SELECT [Control Number] , [Date_] FROM Hottopic WHERE [Control Number] = '10029' Order By [Date_]",objConn,adOpenKeyset,adLockOptimistic, adCmdText
%>
Om Prakash
|
|
January 31st, 2005, 05:26 PM
|
|
Authorized User
|
|
Join Date: Jan 2005
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Thanks everybody it works great :)
Code:
<HTML>
<HEAD>
</HEAD>
<Body>
<%@Language = "VbScript"%>
<%
dim objConn,objRec
set objConn=Server.CreateObject("ADODB.Connection")
objConn.Provider="Microsoft.Jet.OLEDB.4.0"
objConn.Open "\\lou1-web01\robohelp\shpsmate\shpsmate v3\asp\hot.mdb"
Set objRec = Server.CreateObject ("ADODB.Recordset")
objRec.Open "SELECT [Control Number], [Date_], [Company Name], [Comments] FROM Hottopic WHERE [Control Number]=22052 Order By [Date_] DESC",objConn,adOpenKeyset,adLockOptimistic, adCmdText
%>
<TABLE BORDER=0 WIDTH=600>
<TR><TD COLSPAN=4 ALIGN=CENTER><B>Hot Topics SOG</B></TD></TR>
<%
While NOT objRec.EOF
Response.Write "<TR><TD BGCOLOR=""#FFFF66"">" & objRec("Date_") & "</TD>"
Response.Write "<TD>" & objRec("Control Number") & "</TD>"
Response.Write "<TD>" & objRec("Company Name") & "</TD>"
Response.Write "<TD>" & objRec("Comments") & "</TD></TR>"
objRec.MoveNext
Wend
%>
|
|
|
