Link to home
Create AccountLog in
Avatar of Tom Knowlton
Tom KnowltonFlag for United States of America

asked on

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

SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
I believe this line:
parameter.Value = null;

Open in new window

should be
parameter.Value = DBNull.Value;

Open in new window

SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Tom Knowlton

ASKER

parameter.Value = DBNull.Value;


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

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.
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

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

Crap...you're right....


Let me fix... brb....  : /
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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

Thank you all.  : )


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