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

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

LVL 5
Tom KnowltonWeb developerAsked:
Who is Participating?
 
Shaun KlineLead Software EngineerCommented:
You can also use one of the SQLParameter's overloads to combine the first three statements. The fourth (the direction) is not generally needed unless your parameter is doing something other than input.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
It executes a Stored Procedure named DHD_GetTicketQueue with a single parameter named @vs_TicketNumber that have NULL value.
0
 
Shaun KlineLead Software EngineerCommented:
I believe this line:
parameter.Value = null;

Open in new window

should be
parameter.Value = DBNull.Value;

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Anthony PerkinsCommented:
Trying to get to the bottom of this, but I need some troubleshooting help.
The Stored Procedure DHD_GetTicketQueue is not using SET NOCOUNT ON
0
 
Tom KnowltonWeb developerAuthor Commented:
parameter.Value = DBNull.Value;


This did nothing to help.  Still says "HasRows = false"
0
 
Shaun KlineLead Software EngineerCommented:
If you ran your stored procedure in your database with NULL as the parameter, do you get rows returned?
0
 
Tom KnowltonWeb developerAuthor 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

0
 
Shaun KlineLead Software EngineerCommented:
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.
0
 
Tom KnowltonWeb developerAuthor 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

0
 
Shaun KlineLead Software EngineerCommented:
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

0
 
Tom KnowltonWeb developerAuthor Commented:
Crap...you're right....


Let me fix... brb....  : /
0
 
Tom KnowltonWeb developerAuthor 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

0
 
Tom KnowltonWeb developerAuthor Commented:
Thank you all.  : )


I'll look into overloading the parameter method as you suggested.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.