向存储过程调用中添加参数时出错

问题描述

在ShippedContainerSettlement程序中，我试图将参数添加到在远程服务器(plex)上创建的存储过程的SQL语句中.

In the ShippedContainerSettlement program I am trying to add parameters to a SQL statement on a stored procedure that I created on the remote server (plex).

public void checkGradedSerials()
{
    localTable = "Graded_Serials";

    List<string> gradedHides = new List<string>();

    string queryString = "call sproc164407_2053096_650214('@startDate', '" + endDate + "');";
    OdbcDataAdapter adapter = new OdbcDataAdapter();

    OdbcCommand command = new OdbcCommand(queryString, connection);
    command.CommandType = CommandType.StoredProcedure;

    command.Parameters.AddWithValue("@startDate", startDate);
    adapter.SelectCommand = command;

    connection.Open();

    while (rowsCollected == false)
    {
       if (retries <= 5)
       {
          try
          {
              DataTable table = new DataTable();
              adapter.Fill(table);

当我使用参数@startDate并为其赋予一个值时，将引发错误.但是，当我运行程序并添加参数后，endDate可以正常运行吗?

An error is thrown when I use the parameter @startDate and give it a value. However, when I run the program, and add the parameters how I have done for endDate, it runs fine?

我回来的错误是:

任何想法我在做什么错

我已经合并了以下提到的一些更改.这是我使用的代码.

I have incorporated some of the changes mentioned below. Here is the code I used.

public void checkGradedSerials()
        {
            localTable = "Graded_Serials";
            List<string> gradedHides = new List<string>();
            OdbcCommand command = new OdbcCommand("sproc164407_2053096_650214", odbcConnection);
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.AddWithValue("@startDate", startDate);
            command.Parameters.AddWithValue("@endDate", endDate);

            OdbcDataAdapter adapter = new OdbcDataAdapter();
            adapter.SelectCommand = command;
            odbcConnection.Open();

            while (rowsCollected == false)
            {
                if (retries <= 5)
                {
                    try
                    {
                        DataTable table = new DataTable();
                        adapter.Fill(table);

但是由于出现此错误，它似乎没有收到我要发送的参数.

But it doesn't seem to be receiving the parameters i am sending through as I am getting this error.

这是我正在使用的存储过程.这可能看起来很奇怪，但是请记住，当我简单地将一个字符串传递给选择命令时，这是可行的(请参见上面第一个代码示例中的endDate).

Here is the stored procedure I am using. This might look odd but remember this is working when I simply pass a string into a select command (see endDate in first code example above).

SELECT  DISTINCT(Serial_No)
FROM    Part_v_Container_Change2 AS CC
WHERE   CC.Change_Date > @Change_Start_Date AND
        CC.Change_Date <= @Change_End_Date AND
        CC.Location = 'H Grading';

并将参数添加到此处:

推荐答案

您应使用System.Data.SqlClient.您可以显式声明要发送的参数的数据类型，如下所示:

You should use the System.Data.SqlClient. You can explicitly declare the datatypes of paramaters you are sending... like this:

SqlConnection cn;

cn = new SqlConnection(ConnectionString);
SqlCommand cmd;
cmd = new SqlCommand("sproc164407_2053096_650214", cn);
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@startDate", SqlDbType.DateTime);
cmd.Parameters["@startDate"].Value = startDate;

cmd.Parameters.Add("@enddate", SqlDbType.VarChar);
cmd.Parameters["@enddate"].Value = enddate;

如果必须使用ODBC，则必须使用ODBC CALL语法，该语法不支持命名参数.因此，将您的queryString行更改为:

If you must use the ODBC, then you do have to use the ODBC CALL syntax, which does not support named parameters. So change your queryString line to:

string queryString = "{call sproc164407_2053096_650214 (?, ?)}";

然后您可以添加参数:

command.Parameters.Add("@startDate", OdbcType.DateTime).Value=startDate;
command.Parameters.Add("@endDate", OdbcType.DateTime).Value=endDate;

这篇关于向存储过程调用中添加参数时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！