SQL query problem

[Alex C]

Hey,

I'm running this SQL Query in my ASP page,

' Create command string
sqlCommandUpdateFields = _
" SELECT" _
& " NoticeBoardID" _
& " , NoticeBoardNm" _
& " , NoticeBoard.DivisionID" _
& " , NoticeBoard.HomeBaseID" _
& " , CheckIn" _
& " , CheckOut" _
& " , Location" _
& " , DueDate" _
& " , DueTime" _
& " , Title" _
& " , OfficePhNo" _
& " , MobilePhNo" _
& " , Division.DivisionNm" _
& " , HomeBase.HomeBaseNm" _
& " FROM" _
& " NoticeBoard" _
& " INNER JOIN Division" _
& " ON NoticeBoard.DivisionID=Division.DivisionID" _
& " INNER JOIN HomeBase" _
& " ON NoticeBoard.HomeBaseID=HomeBase.HomeBaseID" _
& " WHERE" _
& " NoticeBoardID=" & RecordNumber

Then I'm outputting some of these values in a table with this code,

<table width="100%"><tr><td colspan="2">
<font size="4"><b>Employee Information</b></font>
<br><br>
</td></tr>
<tr><td>
<font size="3"><b>Title:</b></font>
</td><td>
<%=RsUpdate("Title")%>
</td></tr>
<tr><td>
<font size="3"><b>Division:</b></font>
</td><td>
<%=RsUpdate("DivisionNm")%>
</td></tr>
<tr><td>
<font size="3"><b>Home base:</b></font>
</td><td>
<%=RsUpdate("HomeBaseNm")%>
</td></tr>
<tr><td>
<font size="3"><b>Office No:</b></font>
</td><td>
<%=RsUpdate("OfficePhNo")%>
</td></tr>
<tr><td>
<font size="3"><b>Mobile No:</b></font>
</td><td>
<%=RsUpdate("MobilePhNo")%>
</td></tr>
<tr><td>
<font size="3"><b>Email Address:</b></font>
</td><td>
email address
</td></tr></table>

But there seems to be a problem with my query because none of the values are shown. Everything works fine without the second INNER JOIN, NoticeBoard.HomeBaseID and HomeBase.HomeBaseNm lines. I'm wondering if this is my problem, can anyone see what I'm doing wrong?


Thanks,
Alex

[zingmatter]

Shouldn't the FROM part of the SQL read:


& " FROM" _
& " NoticeBoard, HomeBase" _


i.e. you missed out 'Homebase'

Hope this helps

[Alex C]

Originally posted by zingmatter
Shouldn't the FROM part of the SQL read:


& " FROM" _
& " NoticeBoard, HomeBase" _


i.e. you missed out 'Homebase'

Hope this helps
I'm not sure that is my problem. I think INNER JOIN's are supposed to take care of the linking to the other 2 tables.


Regards,
Alex

[zingmatter]

You're probably right, just when I do inner joins I would write:

SELECT tab1.foo, tab2.bar, tab3.foo
FROM tab1, tab2, tab3
WHERE tab1.tab1id = tab2.tab1id
AND tab1.tab3id = tab3.tab3id
AND tab1.tab1id = X

if that makes any sense.

[lmf232s]

after you open a connection to your database do something like this to see if any records are returned

If RsUpdate.EOF then
response.write "END OF FILE"
else
I = 1
Do While not RsUpdate.EOF
response.write I
RsUpdate.Movenext
I = I + 1
loop
end if


Could be you are returning no records. Your Inner Join looks ok but it may have something to do with not having correct ID's that you are using to link the tables.

[zingmatter]

Have you tried using

response.write sqlCommandUpdateFields

and copy/paste into your database directly - certainly with Access it can be more specific about any typos, empty parameters or just tell you if the SQL itself if OK