Error 80040e14 - expects parameter which was not supplied 1

[Dabase]

Hi,

I am getting the following error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Procedure 'sp_All_Contract_Jobs_In_Specific_Dates_AD' expects parameter '@DateFrom', which was not supplied.

../CJ_Dates_by_All_AD.asp, line 87

The above error only occurs when I press next to go to Page 2 of the recordset.

Page 1 displays correctly.

Below is the SP:

create  procedure sp_All_Contract_Jobs_In_Specific_Dates_AD
	@User varchar(40),
	@DateFrom smalldatetime, 
	@DateTo	smalldatetime
as

create table #Temp (
[Username] [nvarchar] (100),
[group_id] [char] (4),
[parent_id] [char] (4),
[group_name] [nvarchar] (50)
) ON [PRIMARY]

insert into #Temp ([Username],
[group_id],
[parent_id],
[group_name]
) 

select u.[user_name] , g.[group_id], g.[parent_id], g.[group_name]
from uslist u
left outer join usgroup g on group_id = u.teamcode
where u.exstaff = 0


select 
vposition as [Vacancy Position],
company as [Company Name],
(rtrim(ltrim(firstname)) + ' ' + rtrim(ltrim(surname))) as [Contact Name], 
u.[user_name] as [Created By],
g.group_name as [Team],
convert(nvarchar (30), [dateenter], 106) as [Date Created]
from convacs c
left outer join uslist u on c.[enterby] = u.[usercode]
left outer join usgroup g on u.[teamcode] = g.[group_id]
where ([dateenter] between @DateFrom and @DateTo)
and u.[user_name] in 
	(select distinct(Username) from #Temp t
left outer join usgroup g on g.parent_id = t.parent_id
left outer join AD a on a.displayname = ltrim(rtrim(g.[group_name]))
where ('ASP\' + a.samaccountname = @User))
order by g.group_name, u.[user_name], dateenter

Below is the ASP code:

					<% 
						Dim dbCVP
						Dim sql
						Dim RSCVP
						Dim CmdTemp
						Dim CVPUser
						Dim Count
						Dim i
						Dim Color
						Dim Page_Size
						Dim Current_Page
						Dim Page_Count
						Dim Date_From
						Dim Date_To

						Page_Size = 20

						If Request("Page") = "" Then
							Current_Page = 1
						Else
							Current_Page = CInt(Request("Page"))
						End If

						Set dbCVP = Server.createObject("ADODB.Connection")
						dbCVP.open Application("ConnectionString")
						Set CmdTemp = Server.CreateObject("ADODB.Command")
						set rsCVP = server.createobject("adodb.recordset")
						set CVPUser = (request.servervariables("LOGON_USER"))
						set Date_From = request("DateFrom")
						set Date_To = request("DateTo")

						rsCVP.CursorLocation = adUseClient
						rsCVP.Pagesize = Page_Size
						rsCVP.CursorType = adOpenstatic

						cmdTemp.CommandType = adCmdStoredProc
						cmdTemp.ActiveConnection = dbCVP
						cmdTemp.CommandText = "sp_All_Contract_Jobs_In_Specific_Dates_AD"
						cmdTemp.parameters.append cmdtemp.createParameter("@User", advarchar, adParamInput, 40, CVPUser)
						cmdTemp.parameters.append cmdtemp.createParameter("@DateFrom", advarchar, adParamInput, 10, Date_From)
						cmdTemp.parameters.append cmdtemp.createParameter("@DateTo", advarchar, adParamInput, 10, Date_To)
						
						
						rsCVP.Open cmdTemp
						
						Page_Count = rsCVP.PageCount

						Count = rsCVP.RecordCount
						
						If 1 > Current_Page Then Current_Page = 1
						If Current_Page > Page_Count Then Current_Page = Page_Count

					%>



						<center> <p class="style3">  	<%
										If rsCVP.BOF = True AND rsCVP.EOF = True Then
										Response.Write("| No records found! |")
										Response.End
										End If		 
										%> </p> </center>

					<%

						rsCVP.AbsolutePage = Current_Page
										
					%>

						<center> <p class="style3"> | <% Response.Write("Total number of entries found: " & Count) %> | </p> </center>

  
				<table border="0" class="style3" align="center">

					<tr>
						<td><b>Vacancy Position</b></td>
						<td><b>Company Name</b></td>
						<td><b>Contact Name</b></td>
						<td><b>Created by</b></td>
						<td><b>Team</b></td>
						<td><b>Date Created</b></td>
					</tr>


					<%
					i = 0
					while not rsCVP.eof
						if i Mod 2 = 0 then
							color = "#F8F8F8"
						else
							color = "#CCCCCC"
						end if	

							response.write("<tr bgcolor=" & color & "><td>" & rsCVP("Vacancy Position") & "</td><td>" & rsCVP("Company Name") & "</td><td>" & rsCVP("Contact Name") & "</td><td>" & rsCVP("Created By") & "</td><td>" & rsCVP("Team") & "</td><td>" & rsCVP("Date Created") & "</td></tr>") 					
							i = i + 1

					rsCVP.movenext
					wend
					%>

	
				</table>	

					<center> <p class="style3">

					<%
						If Current_Page <> 1 Then
							Response.Write "<a href=""CJ_Dates_by_All_AD.asp?Page="
							Response.Write Current_Page - 1

							' Where an image is available for Next Page use the line below 
							'Response.Write """><img src=""../prev_1.gif"" border=""0""></a>" & vbCrLf

							Response.Write """>Previous Page</a>" & vbCrLf
							Response.Write " " & vbCrLf
						End If

						If Current_Page < Page_Count Then
							Response.Write "<a href=""CJ_Dates_by_All_AD.asp?Page="
							Response.Write Current_Page + 1

							' Where an image is available for Previous Page use the line below
							'Response.Write """><img src=""../next_1.gif"" border=""0""></a>" & vbCrLf

							Response.Write """>Next Page</a>" & vbCrLf
						End IF


					%>

					</p></center>

					<center> <p class="style3"> | <% Response.Write ("Total number of pages: " & rsCVP.PageCount) %> | </p></center>


		</body>

</html>

Below is line 87:

rsCVP.Open cmdTemp

Any help on this is greatly appreciated.

Thanks
Dabase

 

[neilharris]

the problem lies with this code:

If Current_Page < Page_Count Then
     Response.Write "<a href=""CJ_Dates_by_All_AD.asp?Page="
     Response.Write Current_Page + 1

the querystring only passes through a page number which means on page 2 onwards:

set Date_From = request("DateFrom")
set Date_To = request("DateTo")

will result in Date_From and Date_To both being null.
The querystrings in the page navigator buttons need to include the DateFrom and DateTo values as well as the page number.

 

[Dabase]

Hi neilharris,

Thanks for the quick response.

I am still getting the same error after including the following code:

							Response.Write "<a href=""CJ_Dates_by_All_AD.asp?Page="
							Response.Write Current_Page + 1
							Response.Write "&@DateFrom="
							Response.Write Date_from

                           					Response.Write """>Next Page</a>" & vbCrLf

Thanks
Dabase

 

[Lothario]

ok i think here is the problem...

you are getting your Date_From variable in the following way...

set Date_From = request("DateFrom")

ie...you are getting from a form...in order for the paging to work and this variable to pass on to all the pages...make sure you use GET method instead of POST method...

which would then change your Date_From retrieval to...

set Date_From = request.QueryString("DateFrom")

Let me know if you have any other problems....

-L

 

[neilharris]

You have an unnecessary @ sign in that, it should read like this:

Response.Write "<a href=""CJ_Dates_by_All_AD.asp?Page="
                            Response.Write Current_Page + 1
                            Response.Write "&DateFrom="
                            Response.Write Date_from
                            Response.Write "&DateTo="
                            Response.Write Date_to
                                               Response.Write """>Next Page</a>" & vbCrLf

 

[Dabase]

Hi neilharris & Lothario,

I tried your suggestion and pages 2,3,etc... are now being displayed correctly. It was not necessary to change the form method to GET.

Thank you both for your time.

I have, however, a similar problem on a different page:

Chunks of the ASP code:

					<% 

						Dim dbCVP
						Dim sql
						Dim RSCVP
						Dim CmdTemp
						Dim Count
						Dim i
						Dim Color
						Dim Page_Size
						Dim Current_Page
						Dim Page_Count
						Dim Mgr

...

set Mgr = request("ManagerName")

...

cmdTemp.CommandText = "sp_Manager_Contract_Jobs_In_Last_Week_D"
						cmdTemp.parameters.append cmdtemp.createParameter("@Manager", advarchar, adParamInput, 40, Mgr)

...

If Current_Page < Page_Count Then
							Response.Write "<a href=""CJ_Last_Week_by_Manager_D.asp?Page="
							Response.Write Current_Page + 1
							Response.Write "&Manager="
							Response.Write Mgr 

						Response.Write """>Next Page</a>" & vbCrLf
End IF

Again, with the above code, the first page is displayed correctly, but when next is clicked the following message is displayed:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Procedure 'sp_Manager_Contract_Jobs_In_Last_Week_D' expects parameter '@Manager', which was not supplied.

../CJ_Last_Week_by_Manager_D.asp, line 84

Line 84:

rsCVP.Open cmdTemp

Below is the text displayed on the browser address box in Page 2:

../CJ_Last_Week_by_Manager_D.asp?Page=2&Manager=Joe%20Bloggs

Once again thanks for the help on this.

Thanks
Dabase

 

[neilharris]

Your querystring variable is named incorrectly, it should read:

Response.Write "<a href=""CJ_Last_Week_by_Manager_D.asp?Page="
                            Response.Write Current_Page + 1
                            Response.Write "&ManagerName="
                            Response.Write Mgr

 

[Dabase]

Hi neilharris,

Thank you for pointing that mistake to me. I changed the code and it is now working beautifully!!




Thanks
Dabase