problem with QueryString and two tables

[fran7]

Hi, I am having a problem with this little click through counter.

I am not sure its written correctly but works fine adding up the visits from links on my site to external websites, when I query with postcardid

I am now using author as a query so need it to do the same for each author. The postcardid is in tblgreetingpost cards and linked to id in tblLinkTrackerL og

Each postcardid has a unique author in tblgreetingpost cards
The two tables in question are tblLinkTrackerL og and tblgreetingpost cards.

Basically I suppose as postcardid from tblgreetingpost cards is related to id in tblLinkTrackerL og it somehow needs to add where postcardid = author or a query author = postcardid
Any help would be great.
Thanks
Richard

<%
 
Const adOpenStatic = 3
Const adLockReadOnly = 1
Const adCmdText = &H0001
' This needs to be ' for SQL Server and # for Access
Const DATE_DELIMITER = "#"
Dim iLinkId
Dim cnnLinkTracker, rsLinkTracker
Dim strTemp
iLinkId = CLng(Request.QueryString("lid"))
 
iLinkId = CLng(Request.QueryString("postcardid"))
 
Set cnnLinkTracker = Server.CreateObject("ADODB.Connection")
cnnLinkTracker.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
	Server.MapPath("/fpdb/greetingcardpro.mdb"), "", ""
Set rsLinkTracker = Server.CreateObject("ADODB.Recordset")
rsLinkTracker.Open "SELECT SUM(hit_count) FROM tblLinkTrackerLog WHERE link_id = " & iLinkId & ";", cnnLinkTracker, adOpenStatic, adLockReadOnly, adCmdText
Response.Write "" & rsLinkTracker.Fields(0).Value & "" & vbCrLf
rsLinkTracker.Close
 
Set rsLinkTracker = Nothing
 
cnnLinkTracker.Close
Set cnnLinkTracker = Nothing
%>

[jhardman]

So right now you are only pulling up data from one table.

"SELECT SUM(hit_count) FROM tblLinkTrackerLog WHERE link_id = " & iLinkId & ";"

Perhaps you are looking for something more like this:[code=asp]"SELECT SUM(hit_count) FROM tblLinkTrackerL og, tblgreetingpost cards WHERE tblLinkTrackerL og.postcardid = tblgreetingpost cards.id AND author = " & request("author ") & ";"[/code]Does this make sense? If you use this technique, your "Where" clause needs to specify how the two tables relate (tblLinkTracker Log.postcardid = tblgreetingpost cards.id) and which records to pull up (author = 291). Let me know if this helps.

Jared

[fran7]

Thanks Jared, I see the logic completely. I am still getting object required error on that line but will tinker till i get it.
Thanks
Richard

[jhardman]

Thanks Jared, I see the logic completely. I am still getting object required error on that line but will tinker till i get it.
Thanks
Richard

Oh I hate that error! It always seems to take too long to find the real problem. Make sure you can use the objects mentioned on that line in simpler cases.

Jared

[fran7]

Dear Jared, I have been looking again and have this error.

Microsoft JET Database Engine error '80040e14'

Syntax error (missing operator) in query expression 'tblLinkTracker Log.id = tblgreetingpost cards.postcardi d AND website ='.

/business.asp, line 344

and have the code like this

<%
Const adOpenStatic = 3
Const adLockReadOnly = 1
Const adCmdText = &H0001

' This needs to be ' for SQL Server and # for Access
Const DATE_DELIMITER = "#"

Dim iLinkId
Dim cnnLinkTracker, rsLinkTracker
Dim strTemp

iLinkId = CLng(Request.QueryString("postcardid"))

Set cnnLinkTracker = Server.CreateObject("ADODB.Connection")
cnnLinkTracker.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
	Server.MapPath("/fpdb/greetingcardpro.mdb"), "", ""

Set rsLinkTracker = Server.CreateObject("ADODB.Recordset")
rsLinkTracker.Open "SELECT * FROM tblLinkTrackerLog WHERE link_id = " & iLinkId & " ORDER BY hit_date DESC;", cnnLinkTracker, adOpenStatic, adLockReadOnly, adCmdText
rsLinkTracker.Close
rsLinkTracker.Open "SELECT SUM(hit_count) FROM tblLinkTrackerLog, tblgreetingpostcards WHERE tblLinkTrackerLog.id = tblgreetingpostcards.postcardid AND website = " & request("website") & ";", cnnLinkTracker, adOpenStatic, adLockReadOnly, adCmdText
Response.Write "" & rsLinkTracker.Fields(0).Value & "" & vbCrLf
rsLinkTracker.Close

Set rsLinkTracker = Nothing

cnnLinkTracker.Close
Set cnnLinkTracker = Nothing
%>

line 344 is the last select.

I just wondered if that makes anything clearer to you.
Thanks
Richard

[fran7]

Dear Jared, Just worked it out, just took the request out of this bet
[HTML]" & request ("website") & ";[/HTML]
and it worked a treat.
Thanks for sorting that one.
richard

[fran7]

Dear Jared, actually spoke too soon, it did present a number. I checked and it was the same for all page links, the number 22. So back to the drawing board.
richard

[jhardman]

A good trouble-shooting step at this point is putting the query in a variable like this:
[code=asp]query = "SELECT SUM(hit_count) FROM tblLinkTrackerL og, tblgreetingpost cards WHERE tblLinkTrackerL og.id = tblgreetingpost cards.postcardi d AND website = " & request("websit e") & ";"
response.write "<!-- query: " & query & " -->" & vbNewLine
rsLinkTracker.O pen query, cnnLinkTracker, adOpenStatic, adLockReadOnly, adCmdText[/code]Check how the query looks in the comment line (<!--...-->) and make sure it looks OK. Second, try inputting this query into a query analyzer. Is this an Access DB? I was just going to say that most DB's have query analyzers built in, but I'm not sure where to find it in Access.

Jared

[fran7]

Dear Jared, Thanks for your help. Found out I just messed the
' ' around your code modification

'" & request("website") & "'

Works a treat now.
Thanks for the solution
Richard