SQL Parameter missing, but not missing

Question

0.00/5 (No votes)

See more:

I am running into an issue where the error does not make sense. The immediate window says that the parameter is not missing but the debugger says that it is missing.

Error: Procedure or function 'cpWeeklyPharmDataintDrvr' expects parameter '@companyID', which was not supplied.

Immediate Window:
?cmd.Parameters["@companyID"].Value
1356

C#

SqlCommand cmd = new SqlCommand("cpWeeklyPharmDataintDrvr", sqlC);

            SqlConnection sqlC = new SqlConnection(ConfigurationManager.ConnectionStrings["Reports"].ToString());
            SqlCommand cmd = new SqlCommand("cpWeeklyPharmDataintDrvr", sqlC);

            sqlC.Open();

            cmd.Parameters.Add(new SqlParameter("@companyID", SqlDbType.Int)).Value = Master.CompanyId;
            cmd.Parameters.Add(new SqlParameter("@sStartDate", SqlDbType.NVarChar, 30)).Value = Master.StartDate;
            cmd.Parameters.Add(new SqlParameter("@sEndDate", SqlDbType.NVarChar, 30)).Value = Master.EndDate;
            if (Master.RegionId > 0)
                cmd.Parameters.Add(new SqlParameter("@region", SqlDbType.Int)).Value = Master.RegionId;
            if (Master.DistrictId > 0)
                cmd.Parameters.Add(new SqlParameter("@district", SqlDbType.Int)).Value = Master.DistrictId;
            if (Master.StoreId > 0)
                cmd.Parameters.Add(new SqlParameter("@store", SqlDbType.NVarChar)).Value = Master.StoreId.ToString();

            SqlDataReader dR = cmd.ExecuteReader(); //This is where error occurs.
            DataTable d = new DataTable();
            d.Load(dR);

            sqlC.Close();

Posted 25-Sep-13 21:29pm

Kyle Gottfried

Updated 4-Oct-13 13:37pm

v4

Add a Solution

Comments

Thanks7872 26-Sep-13 3:56am

The conditions you have specified is wrong. It will result in error if any of the parameter is not supplied.

Kyle Gottfried 26-Sep-13 12:37pm

Can you go into more detail?

Thanks7872 27-Sep-13 0:03am

suppose your stored procedure requires companyID as parameter and if(Master.CompnayID>0) is not true,then that parameter will not be passed and you will obviously get an error.

Kyle Gottfried 4-Oct-13 19:32pm

Code changed, still experiencing same error.

5 solutions

Solution 4

Include the parameter @companyID in your Stored Procedure

Posted 4-Oct-13 18:33pm

Tom Marvolo Riddle

Solution 2

just remove

if (Master.CompanyId > 0)

and check it

Posted 25-Sep-13 21:33pm

ShajuAntony

Solution 3

Mssing cmd.CommandType = CommandType.StoredProcedure;

Posted 4-Oct-13 14:06pm

Kyle Gottfried

Solution 5

hello professional

please try to one more time review as per your condition you know more then us to flow of your logic

but i think
Your stored proc cpWeeklyPharmDataintDrvr expects you to supply @companyID parameter.. which you have not.
and
You need to use SqlCommand.Parameters.AddWithValue:

happy to help!!!

Posted 4-Oct-13 19:12pm

Shubh Agrahari

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)

Pratik Bhuva · Accepted Answer · 2013-10-04T21:23:00

First Add...

C#

cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@CompanyID",CompanyID);
//add your other parameters also

May be you have forget to Declare @companyID
And Make sure You are dealing with SP like...

Stored Procedure

SQL

CREATE dbo.mySP
   @companyID INT,
   @companyName NVARCHAR
   <--Here You need to declare all parameters-->
AS
BEGIN
   INSERT INTO TBL VALUES(@companyID,@companyName)
END

Hope This Help
------------------
Pratik Bhuva