Web Wiz - Green Windows Web Hosting

  New Posts New Posts RSS Feed - How to filter some data out of a database
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

How to filter some data out of a database

 Post Reply Post Reply
Author
eagle00789 View Drop Down
Mod Builder Group
Mod Builder Group
Avatar

Joined: 24 March 2003
Location: Netherlands
Status: Offline
Points: 132
Post Options Post Options   Thanks (0) Thanks(0)   Quote eagle00789 Quote  Post ReplyReply Direct Link To This Post Topic: How to filter some data out of a database
    Posted: 20 May 2003 at 3:42am

I have a database containing ALL the links of my webpage. I want to create a menu out of this. That menu is working but there are some links that doesn't has to be in the menu. i can check this thru a special field in the database. if it is empty, then that link doesn't has to be in the menu, but i dont know how to filter. Below is the code for the database. i left out a lot of code for creating the menu. see the red comment in the code below to see the things that do and don't work. If possible, i need a quick answer.

Greetings Chris

---- menu.asp ----

Dim DataConn, cmdDC, rsDC
Dim Item
Dim iFieldCount, iLoopVar
Dim strLTorGT, iCriteria, strSortBy, strOrder
Dim sRequestIP

' Get request information
sRequestIP=Request.ServerVariables("REMOTE_ADDR")

' Set general variables
FoundRecordCount=0
'sDataSource = "d:\desktop\Webpagina gravenrode\links.mdb"
sDataSource = "c:\inetpub\wwwroot\links.mdb" ' Local Path to Database
BackgroundColor="FFFFFF"
RecordFound=0

' Create and establish data connection
Set DataConn = Server.CreateObject("ADODB.Connection")
DataConn.ConnectionTimeout = 15
DataConn.CommandTimeout = 30

'Access connection code
DataConn.Open "DBQ=" & sDataSource & ";Driver={Microsoft Access Driver (*.mdb)};DriverId=25;MaxBufferSize=8192;Threads=20;", "username", "password"


' Create and link command object to data connection then set attributes and SQL query
Set cmdDC = Server.CreateObject("ADODB.Command")
cmdDC.ActiveConnection = DataConn
cmdDC.CommandText = "SELECT * FROM Links"
cmdDC.CommandType = 1

' Create recordset
Set rsDC = Server.CreateObject("ADODB.Recordset")
' Opening record set with a forward-only cursor (the 0) and in read-only mode (the 1)
rsDC.Open cmdDC, , 1, 1

' Hier komt nu de loop om het menu in te lezen
    txtStream.WriteLine "//begin of database lookup"
    rsDC.MoveFirst
    Do while not rsDC.EOF 
    'this doesn't work why??
    If rsDC.fields("Menu_Location") = "" then
        rsDC.Movenext
    Else
 'this does work
 txtStream.WriteLine "Menu" & rsDC.fields("Menu_Location") & "=new Array(""" & rsDC.fields("LinkDescription") & """,""" & rsDC.fields("LinkUrl")& ""","",0,20,110);"
    End If
    rsDC.Movenext
    loop
    txtStream.WriteLine "//end of database lookup"
' Dit is het einde van de loop om het menu in te lezen

' Close Data Access Objects
rsDC.Close
Set rsDC = Nothing
Set cmdDC = Nothing
DataConn.Close
Set DataConn = Nothing

---- end menu.asp ----

Using forum at Gravenrode and here and here
Back to Top
ljamal View Drop Down
Mod Builder Group
Mod Builder Group


Joined: 16 April 2003
Status: Offline
Points: 888
Post Options Post Options   Thanks (0) Thanks(0)   Quote ljamal Quote  Post ReplyReply Direct Link To This Post Posted: 20 May 2003 at 10:56am
It may not work because the field is NULL and not ""
The best way to filter out these unwanted links is with your SQL statement.
you could use
SELECT * FROM Links where len(Menu_Location)>0

With your current code even if it did work the way you wish, it could potential cause an error because you could be moving 2 rows in the record set without checking if you are at the end of the recordset.
Back to Top
michael View Drop Down
Senior Member
Senior Member
Avatar

Joined: 08 April 2002
Location: United States
Status: Offline
Points: 4670
Post Options Post Options   Thanks (0) Thanks(0)   Quote michael Quote  Post ReplyReply Direct Link To This Post Posted: 20 May 2003 at 11:16am
I would check if it is not null like
SELECT whatever from table where field IS NOT NULL
Back to Top
eagle00789 View Drop Down
Mod Builder Group
Mod Builder Group
Avatar

Joined: 24 March 2003
Location: Netherlands
Status: Offline
Points: 132
Post Options Post Options   Thanks (0) Thanks(0)   Quote eagle00789 Quote  Post ReplyReply Direct Link To This Post Posted: 20 May 2003 at 12:40pm
i got it to work by using the following SQLstring:
SELECT Links.* FROM Links WHERE (((Links.Menu_Location) Is Not Null)) ORDER BY Links.Menu_Location;
Using forum at Gravenrode and here and here
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.08
Copyright ©2001-2026 Web Wiz Ltd.


Become a Fan on Facebook Follow us on X Connect with us on LinkedIn Web Wiz Blogs
About Web Wiz | Contact Web Wiz | Terms & Conditions | Cookies | Privacy Notice

Web Wiz is the trading name of Web Wiz Ltd. Company registration No. 05977755. Registered in England and Wales.
Registered office: Web Wiz Ltd, Unit 18, The Glenmore Centre, Fancy Road, Poole, Dorset, BH12 4FB, UK.

Prices exclude VAT at 20% unless otherwise stated. VAT No. GB988999105 - $, € prices shown as a guideline only.

Copyright ©2001-2026 Web Wiz Ltd. All rights reserved.