Home > Archive > ASP > February 2006 > error if db search finds nothing
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
error if db search finds nothing
|
|
| abbylee26@hotmail.com 2006-02-22, 6:55 pm |
| my page works fine if the db search finds at least one record that
satifies the query.
but if it does not find a match I get the following error message.
Error Type:
ADODB.Field (0x80020009)
Either BOF or EOF is True, or the current record has been deleted.
Requested operation requires a current record.
What do I need to change here to make this just display the page with
no data?
sSQL = "SELECT [Billing Date], [Service Number], [Subscriber User
Name]," & _
" Fund, Account,[Activity Code], Description, [Total Charge]" & _
" FROM 06_Statements" & _
" WHERE [Billing Date] = '" & Request.Form("cmonth") & "'" & _
" AND [Service Number] = '" & Request.Form("cnumb") & "'" & _
" AND cstr([Total Charge]) = '" & cstr(Request.Form("camount")) & "'"
set rs = Connect.Execute(sSQL)
<%
Do until rs.eof
%>
<tr>
<td><%=rs("Service Number")%></td>
<td><%=rs("Subscriber User Name")%></td>
<td><%=rs("Fund")%></td>
<td><%=rs("Account")%></td>
<td><%=rs("Activity Code")%></td>
<td><%=rs("Description")%></td>
<td><%=rs("Total Charge")%></td>
</tr>
<% End If
rs.MoveNext
Loop %>
| |
| Paxton 2006-02-22, 6:55 pm |
|
abbyle...@hotmail.com wrote:
> my page works fine if the db search finds at least one record that
> satifies the query.
> but if it does not find a match I get the following error message.
>
> Error Type:
> ADODB.Field (0x80020009)
> Either BOF or EOF is True, or the current record has been deleted.
> Requested operation requires a current record.
>
> What do I need to change here to make this just display the page with
> no data?
>
> sSQL = "SELECT [Billing Date], [Service Number], [Subscriber User
> Name]," & _
> " Fund, Account,[Activity Code], Description, [Total Charge]" & _
> " FROM 06_Statements" & _
> " WHERE [Billing Date] = '" & Request.Form("cmonth") & "'" & _
> " AND [Service Number] = '" & Request.Form("cnumb") & "'" & _
> " AND cstr([Total Charge]) = '" & cstr(Request.Form("camount")) & "'"
> set rs = Connect.Execute(sSQL)
>
> <%
> Do until rs.eof
> %>
> <tr>
> <td><%=rs("Service Number")%></td>
> <td><%=rs("Subscriber User Name")%></td>
> <td><%=rs("Fund")%></td>
> <td><%=rs("Account")%></td>
> <td><%=rs("Activity Code")%></td>
> <td><%=rs("Description")%></td>
> <td><%=rs("Total Charge")%></td>
> </tr>
> <% End If
> rs.MoveNext
> Loop %>
If not rs.eof then
Do until rs.eof
%>
<tr>
<td><%=rs("Service Number")%></td>
<td><%=rs("Subscriber User Name")%></td>
<td><%=rs("Fund")%></td>
<td><%=rs("Account")%></td>
<td><%=rs("Activity Code")%></td>
<td><%=rs("Description")%></td>
<td><%=rs("Total Charge")%></td>
</tr>
<% End If
rs.MoveNext
Loop
Else
response.write "No records to show"
End If
%>
/P.
| |
| Paxton 2006-02-22, 6:55 pm |
|
Paxton wrote:
> abbyle...@hotmail.com wrote:
>
> If not rs.eof then
> Do until rs.eof
> %>
> <tr>
> <td><%=rs("Service Number")%></td>
> <td><%=rs("Subscriber User Name")%></td>
> <td><%=rs("Fund")%></td>
> <td><%=rs("Account")%></td>
> <td><%=rs("Activity Code")%></td>
> <td><%=rs("Description")%></td>
> <td><%=rs("Total Charge")%></td>
> </tr>
> <% End If
> rs.MoveNext
> Loop
> Else
> response.write "No records to show"
> End If
> %>
>
> /P.
Oops. I didn't spot an odd 'End If' already in your code - just before
rs.movenext. You need to remove that - it serves no purpose that I can
see in the snippet.
/P.
| |
| abbylee26@hotmail.com 2006-02-22, 6:55 pm |
| that didn't work even after I removed my "End If"
I got the error:
Error Type:
Microsoft VBScript compilation (0x800A040E)
'loop' without 'do'
| |
| Paxton 2006-02-22, 6:55 pm |
|
abbyle...@hotmail.com wrote:
> that didn't work even after I removed my "End If"
> I got the error:
> Error Type:
> Microsoft VBScript compilation (0x800A040E)
> 'loop' without 'do'
What's your revised code?
/P.
| |
| abbylee26@hotmail.com 2006-02-22, 6:55 pm |
| If not rs.eof then
Do until rs.eof
%>
<tr>
<td><%=rs("Service Number")%></td>
<td><%=rs("Subscriber User Name")%></td>
<td><%=rs("Fund")%></td>
<td><%=rs("Account")%></td>
<td><%=rs("Activity Code")%></td>
<td><%=rs("Description")%></td>
<td><%=rs("Total Charge")%></td>
</tr>
<%
rs.MoveNext
Loop
Else
response.write "No records to show"
End If
%>
I still get the
Error Type:
Microsoft VBScript compilation (0x800A040E)
'loop' without 'do'
| |
| Paxton 2006-02-22, 6:55 pm |
| abbylee26@hotmail.com wrote in news:1140642626.234880.326860
@g14g2000cwa.googlegroups.com:
> If not rs.eof then
> Do until rs.eof
> %>
> <tr>
> <td><%=rs("Service Number")%></td>
> <td><%=rs("Subscriber User Name")%></td>
> <td><%=rs("Fund")%></td>
> <td><%=rs("Account")%></td>
> <td><%=rs("Activity Code")%></td>
> <td><%=rs("Description")%></td>
> <td><%=rs("Total Charge")%></td>
> </tr>
> <%
> rs.MoveNext
> Loop
> Else
> response.write "No records to show"
> End If
> %>
>
>
> I still get the
> Error Type:
> Microsoft VBScript compilation (0x800A040E)
> 'loop' without 'do'
>
>
I think we need more of your code. There's something else going on. It
could be that the End If you removed closed an if statement you started
somewhere else, although the original position of it is strange. Could you
post all of it?
/P.
| |
| abbylee26@hotmail.com 2006-02-22, 6:55 pm |
| Wait...I've been cutting everything I didn't think was really part of
the code. Here's the full thing.
<%
Dim sBackgroundColor
sBackgroundColor = "#ffffff"
If not rs.eof then
Do until rs.eof
If rs("Service Number") <> "" Then
If sBackgroundColor = "#ffffff" Then
sBackgroundColor = "#f7f7f7"
Else
sBackgroundColor = "#ffffff"
End If
%>
<tr bgcolor="<%=sBackgroundColor%>">
<td class="pagefont"><%=rs("Service Number")%></td>
<td class="pagefont"><%=rs("Subscriber User Name")%></td>
<td class="pagefont"><div align="center"><%=rs("Fund")%></div></td>
<td class="pagefont"><div
align="center"><%=rs("Account")%></div></td>
<td class="pagefont"><div align="center"><%=rs("Activity
Code")%></div></td>
<td class="pagefont"><%=rs("Description")%></td>
<td class="pagefont"><div align="center"><%=rs("Total
Charge")%></div></td>
</tr>
<%
End If
rs.MoveNext
Loop
End If
%>
| |
| Paxton 2006-02-22, 6:55 pm |
| abbylee26@hotmail.com wrote in news:1140643867.672604.95680
@g14g2000cwa.googlegroups.com:
> Wait...I've been cutting everything I didn't think was really part of
> the code. Here's the full thing.
>
> <%
> Dim sBackgroundColor
> sBackgroundColor = "#ffffff"
>
> If not rs.eof then
> Do until rs.eof
>
> If rs("Service Number") <> "" Then
> If sBackgroundColor = "#ffffff" Then
> sBackgroundColor = "#f7f7f7"
> Else
> sBackgroundColor = "#ffffff"
> End If
> %>
> <tr bgcolor="<%=sBackgroundColor%>">
> <td class="pagefont"><%=rs("Service Number")%></td>
> <td class="pagefont"><%=rs("Subscriber User Name")%></td>
> <td class="pagefont"><div align="center"><%=rs("Fund")%></div></td>
> <td class="pagefont"><div
> align="center"><%=rs("Account")%></div></td>
> <td class="pagefont"><div align="center"><%=rs("Activity
> Code")%></div></td>
> <td class="pagefont"><%=rs("Description")%></td>
> <td class="pagefont"><div align="center"><%=rs("Total
> Charge")%></div></td>
> </tr>
> <%
> End If
> rs.MoveNext
> Loop
> End If
> %>
>
>
The errant End If, before rs.movenext needs to be moved under 'Loop'. so
the last 5 lines will be
<%
rs.Movenext
Loop
End if
End if
Incidentally, your logic concerning the backgound colour will always result
in a back ground with '#f7f7f7'.
/P.
| |
| Paxton 2006-02-22, 6:55 pm |
| Paxton <paxtonend@hotmail.com> wrote in
news:Xns9772DC0AE767mytokenM@195.188.240.200:
> abbylee26@hotmail.com wrote in news:1140643867.672604.95680
> @g14g2000cwa.googlegroups.com:
>
>
> The errant End If, before rs.movenext needs to be moved under 'Loop'.
> so the last 5 lines will be
>
> <%
> rs.Movenext
> Loop
> End if
> End if
>
Or... depending on what condition you are using to change the background
colour, that End If might belong further up:
If rs("Service Number") <> "" Then
If sBackgroundColor = "#ffffff" Then
sBackgroundColor = "#f7f7f7"
Else
sBackgroundColor = "#ffffff"
End If
End If <--- here
but your background will still always be grey. You set it to white
earlier on, then you code says, if it's white (which it is) then change
it to grey, otherwise make it white. The otherwise part of the code
will never be run.
/P.
| |
| abbylee26@hotmail.com 2006-02-22, 6:55 pm |
| <%
rs.Movenext
Loop
End if
End if
I still get the
Error Type:
Microsoft VBScript compilation (0x800A040E)
'loop' without 'do'
My background works good every other line is white, gray...always
starting with white.
| |
| abbylee26@hotmail.com 2006-02-22, 6:55 pm |
| I will try this in the morning...thanks
| |
| Paxton 2006-02-22, 6:55 pm |
| abbylee26@hotmail.com wrote in news:1140645063.400561.61390
@f14g2000cwb.googlegroups.com:
> I will try this in the morning...thanks
>
If you still have problems, post the code for the whole page. There's more
going on than the selected snippets you've shown so far.
/P.
| |
| dNagel 2006-02-22, 6:55 pm |
| I believe the correct logic for recordsets is to use
While Not (rs.BOF or rs.EOF)
...
rs.movenext
Wend
D.
Paxton wrote:
> abbylee26@hotmail.com wrote in news:1140645063.400561.61390
> @f14g2000cwb.googlegroups.com:
>
>
>
>
> If you still have problems, post the code for the whole page. There's more
> going on than the selected snippets you've shown so far.
>
> /P.
| |
| Paxton 2006-02-23, 3:55 am |
|
dNagel wrote:
> I believe the correct logic for recordsets is to use
>
> While Not (rs.BOF or rs.EOF)
> ...
> rs.movenext
> Wend
>
Neither correct or incorrect - just another alternative looping
structure - although the VBScript reference material provided by
Microsoft recommends the use of do... loop over while... wend
http://msdn.microsoft.com/library/d...56c7d54ed17.asp
Personally, with recordsets, I always use GetRows() and For... Next.
/P.
| |
| abbylee26@hotmail.com 2006-02-23, 6:55 pm |
| The code is still having problems. If the number 0.05 is in the db and
I search for either 0.05 or .05 I get all records with that number. If
0.05 is not in the db I get the error message Error Type:
ADODB.Field (0x80020009)
Either BOF or EOF is True, or the current record has been deleted.
Requested operation requires a current record.
Here is my full code:
<!--#include file="include.asp" -->
<%
Dim connect, rs
Dim sSQL
Set connect = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateOBject("ADODB.Recordset")
connect.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sTelecom
connect.open
%>
<%
Dim TheAmount
Set TheAmount = Request.Form("thefield")
sSQL = "SELECT [Billing Date], [Service Number], [Subscriber User
Name]," & _
" Fund, Account,[Activity Code], Description, [Total Charge]" & _
" FROM 06_Statements" & _
" WHERE [Billing Date] = '" & Request.Form("cmonth") & "'" & _
" AND [Service Number] = '" & Request.Form("cnumb") & "'" & _
" AND CDbl([Total Charge]) = '" & CDbl(Request.Form("camount")) & "'"
set rs = Connect.Execute(sSQL)
End If
%>
<html>
<head>
<title>Untitled Document</title>
<link href="../../documents.css" rel="stylesheet" type="text/css">
</head>
<body bgcolor="#ffffff">
<table border="0" align="center" cellpadding="2" cellspacing="0">
<tr>
<td colspan="7"><div align="center" class="menuHeader"><font
color="#FF0000">Statements</font></div></td>
</tr>
<tr>
<td class="menuHeader2">Billing Date</td>
<td class="menuHeader2" colspan="6"><%=rs("Billing Date")%></td>
</tr>
<tr>
<td colspan="7"> </td>
</tr>
<tr>
<td height="20" width="60"> <nobr>
<div class="menuHeader2">Service Number </div>
</nobr></td>
<td width="60"> <nobr><div class="menuHeader2">Subscriber User
Name</div></nobr></td>
<td width="60"> <div class="menuHeader2">
<div align="center">Fund</div>
</div></td>
<td width="30"> <div class="menuHeader2">Account</div></td>
<td width="60"> <div class="menuHeader2">
<div align="center">Activity</div>
</div></td>
<td width="60"> <div class="menuHeader2">Description</div></td>
<td width="60"> <div class="menuHeader2">Amount</div></td>
</tr>
<%
Dim sBackgroundColor
sBackgroundColor = "#ffffff"
If not rs.eof then
Do until rs.eof
If rs("Service Number") <> "" Then
If sBackgroundColor = "#ffffff" Then
sBackgroundColor = "#f7f7f7"
Else
sBackgroundColor = "#ffffff"
End If
End If
%>
<tr bgcolor="<%=sBackgroundColor%>">
<td class="pagefont"><%=rs("Service Number")%></td>
<td class="pagefont"><%=rs("Subscriber User Name")%></td>
<td class="pagefont"><div align="center"><%=rs("Fund")%></div></td>
<td class="pagefont"><div
align="center"><%=rs("Account")%></div></td>
<td class="pagefont"><div align="center"><%=rs("Activity
Code")%></div></td>
<td class="pagefont"><%=rs("Description")%></td>
<td class="pagefont"><div align="center"><%=rs("Total
Charge")%></div></td>
</tr>
<%
rs.MoveNext
Loop
End If
%>
</table>
</body>
</html>
| |
| dNagel 2006-02-23, 6:55 pm |
| You need to check for EOF or BOF... you're only checking for EOF...
Do while not (rs.eof or rs.bof)
D.
abbylee26@hotmail.com wrote:
> The code is still having problems. If the number 0.05 is in the db and
> I search for either 0.05 or .05 I get all records with that number. If
> 0.05 is not in the db I get the error message Error Type:
> ADODB.Field (0x80020009)
> Either BOF or EOF is True, or the current record has been deleted.
> Requested operation requires a current record.
>
> Here is my full code:
>
> <!--#include file="include.asp" -->
> <%
> Dim connect, rs
> Dim sSQL
>
> Set connect = Server.CreateObject("ADODB.Connection")
> Set rs = Server.CreateOBject("ADODB.Recordset")
> connect.ConnectionString = _
> "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source=" & sTelecom
> connect.open
> %>
> <%
> Dim TheAmount
> Set TheAmount = Request.Form("thefield")
>
> sSQL = "SELECT [Billing Date], [Service Number], [Subscriber User
> Name]," & _
> " Fund, Account,[Activity Code], Description, [Total Charge]" & _
> " FROM 06_Statements" & _
> " WHERE [Billing Date] = '" & Request.Form("cmonth") & "'" & _
> " AND [Service Number] = '" & Request.Form("cnumb") & "'" & _
> " AND CDbl([Total Charge]) = '" & CDbl(Request.Form("camount")) & "'"
>
> set rs = Connect.Execute(sSQL)
>
> End If
> %>
>
> <html>
> <head>
> <title>Untitled Document</title>
> <link href="../../documents.css" rel="stylesheet" type="text/css">
> </head>
>
> <body bgcolor="#ffffff">
> <table border="0" align="center" cellpadding="2" cellspacing="0">
> <tr>
> <td colspan="7"><div align="center" class="menuHeader"><font
> color="#FF0000">Statements</font></div></td>
> </tr>
> <tr>
> <td class="menuHeader2">Billing Date</td>
> <td class="menuHeader2" colspan="6"><%=rs("Billing Date")%></td>
> </tr>
> <tr>
> <td colspan="7"> </td>
> </tr>
> <tr>
> <td height="20" width="60"> <nobr>
> <div class="menuHeader2">Service Number </div>
> </nobr></td>
> <td width="60"> <nobr><div class="menuHeader2">Subscriber User
> Name</div></nobr></td>
> <td width="60"> <div class="menuHeader2">
> <div align="center">Fund</div>
> </div></td>
> <td width="30"> <div class="menuHeader2">Account</div></td>
> <td width="60"> <div class="menuHeader2">
> <div align="center">Activity</div>
> </div></td>
> <td width="60"> <div class="menuHeader2">Description</div></td>
> <td width="60"> <div class="menuHeader2">Amount</div></td>
> </tr>
> <%
> Dim sBackgroundColor
> sBackgroundColor = "#ffffff"
>
> If not rs.eof then
> Do until rs.eof
>
> If rs("Service Number") <> "" Then
> If sBackgroundColor = "#ffffff" Then
> sBackgroundColor = "#f7f7f7"
> Else
> sBackgroundColor = "#ffffff"
> End If
> End If
> %>
> <tr bgcolor="<%=sBackgroundColor%>">
> <td class="pagefont"><%=rs("Service Number")%></td>
> <td class="pagefont"><%=rs("Subscriber User Name")%></td>
> <td class="pagefont"><div align="center"><%=rs("Fund")%></div></td>
> <td class="pagefont"><div
> align="center"><%=rs("Account")%></div></td>
> <td class="pagefont"><div align="center"><%=rs("Activity
> Code")%></div></td>
> <td class="pagefont"><%=rs("Description")%></td>
> <td class="pagefont"><div align="center"><%=rs("Total
> Charge")%></div></td>
> </tr>
> <%
> rs.MoveNext
> Loop
> End If
> %>
> </table>
> </body>
> </html>
>
| |
| Bob Barrows [MVP] 2006-02-23, 6:55 pm |
| This is usually not necessary in an ASP page.
In a VB application where you are likely to be navigating both forward and
backward, yes, it is a good idea to check both. In a forward-only recordset,
which is the default, I cannot think of a case where it is necessary to
check BOF.
I strongly suspect the OP's query is not retrieving any records.
Bob Barrows
dNagel wrote:[color=darkred]
> You need to check for EOF or BOF... you're only checking for EOF...
>
> Do while not (rs.eof or rs.bof)
>
> D.
>
>
> abbylee26@hotmail.com wrote:
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
| |
| abbylee26@hotmail.com 2006-02-23, 6:55 pm |
| Yes, only when there are no records to report am I getting the error
message.
I need the page to come up but empty. or maybe a message "sorry no
records"
| |
| dNagel 2006-02-23, 6:55 pm |
|
Bob Barrows [MVP] wrote:
> This is usually not necessary in an ASP page.
> In a VB application where you are likely to be navigating both forward and
> backward, yes, it is a good idea to check both. In a forward-only recordset,
> which is the default, I cannot think of a case where it is necessary to
> check BOF.
>
> I strongly suspect the OP's query is not retrieving any records.
Which is why it's necessary to check BOF... BOF AND EOF are true when theres
an empty recordset.
D.
| |
| Bob Barrows [MVP] 2006-02-23, 6:55 pm |
| dNagel wrote:
> Bob Barrows [MVP] wrote:
>
> Which is why it's necessary to check BOF... BOF AND EOF are true
> when theres an empty recordset.
>
You did not read what I said. When a recordset is opened, if it contains any
records, it is already pointing at the first record. If it contains no
records, EOF will be true. Yes, BOF will also be true, but there is no need
to check for it. The only time you need to check for BOF is
a) if you delete records from the recordset (or in the case of a dynamic
cursor, a record is deleted from the database table)
b) if you attempt to MovePrevious or MoveFirst
Checking BOF immediately after opening a recordset is never necessary.
Checking BOF in a forward-only loop is never necessary. It will never be
true when moving forward.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
| |
| abbylee26@hotmail.com 2006-02-23, 6:55 pm |
| ok, I found the following from a google search
Another possible reason is that you are using a stored procedure that
operates on a temp table, or does other row-affecting operation prior
to your select statement. To get around this, issue the following at
the beginning of your stored procedure:
SET NOCOUNT ON
This will prevent "(n) row(s) affected" messages from being interpreted
by the provider as a resultset.
Could this be my problem...if so how do I put this in my code?
| |
| Bob Barrows [MVP] 2006-02-23, 6:55 pm |
| abbylee26@hotmail.com wrote:
> ok, I found the following from a google search
>
> Another possible reason is that you are using a stored procedure that
> operates on a temp table, or does other row-affecting operation prior
> to your select statement. To get around this, issue the following at
> the beginning of your stored procedure:
>
> SET NOCOUNT ON
>
> This will prevent "(n) row(s) affected" messages from being
> interpreted by the provider as a resultset.
>
>
> Could this be my problem
No, you are
1. not using a stored procedure
2. not using SQL Server
I am working on a reply containing code for you to test. Wait for it :-)
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
| |
| Bob Barrows [MVP] 2006-02-23, 6:55 pm |
| abbylee26@hotmail.com wrote:
> The code is still having problems. If the number 0.05 is in the db and
> I search for either 0.05 or .05 I get all records with that number. If
> 0.05 is not in the db I get the error message Error Type:
> ADODB.Field (0x80020009)
> Either BOF or EOF is True, or the current record has been deleted.
> Requested operation requires a current record.
Which line of code generates this error?
Let's reduce your code to its bare essentials to try and make it easier to
spot the problem. You should always start small. Don't try to do everything
at once. Create a new test page to try out the recommendations I am about to
make:
>
> Here is my full code:
>
> <!--#include file="include.asp" -->
> <%
> Dim connect, rs
> Dim sSQL
>
> Set connect = Server.CreateObject("ADODB.Connection")
> Set rs = Server.CreateOBject("ADODB.Recordset")
> connect.ConnectionString = _
> "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source=" & sTelecom
> connect.open
> %>
Why do you close the script block and open a new one here?
> <%
> Dim TheAmount
> Set TheAmount = Request.Form("thefield")
What is the purpose of this variable? It doesn't appear anywhere after this
point
>
> sSQL = "SELECT [Billing Date], [Service Number], [Subscriber User
> Name]," & _
Why return billing date and service number? You already have form variables
containing these values ...
> " Fund, Account,[Activity Code], Description, [Total Charge]" & _
> " FROM 06_Statements" & _
> " WHERE [Billing Date] = '" & Request.Form("cmonth") & "'" & _
> " AND [Service Number] = '" & Request.Form("cnumb") & "'" & _
> " AND CDbl([Total Charge]) = '" & CDbl(Request.Form("camount")) &
Do you really need to use CDbl([Total Charge]) here? Why?
And why are you surrounding the numeric value in the comparison with quotes
(which turns it into a string)? This makes absolutely no sense.
> "'"
>
> set rs = Connect.Execute(sSQL)
>
> End If
Why is this End If here? I don't see an If statement prior to this.
Let's simplify things now, as well as switching to using parameters (I
really can't stand dynamic sql<grin> ). Here is the code for the new test
page - obviously, i have not been able to test it to verify that no typos
were committed:
<!--#include file="include.asp" -->
<%
Dim connect, rs
Dim sSQL
Set connect = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateOBject("ADODB.Recordset")
connect.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sTelecom
connect.open
sSQL = "SELECT [Subscriber User Name]," & _
" Fund, Account,[Activity Code], Description, [Total Charge]" & _
" FROM 06_Statements" & _
" WHERE [Billing Date] = ? AND [Service Number] = ?" & _
" AND [Total Charge] = ?"
'The ?s are called parameter markers. We will use a command
'object to pass values to these markers
Dim cmd, arParms
'First create a variant array to contain the parameter values
arParms = Array(Request.Form("cmonth"),Request.Form("cnumb") _
Request.Form("camount"))
Set cmd=createobject("adodb.command")
cmd.commandtype=1 'adCmdText
cmd.commandtext=sSQL
set cmd.activeconnection=connect
set rs=cmd.execute(,arParms)
Dim arData, i,j, fld, sTitle
if not rs.eof then arData = rs.GetRows
sTitle = "<tr><th>"
for each fld in rs.Fields
sTitle=sTitle & fld.Name & "</th><th>"
next
sTitle = left(sTitle,len(sTitle)-4) & "</tr>"
rs.close: set rs = nothing
connect.close: set connect = nothing
if isArray(arData) then
response.write "<table border=""0"" align=""center""" & _
" cellpadding=""2"" cellspacing=""0""><tr>"
response.write sTitle
for i = 0 to ubound(arData,2)
response.write "<tr>"
for j = 0 to ubound(arData,1)
response.write "<td>" & arData(j,i) & "</td>"
next
response.write "</tr>"
next
response.write "</table>"
else
response.write "no records were returned"
end if
%>
See if this exhibits the same symptoms.
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
| |
| abbylee26@hotmail.com 2006-02-23, 6:55 pm |
| That works perfectly Bob!
Thank you all.
| |
| Bob Barrows [MVP] 2006-02-23, 6:55 pm |
| abbylee26@hotmail.com wrote:
> That works perfectly Bob!
> Thank you all.
Can you handle putting in your formatting code? And displaying the billing
date and service number?
Let us know if you need help with these things.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
|
|
|
|
|