Paging Recordset - Arguments Of Wrong Type

[rubertoga]

I've built the following page which gets input from a search form on another page. I'm getting the following error:

ADODB.Recordset (0x800A0BB9)
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
/wakeywakey/dispute.asp, line 156

Line 156 is: disputeSrcRS.Open strSQL3,conn1

I've checked the cursors i'm using, i've included the adovbs include file and i've checked for spelling mistakes. This code works fine in another page. I'm not sure what else it could be. The only other thing i think may be causing a problem is the fact that on this page the server variable "content_length" may already be set to eqaul more than 0 because of the data that has come from the search form whereas on the other page where the code works the only data that is posted comes from the navigation form. I've posted some of the ASP page code below. Its a monster i know. Any body got any Ideas?

<%
Dim conn1, userRS, acSrcRS, disputeSrcRS, strSQL, strSQL2, strSQL3, strConnection
Dim intRecordCount, strForename, strSurname, strAccountNo
Dim strAccessLevel, strLocation, intLastPage, intCurrentPage, intTotalPages, intI

Set conn1 = Server.CreateObject("ADODB.Connection")
strConnection = "DSN=wakeywaeky;Database=wakeywaeky;UID=;PWD=;"
conn1.Open strConnection

strSQL = "SELECT * FROM tblUser WHERE txtUserID = '" & strEnumber & "' "

Set userRS = conn1.Execute(strSQL)

IF userRS.BOF = false AND userRS.EOF = false THEN

strForename = userRS("txtForename")
strSurname = userRS("txtSurname")
strLocation = userRS("txtLocation")
strAccessLevel = userRS("bytAccessLevel")
%>

<%if Request.Form("txtAccount") <> "" Then
strAccountNo = Request.Form("txtAccount")
strSQL2 = "SELECT txtAccountNo FROM tblAccount WHERE txtAccountNo = ' " & strAccountNo & " '"
Set acSrcRS = conn1.Execute(strSQL2)
IF acSrcRS.BOF = false AND acSrcRS.EOF = false THEN
strSQL3 = "SELECT tblDispute.intDisputeID, tblDispute.txtAccountNo, tblAccount.txtAccountName, tblDispute.dtmDateRaised, tblReason.txtReasonDescription, tblDispute.bytCurrentStatusID, tblStatus.txtStatusDescription, tblDispute.dtmLastAction FROM tblStatus INNER JOIN (tblReason INNER JOIN (tblAccount INNER JOIN tblDispute ON tblAccount.txtAccountNo = tblDispute.txtAccountNo) ON tblReason.bytReasonCode = tblDispute.bytReasonCode) ON tblStatus.bytStatusCode = tblDispute.bytCurrentStatusID WHERE tblDispute.txtAccountNo = ' " & strAccountNo & " ' ORDER BY tblDispute.bytCurrentStatusID"
strSrcType = "Account"
strSrcValue = strAccountNo
End If

acSrcRS.Close
set acSrcRS = nothing

Else
strAccountNo = Request.Form("txtAccount")
Response.Write("Account <font style=""text-transform:uppercase""><b>" & strAccountNo & " </b></font>is not in the traffic plan.<br><br>If the account number is correct please contact an administrator for <a href=""help.asp"" target=_blank>help</a>.<br><br><br><br><br><br><br><br><br><br><br><br><br><br>")
End If

Const intPageSize = 3
If Request.ServerVariables("CONTENT_LENGTH") = 0 Then
intCurrentPage = 1
Else
intCurrentPage = CInt(Request.Form("CurrentPage"))
intLastPage = CInt(Request.Form("LastPage"))
Select Case Request.Form("Submit")
Case "Previous"
intCurrentPage = intCurrentPage - 1
Case "Next"
intCurrentPage = intCurrentPage + 1
Case "First"
intCurrentPage = 1
Case "Last"
intCurrentPage = intLastPage
End Select
End If

Set disputeSrcRS = Server.CreateObject("ADODB.Recordset")
disputeSrcRS.CursorLocation = adUseClient
disputeSrcRS.CursorType = adOpenStatic
disputeSrcRS.CacheSize = intPageSize

disputeSrcRS.Open strSQL3,conn1

IF disputeSrcRS.BOF = false AND disputeSrcRS.EOF = false THEN

disputeSrcRS.pageSize = intPageSize

If Not(disputeSrcRS.EOF) Then disputeSrcRS.AbsolutePage = intCurrentPage
intTotalPages = disputeSrcRS.PageCount
%>
<table width="100%" border="0">
<tr><td><br></td></tr>
<%For intI = 1 to disputeSrcRS.PageSize%>
<tr>
<td width="50%" colspan ="2" valign = "Top" bgcolor="#262626">
<b>Dispute ID: </b><%=disputeSrcRS("intDisputeID")%>
</td>
</tr>
<tr>
<td width="50%" valign = "Top">
<b>Account:</b> <%=disputeSrcRS("txtAccountNo")%>, <%=disputeSrcRS("txtAccountName")%><br>
<b>Date Raised:</b> <%=disputeSrcRS("dtmDateRaised")%><br>
<b>Reason:</b> <%=disputeSrcRS("txtReasonDescription")%>
<b>Current Status:</b> <%=disputeSrcRS("txtStatusDescription")%>
<b>Last Action:</b> <%=disputeSrcRS("dtmLastAction")%>
</td>
<td width="50%" valign = "Top">
<form action="history.asp" method="post">
<input type="hidden" value="<%=disputeSrcRS("intDisputeID")%>" name="txtDispute">
<input type="button" value="View History" name="Submit">
</form>
</td>
</tr>
<tr><td><br></td></tr>
<%
disputeSrcRS.MoveNext
If disputeSrcRS.EOF Then Exit For
Next
%>
<tr>
<td colspan ="2">Page <%= intCurrentPage%> of <%=intTotalPages%></td></tr>
<tr>
<td colspan="2">
<form action="<%= Request.ServerVariables("SCRIPT_NAME")%>" method="POST">
<input type="hidden" name="CurrentPage" value="<%=intCurrentPage%>">
<input type="hidden" name="LastPage" value="<%=intTotalPages%>">
<% If intCurrentPage >2 Then %>
<input type="submit" name="Submit" value="First">
<%End If%>
<% If intCurrentPage >1 Then %>
<input type="submit" name="Submit" value="Previous">
<%
End If
If intCurrentPage <> intTotalPages Then %>
<input type="submit" name="Submit" value="Next">
<%End If%>
<%If intCurrentPage < intTotalPages Then %>
<input type="submit" name="Submit" value="Last">
<% End If %>
</form>
</td>
</tr>
</table>

 

[FesterSXS]

I assume that tblDispute.txtAccountNo in your database is a text field

Tony
________________________________________________________________________________

http://www.SpiraxSarco.com

 

[rubertoga]

yes it is :0)

 

[FesterSXS]

are tblStatus.bytStatusCode and tblDispute.bytCurrentStatusID both the same data types in the database too?

Tony
________________________________________________________________________________

http://www.SpiraxSarco.com

 

[rubertoga]

Yes they are... tried an interesting test... I took the code out for the data from the search form on the previous page, substituting the acctount number instead of the strAccount variable in the SQL.It worked fine. Now i just need to work out where the problem is. Any ideas?

 

[FesterSXS]

probably not the problem but try taking out the spaces between the single and double quotes...

...WHERE tblDispute.txtAccountNo = ' " & strAccountNo & " '

Tony
________________________________________________________________________________

http://www.SpiraxSarco.com

 

[rubertoga]

I've solved this... basically it was the calling of Server Variable CONTENT_LENGTH. The data had already been posted from the search page so it was not setting the page number = 1.

I rquested the value of the submit button from the form as my marker for a value of 0 rather than the CONTENT_LENGTH variable and then added a hidden field in the form for the buttons to page through the recordset = to strAccountNo. Now have another issue... but if i can't work it out i'll be back