无法通过C＃将参数传递给过程 [英] Unable to pass parameter to the procedure via C#

问题描述

作为主题建议。无法将参数传递给过程。如果我传递int（对于CustomerId）变量，则为类似的代码（在任何需要的地方将字符串更改为int）我成功获得了输出。而对于字符串[在c＃中]，即varchar（20）[在sql]（对于CustomerName），我得到一个例外，说程序期望参数@ipv_CustomerName未提供



我的代码::

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 ::

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 ::

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

从行中抛出异常

Exception is thrown from the line

dr = cmd.ExecuteReader();

跳转到catch块说过程或函数''GetCustomerByName''需要参数''@ ipv_CustomerName''，这是未提供的



请帮帮我。

非常感谢：）

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

推荐答案

当你没有添加指定的参数时会出现此异常，但是在这种情况下你添加了指定的参数，那么另一个问题可能是，你传递给的值存储过程为null，但在您的存储过程中，您没有将其定义为可为空。

因此，请检查运行时的名称值（param2.Value = name）是否为空。



如果为null，则必须对存储过程进行更改，因为它接受可以为空的

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

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

你的代码看起来好。看看这里：

SqlParameter Class [ ^ ]

如何：保护ASP.NET中的SQL注入 [ ^ ]

如何：使用ADO.NET和Visual C＃.NET调用参数化存储过程 [ ^ ]



试试更改存储过程：

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:

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

这里有一些问题，首先要执行存储过程你需要以这种方式调用cmd：

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

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.

这篇关于无法通过C＃将参数传递给过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！