Procedure or function 'Usp_Web_CheckDuplicateColName' expects parameter '@p_QueueID', which was not supplied

Question

1.80/5 (2 votes)

See more:

Hello everyone,

I am getting below error in my error log

Procedure or function 'Usp_Web_CheckDuplicateColName' expects parameter '@p_QueueID', which was not supplied

It works 99 times out of 100, but there is 1 percent failure.
Sometimes it gives error , it is hard to replicate as whenever we debug it runs without any errors.
Please.. Help..

C#

                //This is how i call it
                M_IndexData m_indexData = new M_IndexData();
                m_indexData.BatchName = batch_name;
                m_indexData.InvoiceID = invoice_id;
                m_indexData.SelectedQueue = ddlQueue.SelectedItem.Value;
                m_indexData.ProjectCode = Convert.ToString(Session["ProjectCode"]);

                bool b=GetBusinessManger().CheckDuplicateRequired(m_indexData);
                

        public bool CheckDuplicateRequired(M_IndexData MI)
        {
            M_IndexData m_indexData = null;

           m_indexData =   DAL_Common.CheckDuplicateSetting(MI);
           string duplicateCheck = m_indexData.DuplicateCheck;
           if (duplicateCheck.ToUpper() == "YES")
           {
               return true;
           }
           else
           {
               return false;
           }
        }

public static M_IndexData CheckDuplicateSetting(M_IndexData Mindex)
{

    DataTable dt = new DataTable();
    _param = new SqlParameter[3];


    DBHelper.AddValueToSqlParameter(ref _param[0], "@p_ProjectCode", Mindex.ProjectCode, SqlDbType.VarChar, 100, ParameterDirection.Input);
    DBHelper.AddValueToSqlParameter(ref _param[1], "@p_QueueID", Mindex.SelectedQueue, SqlDbType.VarChar, 100, ParameterDirection.Input);
    DBHelper.AddValueToSqlParameter(ref _param[2], "@p_Duplicate", "", SqlDbType.VarChar, 20, ParameterDirection.Output);
    dt = DBHelper.ExecuteSelectQuery("Usp_Web_CheckDuplicateColName", ref _param, Connection.TranDBConnection(Mindex.ProjectCode).ToString());

    Mindex.DuplicateCheck = _param[2].Value.ToString();

    return Mindex;
}

SQL

CREATE   PROC [dbo].[Usp_Web_CheckDuplicateColName]        
 @p_ProjectCode VARCHAR(100),           
 @p_QueueID VARCHAR(100)  ,     
 @p_Duplicate VARCHAR(20) OUT      
AS         
BEGIN          
SELECT @p_Duplicate=Isnull(duplicate,'') FROM qmaster WHERE Project_Code =@p_ProjectCode AND Qcode=@p_QueueID                 
END

[Added code from comment]

C#

public static void AddValueToSqlParameter(ref SqlParameter _param, string ParamName, object ParamValue,
SqlDbType _dbType, int _Size,ParameterDirection Direction)
{

   if (ParamName == "" && ParamName == null)
   {
      throw new ArgumentException("No Parameter Specified");
   }
   else
   {
      if (_Size == 0)
      {
         _param = new SqlParameter(ParamName, _dbType);
      }
      else
      {
         _param = new SqlParameter(ParamName, _dbType, _Size);
      }
      _param.Direction = Direction;
   }
   _param.Value = ParamValue;
}

public static DataTable ExecuteSelectQuery(string procedure_Name, ref SqlParameter[] Param, String ConnString)
{
   SqlDataAdapter da;
   DataTable dt = new DataTable();
   SqlConnection con = new SqlConnection(ConnString);

   try
   {
      if (procedure_Name != "")
      {
         con.Open();
         da = new SqlDataAdapter(procedure_Name, con);
         da.SelectCommand.CommandType = CommandType.StoredProcedure;
         if (Param != null)
         {
            foreach (SqlParameter Params in Param)
            {
               da.SelectCommand.Parameters.Add(Params);
            }
         }
         da.Fill(dt);
         da.Dispose();
      }

   }
   catch (SqlException ex)
   {
      throw ex;
   }
   catch (Exception ex)
   {
      throw new ArgumentException(ex.Message);
   }
   finally
   {
      if (con.State == ConnectionState.Open)
      {
         con.Close();
      }
   }
   return dt;
}

Posted 27-Jul-15 0:37am

sunil mali

Updated 27-Jul-15 2:08am

Wendelius

v5

Add a Solution

Comments

Kornfeld Eliyahu Peter 27-Jul-15 7:03am

Maybe the problem is with AddValueToSqlParameter...And you should check _param too...
Add some runtime tracing to your code...

Wendelius 27-Jul-15 7:32am

Can you post the code for DBHelper.AddValueToSqlParameter

sunil mali 27-Jul-15 7:55am

public static void AddValueToSqlParameter(ref SqlParameter _param, string ParamName, object ParamValue,
SqlDbType _dbType, int _Size,ParameterDirection Direction)
{

if (ParamName == "" && ParamName == null)
{
throw new ArgumentException("No Parameter Specified");
}
else
{
if (_Size == 0)
{
_param = new SqlParameter(ParamName, _dbType);
}
else
{
_param = new SqlParameter(ParamName, _dbType, _Size);
}
_param.Direction = Direction;
}
_param.Value = ParamValue;
}

public static DataTable ExecuteSelectQuery(string procedure_Name, ref SqlParameter[] Param, String ConnString)
{
SqlDataAdapter da;
DataTable dt = new DataTable();
SqlConnection con = new SqlConnection(ConnString);

try
{
if (procedure_Name != "")
{
con.Open();
da = new SqlDataAdapter(procedure_Name, con);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
if (Param != null)
{
foreach (SqlParameter Params in Param)
{
da.SelectCommand.Parameters.Add(Params);
}
}
da.Fill(dt);
da.Dispose();
}

}
catch (SqlException ex)
{
throw ex;
}
catch (Exception ex)
{

throw new ArgumentException(ex.Message);
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}

}
return dt;
}

Wendelius 27-Jul-15 8:12am

I added the code into the original question. Whenever you need to add more info to the question, use the "Improve question" link to make modications.

And based on the code you provided, it looks like the answer from OriginalGriff is the problem you're facing. Check for null value in the code and inform the user in appropriate way.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2015-07-27T01:09:00

p_QueueID is defined in your SP as a VARCHAR which does not allow NULL values.
So the chances are that the value you are loading from your drop down list is null because the user has not selected an item yet:

C#

m_indexData.SelectedQueue = ddlQueue.SelectedItem.Value;

If so, it will pass the nulkl value to SQL and SQL will not accept this as a value for your SP.

Either allow NULL values, set a DEFAULT value, or check for nulls in your C# code.

That's not quite accurate. NULL is perfectly valid value for VARCHAR parameter. Consider the following:

SQL

CREATE PROCEDURE NullTest @sometext VARCHAR(100) AS
BEGIN
IF @sometext IS NULL PRINT 'Value is null'
ELSE PRINT 'Value is not null'
END;

EXEC NullTest @sometext=null
EXEC NullTest @sometext=''
EXEC NullTest @sometext='A'

Yes - but now try calling that from C#:

C#

using (SqlConnection con = new SqlConnection(strConnect))
    {
    con.Open();
    using (SqlCommand cmd = new SqlCommand("NullTest", con))
        {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@sometext", "A");
        using (SqlDataReader read = cmd.ExecuteReader())
            {
            while (read.Read())
                {
                Console.WriteLine(read[0]);
                }
            }
        }
    using (SqlCommand cmd = new SqlCommand("NullTest", con))
        {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@sometext", "");
        using (SqlDataReader read = cmd.ExecuteReader())
            {
            while (read.Read())
                {
                Console.WriteLine(read[0]);
                }
            }
        }
    using (SqlCommand cmd = new SqlCommand("NullTest", con))
        {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@sometext", null);
        using (SqlDataReader read = cmd.ExecuteReader())
            {
            while (read.Read())
                {
                Console.WriteLine(read[0]);
                }
            }
        }
    }

You will get:

C#

Value is not null
Value is not null
A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

Because the framework doesn't create the NULL parameter.

_Asif_ · Answer 2 · 2015-07-27T01:14:00

You have now learned how critical a file logging utility is to debug issues like this. File logging utility is must have feature of any application that would go on Production.

There are so many file logging components available like Log4Net[^], Logging Application Block[^] etc. Do use any of these and then see what is getting wrong with the code.

Procedure or function 'Usp_Web_CheckDuplicateColName' expects parameter '@p_QueueID', which was not supplied

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0