how to pull out a specific category from a database

[karen987]

The code below is for an asp page that pulls out a list of authors in a given category, from a news weblog. In this case, the category id (CID) is "37" . So the page should list all authors from category "37"

However it seems to pull out authors from all the categories. I think the code may be misplaced a bit, can anyone see what is wrong with it? It needs to be tweaked so that it only draws out from category "37"

Thanks in advance

<!--#include file="inc_header.asp"--> 


<%
	Dim CID
	CID = Trim(Request.QueryString("CID"))
%>


<table width="600" align="center" cellpadding="2" cellspacing="0" border="0"> 
<td width="600" align="center" valign="top"> 
        <table width="600" border="0" align="center"> 
          <tr> 
            <th width="189" valign="top" scope="col">   </th> 
          </tr> 
        </table><br /> 
        <table width="600" height="116" border="0" align="center" cellpadding="0" cellspacing="0" valign="top">         
         
         
        <% Dim I 
         
         IF IS_VALID_ID(CID) THEN 
             CID_SQL = " WHERE ID IN (SELECT fldAID FROM nm_tbl_news WHERE ID IN (SELECT fldNEWS_ID FROM nm_tbl_news_cate WHERE fldCATE_ID = " & CID & "))" 
         END IF 
         
         
        Call OPEN_DB()         
         
        I = 1 
        SQL = "SELECT ID, fldNAME, fldEMAIL, fldWEB, fldIMAGE, fldBIO FROM nm_tbl_agent " & CID_SQL & " ORDER BY fldNAME ASC" 
         
        Set RS = Server.CreateObject("ADODB.Recordset") 
        RS.LockType   = 1 
        RS.CursorType = 0 
        RS.Open SQL, MyConn     
             WHILE NOT RS.EOF                   
                 ID = RS("ID") 
                NAME = RS("fldNAME") 
                EMAIL = RS("fldEMAIL") 
                WEB = RS("fldWEB") 
                IMAGE = RS("fldIMAGE") & "" 
                BIO = RS("fldBIO")         
                %>                 
          <%IF I = 1 THEN%> 
           
          <tr><td width="33%" align="left" valign="top" bordercolor="#E5E5E5" style="padding: 5px;"><div align="justify"> 
            <%IF NOT (IMAGE = "" OR IsNull(IMAGE)) THEN%> 
            <img src="<%=IMAGE%>" width="70" height="80" border="0" align="left" /> 
            <%END IF%> 
             
            <a href="articles.asp?AID=<%=ID%>&NAME=<%=Server.URLEncode(NAME)%>" class="MainMenuLNK"><%=NAME%></a><%=BIO%></div><hr width="100%" size="1" style="color: #4169E1;height: 1px;width: 100%;" /></td> 
              <%END IF%> 
            <%IF I = 2 THEN%>     
            <td width="34%" align="left" valign="top" bordercolor="#E5E5E5" style="padding: 5px;"><div align="justify"> 
              <%IF NOT (IMAGE = "" OR IsNull(IMAGE)) THEN%> 
              <img src="<%=IMAGE%>" width="70" height="80" border="0" align="left" /> 
              <%END IF%> 
              <a href="articles.asp?AID=<%=ID%>&NAME=<%=Server.URLEncode(NAME)%>" class="MainMenuLNK"><%=NAME%></a> <%=BIO%></div><hr width="100%" size="1" style="color: #ffa626;height: 1px;width: 100%;" /></td> 
              <%END IF%>     
            <%IF I = 3 THEN%>     
            <td width="33%" align="left" valign="top" bordercolor="#E5E5E5" style="padding: 5px;"><div align="justify"> 
              <%IF NOT (IMAGE = "" OR IsNull(IMAGE)) THEN%> 
              <img src="<%=IMAGE%>" width="70" height="80" border="0" align="left" /> 
              <%END IF%> 
              <a href="articles.asp?AID=<%=ID%>&NAME=<%=Server.URLEncode(NAME)%>" class="MainMenuLNK"><%=NAME%></a> <%=BIO%></div><hr width="100%" size="1" style="color: #4169E1;height: 1px;width: 100%;" /></td> 
          </tr> 
           
           
           
              <%END IF%>                     
              <% 
                I = I + 1 
                IF I > 3 THEN I = 1                 
                RS.MoveNext 
             WEND 
        RS.Close 
        Set RS = Nothing 
         
        MyConn.Close 
        Set MyConn = Nothing         
         
        %> 
           
          </TR> 
        </TABLE> 
        <!--#include file="inc_footerAuthors.asp"-->

[iburyak]

Go to SQL Server and make sure this statement returns correct data.

SELECT ID, fldNAME, fldEMAIL, fldWEB, fldIMAGE, fldBIO FROM nm_tbl_agent 
WHERE ID IN (SELECT fldAID 
                      FROM nm_tbl_news 
                      WHERE ID IN (SELECT fldNEWS_ID 
                                            FROM nm_tbl_news_cate 
                                            WHERE fldCATE_ID = 37))
ORDER BY fldNAME ASC

If this is not what you want correct it first before going to ASP.

Good Luck.

[karen987]

Go to SQL Server and make sure this statement returns correct data.

SELECT ID, fldNAME, fldEMAIL, fldWEB, fldIMAGE, fldBIO FROM nm_tbl_agent 
WHERE ID IN (SELECT fldAID 
                      FROM nm_tbl_news 
                      WHERE ID IN (SELECT fldNEWS_ID 
                                            FROM nm_tbl_news_cate 
                                            WHERE fldCATE_ID = 37))
ORDER BY fldNAME ASC

If this is not what you want correct it first before going to ASP.

Good Luck.

Thank you iburyak,

I tried this,by replacing "CID" with "37" but it didn't work. It still pulled up all the authors

IF IS_VALID_ID(CID) THEN
		 	CID_SQL = " WHERE ID IN (SELECT fldAID FROM nm_tbl_news WHERE ID IN (SELECT fldNEWS_ID FROM nm_tbl_news_cate WHERE fldCATE_ID = " & [B]37 [/B] & "))"
		 END IF
		
		
		Call OPEN_DB()		
		
		I = 1
		SQL = "SELECT ID, fldNAME, fldEMAIL, fldWEB, fldIMAGE, fldBIO FROM nm_tbl_agent " & [B]37[/B] & " ORDER BY fldNAME ASC"

The CID i want is 37,

I'm not html literate, nor do i undersand what you meant by "make sure this staement runs correct"

I have put the relevant code in the first post. Which part (on which line) would i have to change "CID" if at all, to "37"

Thank you again, much appreciated.

[iburyak]

This line means that CID expected to this page as parameter.

CID = Trim(Request.Qu eryString("CID" ))



It shouldb be passed with a page call something like:


http://www.yor_page_URL_he re.asp?CID=37


You should change bold part as appropriate.
Good Luck.

[karen987]

This line means that CID expected to this page as parameter.

CID = Trim(Request.Qu eryString("CID" ))



It shouldb be passed with a page call something like:


http://www.yor_page_URL_he re.asp?CID=37


You should change bold part as appropriate.
Good Luck.

Iburak, sorry about this, but i don't get what your'e saying. Are you saying i should put
http://www.yor_page_URL_he re.asp?CID=37

where CID is?

There are 3 places where it is, so where should i put it? pls mention the line number from the code i posted in my first post.

Sorry about this, i'm a total novice. Thanks for your patience. Much appreciated.

[iburyak]

It is not in a code it is in Address where you call your page from.


For example where you put.

http://www.thescripts.com

Not sure how to help you, sorry.

[iburyak]

You can do this:

Change From:

<%
    Dim CID
    CID = Trim(Request.QueryString("CID"))
%>

To:

<%
    Dim CID
    CID = 37
%>

But it is called hard code and not recommended.

[karen987]

You can do this:

Change From:

<%
    Dim CID
    CID = Trim(Request.QueryString("CID"))
%>

To:

<%
    Dim CID
    CID = 37
%>

But it is called hard code and not recommended.

actually it works now, and the answer was above, you just weren't articulating it so that i could understand. Somebody else simply said:

From the page where the link is , put
http://www.yourpageand directories..as p?CID=37

and it worked. as simple as that. The code on the page above stays CID,

Thanks anyway:)