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.
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);
Microsoft SQL Server 2008Microsoft SQL Server
Last Comment
Tom Knowlton
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
parameter.Value = DBNull.Value;
This did nothing to help. Still says "HasRows = false"
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?
ASKER
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
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.
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.
ASKER
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;
}
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
Your are only adding the last parameter to your command.Parameters collection.
You should be doing these 6 lines for each parameter:
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);
ASKER
Crap...you're right....
Let me fix... brb.... : /
Let me fix... brb.... : /
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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;
}
ASKER
Thank you all. : )
I'll look into overloading the parameter method as you suggested.
I'll look into overloading the parameter method as you suggested.
Open in new window
should beOpen in new window