Detecting records in db

BarryMVS · Sep 2, 2004

Hi all,

I have a database table which contains lots of records.

When I run a page in my asp script, I need it to do a check on the current SQL lookup recordset to see if there are any records that match the SQL query.
Depending on the results, depends on the html code that gets displayed next.

I have tried this, but I still get an error saying no records found.

Code:

strsql4 = "SELECT * FROM ([PC Software] LEFT JOIN [PC Hardware] ON [PC Software].pcid = [PC Hardware].id) LEFT JOIN Software ON [PC Software].softwareid = Software.id WHERE [PC Hardware].id = " & ID & " ORDER BY AppMan, AppName, AppVer;"
	set rs4 = conn.Execute(strsql4)

' Check to see if there is any software allocaited to this PC.
	
If not rs4.BOF then
%>
<!-- Show software list -->
<html>...
<%
	Do While Not rs4.EOF
	%>
	<tr><td class="normal"><%=rs4.Fields("AppMan").Value%>&nbsp;<%=rs4.Fields("AppName").Value%></td><td class="normal"><%=rs4.Fields("AppVer").Value%></td><td class="normal"><%=rs4.Fields("ActivationKey").Value%></td></tr>
	<%
	rs4.MoveNext
	Loop
	%>
...</html>
<%
else
%>
<!-- No software allocaited -->
<html>
<head>...
...</html>
<%
end if

Any ideas would be most welcomed.

Thanks,

Barry

ICT Network Administrator
IT Services Manager

amorous · Sep 2, 2004

Try this:

strsql4 = "SELECT * FROM [PC Software] LEFT JOIN ([PC Hardware] ON [PC Software].pcid = [PC Hardware].id LEFT JOIN Software ON [PC Software].softwareid = Software.id ) WHERE [PC Hardware].id = " & ID & " ORDER BY AppMan, AppName, AppVer;"

observe the parentheses

-VJ

BarryMVS · Sep 2, 2004

VJ,

I copy & pasted your sql code above, into my asp page in place of my original code, saved and ran the page.

It returned the following error:
[red]Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error in JOIN operation.[/red]
Any ideas?

Thanks,

Barry

ICT Network Administrator
IT Services Manager

amorous · Sep 2, 2004

Try this:

strsql4 = "SELECT * FROM [PC Software] LEFT JOIN ( ([PC Hardware] ON [PC Software].pcid = [PC Hardware].id) LEFT JOIN (Software ON [PC Software].softwareid = Software.id ) ) WHERE [PC Hardware].id = " & ID & " ORDER BY AppMan, AppName, AppVer;"

-VJ

BarryMVS · Sep 2, 2004

VJ,

Thanks but it returned the same error.

I tried changing the order around to the following, but this just gave me the same error as I started with.

Code:

Code:

strsql4 = "SELECT * FROM ([PC Software] LEFT JOIN Software ON [PC Software].softwareid = Software.id) LEFT JOIN [PC Hardware] ON [PC Software].pcid = [PC Hardware].id WHERE [PC Hardware].id = " & ID & " ORDER BY AppMan, AppName, AppVer;"

Error:
[red]ADODB.Field error '80020009'

Either BOF or EOF is True, or the current record has been deleted; the operation requested by the application requires a current record.[/red]

Any other ideas?

Cheers,

Barry

ICT Network Administrator
IT Services Manager

amorous · Sep 2, 2004

That error means that you are getting an empty recordset...

can you post some of your data and the kind of result you are looking for...

may be we can try changing the query

-VJ

BarryMVS · Sep 2, 2004

VJ,

In my first posting I said:

I have a database table which contains lots of records.

When I run a page in my asp script, I need it to do a check on the current SQL lookup recordset to see if there are any records that match the SQL query.
Depending on the results, depends on the html code that gets displayed next.

I know that their may not be any records in the recordset, which is why I need to check this before running the lookup code.

Hope this helps.

Cheers,

Barry

ICT Network Administrator
IT Services Manager

amorous · Sep 2, 2004

Ok then i guess the second query is working fine but not getting any records...so change your code to something like this:

Code:

[b]strsql4 = "SELECT * FROM [PC Software] LEFT JOIN ( ([PC Hardware] ON [PC Software].pcid = [PC Hardware].id)  LEFT JOIN (Software ON [PC Software].softwareid = Software.id ) ) WHERE [PC Hardware].id = " & ID & " ORDER BY AppMan, AppName, AppVer;"[/b]
    set rs4 = conn.Execute(strsql4)

' Check to see if there is any software allocaited to this PC.
    

[b]
If rs4.BOF AND rs4.EOF then

Response.Write "There are no matched records"

Else
[/b]
%>
<!-- Show software list -->
<html>...
<%
    Do While Not rs4.EOF
    %>
    <tr><td class="normal"><%=rs4.Fields("AppMan").Value%>&nbsp;<%=rs4.Fields("AppName").Value%></td><td class="normal"><%=rs4.Fields("AppVer").Value%></td><td class="normal"><%=rs4.Fields("ActivationKey").Value%></td></tr>
    <%
    rs4.MoveNext
    Loop
    %>
...</html>

-VJ

BarryMVS · Sep 3, 2004

VJ,

Thanks for the BOF and EOF code.

Unfortunatlly, it is still returning the missing records error.

I'm not getting an error with the SQL statement so that is OK, but I still can't get the record check to work.

Any other ideas are most welcomed.

Thanks,

Barry

ICT Network Administrator
IT Services Manager

ChrisHirst · Sep 3, 2004

I do think using OR would be better, even though both would be true on an empty recordset.

If rs4.BOF OR rs4.EOF then ...

is the code dropping through to the else part or is the RS getting requested outside the else scope ?

Chris.

Indifference will be the downfall of mankind, but who cares?

http://www.candsdesign.co.uk

A website that proves the cobblers kids adage.

http://www.cram-system.com

Nightclub counting systems

So long, and thanks for all the fish.

BarryMVS · Sep 3, 2004

Chris,

Thanks, but I'm still getting the error.

Could it be that I have a rs4 lookup inside the IF...ELSE?

I'm at a loss as to how to solve this. I know there is a way of checking for records, but I just can not work out what it is!

Any ideas are most welcomed.

Thanks,

Barry

ICT Network Administrator
IT Services Manager

ChrisHirst · Sep 3, 2004

there is two common ways of checking for records,

one is a described above (check for EOF and/or BOF being true) the other is to use RS.recordcount. However this method requires the use of a client-side cursor and can have a negative impact on performance.

What you need to do is check the line number the error is happening on. I would think that this will be outside the if... then... else construct and because of the lack of code indenting is difficult to see.

Chris.

Indifference will be the downfall of mankind, but who cares?

http://www.candsdesign.co.uk

A website that proves the cobblers kids adage.

http://www.cram-system.com

Nightclub counting systems

So long, and thanks for all the fish.

spod · Sep 3, 2004

as a matter of interest, how many rows does the SQL return when you run it direct in Access?

BarryMVS · Sep 3, 2004

Chris,

The error is returned without a line number, but a ? instead. As you can imagine, this makes the dianostics some what frustrating, but I thought this might be caused by the rs4 lookup rather than the EOF or BOF, but the rs4 lookup is inside the if...else!

To save all the explaining, I have included the full code for this part of my page. (See below.)

Spod,

In answer to your question, the SQL will return 0 records as there are none set up to match this search, which is why I need to do this check so when this problem arises, instead of getting an error, my code will just offer me the option of creating the first record.

Code of page:

Code:

strsql4 = "SELECT * FROM ([PC Software] LEFT JOIN [PC Hardware] ON [PC Software].pcid = [PC Hardware].id) LEFT JOIN Software ON [PC Software].softwareid = Software.id WHERE [PC Hardware].id = " & ID & " ORDER BY AppMan, AppName, AppVer;"

	set rs4 = conn.Execute(strsql4)

' Check to see if there is any software allocaited to this PC.
	
If rs4.BOF or rs4.EOF then
%>
<!-- No software allocaited -->
<html>
<head>
	<title>MVS LAN : <%=pagetitle%> - </title>
    <!--#include virtual="/inc/scriptlinks.htm"-->
</head> 
<body><!--  onLoad="document.forms[0].elements[2].focus();" -->
<!--#include virtual="/inc/csheader.bks"-->
<table>
<form name="allsw" action="<%=scriptn%>" method="post">
    <input type="hidden" name="clive" value="7">
	<input type="hidden" name="ID" value="<%=rs("id")%>">
    <tr>
		<td class="normal">Software allocaited to PC - <%=rs4("MachineName")%></td>
	</tr>
	<tr>
		<td class="sub">There is no software allocaited to this PC.</td>
	</tr>
	<tr>
		<td><input type="hidden" name="fromid" value="8"><input type="submit" value="Allocate software to this PC" class="normal"></td>
	</tr>
</form>
</table>
<!--#include virtual="/inc/footer.bks"-->
</body>
</html>
<%
else
%>
<!-- Show software list -->
<html>
<head>
	<title>MVS LAN : <%=pagetitle%> - </title>
    <!--#include virtual="/inc/scriptlinks.htm"-->
</head> 
<body><!--  onLoad="document.forms[0].elements[2].focus();" -->
<!--#include virtual="/inc/csheader.bks"-->
<table>
<form name="adduser" action="<%=scriptn%>" method="post">
    <input type="hidden" name="clive" value="5">
	<input type="hidden" name="ID" value="<%=rs("id")%>">
    <tr>
		<td class="normal">Software allocaited to PC - <%=rs4("MachineName")%></td>
	</tr>
	<tr>
		<th class="sub" align="left">Software Name</th><th class="sub" align="left">Software Version</th><th class="sub" align="left">Software Activation Key</th>
	</tr>
	<%
	Do While Not rs4.EOF
	%>
	<tr><td class="normal"><%=rs4.Fields("AppMan").Value%>&nbsp;<%=rs4.Fields("AppName").Value%></td><td class="normal"><%=rs4.Fields("AppVer").Value%></td><td class="normal"><%=rs4.Fields("ActivationKey").Value%></td></tr>
	<%
	rs4.MoveNext
	Loop
	%>
	<tr><td><input type="submit" value="Return to PC Specification" class="normal"></td><td>&nbsp;</td></tr>
</form>
	<tr>
		<td><form name="allsw" action="<%=scriptn%>" method="post"><input type="hidden" name="clive" value="7"><input type="hidden" name="fromid" value="8"><input type="hidden" name="ID" value="<%= rs("id")%>"><input type="submit" value="Allocate software to this PC" class="normal"></td></form><td>&nbsp;</td>
	</tr>
</table>
<!--#include virtual="/inc/footer.bks"-->
</body>
</html>
<%
end if

Hope this helps.

Any ideas most welcomed.

Thanks,

Barry

ICT Network Administrator
IT Services Manager

ChrisHirst · Sep 3, 2004

Erm

Code:

If rs4.BOF or rs4.EOF then
%>
<!-- No software allocaited -->
<html>
<head>
    <title>MVS LAN : <%=pagetitle%> - </title>
    <!--#include virtual="/inc/scriptlinks.htm"-->
</head> 
<body><!--  onLoad="document.forms[0].elements[2].focus();" -->
<!--#include virtual="/inc/csheader.bks"-->
<table>
<form name="allsw" action="<%=scriptn%>" method="post">
    <input type="hidden" name="clive" value="7">
    <input type="hidden" name="ID" value="<%=rs("id")%>">
    <tr>
[red]        <td class="normal">Software allocaited to PC - <%=rs4("MachineName")%></td>
[/red]    </tr>
    <tr>
        <td class="sub">There is no software allocaited to this PC.</td>

Maybe this should be

Code:

<%=rs("MachineName")%>

Chris.

Indifference will be the downfall of mankind, but who cares?

http://www.candsdesign.co.uk

A website that proves the cobblers kids adage.

http://www.cram-system.com

Nightclub counting systems

So long, and thanks for all the fish.

BarryMVS · Sep 3, 2004

[red]DO'H![/red]

OK, I'm just going to go and bury my head in some sand somewhere.

I think I've been looking at this page to much over the last couple of weeks!

Thanks for all your help.

Sorry it was something so stupid.

Cheers and take care,

Barry

ICT Network Administrator
IT Services Manager

ChrisHirst · Sep 3, 2004

No Problem

Know the feeling only too well. Wish I could say I've never done something similar. [hairpull3]

Chris.

Indifference will be the downfall of mankind, but who cares?

http://www.candsdesign.co.uk

A website that proves the cobblers kids adage.

http://www.cram-system.com

Nightclub counting systems

So long, and thanks for all the fish.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Detecting records in db

BarryMVS

IS-IT--Management

amorous

Programmer

BarryMVS

IS-IT--Management

amorous

Programmer

BarryMVS

IS-IT--Management

amorous

Programmer

BarryMVS

IS-IT--Management

amorous

Programmer

BarryMVS

IS-IT--Management

ChrisHirst

IS-IT--Management

BarryMVS

IS-IT--Management

ChrisHirst

IS-IT--Management

spod

Programmer

BarryMVS

IS-IT--Management

ChrisHirst

IS-IT--Management

BarryMVS

IS-IT--Management

ChrisHirst

IS-IT--Management

Similar threads

Part and Inventory Search

Sponsor