Store Procedure Return Data in MS Managment Studio .Not return Any Data When Call Using C#

[EDN Admin]

==========================STORE PROCEDURE===================<br/>
<pre class="prettyprint lang-sql SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROC [dbo].[SerachTickets]
@StoreID varchar(70),
@StatusID varchar(70),
@CustomerName varchar(400),
@TicketNo varchar(70),
@StratDate varchar(100),
@ESTDate varchar(100),
@StaffID varchar(100),
@ServiceItemID varchar(100)
AS
SET NOCOUNT ON
SET XACT_ABORT ON
declare @sql nvarchar(MAX)

Set @sql=SELECT Ticket.TicketID,Ticket.StaffID,ServiceItemInfo.ServiceItemID,
ISNULL(TicketWorkLog.WorkLogDesc,) WorkLogDesc,
Ticket.SerialNo, Convert(varchar(50),Ticket.CreationDate,101) CreationDate,
Admin_ServiceItem.ServiceItem,Convert(varchar(50),Ticket.CompletionDate,101) CompletionDate, Ticket.IsPickedUp,
Ticket.IsPaid, Ticket.IsVoid, Ticket.IsReadyForPayment, ISNULL(Ticket.TicketDetail,) TicketDetail,
Admin_Staff.StaffFirstName + + Admin_Staff.StaffLastName As StaffFullName
FROM Ticket
LEFT JOIN Admin_Staff ON Ticket.StaffID = Admin_Staff.StaffID
LEFT JOIN TicketWorkLog ON Ticket.TicketID = TicketWorkLog.TicketID
LEFT JOIN ServiceItemInfo On Ticket.TicketID = ServiceItemInfo.TicketID
LEFT JOIN Admin_ServiceItem On ServiceItemInfo.ServiceItemID = Admin_ServiceItem.ServiceItemID
LEFT JOIN Customer On Ticket.CustomerID = Customer.CustomerID
WHERE Ticket.StoreID=+@StoreID

if @StatusID <>
BEGIN
Set @sql =@sql + AND TicketWorkLog.StoreWorkLogID = +@StatusID
END

if @CustomerName <>
BEGIN
Set @sql =@sql + AND (Customer.FirstName Like(%+@CustomerName+%)
END

if @CustomerName <>
BEGIN
Set @sql =@sql + OR Customer.LastName Like(%+@CustomerName+%))
END

if @TicketNo <>
BEGIN
Set @sql =@sql + AND Ticket.SerialNo Like(%+@TicketNo+%)
END

if @StratDate <> 1900-01-01 00:00:00
BEGIN
Set @sql =@sql + AND Ticket.CreationDate Between +Convert(varchar(100),Convert(DateTime,@StratDate),101)+ AND +Convert(varchar(100),Convert(DateTime,@StratDate),101)+
END

if @ESTDate <> 1900-01-01 00:00:00
BEGIN
Set @sql =@sql + AND Ticket.CompletionDate Between +Convert(varchar(100),Convert(DateTime,@ESTDate),101)+ AND +Convert(varchar(100),Convert(DateTime,@ESTDate),101)+
END
if @StaffID <> 0
BEGIN
Set @sql =@sql + AND Ticket.StaffID =+@StaffID
END

if @ServiceItemID <>
BEGIN
Set @sql =@sql + AND ServiceItemInfo.ServiceItemID =+@ServiceItemID
END

--print @sql;
--exec SerachTickets 1,,,,1900-01-01 00:00:00,1900-01-01 00:00:00,0,
Exec (@sql)[/code]
<br/>
<br/>
==============================C# CODE==================================<br/>
<pre class="prettyprint try
{
using (SqlConnection sqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["SqlServer"].ToString()))
{
DateTime? dtStart = Convert.ToDateTime("01/01/1900");
DateTime? dtEst = Convert.ToDateTime("01/01/1900");
int intAssign = 0;

if (!string.IsNullOrEmpty(Request.QueryString["sdate"]))
{
dtStart = Convert.ToDateTime(Request.QueryString["sdate"]);
}

if (!string.IsNullOrEmpty(Request.QueryString["estdate"]))
{
dtEst = Convert.ToDateTime(Request.QueryString["estdate"]);
}

if (!string.IsNullOrEmpty(Request.QueryString["assign"]))
{
intAssign = Convert.ToInt32(Request.QueryString["assign"]);
}


using (SqlCommand cmd =sqlCon.CreateCommand())
{
DataSet dataset = new DataSet();

sqlCon.Open();

cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "SerachTickets";

cmd.Parameters.AddWithValue("@StoreID", Session["StoreID"]);
cmd.Parameters.AddWithValue("@StatusID", Request.QueryString["sid"]);
cmd.Parameters.AddWithValue("@CustomerName", Request.QueryString["cname"]);
cmd.Parameters.AddWithValue("@TicketNo", Request.QueryString["tno"]);
cmd.Parameters.AddWithValue("@StratDate", dtStart);
cmd.Parameters.AddWithValue("@ESTDate", dtEst);
cmd.Parameters.AddWithValue("@StaffID", intAssign);
cmd.Parameters.AddWithValue("@ServiceItemID", Request.QueryString["itemid"]);
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{

da.Fill(dataset);
cmd.Connection.Close();
grdTickets.DataSource = dataset.Tables[0];
grdTickets.DataBind();

}
}
}

}
catch (Exception ee)
{
}[/code]
<br/>
<br/>
==============================================================================<br/>
<br/>
I have checked using SQL PROILER What Values C# Sending To The SQL Server..<br/>
<br/>
exec SerachTickets @StoreID=1,@StatusID=N,@CustomerName=N,@TicketNo=N,@StratDate=1900-01-01 00:00:00,@ESTDate=1900-01-01 00:00:00,@StaffID=0,@ServiceItemID=N<br/>
<br/>
When run this query in MS Managment Studio It return the desired result but in C# No Dara return..
<p style="padding-right:0px; font-size:14px; vertical-align:baseline; clear:both; word-wrap:break-word; font-family:Arial,Liberation Sans,DejaVu Sans,sans-serif; line-height:18px
<br/>


View the full article