How can display the sql before the error occurs?
 

I'm getting this error on my asp/access page. It's a sql error
triggered by Recordset1.open sql,conn,1,1. The sql is constructed from
user input on a search page.

ADODB.Recordset error '800a0bb9'
Arguments are of the wrong type, are out of acceptable range, or are
in conflict with one another.

The problem I'm having is getting a Response.Write(sql) to actually
display the sql code before the open statement.

Can someone please help me display my generated sql code?

Thank you!

Re: How can display the sql before the error occurs?
 

"Big Moxy" <bigmoxy@gmail.com> wrote in message
news:5d976162-57a4-4548-99c5-1924ad41e414@d4g2000prg.googlegroups.com...
> I'm getting this error on my asp/access page. It's a sql error
> triggered by Recordset1.open sql,conn,1,1. The sql is constructed from
> user input on a search page.
>
> ADODB.Recordset error '800a0bb9'
> Arguments are of the wrong type, are out of acceptable range, or are
> in conflict with one another.
>
> The problem I'm having is getting a Response.Write(sql) to actually
> display the sql code before the open statement.
>
> Can someone please help me display my generated sql code?
>


Try using a Response.End directly after the Response.Write sql

--
Anthony Jones - MVP ASP/ASP.NET

Re: How can display the sql before the error occurs?
 

On Nov 29, 3:26 pm, "Anthony Jones" <A...@yadayadayada.com> wrote:
> "Big Moxy" <bigm...@gmail.com> wrote in message
>
> news:5d976162-57a4-4548-99c5-1924ad41e414@d4g2000prg.googlegroups.com...
>
> > I'm getting this error on my asp/access page. It's a sql error
> > triggered by Recordset1.open sql,conn,1,1. The sql is constructed from
> > user input on a search page.
>
> > ADODB.Recordset error '800a0bb9'
> > Arguments are of the wrong type, are out of acceptable range, or are
> > in conflict with one another.
>
> > The problem I'm having is getting a Response.Write(sql) to actually
> > display the sql code before the open statement.
>
> > Can someone please help me display my generated sql code?
>
> Try using a Response.End directly after the Response.Write sql
>
> --
> Anthony Jones - MVP ASP/ASP.NET

I'm sorry to say that it doesn't stop at the Response.End(). The code
continues until the error occurs.

Re: How can display the sql before the error occurs?
 

Big Moxy wrote:
> On Nov 29, 3:26 pm, "Anthony Jones" <A...@yadayadayada.com> wrote:
>> "Big Moxy" <bigm...@gmail.com> wrote in message
>>
>> news:5d976162-57a4-4548-99c5-1924ad41e414@d4g2000prg.googlegroups.com...
>>
>>> I'm getting this error on my asp/access page. It's a sql error
>>> triggered by Recordset1.open sql,conn,1,1. The sql is constructed
>>> from user input on a search page.
>>
>>> ADODB.Recordset error '800a0bb9'
>>> Arguments are of the wrong type, are out of acceptable range, or are
>>> in conflict with one another.
>>
>>> The problem I'm having is getting a Response.Write(sql) to actually
>>> display the sql code before the open statement.

The error you are getting is unlikely to be caused by the sql string you are
using. It is more likely to be a problem with the arguments used for the
Open statement.

>>
>>> Can someone please help me display my generated sql code?
>>
>> Try using a Response.End directly after the Response.Write sql
>>

> I'm sorry to say that it doesn't stop at the Response.End(). The code
> continues until the error occurs.

And you have the Response.Write statement BEFORE the Open statement? You are
wasting your time and ours by failing to show us the code that's giving you
the problem.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: How can display the sql before the error occurs?
 

On Nov 29, 4:05 pm, "Bob Barrows [MVP]" <reb01...@NOyahoo.SPAMcom>
wrote:
> Big Moxy wrote:
> > On Nov 29, 3:26 pm, "Anthony Jones" <A...@yadayadayada.com> wrote:
> >> "Big Moxy" <bigm...@gmail.com> wrote in message
>
> >>news:5d976162-57a4-4548-99c5-1924ad41e414@d4g2000prg.googlegroups.com...
>
> >>> I'm getting this error on my asp/access page. It's a sql error
> >>> triggered by Recordset1.open sql,conn,1,1. The sql is constructed
> >>> from user input on a search page.
>
> >>> ADODB.Recordset error '800a0bb9'
> >>> Arguments are of the wrong type, are out of acceptable range, or are
> >>> in conflict with one another.
>
> >>> The problem I'm having is getting a Response.Write(sql) to actually
> >>> display the sql code before the open statement.
>
> The error you are getting is unlikely to be caused by the sql string you are
> using. It is more likely to be a problem with the arguments used for the
> Open statement.
>
>
>
> >>> Can someone please help me display my generated sql code?
>
> >> Try using a Response.End directly after the Response.Write sql
>
> > I'm sorry to say that it doesn't stop at the Response.End(). The code
> > continues until the error occurs.
>
> And you have the Response.Write statement BEFORE the Open statement? You are
> wasting your time and ours by failing to show us the code that's giving you
> the problem.
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"- Hide quoted text -
>
> - Show quoted text -

This is the code that builds the SQL.

Response.Write(Request.ServerVariables("REQUEST_ME THOD") & "<br>")
If (Request.ServerVariables("REQUEST_METHOD") = "POST") Then
If Request.Form("ctlSearchField") <> "" Then
Dim strWhere, strQuote
If (IsNumeric(ctlSearchFor) AND ctlSearchOption <> "LIKE" AND
ctlSearchOption <> "HAS") Then
strQuote = ""
Else
strQuote = "'"
End If
Response.Write("strQuote=" & strQuote & "x<br>")

If ctlSearchOption = "LIKE" Then
ctlSearchFor = ctrlSearchFor & "*"
End If
If ctlSearchOption = "HAS" Then
ctlSearchOption = "LIKE"
ctlSearchFor = "*" & ctrlSearchFor & "*"
End If
Response.Write("ctlSearchOption=" & ctlSearchOption & "+<br>")
Response.Write("ctlSearchFor=" & ctlSearchFor & "+<br>")
If ctlSearchField = "*" Then
strWhere = "ID " & ctlSearchOption & " " & strQuote & ctlSearchFor &
strQuote & " OR " &_
"[Equipment Description] " & ctlSearchOption & " " & strQuote &
ctlSearchFor & strQuote & " OR " &_
"Day " & ctlSearchOption & " " & strQuote & ctlSearchFor &
strQuote & " OR " &_
"Week " & ctlSearchOption & " " & strQuote & ctlSearchFor &
strQuote & " OR " &_
"Month " & ctlSearchOption & " " & strQuote & ctlSearchFor &
strQuote & " "
Else
strWhere = "WHERE " & ctlSearchField & " " & ctlSearchOption & " " &
strQuote & ctlSearchFor & strQuote & " "
End If
Response.Write("strWhere=" & strWhere & "+<br>")
Response.End()
sql = "SELECT * FROM [" & tables(tableID) & "] " &_
"WHERE " & strWhere &_
"ORDER BY ID ASC"
Response.Write(sql & "<br>")
Response.End()

End If
Else
sql = "SELECT * FROM [" & tables(tableID) & "] ORDER BY ID ASC"
Response.Write(sql & "<br>")
End If

Recordset1.open sql,conn,1,1

If not Recordset1.eof then
Recordset1_total = Recordset1.recordcount
Else
Recordset1_total = 0
End if

Recordset1_numRows = Recordset1_total

This is the seach form:

<form action="db.asp?ID=<%=tableID%>" method="post" name="search"
target="_self">
<TABLE width="500" align="center" border="1">
<TBODY>
<TR>
<TD vAlign="middle" align="center"><h3><B>Search for: </B></h3></TD>
</TR>
<TR>
<TD align="left">
<select id="ctlSearchField">
<option value="*">Any field</option>
<option value="ID">ID</option>
<option value='Equipment Description'>Equipment Description</option>
<option value='Day'>Day</option>
<option value='Week'>Week</option>
<option value='Month'>Month</option>
</select>
</TD>
</TR>
<TR>
<TD align="left">
<select id="ctlSearchOption">
<option value="HAS">Contains</option>
<option value="=">Equals</option>
<option value="LIKE">Starts with ...</option>
<option value=">">More than ...</option>
<option value="<">Less than ...</option>
<option value=">=">Equal or more than ...</option>
<option value="<=">Equal or less than ...</option>
</select>
</TD>
</TR>
<TR>
<TD align="left"><INPUT name="ctlSearchFor" type="text"
id="ctlSearchFor" size="40">
</TD>
</TR>
<TR>
<TD align="left"><INPUT type="submit" value="Search">&nbsp;<INPUT
type="submit" value="Show all">
</TD>
</TR>
</TBODY>
</TABLE>
</form>

Re: How can display the sql before the error occurs?
 

Big Moxy wrote:
> sql = "SELECT * FROM [" & tables(tableID) & "] ORDER BY ID ASC"
> Response.Write(sql & "<br>")
> End If

You need the Response.End right here, immediately before the Open statement

>
> Recordset1.open sql,conn,1,1
>
> If not Recordset1.eof then
> Recordset1_total = Recordset1.recordcount
> Else
> Recordset1_total = 0
> End if
>
> Recordset1_numRows = Recordset1_total
>

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: How can display the sql before the error occurs?
 

On Nov 29, 4:47 pm, "Bob Barrows [MVP]" <reb01...@NOyahoo.SPAMcom>
wrote:
> Big Moxy wrote:
> > sql = "SELECT * FROM [" & tables(tableID) & "] ORDER BY ID ASC"
> > Response.Write(sql & "<br>")
> > End If
>
> You need the Response.End right here, immediately before the Open statement
>
>
>
> > Recordset1.open sql,conn,1,1
>
> > If not Recordset1.eof then
> > Recordset1_total = Recordset1.recordcount
> > Else
> > Recordset1_total = 0
> > End if
>
> > Recordset1_numRows = Recordset1_total
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"

I placed it where I did because the initial page display uses sql =
"SELECT * FROM [" & tables(tableID) & "] ORDER BY ID ASC" which works
just fine. I will never get the search option if the Response.End is
placed where you suggest.

Re: How can display the sql before the error occurs?
 

Big Moxy wrote:

>
> I placed it where I did because the initial page display uses sql =
> "SELECT * FROM [" & tables(tableID) & "] ORDER BY ID ASC" which works
> just fine. I will never get the search option if the Response.End is
> placed where you suggest.

I don't get you. if you want to troubleshoot the sql statement, then you
have to print it out before it is executed ...
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: How can display the sql before the error occurs?
 

On Nov 29, 5:10 pm, "Bob Barrows [MVP]" <reb01...@NOyahoo.SPAMcom>
wrote:
> Big Moxy wrote:
>
> > I placed it where I did because the initial page display uses sql =
> > "SELECT * FROM [" & tables(tableID) & "] ORDER BY ID ASC" which works
> > just fine. I will never get the search option if the Response.End is
> > placed where you suggest.
>
> I don't get you. if you want to troubleshoot the sql statement, then you
> have to print it out before it is executed ...
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"

Perhaps you are not understanding the sequence of events.

The page with the problem serves two purposes.

1. It displays a tabular list of data
2. Underneath the list is a search option -

-Select a field to search on
-Select the type of search
-Click the search button

The initial display is generated by an HTTP GET so the initial SQL is
"SELECT * FROM [" & tables(tableID) & "] ORDER BY ID ASC" and that
ALWAYS works. The problem is with the SQL generated from the search
form.

A search results display is generated by an HTTP POST of the search
form so it follows the IF "POST" Then logic.

There is ONE open statement for both page entry points. Besides what
is wrong is conditionally executing the Response.End? Having the
Response.End inside of an If statement should work. The concept works
in other languages I have coded in. Is VBscript an exception?

If <some condition> Then
build sql statement
response.write(sql)
response.end
Else
build sql statement
End If
Recordset.open...

So as you can see if I place the Response.End after the End If and
before the Open I will never get a display.

Re: How can display the sql before the error occurs?
 

On Nov 29, 5:36 pm, Big Moxy <bigm...@gmail.com> wrote:
> On Nov 29, 5:10 pm, "Bob Barrows [MVP]" <reb01...@NOyahoo.SPAMcom>
> wrote:
>
> > Big Moxy wrote:
>
> > > I placed it where I did because the initial page display uses sql =
> > > "SELECT * FROM [" & tables(tableID) & "] ORDER BY ID ASC" which works
> > > just fine. I will never get the search option if the Response.End is
> > > placed where you suggest.
>
> > I don't get you. if you want to troubleshoot the sql statement, then you
> > have to print it out before it is executed ...
> > --
> > Microsoft MVP - ASP/ASP.NET
> > Please reply to the newsgroup. This email account is my spam trap so I
> > don't check it very often. If you must reply off-line, then remove the
> > "NO SPAM"
>
> Perhaps you are not understanding the sequence of events.
>
> The page with the problem serves two purposes.
>
> 1. It displays a tabular list of data
> 2. Underneath the list is a search option -
>
> -Select a field to search on
> -Select the type of search
> -Click the search button
>
> The initial display is generated by an HTTP GET so the initial SQL is
> "SELECT * FROM [" & tables(tableID) & "] ORDER BY ID ASC" and that
> ALWAYS works. The problem is with the SQL generated from the search
> form.
>
> A search results display is generated by an HTTP POST of the search
> form so it follows the IF "POST" Then logic.
>
> There is ONE open statement for both page entry points. Besides what
> is wrong is conditionally executing the Response.End? Having the
> Response.End inside of an If statement should work. The concept works
> in other languages I have coded in. Is VBscript an exception?
>
> If <some condition> Then
> build sql statement
> response.write(sql)
> response.end
> Else
> build sql statement
> End If
> Recordset.open...
>
> So as you can see if I place the Response.End after the End If and
> before the Open I will never get a display.

NEVER MIND. I WILL SIMPLY SPLIT THE PAGES AND TROUBLESHOOT THE SEARCH
RESULTS SEPARATELY.