Hi,
I have requirement like the stored procedure should return two values(select and count). here is my stored procedure.
CREATE PROCEDURE [dbo].[GetCustomersPageWise]
@PageIndex INT = 1
,@PageSize INT = 10
,@Count INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT ROW_NUMBER() OVER
(
ORDER BY [CustId] ASC
)AS RowNumber
,[CustId]
,[Name]
,[Title]
INTO #Results
FROM [tbl_Customer]
SELECT @Count = COUNT(*)
FROM #Results
SELECT * FROM #Results
WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
DROP TABLE #Results
END
and the below is aspx page
<div>
PageSize:
<asp:DropDownList ID="ddlPageSize" runat="server" AutoPostBack="true" OnSelectedIndexChanged="PageSize_Changed">
<asp:ListItem Text="10" Value="10" />
<asp:ListItem Text="25" Value="25" />
<asp:ListItem Text="50" Value="50" />
<asp:ListItem Text="100" Value="100" />
</asp:DropDownList>
<hr />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false">
<Columns>
<asp:TemplateField HeaderText="Publication No" SortExpression="Publication_No">
<ItemTemplate>
<asp:HyperLink ID="NameLink" runat="server" Text='<%# Eval("Name") %>' Target="_blank" Font-Underline="false" ForeColor="Blue" Font-Bold="false"></asp:HyperLink>
</ItemTemplate>
<ItemStyle ForeColor="Blue" Width="100px"/>
</asp:TemplateField>
<asp:BoundField HeaderText=" CustId" DataField="CustId" />
<asp:BoundField HeaderText="Title" DataField="Title" />
</Columns>
</asp:GridView>
<br />
<asp:Repeater ID="rptPager" runat="server">
<ItemTemplate>
<asp:LinkButton ID="lnkPage" runat="server" Text = '<%#Eval("Text") %>' CommandArgument = '<%# Eval("Value") %>' Enabled = '<%# Eval("Enabled") %>' OnClick = "Page_Changed"></asp:LinkButton>
</ItemTemplate>
</asp:Repeater>
</div>
and code behind as follows
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
If Not IsPostBack Then
Me.GetCustomersPageWise(1)
End If
End Sub
Protected Sub PageSize_Changed(ByVal sender As Object, ByVal e As EventArgs)
Me.GetCustomersPageWise(1)
End Sub
Private Sub GetCustomersPageWise(ByVal pageIndex As Integer)
Try
Dim constring As String = ConfigurationManager.ConnectionStrings("SqlConn").ConnectionString
Dim con As SqlConnection = New SqlConnection(constring)
Dim cmd As SqlCommand = New SqlCommand("GetCustomersPageWise", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@PageIndex", pageIndex)
cmd.Parameters.AddWithValue("@PageSize", Integer.Parse(ddlPageSize.SelectedValue))
cmd.Parameters.Add("@Count", SqlDbType.Int, 4)
cmd.Parameters("@Count").Direction = ParameterDirection.Output
con.Open()
Dim idr As IDataReader = cmd.ExecuteReader
GridView1.DataSource = idr
GridView1.DataBind()
idr.Close()
con.Close()
Dim recordCount As Integer = Convert.ToInt32(cmd.Parameters("@Count").Value)
Me.PopulatePager(recordCount, pageIndex)
Catch ex As Exception
Response.Write(ex.Message)
End Try
End Sub
Protected Sub Page_Changed(ByVal sender As Object, ByVal e As EventArgs)
Dim pageIndex As Integer = Integer.Parse(CType(sender, LinkButton).CommandArgument)
Me.GetCustomersPageWise(pageIndex)
End Sub
Private Sub PopulatePager(ByVal recordCount As Integer, ByVal currentPage As Integer)
Dim dblPageCount As Double = CType((CType(recordCount, Decimal) / Decimal.Parse(ddlPageSize.SelectedValue)), Double)
Dim pageCount As Integer = CType(Math.Ceiling(dblPageCount), Integer)
Dim pages As New List(Of ListItem)
If (pageCount > 0) Then
pages.Add(New ListItem("First", "1", (currentPage > 1)))
Dim i As Integer = 1
Do While (i <= pageCount)
pages.Add(New ListItem(i.ToString, i.ToString, (i <> currentPage)))
i = (i + 1)
Loop
pages.Add(New ListItem("Last", pageCount.ToString, (currentPage < pageCount)))
End If
rptPager.DataSource = pages
rptPager.DataBind()
End Sub
Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView1.RowDataBound
Dim pubNohLink As HyperLink = TryCast(e.Row.Cells(0).FindControl("NameLink"), HyperLink)
SqlCon = New SqlConnection(ConfigurationManager.ConnectionStrings("SqlConn").ToString())
SqlCon.Open()
Sqlcmd = New SqlCommand("GetPatentDetailsOnPopup", SqlCon)
Sqlcmd.CommandType = CommandType.StoredProcedure
Sqlcmd.Parameters.AddWithValue("@ProjId", "8")
Sqlcmd.Parameters.AddWithValue("@Name", pubNohLink.Text)
Sqldr = Sqlcmd.ExecuteReader()
Sqldr.Read()
Dim p1 As String = Sqldr("DeptName").ToString()
Dim p2 As String = Sqldr("Description").ToString()
Dim p3 As String = Sqldr("Summary").ToString()
pubNohLink.Attributes("onmouseover") = "javascript:ShowPopup('" & p1 & "'" & "," & "'" & p2 & "'" & "," & "'" & p3 & "');"
pubNohLink.Attributes("onmouseout") = "javascript:HidePopup();"
Sqlcmd.Dispose()
SqlCon.Close()
End Sub
When I executed the above code i am getting "SqlParameter with ParamName '@Count' is not contained by this SqlParameterCollection" error message. if I commented code under GridView1_RowDataBound then it's working fine as i expected but as per my requirement when user mouse over on name, the popup should appear. How can i overcome the above issue??
