Coldfusion and database issue

P

paulnet

Guppy
Hi All,

I'm developing a CF site for a church and all the pages are dynamic (page.cfm?pid=xx). I'm using a datasource connection (both DSNs are the same. The one on my development computer and the one I have setup here at Jodo) to an MS SQL database. Here's the issue I'm getting:

When I browse to the index page on Jodo's servers (it's content is coming from the MS SQL DB), I get the correct page title (from the PAGES table), the date when it was updated (from the CONTENT table), but NO page content (from the CONTENT table). But, when I browse to it on my development computer, everything shows up fine. (I'm connecting to the same MS SQL DB on my development computer too).

I don't think it's my SQL statments, because if it were the case, the problem would happen on both my laptop and the Jodo server. My laptop runs MX 7, but I'm not sure if that makes a difference?

Has any one else experienced a problem similar to mine? Or is this issue a better trouble ticket than a forum post?

Thanks for your time.

-Paul
 
Would help to see some code so we can look for errors that may be there, whether it's a problem between MX6 and MX7, or something else.

Are you getting any errors, or just blank space where the content should be?
 
I don't get any error, just blank where there should be "stuff".

Here's the SQL statement

Code: 
<cfquery name="content" datasource="******">
SELECT TOP 1 CMS_CONTENT.content, CMS_CONTENT.date_post, CMS_PAGE.page_title, CMS_CATEGORY.category_name, CMS_PAGE.page_id, CMS_CONTENT.content_id FROM CMS_CATEGORY INNER JOIN (USERS INNER JOIN (CMS_PAGE INNER JOIN CMS_CONTENT ON CMS_PAGE.page_id = CMS_CONTENT.page_id) ON (USERS.user_id = CMS_CONTENT.user_id) AND (USERS.user_id = CMS_PAGE.user_id)) ON CMS_CATEGORY.category_id = CMS_PAGE.category_id WHERE (((CMS_PAGE.page_id)=1)) ORDER BY CMS_CONTENT.content_id DESC
</cfquery>

...Below is the output code...

Code: 
<div id="contentcenter">
    <p class="topleftlinks"><a href="index.cfm">Home</a> &gt; <cfoutput>#content.category_name#</cfoutput> &gt; <cfoutput>#content.page_title#</cfoutput></p>
    <p><cfoutput>#content.content#</cfoutput></p>
    <p>&nbsp;</p>
    <p>&nbsp;</p>
    <p>&nbsp;</p>
    <p>&nbsp;</p>
    <p>&nbsp;</p>
    <p>&nbsp;</p>
    <p>&nbsp;</p>
    <p class="updatedts">This page was updated on <cfoutput>#LSDateFormat(content.date_post,'DDDD, MMMM DD, YYYY')#</cfoutput>.</p>
  </div>

When I view the source on this page and scroll to the line where the #content.content# should be, all I see is "<p></p>".


I'm not sure if you need it, but here's the code for the entire index.cfm page. But I should also let you know that none of the pages are able to retrieve any "content" from the DB.

Code: 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<cfquery name="content" datasource="*********">
SELECT TOP 1 CMS_CONTENT.content, CMS_CONTENT.date_post, CMS_PAGE.page_title, CMS_CATEGORY.category_name, CMS_PAGE.page_id, CMS_CONTENT.content_id FROM CMS_CATEGORY INNER JOIN (USERS INNER JOIN (CMS_PAGE INNER JOIN CMS_CONTENT ON CMS_PAGE.page_id = CMS_CONTENT.page_id) ON (USERS.user_id = CMS_CONTENT.user_id) AND (USERS.user_id = CMS_PAGE.user_id)) ON CMS_CATEGORY.category_id = CMS_PAGE.category_id WHERE (((CMS_PAGE.page_id)=1)) ORDER BY CMS_CONTENT.content_id DESC
</cfquery>
<head>
<title><cfoutput>#content.page_title#</cfoutput></title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<link href="styles/layout.css" rel="stylesheet" type="text/css" />
<link href="styles/main.css" rel="stylesheet" type="text/css" />

<script language="JavaScript">
<!--
function mmLoadMenus() {
  if (window.mm_menu_0327162814_0) return;
    window.mm_menu_0327162814_0 = new Menu("root",78,16,"Tahoma, verdana, Arial, sans-serif",10,"#000000","#CC0000","#F3DE2B","#FFFF99","left","middle",3,0,1000,-5,7,true,true,true,0,true,true);
  mm_menu_0327162814_0.addMenuItem("St.&nbsp;John's","location='page.cfm?pid=5'");
   mm_menu_0327162814_0.fontWeight="bold";
   mm_menu_0327162814_0.hideOnMouseOut=true;
   mm_menu_0327162814_0.bgColor='#555555';
   mm_menu_0327162814_0.menuBorder=1;
   mm_menu_0327162814_0.menuLiteBgColor='#ffffff';
   mm_menu_0327162814_0.menuBorderBgColor='#555555';

mm_menu_0327162814_0.writeMenus();
} // mmLoadMenus()
//-->
</script>
<script language="JavaScript" src="mm_menu.js"></script>
</head>
<cfquery name="todaysEvents" datasource="********">
SELECT CMS_EVENT.description, CMS_EVENT.time, CMS_LOCATION.location_name, CMS_LOCATION.location_address, CMS_LOCATION.location_city, CMS_LOCATION.location_state, CMS_LOCATION.location_zip FROM CMS_LOCATION INNER JOIN CMS_EVENT ON CMS_LOCATION.location_id = CMS_EVENT.location_id WHERE ((CMS_EVENT.date)=convert(datetime, CONVERT(varchar(8), getdate(),112)) ) ORDER BY CMS_EVENT.time
</cfquery>
<body>
<script language="JavaScript1.2">mmLoadMenus();</script>
<div id="frame">
  <div id="contentheader">
    <p>&nbsp;</p>
  </div>
  <div id="navbar"> <a href="javascript:;" onmouseover="MM_showMenu(window.mm_menu_0327162814_0,0,50,null,'image1')" onmouseout="MM_startTimeout();"><img src="images/images/navbar_01.gif" name="image1" width="101" height="50" border="0" id="image1" /></a><img src="images/images/navbar_02.gif" width="146" height="50" /><img src="images/images/navbar_03.gif" width="140" height="50" /><img src="images/images/navbar_04.gif" width="120" height="50" /><img src="images/images/navbar_05.gif" width="143" height="50" /> </div>
  <div id="quicklinkbar">
    <p><a href="index.cfm">Home</a> - Links </p>
  </div>
  <div id="contentcenter">
    <p class="topleftlinks"><a href="index.cfm">Home</a> &gt; <cfoutput>#content.category_name#</cfoutput> &gt; <cfoutput>#content.page_title#</cfoutput></p>
    <p><cfoutput>#content.content#</cfoutput></p>
    <p>&nbsp;</p>
    <p>&nbsp;</p>
    <p>&nbsp;</p>
    <p>&nbsp;</p>
    <p>&nbsp;</p>
    <p>&nbsp;</p>
    <p>&nbsp;</p>
    <p class="updatedts">This page was updated on <cfoutput>#LSDateFormat(content.date_post,'DDDD, MMMM DD, YYYY')#</cfoutput>.</p>
  </div>
  <div id="contentright">
    <p class="toprightlinks">Contact Us - <a href="login.cfm">Login</a> </p>
    <p><img src="images/Prayer-Request-Button.gif" width="145" height="84" /></p>
    
	
	<cfoutput query="todaysEvents">
	  <cfif todaysEvents.RecordCount GT 0>
        <p><strong>#todaysEvents.description#</strong><br />
                #LSTimeFormat(todaysEvents.time,'h:mm tt')# at <a href="http://www.mapquest.com/directions/main.adp?2a=#todaysEvents.location_address#&2c=#todaysEvents.location_city#&2s=#todaysEvents.location_state#&2z=#todaysEvents.location_zip#&CID=lfddlink&1sb=revise" target="_blank">#todaysEvents.location_name#</a><br />
        </p>
      </cfif>
    </cfoutput>
<p>&nbsp;</p>
  </div>

  <div id="contentbottom">
    <p class="copyright">(C) Copyright 2007 by PaulNet Designs</p>
    <p class="copyright">St. John's/St. Paul's Lutheran Parish<br />
      31385 Co Hwy 4<br />
      Vergas, MN 56587<br />
      (218).342.2379</p>
    <p class="copyright">[email protected]</p>
  </div>
</div>
</body>
</html>

Thanks again for your help.

-Paul
 
I'm not sure if this makes a difference, but the datatype for the content colum is ntext with a length of 16.

Also, this SQL DB was upsized from an Access database.

Again, I don't know if this info helps, but I guess it's probably better to give more info than less.

-Paul
 
When you get weird things like this you really need to break down the problem by eliminating things.

Is your query returning a row with the content blank or is it returning no rows at all? In other words, is it is row selection issue or is it a problem of getting the data for the content column? Is the content.page_title displaying? If it is then I guess it is selecting a row. Displaying content.RecordCount would be the sure way to check.

If everything looks good except that content is blank then it sounds like the db driver at Jodo handles the data type of the content column differently to your machine. If it was Text in Access then its probably Text in SQL Server. Perhaps varchar with a suitable size might be better. I think a varchar can be up to 8000 on SQL Server. That's just a thought with no particular reasoning behind it. Text / Blob type columns can behave differently to "normal" type columns.

If you're still getting nowhere, I'd start with a simplified query with just your CMS_CONTENT table and a hardcoded page_id. Perhaps on a test page with that and nothing else. Does that work? If that works then join in the other tables and see when it stops working.

When I'm stumped I always try to get back to basics and get something really simple working and then bring back the complications.

Ross
 
You must log in or register to reply here.
Back
Top