recently added SQL

[reubenb]

hi there,
what is wrong with the SQL below?
i have 10 tables and i want to get the combined top 5 from all of them ordered by date.

why doesnt it work?
there is no error, it just doesnt display any data

thanks



<p class="maintext">Recently Added<br>
<%
CreateConn()
SQL = "SELECT TOP 5 articles_title, type, articles_id, articles_level, articles_category, articles_subcategory, articles_addedbydate FROM articles WHERE articles_level >= " & author_status & "
UNION ALL SELECT TOP 5 songslyrics_title, type, songslyrics_id, songslyrics_level, songslyrics_category, songslyrics_subcategory, songslyrics_addedbydate FROM songslyrics WHERE songslyrics_level >= " & author_status & "
UNION ALL SELECT TOP 5 images_title, type, images_id, images_level, images_category, images_subcategory, images_addedbydate FROM images WHERE images_level >= " & author_status & "
UNION ALL SELECT TOP 5 videos_title, type, videos_id, videos_level, videos_category, videos_subcategory, videos_addedbydate FROM videos WHERE videos_level >= " & author_status & "
UNION ALL SELECT TOP 5 websites_title, type, websites_id, websites_level, websites_category, websites_subcategory, websites_addedbydate FROM websites WHERE websites_level >= " & author_status & "
UNION ALL SELECT TOP 5 peulot_title, type, peulot_id, peulot_level, peulot_category, peulot_subcategory, peulot_addedbydate FROM peulot WHERE peulot_level >= " & author_status & "
UNION ALL SELECT TOP 5 quotations_title, type, quotations_id, quotations_level, quotations_category, quotations_subcategory, quotations_addedbydate FROM quotations WHERE quotations_level >= " & author_status & "
UNION ALL SELECT TOP 5 generalchinuch_title, type, generalchinuch_id, generalchinuch_level, generalchinuch_category, generalchinuch_subcategory, generalchinuch_addedbydate FROM generalchinuch WHERE generalchinuch_level >= " & author_status & "
UNION ALL SELECT TOP 5 keythemesquestions_title, type, keythemesquestions_id, keythemesquestions_level, keythemesquestions_category, keythemesquestions_subcategory, keythemesquestions_addedbydate FROM keythemesquestions WHERE keythemesquestions_level >= " & author_status & "
UNION ALL SELECT TOP 5 programideas_title, type, programideas_id, programideas_level, programideas_category, programideas_subcategory, programideas_addedbydate FROM programideas WHERE programideas_level >= "& author_status & " ORDER BY articles_addedbydate ASC"

CreateRS()
Do While Not ObjRS.EOF
%>
egweg
<a href="view.asp?id=<%=objRS("articles_id")%>&type=<%=objRS("type")%>">
<%=objRS("articles_title")%></a><br>
<%
ObjRS.MoveNext
Loop
%>

</p>

[neocool00]

For starters, you are missing " in front of each new line of the sql statement and & _ at the end of each line.

"UNION..." & _
"UNION..." & _
etc.

[reubenb]

yeah, i know that - its one line in my code but i put linebreaks here to make it easier to read.

anyone have any ideas, pls?

thx.

[Freon22]

I am pulling straws here but I guess that these two you are calling functions.

CreateConn()
CreateRS()

So show us the CreateRS function. You may have a problem there since your recordset is empity. And to be trueful I don't think you are calling these funtion right. I don't see you passing a argument to the function, in this case your argument is your sql statement. So depending on how you have setup your recordset function I think you need to call it like this.

CreateRS SQL

But your going to have to show us your functions :-)

Edit: Sorry I just tested it and you can pass an argument the way that you have it. But I still think the trouble maybe in that function since that function is what is returning your recordset.

[reubenb]

You can...


Dim dbCon, objCon, objRS, SQL

dbCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
dbCon = dbCon & "" & "H:\mdb.mdb" &";User Id=;Password="

Sub CreateConn()
Set objCon = Server.CreateObject("ADODB.Connection")
objCon.Open dbCon
End Sub

Sub CreateRS()
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open SQL, objCon, adOpenForwardOnly, adLockReadOnly, adCmdText
End Sub

Sub CloseRS()
objRS.close
set objRS = Nothing
End Sub

Sub CloseConn()
objCon.close
set objCon = Nothing
End Sub

[Roelf]

have you executed this query directly from Access? Did it return results?

[reubenb]

Yes, I have and it did not return anything...

[Roelf]

then execute each part of the query (parts between the union statements) to check the validity of each part. I think there is something wrong with your where clause,