We help IT Professionals succeed at work.

What was executed on the server:  C# / SQL Server 2008

214 Views
Last Modified: 2014-08-29
When I try to run this command:

 SqlDataReader reader = command.ExecuteReader(cb);


in the watch window it says:    reader.HasRows = false


Trying to get to the bottom of this, but I need some troubleshooting help.

One thing I'd like to find-out is what T-SQL was executed with what parameters on the server side, but I cannot find that.




using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    // Create the command and set its properties.
                    SqlCommand command = new SqlCommand();
                    command.Connection = connection;
                    command.CommandText = "DHD_GetTicketQueue";
                    command.CommandType = CommandType.StoredProcedure;
                    command.CommandTimeout = 100;
                    command.StatementCompleted += command_StatementCompleted;
                    command.Disposed += command_Disposed;
                    command.NotificationAutoEnlist = true;
                    CommandBehavior cb;

                    cb = CommandBehavior.Default;

                    

                    // Add the input parameter and set its properties.
                    SqlParameter parameter = new SqlParameter();
                    parameter.IsNullable = true;
                    parameter.ParameterName = "@vs_TicketNumber";
                    parameter.SqlDbType = SqlDbType.NVarChar;
                    parameter.Direction = ParameterDirection.Input;
                    parameter.Value = null;

                    // Add the parameter to the Parameters collection. 
                    command.Parameters.Add(parameter);

                    // Open the connection and execute the reader.
                    connection.Open();
                    SqlDataReader reader = command.ExecuteReader(cb);

Open in new window

Comment
Watch Question

Vitor Montalv√£oIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Shaun KlineLead Software Engineer
CERTIFIED EXPERT

Commented:
I believe this line:
parameter.Value = null;

Open in new window

should be
parameter.Value = DBNull.Value;

Open in new window

CERTIFIED EXPERT
Top Expert 2012
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Tom KnowltonWeb developer

Author

Commented:
parameter.Value = DBNull.Value;


This did nothing to help.  Still says "HasRows = false"
Shaun KlineLead Software Engineer
CERTIFIED EXPERT

Commented:
If you ran your stored procedure in your database with NULL as the parameter, do you get rows returned?
Tom KnowltonWeb developer

Author

Commented:
If this call made in SSMS query windows brings back records, then what should my code-behind look like to match it?


USE [CoDGWEdwardBus]
GO

DECLARE	@return_value int

EXEC	@return_value = [dbo].[DHD_GetTicketQueue]
		@vs_QueueName = '',
		@vs_Priority = '',
		@vs_TicketStatus = '',
		@vs_TicketCallType = '',
		@vs_AssignedTo = '',
		@vs_CustomerNumber = '',
		@vs_CustomerName = '',
		@vs_DateTime = '',
		@vs_TicketNumber = '',
		@vs_OpenCallsOnly = ''

SELECT	'Return Value' = @return_value

GO

Open in new window

Shaun KlineLead Software Engineer
CERTIFIED EXPERT

Commented:
You would need to create a SQLParameter for each of the parameters listed, and set the value property to an empty string.

It is possible that if all of the parameters in the Stored Procedure definition have default values, you would not have to define any SQLParameters in your code. I have not done this, so I cannot give a definitive answer.
Tom KnowltonWeb developer

Author

Commented:
Here is my modified code-behind:


public Dictionary<string, Ticket> GetTickets()
        {
            Dictionary<string, Ticket> tickets = new Dictionary<string, Ticket>();

            //try
            //{
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    // Create the command and set its properties.
                    SqlCommand command = new SqlCommand();
                    command.Connection = connection;
                    command.CommandText = "DHD_GetTicketQueue";
                    command.CommandType = CommandType.StoredProcedure;
                    command.CommandTimeout = 100;
                    command.StatementCompleted += command_StatementCompleted;
                    command.Disposed += command_Disposed;
                    command.NotificationAutoEnlist = true;
                    CommandBehavior cb;

                    cb = CommandBehavior.Default;

                    

                    // Add the input parameter and set its properties.
                    SqlParameter parameter = new SqlParameter();                    
                    parameter.ParameterName = "@vs_QueueName";
                    parameter.SqlDbType = SqlDbType.NVarChar;
                    parameter.Direction = ParameterDirection.Input;
                    parameter.Value = "";

                    parameter.ParameterName = "@vs_Priority";
                    parameter.SqlDbType = SqlDbType.NVarChar;
                    parameter.Direction = ParameterDirection.Input;
                    parameter.Value = "";

                    parameter.ParameterName = "@vs_TicketStatus";
                    parameter.SqlDbType = SqlDbType.NVarChar;
                    parameter.Direction = ParameterDirection.Input;
                    parameter.Value = "";

                    parameter.ParameterName = "@vs_TicketCallType";
                    parameter.SqlDbType = SqlDbType.NVarChar;
                    parameter.Direction = ParameterDirection.Input;
                    parameter.Value = "";

                    parameter.ParameterName = "@vs_AssignedTo";
                    parameter.SqlDbType = SqlDbType.NVarChar;
                    parameter.Direction = ParameterDirection.Input;
                    parameter.Value = "";

                    parameter.ParameterName = "@vs_CustomerNumber";
                    parameter.SqlDbType = SqlDbType.NVarChar;
                    parameter.Direction = ParameterDirection.Input;
                    parameter.Value = "";

                    parameter.ParameterName = "@vs_CustomerName";
                    parameter.SqlDbType = SqlDbType.NVarChar;
                    parameter.Direction = ParameterDirection.Input;
                    parameter.Value = "";

                    parameter.ParameterName = "@vs_DateTime";
                    parameter.SqlDbType = SqlDbType.NVarChar;
                    parameter.Direction = ParameterDirection.Input;
                    parameter.Value = "";

                    parameter.ParameterName = "@vs_TicketNumber";
                    parameter.SqlDbType = SqlDbType.NVarChar;
                    parameter.Direction = ParameterDirection.Input;
                    parameter.Value = "";

                    parameter.ParameterName = "@vs_OpenCallsOnly";
                    parameter.SqlDbType = SqlDbType.Bit;
                    parameter.Direction = ParameterDirection.Input;
                    parameter.Value = 1;               

                    // Add the parameter to the Parameters collection. 
                    command.Parameters.Add(parameter);

                    // Open the connection and execute the reader.
                    connection.Open();
                    SqlDataReader reader = command.ExecuteReader(cb);

                   

                    if (reader.HasRows)  ////////////////////////////////////////////  STILL SAYS HASROWS = FALSE  /////////////////////
                    {
                        while (reader.Read())
                        {
                            Ticket ticket = new Ticket();
                            ticket.TicketNumber = reader["TicketNumber"].ToString();

                            ticket.Priority = reader["Priority"].ToString();
         
                            tickets.Add(ticket.TicketNumber, ticket);
                        }
                    }
                    else
                    {
                        Console.WriteLine("No rows found.");
                    }
                    reader.Close();
                }

            return tickets;
        }

Open in new window




Here is the entirety of the Stored Proc:

USE CoDGWEdwardBus
/****** Object:  StoredProcedure [dbo].[DHD_GetTicketQueue]    Script Date: 08/26/2014 08:53:44 ******/
SET ANSI_NULLS ON
GO

SET NOCOUNT ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF OBJECTPROPERTY(object_id('dbo.DHD_GetTicketQueue'), N'IsProcedure') = 1
DROP PROCEDURE [dbo].[DHD_GetTicketQueue]
GO

CREATE PROCEDURE [dbo].[DHD_GetTicketQueue]
    @vs_QueueName VARCHAR(50)=NULL,                      --SHAgents.PrefFullName (later on will use SCCalls.GroupName?)
    @vs_Priority VARCHAR(15)=NULL,                         --SCPriorities.Priority
    @vs_TicketStatus VARCHAR(255)=NULL,                    --SCCallStatusTypes.Description
    @vs_TicketCallType VARCHAR(50)=NULL,                   --SCCallTypes.Description
    @vs_AssignedTo VARCHAR(50)=NULL,                             --SHAgents.PrefFullName
    @vs_CustomerNumber VARCHAR(32)=NULL,                   --ARCustomers.CustomerNumber
    @vs_CustomerName VARCHAR(128)=NULL,                    --ARCustomers.CustomerName
    @vs_DateTime DATETIME=NULL,                                   --does not correspond to a field ... is passed in to obtain delta in records   
    @vs_TicketNumber VARCHAR(255)=NULL,                      --SCCalls.CallNumber
      @vs_OpenCallsOnly bit=1                                     -- 1 = true    0 = False

AS
  
if @vs_QueueName=' ' set @vs_QueueName=''
if @vs_Priority=' ' set @vs_Priority =''
if @vs_TicketStatus=' ' set @vs_TicketStatus=''
if @vs_TicketNumber=' ' set @vs_TicketNumber=''
if @vs_TicketCallType=' ' set @vs_TicketCallType=''
if @vs_AssignedTo=' ' set @vs_AssignedTo=''
if @vs_CustomerNumber=' ' set @vs_CustomerNumber=''
if @vs_CustomerName=' ' set @vs_CustomerName=''
if @vs_DateTime=' ' set @vs_DateTime=''



SELECT      
        sha.PrefFullName  AS QueueName,               --ShAgent.PrefFullName (later will be SCCalls.GroupName)
      scp.Priority,                                               --SCPriorities.Priority
      scp.Rank                AS PriorityRank,        --SCPriorities.Rank     
      scc.Date                AS DateEntered,               --SCCalls.Date
      scc.Description         AS TicketDescription,   --SCCalls.Description
      arc.CustomerName,                                     --ARCustomers.CustomerName
      arc.CustomerNumber,                                         --ARCustomers.CustomerNumber
      scc.Caller              AS CallerName,                --SCCalls.Caller or if contact record look there first
      scc.AddressCity         AS Location,                  --SCCalls.AddressCity
      scc.CallNumber          AS TicketNumber,        --SCalls.CalNumber (later will be SCCalls.GroupName)
      scs.Description         AS TicketStatus,        --SCCallStatusTypes.Description
      scy.Description         AS CallType,                  --SCCallTypes.Description
      sbc.Description         AS BillCode,                  --SCBillCodes.Description
      sha.PrefFullName  As AssignedTo,                --ShAgents.PrefFullName
      scc.ReqDate             AS RequiredDate,        --SCCalls.ReqDate
      swo.WorkOrderNumber                                         --SCWorkOrders.WorkOrderNumber
      
      FROM SCCalls scc
      INNER JOIN SCPriorities scp ON scp.PriorityID = scc.PriorityID
      INNER JOIN SCTechnicians sct ON sct.TechnicianID = scc.TechnicianID
      INNER JOIN ShAgents sha ON sha.AgentID = sct.TechnicianID
      INNER JOIN ARCustomers arc ON arc.CustomerID = scc.CustomerID
      INNER JOIN SCCallStatusTypes scs ON scs.Type = scc.Status
      INNER JOIN SCCallTypes scy ON scy.CallTypeID = scc.CallTypeID
      INNER JOIN SCBillCodes sbc ON sbc.BillCodeID = scc.BillCodeID
      INNER JOIN SCWorkOrders swo ON swo.WorkOrderID = scc.WorkOrderID

where
(
((sha.PrefFullName like '%'+@vs_QueueName+'%' and @vs_QueueName>' ')or (@vs_QueueName='' or @vs_QueueName=' '))
and ((scp.Priority like '%'+@vs_Priority+'%' and @vs_Priority>' ')or (@vs_Priority='' or @vs_Priority=' '))
and ((scc.CallNumber like '%'+@vs_TicketNumber+'%' and @vs_TicketNumber>' ')or (@vs_TicketNumber='' or @vs_TicketNumber=' '))
and ((scy.Description like '%'+@vs_TicketCallType+'%' and @vs_TicketCallType>' ')or (@vs_TicketCallType='' or @vs_TicketCallType=' '))
and ((sha.PrefFullName like '%'+@vs_AssignedTo+'%' and @vs_AssignedTo>' ')or (@vs_AssignedTo='' or @vs_AssignedTo=' '))
and ((arc.CustomerNumber like '%'+@vs_CustomerNumber+'%' and @vs_CustomerNumber>' ')or (@vs_CustomerNumber='' or @vs_CustomerNumber=' '))
and ((arc.CustomerName like '%'+@vs_CustomerName+'%' and @vs_CustomerName>' ')or (@vs_CustomerName='' or @vs_CustomerName=' '))
and ((scs.Description like '%'+@vs_TicketStatus+'%' and @vs_TicketStatus>' ')or (@vs_TicketStatus='' or @vs_TicketStatus=' '))
and ((scc.CallNumber like '%'+@vs_TicketNumber+'%' and @vs_TicketNumber>' ')or (@vs_TicketNumber='' or @vs_TicketNumber=' '))
and ((@vs_OpenCallsOnly=1 and scs.type not in ('CMP','X','OKB','I')) or (@vs_OpenCallsOnly=0))
and scc.LastUpdate >= @vs_DateTime
)


GO

Open in new window

Shaun KlineLead Software Engineer
CERTIFIED EXPERT

Commented:
Your are only adding the last parameter to your command.Parameters collection.

You should be doing these 6 lines for each parameter:
SqlParameter parameter = new SqlParameter();                    
parameter.ParameterName = "@vs_QueueName";
parameter.SqlDbType = SqlDbType.NVarChar;
parameter.Direction = ParameterDirection.Input;
parameter.Value = "";
command.Parameters.Add(parameter);

Open in new window

Tom KnowltonWeb developer

Author

Commented:
Crap...you're right....


Let me fix... brb....  : /
Lead Software Engineer
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Tom KnowltonWeb developer

Author

Commented:
Success!!!!!!!!


public Dictionary<string, Ticket> GetTickets()
        {
            Dictionary<string, Ticket> tickets = new Dictionary<string, Ticket>();

            //try
            //{
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    // Create the command and set its properties.
                    SqlCommand command = new SqlCommand();
                    command.Connection = connection;
                    command.CommandText = "DHD_GetTicketQueue";
                    command.CommandType = CommandType.StoredProcedure;
                    command.CommandTimeout = 100;
                    command.StatementCompleted += command_StatementCompleted;
                    command.Disposed += command_Disposed;
                    command.NotificationAutoEnlist = true;
                    CommandBehavior cb;

                    cb = CommandBehavior.Default;

                    

                    // Add the input parameter and set its properties.
                    SqlParameter parameter = new SqlParameter();                    
                    parameter.ParameterName = "@vs_QueueName";
                    parameter.SqlDbType = SqlDbType.NVarChar;
                    parameter.Direction = ParameterDirection.Input;
                    parameter.Value = "";
                    command.Parameters.Add(parameter);

                    parameter = new SqlParameter();                    
                    parameter.ParameterName = "@vs_Priority";
                    parameter.SqlDbType = SqlDbType.NVarChar;
                    parameter.Direction = ParameterDirection.Input;
                    parameter.Value = "";
                    command.Parameters.Add(parameter);

                    parameter = new SqlParameter();                    
                    parameter.ParameterName = "@vs_TicketStatus";
                    parameter.SqlDbType = SqlDbType.NVarChar;
                    parameter.Direction = ParameterDirection.Input;
                    parameter.Value = "";
                    command.Parameters.Add(parameter);

                    parameter = new SqlParameter();                    
                    parameter.ParameterName = "@vs_TicketCallType";
                    parameter.SqlDbType = SqlDbType.NVarChar;
                    parameter.Direction = ParameterDirection.Input;
                    parameter.Value = "";
                    command.Parameters.Add(parameter);

                    parameter = new SqlParameter();                    
                    parameter.ParameterName = "@vs_AssignedTo";
                    parameter.SqlDbType = SqlDbType.NVarChar;
                    parameter.Direction = ParameterDirection.Input;
                    parameter.Value = "";
                    command.Parameters.Add(parameter);

                    parameter = new SqlParameter();                    
                    parameter.ParameterName = "@vs_CustomerNumber";
                    parameter.SqlDbType = SqlDbType.NVarChar;
                    parameter.Direction = ParameterDirection.Input;
                    parameter.Value = "";
                    command.Parameters.Add(parameter);

                    parameter = new SqlParameter();                    
                    parameter.ParameterName = "@vs_CustomerName";
                    parameter.SqlDbType = SqlDbType.NVarChar;
                    parameter.Direction = ParameterDirection.Input;
                    parameter.Value = "";
                    command.Parameters.Add(parameter);

                    parameter = new SqlParameter();                    
                    parameter.ParameterName = "@vs_DateTime";
                    parameter.SqlDbType = SqlDbType.NVarChar;
                    parameter.Direction = ParameterDirection.Input;
                    parameter.Value = "";
                    command.Parameters.Add(parameter);

                    parameter = new SqlParameter();                    
                    parameter.ParameterName = "@vs_TicketNumber";
                    parameter.SqlDbType = SqlDbType.NVarChar;
                    parameter.Direction = ParameterDirection.Input;
                    parameter.Value = "";
                    command.Parameters.Add(parameter);

                    parameter = new SqlParameter();                    
                    parameter.ParameterName = "@vs_OpenCallsOnly";
                    parameter.SqlDbType = SqlDbType.Bit;
                    parameter.Direction = ParameterDirection.Input;
                    parameter.Value = 1;                    
                    command.Parameters.Add(parameter);

                    // Open the connection and execute the reader.
                    connection.Open();
                    SqlDataReader reader = command.ExecuteReader(cb);

                   

                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            Ticket ticket = new Ticket();
                            ticket.TicketNumber = reader["TicketNumber"].ToString();

                            ticket.Priority = reader["Priority"].ToString();
                            //ticket.Queue = reader["QueueName"].ToString();
                            //  ticket.Age = ReturnDateDiff(reader["DateEntered"].ToString());
                            //   ticket.Status = reader["Status"].ToString().Trim();
                            //   ticket.TicketEnteredDate = reader["DateEntered"].ToString();
                            //   ticket.TicketDescription = reader["Description"].ToString();
                            //    ticket.CallerName = reader["Caller"].ToString();
                            //ticket.Customer = reader["CustomerName"].ToString();
                            //     ticket.Location = reader["Location"].ToString();
                            //     ticket.TicketCallType = reader["CallType"].ToString();

                            //unsure...
                            //ticket.AssignedTo = reader["BillCode"].ToString();

                            tickets.Add(ticket.TicketNumber, ticket);
                        }
                    }
                    else
                    {
                        Console.WriteLine("No rows found.");
                    }
                    reader.Close();
                }

            return tickets;
        }

Open in new window

Tom KnowltonWeb developer

Author

Commented:
Thank you all.  : )


I'll look into overloading the parameter method as you suggested.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.