Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2014-08-29
13
Medium Priority
?
200 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

0
Comment
Question by:Tom Knowlton
13 Comments
 
LVL 53

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 200 total points
ID: 40292803
It executes a Stored Procedure named DHD_GetTicketQueue with a single parameter named @vs_TicketNumber that have NULL value.
0
 
LVL 27

Expert Comment

by:Shaun Kline
ID: 40292848
I believe this line:
parameter.Value = null;

Open in new window

should be
parameter.Value = DBNull.Value;

Open in new window

0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 200 total points
ID: 40293217
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 5

Author Comment

by:Tom Knowlton
ID: 40293362
parameter.Value = DBNull.Value;


This did nothing to help.  Still says "HasRows = false"
0
 
LVL 27

Expert Comment

by:Shaun Kline
ID: 40293389
If you ran your stored procedure in your database with NULL as the parameter, do you get rows returned?
0
 
LVL 5

Author Comment

by:Tom Knowlton
ID: 40293397
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
 
LVL 27

Expert Comment

by:Shaun Kline
ID: 40293413
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
 
LVL 5

Author Comment

by:Tom Knowlton
ID: 40293445
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
 
LVL 27

Expert Comment

by:Shaun Kline
ID: 40293484
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
 
LVL 5

Author Comment

by:Tom Knowlton
ID: 40293491
Crap...you're right....


Let me fix... brb....  : /
0
 
LVL 27

Accepted Solution

by:
Shaun Kline earned 1600 total points
ID: 40293497
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
 
LVL 5

Author Comment

by:Tom Knowlton
ID: 40293517
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
 
LVL 5

Author Closing Comment

by:Tom Knowlton
ID: 40293524
Thank you all.  : )


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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question