473,320 Members | 1,914 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Filtering out the "category name" from a database

114 100+
I have a webpage called "topicview.asp" on a news website with ASP pages, it's a simple news publishing software. You add the news from the Admin section and all the details are stored in a database.

This page shows all the categories filtered out, (10 to a page) but it doesn't show the NAME of the category anywhere on the page. How can i add the name of the category? In the database the title of the secction that stores the

nm_tbl_cate and the field name is "fldNAME"

Let me give an example,

The news category, "Business and finance" is CID 13 and the browser window shows

http://www.myweb/TopicView.asp?CID=13

However nowhere in the page does it say, "Business and finance"

Because i have many categories, it will be tedious for me to physically write out a page for each. Isn't there anything i can add to the code of this page, so that i automatically filters out the Name of the category too? Here is how the name is stored in the database:

nm_tbl_cate and the field name is "fldNAME"

and here is the code of my page,
Expand|Select|Wrap|Line Numbers
  1. <!--#include file="inc_header.asp"-->
  2.  
  3.     <TABLE WIDTH=750 BORDER=0 CELLPADDING=4 CELLSPACING=0 align="center">
  4.   <TR> 
  5.  
  6.     <TR> 
  7.           <TD width="600" valign="top" class="leftcol"> 
  8.  
  9.         Channels.
  10.  
  11.     <%Dim iStart, iOffset, SQL, RS, EOF_VAL, strRETURNED_DATA, iRows, iCols, iStop, iRowLoop, strPREV_LINK, strNEXT_LINK, CID, CNAME, AUTHOR, NID, SUMMARY, IMAGE, CATEGORIES, TITLE, POSTED, XI
  12.  
  13.     CID     = Trim(Request.QueryString("CID"))
  14.     iStart  = Request.QueryString("Start")
  15.     iOffset = Request.QueryString("Offset")
  16.  
  17.     IF IS_VALID_ID(CID) THEN
  18.  
  19.         If Not IsNumeric(iStart) or Len(iStart) = 0 then
  20.           iStart = 0
  21.         Else
  22.           iStart = CInt(iStart)
  23.         End If
  24.         If Not IsNumeric(iOffset) or Len(iOffset) = 0 then
  25.           iOffset = ALL_ARTICLES_PAGE_SIZE
  26.         Else
  27.           iOffset = Cint(iOffset)
  28.         End If    
  29.  
  30.         IF DB_TO_USE = 1 OR DB_TO_USE = 3 THEN    
  31.             SQL = "SELECT nm_tbl_news.ID AS NID, nm_tbl_news.fldSUMMARY AS SUMMARY, nm_tbl_news.fldIMAGE AS NIMAGE, nm_tbl_news.fldTITLE AS TITLE, nm_tbl_agent.fldNAME AS ANAME, nm_tbl_news.fldPOSTED AS POSTED FROM nm_tbl_news, nm_tbl_agent WHERE (nm_tbl_agent.ID = nm_tbl_news.fldAID) AND (nm_tbl_news.fldACTIVE=1) AND (Now() BETWEEN fldPOSTED AND fldEXPIRES) AND (nm_tbl_news.ID IN (SELECT fldNEWS_ID FROM nm_tbl_news_cate WHERE fldCATE_ID = " & CID & ")) ORDER BY fldPOSTED DESC"
  32.         ELSE
  33.             SQL = "SELECT nm_tbl_news.ID AS NID, nm_tbl_news.fldSUMMARY AS SUMMARY, nm_tbl_news.fldIMAGE AS NIMAGE, nm_tbl_news.fldTITLE AS TITLE, nm_tbl_agent.fldNAME AS ANAME, nm_tbl_news.fldPOSTED AS POSTED FROM nm_tbl_news, nm_tbl_agent WHERE (nm_tbl_agent.ID = nm_tbl_news.fldAID) AND (nm_tbl_news.fldACTIVE=1) AND (GetDate() BETWEEN fldPOSTED AND fldEXPIRES) AND (nm_tbl_news.ID IN (SELECT fldNEWS_ID FROM nm_tbl_news_cate WHERE fldCATE_ID = " & CID & ")) ORDER BY fldPOSTED DESC"
  34.         END IF
  35.  
  36.         Call OPEN_DB()
  37.  
  38.         Set RS = MyConn.Execute(SQL)
  39.         IF NOT RS.EOF THEN
  40.             strRETURNED_DATA = RS.getrows
  41.             EOF_VAL = False
  42.         ELSE
  43.             EOF_VAL = True    
  44.         END IF    
  45.         RS.close
  46.         Set RS = Nothing
  47.         ' Get settings
  48.         Dim DATE_F
  49.         DATE_F = GET_SETTINGS(False, "fldDATE_F")
  50.  
  51.  
  52.         IF not EOF_VAL = True THEN
  53.             iRows = UBound(strRETURNED_DATA, 2)
  54.             iCols = UBound(strRETURNED_DATA, 1)
  55.             If iRows > (iOffset + iStart) Then
  56.                iStop = iOffset + iStart - 1
  57.             Else
  58.                iStop = iRows
  59.             End If            
  60.             FOR iRowLoop = iStart to iStop
  61.                 NID = strRETURNED_DATA(0, iRowLoop)
  62.                 SUMMARY = strRETURNED_DATA(1, iRowLoop)
  63.                 IMAGE = strRETURNED_DATA(2, iRowLoop)
  64.                 TITLE = strRETURNED_DATA(3, iRowLoop)
  65.                 AUTHOR = strRETURNED_DATA(4, iRowLoop)
  66.                 POSTED = strRETURNED_DATA(5, iRowLoop)
  67.                 TITLE = PROCESS_SHORTCUTS(False, TITLE)
  68.                 SUMMARY = PROCESS_SHORTCUTS(False, SUMMARY)    
  69.                 CATEGORIES = GET_CATES(NID)        
  70.                             %>
  71.  
  72.  
  73.                  <table cellpadding="2" cellspacing="0" border="0" width="100%"><tr>    
  74.                  <td><a href="view.asp?ID=<%=NID%>" class="aTITLE"><%= TITLE %></a></td>
  75.                 </tr>
  76.                 <tr><td colspan="2" class="divPOSTEDON"><div align="justify"><%= FormatDateTime(POSTED,DATE_F) %>
  77.                   <% IF SHOW_AUTHOR = True THEN %>
  78. &nbsp;&nbsp; By:<%= AUTHOR %>
  79. <% Else %>
  80. &nbsp;
  81. <% End If %>
  82.                 </div></td></tr>
  83.  
  84.                 <tr><td colspan="2" class="tdSUMMARY"><div align="justify">
  85.                     <%IF NOT (IMAGE = "" OR IsNull(IMAGE)) THEN%>
  86.                       <img src="<%=IMAGE%>" width="50" height="50" border="1" align="left" />
  87.                   <% END IF %>
  88.                   <%= SUMMARY %></div></td></tr>
  89.                 </table>
  90.                 <br /><br />
  91.  
  92.                 <%
  93.             NEXT
  94.              ' Close DB        
  95.             MyConn.close            
  96.             Set MyConn = Nothing        
  97.  
  98.             If iStart > 0 Then
  99.              strPREV_LINK = "<A class=""clsPAGING"" HREF=""TopicView.asp?Start=" & iStart-iOffset & "&Offset=" & iOffset & "&CID=" & CID & """>Previous " & iOffset & "</A>"
  100.             Else
  101.                 strPREV_LINK = "&nbsp;"
  102.             End If    
  103.             If iStop < iRows Then
  104.              strNEXT_LINK = " <A class=""clsPAGING"" HREF=""TopicView.asp?Start=" & iStart+iOffset & "&Offset=" & iOffset & "&CID=" & CID & """>Next " & iOffset & "</A>"
  105.             Else        
  106.              strNEXT_LINK = "&nbsp;"    
  107.             End If %>                    
  108.  
  109.             <table width="100%" align="center" cellpadding="2" cellspacing="0" border="0"><tr class="trPAGING">
  110.              <td width="50%" align="left"><%=strPREV_LINK%></td>
  111.              <td width="50%" align="right"><%=strNEXT_LINK%></td>
  112.             </tr></table>    
  113.  
  114.         <%ELSE%>    
  115.  
  116.                 <table width="100%" align="center" cellpadding="2" cellspacing="0" border="0"><tr><td>No articles have been found.</td></tr></table>
  117.  
  118.         <%END IF%>        
  119.     <%END IF%>
  120.  
  121. <!--#include file="inc_footer.asp"-->
Jul 1 '07 #1
6 1983
karen987
114 100+
Also, i wuoldn't mind if the title of the category showed in the browser too. At the moment, in the browser, it shows the ID, but not the name of the category.

I don't think the one you mentioned is the one, i think this needs to be added to the sql statement, because the names of the categories are stored in this column called "fldNAME"



nm_tbl_cate and the field name is "fldNAME"

What i need to do, is to somehow filter each "fldNAME" as well as the ID (which already shows in the browser)

I'd like to show the fldNAME as Category name on the webpage. If it can be shown in the browswer too, good, if not i'd like it on the webpage.
Jul 2 '07 #2
jhardman
3,406 Expert 2GB
Karen,

In the database query there is a "Select" clause. This tells the asp page which fields in the db to look up. You need to add the field you want to this list using this syntax:
SELECT tableName.fieldName, tableName2.fieldName2, etc

then after the db connection is opened, you can refer to this db field as:
RS("tableName.fieldName")

So for example, if you have a field named "itemNames" in a table named "breakfastFoods" and you wanted this to be the title of the web page you could say:
Expand|Select|Wrap|Line Numbers
  1. response.write "<title>"
  2. response.write RS("breakfastFoods.fieldName")
  3. response.write "</title>" & vbNewLine
this would print out:[html]<title>Toast</title>[/html]Does this make sense?

Jared
Jul 3 '07 #3
karen987
114 100+
Karen,

In the database query there is a "Select" clause. This tells the asp page which fields in the db to look up. You need to add the field you want to this list using this syntax:
SELECT tableName.fieldName, tableName2.fieldName2, etc

then after the db connection is opened, you can refer to this db field as:
RS("tableName.fieldName")

So for example, if you have a field named "itemNames" in a table named "breakfastFoods" and you wanted this to be the title of the web page you could say:
Expand|Select|Wrap|Line Numbers
  1. response.write "<title>"
  2. response.write RS("breakfastFoods.fieldName")
  3. response.write "</title>" & vbNewLine
this would print out:[html]<title>Toast</title>[/html]Does this make sense?

Jared
Hi jared,

So that means,

i need to add
Expand|Select|Wrap|Line Numbers
  1. nm_tbl_cate.fldName
do i need to repeat this for example i have added it to the sql statement here,

Expand|Select|Wrap|Line Numbers
  1.    IF DB_TO_USE = 1 OR DB_TO_USE = 3 THEN    
  2.             SQL = "SELECT nm_tbl_news.ID AS NID, nm_tbl_news.fldSUMMARY AS SUMMARY, nm_tbl_news.fldIMAGE AS NIMAGE, nm_tbl_news.fldTITLE AS TITLE, nm_tbl_agent.fldNAME AS ANAME, nm_tbl_news.fldPOSTED AS POSTED FROM nm_tbl_news, nm_tbl_cate.fldName as CATEGORY, nm_tbl_agent WHERE (nm_tbl_agent.ID = nm_tbl_news.fldAID) AND (nm_tbl_news.fldACTIVE=1) AND (Now() BETWEEN fldPOSTED AND fldEXPIRES) AND (nm_tbl_news.ID IN (SELECT fldNEWS_ID FROM nm_tbl_news_cate WHERE fldCATE_ID = " & CID & ")) ORDER BY fldPOSTED DESC"
and then i have to add
RS("nm_tbl_cate.fldName") , do you mean in the call open DB part below? and where exactly should i do this?

Expand|Select|Wrap|Line Numbers
  1. Call OPEN_DB()
  2.  
  3.         Set RS = MyConn.Execute(SQL)
  4.         IF NOT RS.EOF THEN
  5.             strRETURNED_DATA = RS.getrows
  6.             EOF_VAL = False
  7.         ELSE
  8.             EOF_VAL = True    
  9.         END IF    
  10.         RS.close
  11.         Set RS = Nothing
  12.         ' Get settings
  13.         Dim DATE_F
  14.         DATE_F = GET_SETTINGS(False, "fldDATE_F")
  15.  
  16.  
  17.         IF not EOF_VAL = True THEN
  18.             iRows = UBound(strRETURNED_DATA, 2)
  19.             iCols = UBound(strRETURNED_DATA, 1)
  20.             If iRows > (iOffset + iStart) Then
  21.                iStop = iOffset + iStart - 1
  22.             Else
  23.                iStop = iRows
  24.             End If            
  25.             FOR iRowLoop = iStart to iStop
  26.                 NID = strRETURNED_DATA(0, iRowLoop)
  27.                 SUMMARY = strRETURNED_DATA(1, iRowLoop)
  28.                 IMAGE = strRETURNED_DATA(2, iRowLoop)
  29.                 TITLE = strRETURNED_DATA(3, iRowLoop)
  30.                 AUTHOR = strRETURNED_DATA(4, iRowLoop)
  31.                 POSTED = strRETURNED_DATA(5, iRowLoop)
  32.                 TITLE = PROCESS_SHORTCUTS(False, TITLE)
  33.                 SUMMARY = PROCESS_SHORTCUTS(False, SUMMARY)    
  34.                 CATEGORIES = GET_CATES(NID)        
  35.                             %>

and then to use it in the table in the page i would use

response.write "<title>"
response.write RS("nm_tbl_cate.fldName")
response.write "</title>" & vbNewLine[/code]

is this correct? someone gave me a join statement at this website, I'll post the link so you may understand what their solution was that didnt work.
http://www.webdeveloper.com/forum/sh...386#post771386
but it doesn't work and i'm stuck. It seems like such a simple thing,

Thanks for your help.
Jul 3 '07 #4
karen987
114 100+
no responsees to this? i'm disappointed!!:(
Jul 23 '07 #5
jhardman
3,406 Expert 2GB
Hi jared,

So that means I need to add
Expand|Select|Wrap|Line Numbers
  1. nm_tbl_cate.fldName
do i need to repeat this for example i have added it to the sql statement here,
you need to add it to what ever sql statement you use to open the db when you want to use it. Your project is becoming so complicated that it will be very difficult for anyone but you to figure out what is going on. If you have relied too much on other people's help, then it will be that much harder for you to straighten it out. I have tried to avoid giving you the exact code that will solve the problem partly because I think it would be a good idea if you were the ultimate authority on how your site is programmed. re-read my earlier post, I was pretty clear.
and then i have to add
RS("nm_tbl_cate.fldName") , do you mean in the call open DB part below? and where exactly should i do this?

and then to use it in the table in the page i would use

response.write "<title>"
response.write RS("nm_tbl_cate.fldName")
response.write "</title>" & vbNewLine[/code]
You only have to do the last part. When you add the field name to the select clause, that means you can access that field every time you want by typing
Expand|Select|Wrap|Line Numbers
  1. RS("nm_tbl_cate.fldName")
I then showed the exact way to use that field in the title bar in order to illustrate its use. I'm sorry if that confused you, it was your original question.
is this correct? someone gave me a join statement at this website, I'll post the link so you may understand what their solution was that didnt work.
http://www.webdeveloper.com/forum/sh...386#post771386
but it doesn't work and i'm stuck. It seems like such a simple thing,

Thanks for your help.
I don't like using JOIN, I am not an SQL expert, so I try to keep my SQL as simple and as easy to troubleshoot as I can.

Jared
Jul 23 '07 #6
karen987
114 100+
thanks Jared,

it's kinda working now, i'm just tidying it up a bit, thank you
Jul 24 '07 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

6
by: Jochen Daum | last post by:
Hi ! If I have an input field <form ... method="post"> <input type="text" name="abc def"> </form>
8
by: Remy Blank | last post by:
Hello unittest users, In a project I am working on, I have a series of tests that have to be run as root, and others as a normal user. One solution is to separate the tests into two different...
0
by: Joeyej | last post by:
Hi - I'm trying to move/use a web form (containing some javascript field checks) previously hosted on a Windows 2000 server. However, the FORM METHOD="post..." command in the form (shown below)...
5
by: Kevin R | last post by:
I'm trying to update a sql database. It's modified Oledb code from an example that did work with an access database. How can I tweak my code to make it work? Thanks in advance. Kevin...
3
by: NG | last post by:
I installed DB2 in Linux OS. I tried to create database and I am getting the following error message: SQL0444N Routine "db2spcat" (specific name "SQL060216094049640") is implemented with code...
6
by: webonomic | last post by:
"The connection name 'LocalSqlServer' was not found in the applications configuration or the connection string is empty." I get the error above. It tells me the error is in Line 120: ...
0
by: Eil | last post by:
Have 2 databases under the same db2 udb instance (aix 5.3.0.0, db2 udb V8.1, fixpak 9). Able to run the db2advis command successfully against one database. On the other database, always...
5
by: Chris Botha | last post by:
There is a database table with one of the columns named "System". When creating a new Dataset for this table, the project gives a huge number of compile errors. If I change the column name to be...
8
MMcCarthy
by: MMcCarthy | last post by:
Hi everyone I had an interesting problem today with a client. I have set up security and privilege on a database depending on NT User Login. However, I've only just realised that the code...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.