Web Wiz - Green Windows Web Hosting
Web Wiz Homepage Search Web Wiz

  New Posts New Posts RSS Feed - WHERE in SQL
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

WHERE in SQL
 Post Reply Post Reply
Author
  Topic Search Topic Search  Topic Options Topic Options
Pegaso View Drop Down
Mod Builder Group
Mod Builder Group


Joined: 13 February 2003
Location: Switzerland
Status: Offline
Points: 138 		Post Options Post Options   Thanks (0) Thanks(0)   Quote Pegaso Quote  Post ReplyReply Direct Link To This Post Topic: WHERE in SQL
    Posted: 18 April 2003 at 9:55am

Little problem, the following script didn't work, why?

Dim rsNewestMembersMod
Dim strSQLMembersMod
Dim adoConMod
Dim dblActiveFrom1

dblActiveFrom1 = Session("dtmLastVisit")

strMembersModCon = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("admin/database/wwforum.mdb")
Set rsNewestMembersMod = Server.CreateObject("ADODB.Recordset")

strSQLMembersMod = "SELECT tblAuthor.Author_ID, tblAuthor.Username, tblAuthor.JoinDate FROM tblAuthor WHERE Join_Date > " & dblActiveFrom1
strSQLMembersMod = strSQLMembersMod & "ORDER BY tblAuthor.Author_ID DESC;"

rsNewestMembersMod.Open strSQLMembersMod, strMembersModCon

The error must be in the red lines but i didn't find the error. Can someone help me.

Thanks.
Back to Top
pmormr View Drop Down
Senior Member
Senior Member


Joined: 06 January 2003
Location: United States
Status: Offline
Points: 1479 		Post Options Post Options   Thanks (0) Thanks(0)   Quote pmormr Quote  Post ReplyReply Direct Link To This Post Posted: 18 April 2003 at 11:48am

Your problem is that you have no database to open a recordset for. You need to open your database before creating a recordset. Also, try not to make your variable name soooo long, there prone to typos and are easy to forget. I'm not to sure about your field names in your table double check them for me. Only use the field name, it knows what table your selecting from when you use the FROM clause. Try the following code.

Dim rs
Dim strSQL
Dim dblActiveFrom1
Dim datasource
Dim conn
Set conn = Server.CreateObject("ADODB.connection")
conn.provider = Microsoft.Jet.OLEDB.4.0

dblActiveFrom1 = Session("dtmLastVisit")

datasource = Server.MapPath("admin/database/wwforum.mdb")
conn.open ("DATA SOURCE =" & datasource)

Set rs = Server.CreateObject("ADODB.Recordset")

strSQL = "SELECT Author_ID, Username, Join_Date FROM tblAuthor WHERE Join_Date > " & dblActiveFrom1 "ORDER BY author_id DESC"

rs.Open strSQL, conn
Paul A Morgan

http://www.pmorganphoto.com/
Back to Top
pmormr View Drop Down
Senior Member
Senior Member


Joined: 06 January 2003
Location: United States
Status: Offline
Points: 1479 		Post Options Post Options   Thanks (0) Thanks(0)   Quote pmormr Quote  Post ReplyReply Direct Link To This Post Posted: 18 April 2003 at 11:50am

if you could tell me what your trying to do then i could be of more assistance
Paul A Morgan

http://www.pmorganphoto.com/
Back to Top
glypher View Drop Down
Mod Builder Group
Mod Builder Group


Joined: 25 March 2003
Location: United States
Status: Offline
Points: 38 		Post Options Post Options   Thanks (0) Thanks(0)   Quote glypher Quote  Post ReplyReply Direct Link To This Post Posted: 18 April 2003 at 2:55pm

ummm... it's all about the details. you're missing a space after the dblActiveFrom1

strSQLMembersMod = "SELECT tblAuthor.Author_ID, tblAuthor.Username, tblAuthor.JoinDate FROM tblAuthor WHERE Join_Date > " & dblActiveFrom1
strSQLMembersMod = strSQLMembersMod & " ORDER BY tblAuthor.Author_ID DESC;"

you see where i'm talking about?  right before "ORDER" you need to seperate it.

do a quick response.write strSQLMembersMod

that ought to show you waht i mean.



Edited by glypher
glypher said so.

GainesvilleBands.com
Back to Top
Pegaso View Drop Down
Mod Builder Group
Mod Builder Group


Joined: 13 February 2003
Location: Switzerland
Status: Offline
Points: 138 		Post Options Post Options   Thanks (0) Thanks(0)   Quote Pegaso Quote  Post ReplyReply Direct Link To This Post Posted: 19 April 2003 at 9:51am

OK I have tried your solution but it didn't work .

Sorry for the uncomplete informations. Here I give you the original  script that works correctly:

 <%
Dim rsNewestMembersMod
Dim strSQLMembersMod
Dim adoConMod

strMembersModCon = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("admin/database/wwforum.mdb")
Set rsNewestMembersMod = Server.CreateObject("ADODB.Recordset")

strSQLMembersMod = "SELECT Top 5 tblAuthor.Author_ID, tblAuthor.Username FROM tblAuthor "
strSQLMembersMod = strSQLMembersMod & "ORDER BY tblAuthor.Author_ID DESC;"

rsNewestMembersMod.Open strSQLMembersMod, strMembersModCon

If rsNewestMembersMod.EOF and strNewredir ="" Then
 Response.Write "<span class=""smltext"">No Forum Members</span>"
ElseIf strNewredir <>"" Then
 Response.Write "<div align=""center""><span class=""smltext"">You must login</span></div>"
Else
 Do while NOT rsNewestMembersMod.EOF and strNewredir = ""
  If len(rsNewestMembersMod("Username"))>18 then
   strMemebersUsername = left(rsNewestMembersMod("Username"),18) & "...&nbsp;"
  Else
   strMemebersUsername = rsNewestMembersMod("Username")
  End If
  
  Response.Write("<a href=""javascript:openWin('pop_up_profile.asp?PF=" & rsNewestMembersMod("Author_ID") & "','profile','toolbar=0,location=0,status=0,menubar=0,scrollbars=1,resizable=1,width=590,height=425')"" class=""smltext"">") & strMemebersUsername & ("</a>")
  
 rsNewestMembersMod.MoveNext
 If NOT rsNewestMembersMod.EOF Then Response.Write("<br>")
 Loop
End If

rsNewestMembersMod.Close
Set rsNewestMembersMod = Nothing
%>

This Script displays the last 5 registered users in the forum. Now i will show only the members that have registered after my last login in the forum. Also I have added the following lines in the script:

Dim dblActiveFrom1

dblActiveFrom1 = Session("dtmLastVisit")

And this script part works correctly. The error is in the SQL selection line where I have edited the SQL script as following:

strSQLMembersMod = "SELECT tblAuthor.Author_ID, tblAuthor.Username, tblAuthor.Join_Date FROM tblAuthor WHERE Join_Date > " & dblActiveFrom1

And this script give me the following error:

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

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Join_Date > 19/04/2003 16:20:17ORDER BY tblAuthor.Author_ID DESC'.

/testing.asp, line 361

In this error script you have the confirmation that the dblActiveFrom1 correctly work.

But I have no idea why this error line appears. Help please.

 



Edited by Pegaso
Back to Top
pmormr View Drop Down
Senior Member
Senior Member


Joined: 06 January 2003
Location: United States
Status: Offline
Points: 1479 		Post Options Post Options   Thanks (0) Thanks(0)   Quote pmormr Quote  Post ReplyReply Direct Link To This Post Posted: 19 April 2003 at 11:28am

again what glypher said, for one you need a space between your dblactiveforum variable and the group by clause. Also, look carefully at the error your variable is returning the time, date, and seconds i don't think it can process it like that. 
Paul A Morgan

http://www.pmorganphoto.com/
Back to Top
Pegaso View Drop Down
Mod Builder Group
Mod Builder Group


Joined: 13 February 2003
Location: Switzerland
Status: Offline
Points: 138 		Post Options Post Options   Thanks (0) Thanks(0)   Quote Pegaso Quote  Post ReplyReply Direct Link To This Post Posted: 20 April 2003 at 8:37am

OK now it's work correctly on my ASP hosting but it didn't work on my IIS, not a problem

Here you have the correct code:

Dim rsNewestMembersMod
Dim strSQLMembersMod
Dim adoConMod
Dim dblActiveFrom1

dblActiveFrom1 = FormatDateTime((Session("dtmLastVisit")),2)

strMembersModCon = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("admin/database/wwforum.mdb")
Set rsNewestMembersMod = Server.CreateObject("ADODB.Recordset")

strSQLMembersMod = "SELECT tblAuthor.Author_ID, tblAuthor.Username, tblAuthor.Join_Date FROM tblAuthor WHERE tblAuthor.Join_Date < " & dblActiveFrom1
strSQLMembersMod = strSQLMembersMod & " ORDER BY tblAuthor.Author_ID DESC;"

rsNewestMembersMod.Open strSQLMembersMod, strMembersModCon

The error was in the red line

Thanks to all for the help:
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.