Very strange output

tdaustin · August 2nd, 2004, 01:22 AM

Hi Everyone,

I have come across an issue with displaying of some fields from a SQL2000 db. I am totally stumped why, becuase it works fine for Access & mySQL but not SQL2000.

All im doing is pulling a record out and displaying it on the screen.

There are 3 fiedls NewsTitle, NewsDesc, NewsImage

I display the title then the i place the image field in a table and align to the right so the description will wrap around the image, then the description field. The table where the image is display to show only if there is a image referenced on the database.

this all works perfect for access and mysql but for 2000 when a image is there it chops the description as if the condition statement wasn't closed.

Has anyone had this problem?

Thanks Tim

Code:

          <%
          ' Loop through all records
          DO WHILE NOT objRS.EOF
          %>
          <table width="100%" align="center" class="basictext">
            <tr>
              <td colspan="3" class="DisplayHeaders"> <%= objRS("NewsTitle") %><a name="<%= objRS("NewsID") %>" id="<%= objRS("NewsID") %>"></a></td>
            </tr>
            <tr valign="top">
              <td colspan="3" class="basictext"> 
              <% If objRS("NewsImage") = "" Then %>
                <table width="100" border="0" align="right" cellpadding="0" cellspacing="1" class="searchbar">
                  <tr>
                    <td><img src="uploads/<%= objRS("NewsImage") %>" width="213" height="160"></td>
                  </tr>
                </table>
              <% End If %>  

              <%= objRS("NewsDesc") %>              </td>
            </tr>
            <tr>
              <td width="2%" height="17">
              <% If objRS("NewsFile") <> "" Then %>
              <img src="images/blue-arrow.gif" alt="More Information" width="9" height="9"> 
              <% End If %>
              </td>
              <td width="48%">
              <% If objRS("NewsFile") <> "" Then %>
              <strong><a href="uploads/<%= objRS("NewsFile") %>" target="_blank">Download Article </a></strong>
              <% End If %>
              </td>
              <td width="50%" align="right" nowrap><a href="#top"><img src="images/b2top.gif" alt="Back to the top" width="31" height="13" border="0"></a></td>
            </tr>
          </table>


          <%
          objRS.MoveNext
          Loop
          %>

TDA

rajanikrishna · August 2nd, 2004, 03:04 AM

Hello,
Wat it displays with SQL2000? Displays anything or nothing?

madhukp · August 2nd, 2004, 06:03 AM

Eventhough I did not find any problem with the code, I have two suggestions based on my similar experience.

1) I suppose the datatype of the field NewsDesc is text (or ntext). In such case, you need to fetch that field in a seperate query.This means you have to do it like this.

Select NewsID, NewsTitle, NewsFile, NewsImage FROM <news table> WHERE <Condition>
Then loop through each record.
Inside the loop you have to fetch the news details with the following query.

Select NewsDesc FROM <news table> WHERE NewsID=<current news id>

Then it should work.

This is because SQL server truncates binary data when it is mixed up with non-binary data in a query.

2) The table in which you are showing the image has its alignment as right. Then you may need to use <br clear="right"> to get the other content in the TD to be properly aligned. You need to do it only if you find any alignment problem.

happygv · August 2nd, 2004, 06:30 AM

Hi TDA,

I don't think this is strange output. I believe, it works the way you coded. You are checking for = "". Does this make sense?

Obviously, you will see the IMAGE not displayed. What it means is you are trying to show image when there is nothing coming in from the recordset. Is that what you want to do? Little down the code in another place you are checking for <> "". That should be only the flaw I could make out from your code.

Code:

<% If objRS("NewsImage") = "" Then %>
    <table width="100" border="0" align="right" cellpadding="0" cellspacing="1" class="searchbar">
        <tr>
            <td><img src="uploads/<%= objRS("NewsImage") %>" width="213" height="160"></td>
        </tr>
    </table>
<% End If %>

I would suggest you to always check for len(objRS("NewsImage")) > 0, as it can be NULL or Zero Length String(ZLS), to have it check for both the possiblity, it is better to check if its length is greater that ZERO and then display if it is.

Also post the datatypes of all the columns involved in there.

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

happygv · August 2nd, 2004, 06:41 AM

Quote:

quote:Originally posted by madhukp
Select NewsID, NewsTitle, NewsFile, NewsImage FROM <news table> WHERE <Condition>
Then loop through each record.
Inside the loop you have to fetch the news details with the following query.

Select NewsDesc FROM <news table> WHERE NewsID=<current news id>

Then it should work.

This is because SQL server truncates binary data when it is mixed up with non-binary data in a query.

2) The table in which you are showing the image has its alignment as right. Then you may need to use <br clear="right"> to get the other content in the TD to be properly aligned. You need to do it only if you find any alignment problem.

Madhu,
If that column is of text/ntext datatype, then it doesn't require to hit the db again using a separate SELECT statement within a primary loop to get its value. It should be specified as last column in the same SELECT statement list as...
Select NewsID, NewsTitle, NewsFile, NewsImage, NewsDesc FROM <news table> WHERE <Condition>
Only if that is specified somewhere in the middle of the select list, it disappears.

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

tdaustin · August 2nd, 2004, 07:51 PM

Hi Everyone,

Thanks for your help.

All you guys were right. There was 2 issues that i didn't know.

1. SQL server truncates binary data
2. Very sloppy validation of data or null values

Yes the NewsDesc was a text datatype and by calling NewsDesc last in my SELECT Statement displayed my text, but this killed the image, but when i validated as Vijay said ( as you should) , All works perfect!

Thanks for all your help

Regards,

Tim :)

TDA