Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server ASP
|
SQL Server ASP Discussions about ASP programming with Microsoft's SQL Server. For more ASP forums, see the ASP forum category.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server ASP section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 29th, 2003, 06:31 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 215
Thanks: 0
Thanked 0 Times in 0 Posts
Default

First things first, Response.Writing the thing may not say whether it is null or not. Response.Write would also show nothing if there was an empty string, or possibly if there was a space. Use IsNull in ASP to check for NULL, eg:
Code:
<%
If IsNull(rs("MyCol")) Then
    Response.Write "Null value"
End If
%>
That said the best way to check this is to test the query in Query analyser. Are you using an SQL string generated in the ASP page? If so add in:
Response.Write SQL
Response.End
between setting the SQL string and executing it.

Then cut and paste into Query Analyser.

If you are using a parameterised proc, then print out any parameters, stop page execution and execute the proc in Query Analyser with the parameters that the ASP page gives you.

If none of that points out where the error is, post your asp page to the forum.

regards
David Cameron
 
Old August 3rd, 2003, 03:24 PM
Authorized User
 
Join Date: Jun 2003
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Sorry for replying so late, had some problems. Well, I followed your instructions and everything works as I said. img_id does NOT show up. I have put comment in the execution of the sproc, and I execute the query from my ASP page. If I find the solution, I'm sure the sproc will work too (it's the same query). I post my code below:



Code:
<%
Dim rsWelc, imgpos_vert, imgpos_horz, body, img, snd, vid, position, imgurl, imgfile, border, caption, sql2
Set rsWelc = Server.CreateObject("ADODB.Recordset")

sql2 = "SELECT welcome_page.title, welcome_page.body, welcome_page.displ_date, welcome_page.img_id, welcome_page.pos, welcome_page.border, welcome_page.caption, welcome_page.url, welcome_page.snd_id, welcome_page.embed, welcome_page.autoplay, welcome_page.loop, welcome_page.vid_id, users_imgs.img_file, users_imgs.img_width, users_imgs.img_height, users_imgs.img_alt, users_sounds.snd_file, users_sounds.snd_name, users_vids.vid_file, users_vids.vid_name FROM users_vids RIGHT JOIN (users_sounds RIGHT JOIN (users_imgs RIGHT JOIN welcome_page ON users_imgs.img_id = welcome_page.img_id) ON users_sounds.snd_id = welcome_page.snd_id) ON users_vids.vid_id = welcome_page.vid_id where welcome_page.site_id = " & site_id & ";"
rsWelc.Open sql2, objconn
'rsWelc.Open "exec spSitesfrontWelcome " & site_id, objconn
body = rsWelc("body")
position = rsWelc("pos")
img = rsWelc("img_id")
snd = rsWelc("snd_id")
vid = rsWelc("vid_id")
response.write img
if rsWelc.EOF then 
    response.write "&nbsp;"
else
    if img <> "" then
        imgpos_vert = Left(position, 3)
        imgpos_horz = Mid(position, 4)
        imgurl = rsWelc("url")
        imgfile = rsWelc("img_file")
        border = rsWelc("border")
        caption = rsWelc("caption")
        if imgpos_vert = "top" then
            response.write "<tr><td><div align=" & imgpos_horz & ">"
            if imgurl <> "" then
                response.write "<a href=" & imgurl & " target=new>"
                response.write "<img src=files/" & imgfile & " border=" & border & " alt=""" & caption & """></a>"
            else
                response.write "<img src=files/" & imgfile & " border=" & border & ">"
            end if
            response.write "<br>" & caption & "</div></td></tr>"
        end if
    end if
%>
                    <tr>
                      <td class="titles"><b><%= rsWelc("title") %></b></td>
                    </tr>
                    <tr>
                      <td class="main"><%= body %></td>
                    </tr>
<%
    if img then
        if imgpos_vert = "bot" then
            response.write "<tr><td><div align=" & imgpos_horz & ">"
            if rsWelc("url") <> "" then
                response.write "<a href=" & imgurl & " target=new>"
                response.write "<img src=files/" & imgfile & " border=" & border & " alt=""" & caption & """></a>"
            else
                response.write "<img src=files/" & imgfile & " border=" & border & ">"
            end if
            response.write "<br>" & caption & "</div></td></tr>"
        end if
    end if

' Exei arxeio HXOU..........
Dim sndembed, sndloop, sndauto
if snd then
    sndembed = rsWelc("embed")
    sndloop = rsWelc("loop")
    sndauto = rsWelc("autoplay")

    response.write "<tr><td align=left class=main>"
    if sndembed = "true" then
        response.write "<embed src=""files/" & rsWelc("snd_file") & """ autoplay=""" & sndauto & """ loop=""" & sndloop & """ height=""44"" width=""100"">&nbsp;" & rsWelc("snd_name")
    else
        response.write "<embed src=""files/" & rsWelc("snd_file") & """ autoplay=""" & sndauto & """ loop=""" & sndloop & """>&nbsp;" & rsWelc("snd_name")
    end if
    response.write "</td></tr>"
end if
 
Old August 3rd, 2003, 03:37 PM
Authorized User
 
Join Date: Jun 2003
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
Default

One more thing is that I tried the IsNull function you advised me, and it seems that it's not NULL. It just doesn't show the field's value.

...sorry for messing this post's window...

Nick
 
Old August 3rd, 2003, 06:36 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 215
Thanks: 0
Thanked 0 Times in 0 Posts
Default

You missed the signifigant point in what I said. You need to execute your SQL Statement/proc in query analyser to see what kind of result you are getting. Find out the value of site_id (Response.Write site_id), then execute the sql statement in query analyser, ie:

Code:
SELECT welcome_page.title, welcome_page.body, welcome_page.displ_date,
    welcome_page.img_id, welcome_page.pos, welcome_page.border,
    welcome_page.caption, welcome_page.url, welcome_page.snd_id,
    welcome_page.embed, welcome_page.vid_id, users_imgs.img_file,
    users_imgs.img_width, users_imgs.img_height, users_imgs.img_alt,
    users_sounds.snd_file, users_sounds.snd_name, users_vids.vid_file,
    users_vids.vid_name
FROM welcome_page
    LEFT OUTER JOIN users_imgs ON
    users_imgs.img_id = welcome_page.img_id
    LEFT OUTER JOIN user_sounds ON
    users_sounds.snd_id = welcome_page.snd_id
    LEFT OUTER JOIN users_vids ON
    users_vids.vid_id = welcome_page.vid_id
WHERE welcome_page.site_id = <insert the site id here>
Secondly don't use Access style joins in SQL Server. For one thing they are harder to understand. Secondly they may or may not work.

Get back to me with the results of that.

regards
David Cameron
 
Old August 4th, 2003, 08:28 AM
Authorized User
 
Join Date: Jun 2003
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
Default

David, with my answer I meant that I run the query (the one you see in my code) in Query Analyzer, and returned all results fine. Sorry for not being specific.

I thought that maybe the Access-type joins were a problem, but why are they working in Query Analyzer properly, and not throughmy ASP page?

regards

Nick
 
Old August 4th, 2003, 06:17 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 215
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Just to confirm, you ran it with exactly the same site_id and the results were different? If so, I'm out of ideas. I can only suggest that there is something simple that you might have overlooked.

regards
David Cameron





Similar Threads
Thread Thread Starter Forum Replies Last Post
complex query g_vamsi_krish SQL Language 3 February 27th, 2006 10:48 AM
Help need with designing this complex query method Access 2 July 1st, 2005 05:29 AM
query too complex [email protected] Access 3 August 28th, 2003 09:01 AM
COMPLEX QUERY PROBLEM nikosdra Classic ASP Databases 2 July 28th, 2003 02:13 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.