|
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
|
|
|
July 29th, 2003, 06:31 PM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 215
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|
August 3rd, 2003, 03:24 PM
|
Authorized User
|
|
Join Date: Jun 2003
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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 " "
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""> " & rsWelc("snd_name")
else
response.write "<embed src=""files/" & rsWelc("snd_file") & """ autoplay=""" & sndauto & """ loop=""" & sndloop & """> " & rsWelc("snd_name")
end if
response.write "</td></tr>"
end if
|
August 3rd, 2003, 03:37 PM
|
Authorized User
|
|
Join Date: Jun 2003
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|
August 3rd, 2003, 06:36 PM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 215
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|
August 4th, 2003, 08:28 AM
|
Authorized User
|
|
Join Date: Jun 2003
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|
August 4th, 2003, 06:17 PM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 215
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|
|
|