On the following page I get this error:
Microsoft OLE DB Provider for SQL Server error '80040e10'

Procedure 'spSearchProducts' expects parameter '@SearchParam', which was not supplied.

/search.asp, line 95

The code works on my local computer but on the web it does not.
Probably something to do with the IIS version (hosted on W2000)


Code bellow:

<%

Dim spSearch__SearchParam
spSearch__SearchParam = "cd player"
if(Request("SearchParam") <> "") then spSearch__SearchParam = Request("SearchParam")

%>
<%

set spSearch = Server.CreateObject("ADODB.Command")
spSearch.ActiveConnection = MM_storeSQL_STRING
spSearch.CommandText = "dbo.spSearchProducts"
spSearch.Parameters.Append spSearch.CreateParameter("@RETURN_VALUE", 3, 4)
spSearch.Parameters.Append spSearch.CreateParameter("@SearchParam", 200, 1,255,spSearch__SearchParam)
spSearch.CommandType = 4
spSearch.CommandTimeout = 0
spSearch.Prepared = true
set rsSearch = spSearch.Execute
rsSearch_numRows = 0

%>

<%
Dim Repeat2__numRows
Dim Repeat2__index

Repeat2__numRows = 10
Repeat2__index = 0
rsSearch_numRows = rsSearch_numRows + Repeat2__numRows
%>
<%
' *** Recordset Stats, Move To Record, and Go To Record: declare stats variables

Dim rsSearch_total
Dim rsSearch_first
Dim rsSearch_last

' set the record count
rsSearch_total = rsSearch.RecordCount

' set the number of rows displayed on this page
If (rsSearch_numRows < 0) Then
  rsSearch_numRows = rsSearch_total
Elseif (rsSearch_numRows = 0) Then
  rsSearch_numRows = 1
End If

' set the first and last displayed record
rsSearch_first = 1
rsSearch_last = rsSearch_first + rsSearch_numRows - 1

' if we have the correct record count, check the other stats
If (rsSearch_total <> -1) Then
  If (rsSearch_first > rsSearch_total) Then
    rsSearch_first = rsSearch_total
  End If
  If (rsSearch_last > rsSearch_total) Then
    rsSearch_last = rsSearch_total
  End If
  If (rsSearch_numRows > rsSearch_total) Then
    rsSearch_numRows = rsSearch_total
  End If
End If
%>
<%
' *** Recordset Stats: if we don't know the record count, manually count them

If (rsSearch_total = -1) Then

  ' count the total records by iterating through the recordset
  rsSearch_total=0
  While (Not rsSearch.EOF)
    rsSearch_total = rsSearch_total + 1
    rsSearch.MoveNext
  Wend

  ' reset the cursor to the beginning
  If (rsSearch.CursorType > 0) Then
    rsSearch.MoveFirst
  Else
    rsSearch.Requery
  End If

  ' set the number of rows displayed on this page
  If (rsSearch_numRows < 0 Or rsSearch_numRows > rsSearch_total) Then
    rsSearch_numRows = rsSearch_total
  End If

  ' set the first and last displayed record
  rsSearch_first = 1
  rsSearch_last = rsSearch_first + rsSearch_numRows - 1

  If (rsSearch_first > rsSearch_total) Then
    rsSearch_first = rsSearch_total
  End If
  If (rsSearch_last > rsSearch_total) Then
    rsSearch_last = rsSearch_total
  End If

End If
%>
<%
' *** Move To Record and Go To Record: declare variables

Dim MM_rs
Dim MM_rsCount
Dim MM_size
Dim MM_uniqueCol
Dim MM_offset
Dim MM_atTotal
Dim MM_paramIsDefined

Dim MM_param
Dim MM_index

Set MM_rs = rsSearch
MM_rsCount = rsSearch_total
MM_size = rsSearch_numRows
MM_uniqueCol = ""
MM_paramName = ""
MM_offset = 0
MM_atTotal = false
MM_paramIsDefined = false
If (MM_paramName <> "") Then
  MM_paramIsDefined = (Request.QueryString(MM_paramName) <> "")
End If
%>
<%
' *** Move To Record: handle 'index' or 'offset' parameter

if (Not MM_paramIsDefined And MM_rsCount <> 0) then

  ' use index parameter if defined, otherwise use offset parameter
  MM_param = Request.QueryString("index")
  If (MM_param = "") Then
    MM_param = Request.QueryString("offset")
  End If
  If (MM_param <> "") Then
    MM_offset = Int(MM_param)
  End If

  ' if we have a record count, check if we are past the end of the recordset
  If (MM_rsCount <> -1) Then
    If (MM_offset >= MM_rsCount Or MM_offset = -1) Then ' past end or move last
      If ((MM_rsCount Mod MM_size) > 0) Then ' last page not a full repeat region
        MM_offset = MM_rsCount - (MM_rsCount Mod MM_size)
      Else
        MM_offset = MM_rsCount - MM_size
      End If
    End If
  End If

  ' move the cursor to the selected record
  MM_index = 0
  While ((Not MM_rs.EOF) And (MM_index < MM_offset Or MM_offset = -1))
    MM_rs.MoveNext
    MM_index = MM_index + 1
  Wend
  If (MM_rs.EOF) Then
    MM_offset = MM_index ' set MM_offset to the last possible record
  End If

End If
%>
<%
' *** Move To Record: if we dont know the record count, check the display range

If (MM_rsCount = -1) Then

  ' walk to the end of the display range for this page
  MM_index = MM_offset
  While (Not MM_rs.EOF And (MM_size < 0 Or MM_index < MM_offset + MM_size))
    MM_rs.MoveNext
    MM_index = MM_index + 1
  Wend

  ' if we walked off the end of the recordset, set MM_rsCount and MM_size
  If (MM_rs.EOF) Then
    MM_rsCount = MM_index
    If (MM_size < 0 Or MM_size > MM_rsCount) Then
      MM_size = MM_rsCount
    End If
  End If

  ' if we walked off the end, set the offset based on page size
  If (MM_rs.EOF And Not MM_paramIsDefined) Then
    If (MM_offset > MM_rsCount - MM_size Or MM_offset = -1) Then
      If ((MM_rsCount Mod MM_size) > 0) Then
        MM_offset = MM_rsCount - (MM_rsCount Mod MM_size)
      Else
        MM_offset = MM_rsCount - MM_size
      End If
    End If
  End If

  ' reset the cursor to the beginning
  If (MM_rs.CursorType > 0) Then
    MM_rs.MoveFirst
  Else
    MM_rs.Requery
  End If

  ' move the cursor to the selected record
  MM_index = 0
  While (Not MM_rs.EOF And MM_index < MM_offset)
    MM_rs.MoveNext
    MM_index = MM_index + 1
  Wend
End If
%>
<%
' *** Move To Record: update recordset stats

' set the first and last displayed record
rsSearch_first = MM_offset + 1
rsSearch_last = MM_offset + MM_size

If (MM_rsCount <> -1) Then
  If (rsSearch_first > MM_rsCount) Then
    rsSearch_first = MM_rsCount
  End If
  If (rsSearch_last > MM_rsCount) Then
    rsSearch_last = MM_rsCount
  End If
End If

' set the boolean used by hide region to check if we are on the last record
MM_atTotal = (MM_rsCount <> -1 And MM_offset + MM_size >= MM_rsCount)
%>
<%
' *** Move To Record: set the strings for the first, last, next, and previous links

Dim MM_keepMove
Dim MM_moveParam
Dim MM_moveFirst
Dim MM_moveLast
Dim MM_moveNext
Dim MM_movePrev

Dim MM_urlStr
Dim MM_paramList
Dim MM_paramIndex
Dim MM_nextParam

MM_keepMove = MM_keepBoth
MM_moveParam = "index"

' if the page has a repeated region, remove 'offset' from the maintained parameters
If (MM_size > 1) Then
  MM_moveParam = "offset"
  If (MM_keepMove <> "") Then
    MM_paramList = Split(MM_keepMove, "&")
    MM_keepMove = ""
    For MM_paramIndex = 0 To UBound(MM_paramList)
      MM_nextParam = Left(MM_paramList(MM_paramIndex), InStr(MM_paramList(MM_paramIndex),"=") - 1)
      If (StrComp(MM_nextParam,MM_moveParam,1) <> 0) Then
        MM_keepMove = MM_keepMove & "&" & MM_paramList(MM_paramIndex)
      End If
    Next
    If (MM_keepMove <> "") Then
      MM_keepMove = Right(MM_keepMove, Len(MM_keepMove) - 1)
    End If
  End If
End If

' set the strings for the move to links
If (MM_keepMove <> "") Then
  MM_keepMove = MM_keepMove & "&"
End If

MM_urlStr = Request.ServerVariables("URL") & "?" & MM_keepMove & MM_moveParam & "="

MM_moveFirst = MM_urlStr & "0"
MM_moveLast = MM_urlStr & "-1"
MM_moveNext = MM_urlStr & CStr(MM_offset + MM_size)
If (MM_offset - MM_size < 0) Then
  MM_movePrev = MM_urlStr & "0"
Else
  MM_movePrev = MM_urlStr & CStr(MM_offset - MM_size)
End If
%>

L Ion

__________________
L Ion

Hi

I've got exactly the same problem.

I have a Stored Procedure which is supplied with 4 form variables.
The recordset returns great, however, when I try and create dynamic navigation (Dreamweaver MX 2004), I get the message:-

Procedure {myprocname} expects parameter @CountryID which was not supplied.

The line of the error is exactly at the same point as in your example (so I did'nt bother to post my bad example too !

Anyone know how to get round this ?

I am not sure why Dreamweaver generates this code, and whether it makes sense or not. However, since the recordset is a result of the Execute method of the Command object, you could use this instead of the Requery line:

set rsSearch = spSearch.Execute

It's a bit of a work around for possibly larger, more structural problem, but it might do the trick.

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Thanks Imar...

Unfortunately, although this gets me past the browser fatal error, the nav. buttons do not work and strangely, one of the recordset results goes missing !!!

Anyone else out there know why Dreamweaver does this with the code ?

IS there a simpler way to navigate through a repeating region populated with a recordset ?

Yes, there definitely is.

The code that Dreamweaver creates tries to cater for all possible situations. It has code to support paging, and a page count nav bar. However, the code is quite verbose and pretty slow. I never quite understood why MM didn't rewrite some of their server behaviors with newer releases of DW.

To display the total number of records, it tries to get a RecordCount. To get a RecordCount, the recordset internally uses MoveNext until the end of the recordset is reached and then uses MoveFirst to reposition the cursor.
If the recordset does not support navigation backwards, Dreamweaver still uses MoveNext to get the count and then requeries the database for the initial records (this is where Requery is used).
This is a very slow way to get a count because a) you fire the same query twice and b) you use MoveNext to go to the end.

If you're willing to drop the Dreamweaver generated code and use slimmer and faster paging code look here: http://www.asp101.com/samples/db_paging.asp

It's a basic 101 paging example, but IMO, it's much cleaner than DW's verbose code.

It should be possible to get this to work with the Dreamweaver code as well. However, since there is a lot of code, and I don't have your test environment here to try it out, I can't figure out what the problem is for you.

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: Brand New You're Retro by Tricky (Track 8 from the album: Maxinquaye) What's This?

Well, I had a try to change the code as in the ASP example you supplied the link to, however, I seemed to mess it up !

I'll try again tomorrow, however...

Thanks again for your time Imar