Unable to pass parameter to the procedure via C#

Question

0.00/5 (No votes)

See more:

Hi,

As the subject suggest.Unable to pass parameter to the procedure.A twist is if I pass an "int" (for CustomerId) variable with similar code(changing string to int wherever needed) I successfully get the output. Whereas for a "string"[in c#] i.e. "varchar(20)"[in sql] (for CustomerName) i get an exception saying "The Procedure Expects A Parameter @ipv_CustomerName That Was Not Supplied"

My Code ::

C#

public static CustomerDetails GetCustomerByName(string name)
        {
            SqlConnection con = null;
            SqlCommand cmd = null;
            // SqlDataReader reader = null;
            try
            {
                CustomerDetails customerByName = null;

                con = new SqlConnection(ConnectionString);
                con.Open();
                cmd = new SqlCommand("GetCustomerByName ", con);
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param2 = new SqlParameter();
                param2.ParameterName = "@ipv_CustomerName";
                param2.SqlDbType = SqlDbType.VarChar;
                param2.Value = name;
                param2.Direction = ParameterDirection.Input;
                cmd.Parameters.Add(param2);

                SqlDataReader dr = null;
                dr = cmd.ExecuteReader();

                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        customerByName = new CustomerDetails();
                        customerByName.CustomerId = (int)dr["CustomerId"];
                        customerByName.CustomerName = Convert.ToString(dr["CustomerName"]);
                        customerByName.CustomerPhone = Convert.ToString(dr["CustomerPhone"]);
                        customerByName.CustomerAddr = Convert.ToString(dr["CustomerAddr"]);
                    }
                    return customerByName;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception e)
            {
                throw e;
            }
        }

and my procedure ::

SQL

Create PROCEDURE GetCustomerByName(@ipv_CustomerName varchar(20))
as
select * from Customer
where CustomerName = @ipv_CustomerName

Exception is thrown from the line

C#

dr = cmd.ExecuteReader();

jumping to the catch block saying Procedure or function 'GetCustomerByName' expects parameter '@ipv_CustomerName', which was not supplied

Please help me out.
Thanks a lot :)

Posted 16-May-13 9:18am

Member 9960197

Updated 16-May-13 9:21am

v2

Add a Solution

Comments

Member 9581488 16-May-13 15:32pm

You have extra space at :- cmd = new SqlCommand("GetCustomerByName ", con); remove the space after GetCustomerByName
else Use breakpoint and see the value of name
and try below command to your SQL Server query window and see if its returning any value.
exec GetCustomerByName YourCustomerName

prasanthaKumara 16-May-13 17:17pm

It should work if you have use these code as it is. As the exception says parameter was not supplied, that mean it has been udentified the stored procedure.So there is no issue with the space at procedure name.

Member 9581488 16-May-13 20:07pm

thank you..but I have given the user other options as well.....and sometimes SQL statement doesnt work if it finds any spaces so I have given HINT!

Member 9960197 17-May-13 10:23am

done that already sir..the proc works fine ... as said,while debugging the debugger jumps from
dr = cmd.ExecuteReader();
to the catch block giving the above mentioned exception

Member 9581488 17-May-13 10:26am

what is the value of name? try using break point.

[no name] 16-May-13 16:00pm

What happens if you try cmd.Parameters.AddWithValue instead?

Member 9960197 17-May-13 10:24am

no difference ..........

3 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)

prasanthaKumara · Answer 1 · 2013-05-17T10:19:00

This exception ocures when you do not add specified paramater , but in this case you have added the specified parameter, So the other issue may be, the value you r passing to the store procedure is null, But in you stored procedure you havent defined that as nullable.
So check the name value at run time (param2.Value = name) whether it is not null.

If it is null you have to made the changes to the stored procedure as it accepts nullable

SQL

create PROCEDURE [dbo].[GetCustomerByName]
@ipv_CustomerName varchar(20) = NULL
as
select * from Customers
where CustomerName = @ipv_CustomerName

Let me know if its solve your issue,

Best Regards

Maciej Los · Answer 2 · 2013-05-20T09:21:00

Your code looks good. Have a look here:
SqlParameter Class[^]
How To: Protect From SQL Injection in ASP.NET[^]
HOW TO: Call a Parameterized Stored Procedure by Using ADO.NET and Visual C# .NET[^]

Try to change your stored procedure:

SQL

ALTER PROCEDURE GetCustomerByName
    @ipv_CustomerName varchar(20)
AS
BEGIN
    SELECT *
    FROM Customer
    WHERE CustomerName = @ipv_CustomerName
END

Yuriy Loginov · Answer 3 · 2013-05-16T11:26:00

There are a few things going wrong here, first of all to execute a stored procedure you need to call cmd this way:

C#

cmd.ExecuteNonQuery();

Second of all stored procedures don't return data sets, they only return integer values. So you may want to change your stored procedure to a function or just run the query directly.

Unable to pass parameter to the procedure via C#

3 solutions

Solution 2

Solution 3

Solution 1

Add your solution here

Preview 0