oops, sorry about the typo... thanks.
I'm using the SqlDataReader for a paging datagrid. All the examples I've
seen say that you must query the database to get a value for
VirtualItemCount and then use the ExecuteReader() to get your result set. I
figured I could use an output parameter and only query the db once this
way.... I can't get output parameters to return any values with
ExecuteReader() no matter what I do, so I wonder if it is possible? The
stored procedure works great if I us a select @@rowcount in and return the
results in a second result set, then get the virtual item count from the
first result set then call NextResult() to populate the datagrid
Anyway... here's the code, if you see why it won't work please let me know
Thanks!
===============================
// this doesn't work...
private void dgCustomers_DataBind()
{
SqlConnection cn = new
SqlConnection(ConfigurationSettings.AppSettings["NorthwindDB"]);
SqlCommand cmd = new SqlCommand("usp_CustomersSelect", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@pageSize", SqlDbType.Int).Value =
dgCustomers.PageSize;
cmd.Parameters.Add("@rowStartIndex", SqlDbType.Int).Value =
(dgCustomers.CurrentPageIndex * dgCustomers.PageSize);
cmd.Parameters.Add("@virtualItemCount", SqlDbType.Int).Direction =
ParameterDirection.Output;
try
{
cn.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
dgCustomers.VirtualItemCount =
(int)cmd.Parameters["@virtualItemCount"].Value;
dgCustomers.DataSource = dr;
dgCustomers.DataBind();
}
dr.Close();
lblVirtualItemCount.Text = String.Format("Total results: {0}",
dgCustomers.VirtualItemCount);
}
catch (Exception ex)
{
errMessage.InnerText = ex.ToString();
errMessage.Visible = true;
}
finally
{
cn.Dispose();
}
}
===================================
CREATE procedure usp_CustomersSelect
@pageSize int,
@rowStartIndex int,
@virtualItemCount int output
as
declare @rowEndIndex int
set @rowEndIndex = @rowStartIndex + @pageSize - 1
select identity(int,0,1) as rowIndex,
CustomerID, CompanyName, ContactName,
ContactTitle, Address, City, Region,
PostalCode, Country, Phone, Fax
into #TEMP_NWindCustomers
from Customers order by CompanyName
set @virtualItemCount = @@rowcount
select rowIndex, CustomerID, CompanyName, ContactName,
ContactTitle, Address, City, Region, PostalCode,
Country, Phone, Fax
from #TEMP_NWindCustomers
where rowIndex between @rowStartIndex and @rowEndIndex order by 1
====================================================
This works fine:
private void dgCustomers_DataBind()
{
SqlConnection cn = new
SqlConnection(ConfigurationSettings.AppSettings["NorthwindDB"]);
SqlCommand cmd = new SqlCommand("usp_CustomersSelect", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@pageSize", SqlDbType.Int).Value =
dgCustomers.PageSize;
cmd.Parameters.Add("@rowStartIndex", SqlDbType.Int).Value =
(dgCustomers.CurrentPageIndex * dgCustomers.PageSize);
try
{
cn.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
dgCustomers.VirtualItemCount = (int)dr["virtualItemCount"];
}
while (dr.NextResult())
{
dgCustomers.DataSource = dr;
dgCustomers.DataBind();
}
dr.Close();
lblVirtualItemCount.Text = String.Format("Total results: {0}",
dgCustomers.VirtualItemCount);
}
catch (Exception ex)
{
errMessage.InnerText = ex.ToString();
errMessage.Visible = true;
}
finally
{
cn.Dispose();
}
}
private void dgCustomers_PageIndexChanged(object source,
DataGridPageChangedEventArgs e)
{
dgCustomers.CurrentPageIndex = e.NewPageIndex;
dgCustomers_DataBind();
}
CREATE procedure usp_CustomersSelect
@pageSize int,
@rowStartIndex int
as
declare @rowEndIndex int
set @rowEndIndex = @rowStartIndex + @pageSize - 1
select identity(int,0,1) as rowIndex,
CustomerID, CompanyName, ContactName,
ContactTitle, Address, City, Region,
PostalCode, Country, Phone, Fax
into #TEMP_NWindCustomers
from Customers order by CompanyName
select virtualItemCount = @@rowcount
select rowIndex, CustomerID, CompanyName, ContactName,
ContactTitle, Address, City, Region, PostalCode,
Country, Phone, Fax
from #TEMP_NWindCustomers
where rowIndex between @rowStartIndex and @rowEndIndex order by 1
William Ryan eMVP said:
Looks like a pspelling issue, one references @out_param and the outher
@out_value. Also, make sure the datareader has had it's .read method called
and is closed.
HTH,
Bill
mo said:
should I be able to use output parameters when calling
SqlCommand.ExecuteReader()???
SqlConnection cn = new SqlConnection(ConnectionString);
SqlCommand cmd = new SqlCommand("StoredProcedureName", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@param1", SqlDbType.Int).Value = _param1Value;
cmd.Parameters.Add("@param2", SqlDbType.VarChar, 50).Value = _param2Value
cmd.Parameters.Add("@param3", SqlDbType.VarChar, 50).Value = _param3Value.
cmd.Parameters.Add("@out_param", SqlDbType.Int);
cmd.Parameters["@out_param"].Direction = ParameterDirection.Output;
try
{
SqlDataReader dr = cmd.ExecuteReader();
_out_value = (int)cmd.Parameters["@out_value"].Value;
}
catch ...etc...
when doing this, the value of cmd.Parameters["@out_param"] is always null...
thx,
mo
-----------------------------------------------
"Too much football without a helmet?"
Al Pacino
Scent of a Woman
