Error when adding parameters to stored procedure call

Question

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

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?

The error I get back is:

Any ideas what I am doing wrong.

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

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';

and the parameters are added here:

Where is @Change_Start_Date coming from? Can you show us the sproc? Also if this is sql server as you tagged, why are you using ODBC instead of the native sql server provider? — dmeglio, Aug 18 '15 at 21:37
I need the odbc connection to connect to it. It is the only way to do so. Change_Start_Date is the name of the column that I am using the startDate to select values from. — Danrex, Aug 19 '15 at 00:00
I'm confused, if the parameters in your sproc are `@Change_Start_Date` and `@Change_End_Date` why are you setting `@startDate` and `@endDate`? Can you show the entire sproc (`CREATE PROCEDURE ...`) not just the select statement? — dmeglio, Aug 19 '15 at 00:19
That is how you add it. That is why I said it may look odd. It's just the system that they use. If I change the parameters to @change_Start_Date it makes no difference. — Danrex, Aug 19 '15 at 01:09

Brian Pressler · Accepted Answer · 2015-08-19T14:39:14.940

1

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;

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 (?, ?)}";

Then you can add your parameters:

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

edited Aug 19 '15 at 14:39

answered Aug 18 '15 at 22:36

Brian Pressler

6,653
2
19
40

I need to use an ODBC connection, because this is the only connection method that they have? Is it possible to do it with the ODBC connection? – Danrex Aug 18 '15 at 23:00
This model should work with the ODBC connection as well. Just send only the stored procedure name in your queryString and create both parameters. – Brian Pressler Aug 18 '15 at 23:13
Hi Brian, it doesn't seem to be getting the parameters through. I have edited my question above to reflect the new changes and what happened. Appreciate the help. – Danrex Aug 19 '15 at 00:16
Ah... I didn't realize that ODBC didn't accept named parameters. I have updated my answer. – Brian Pressler Aug 19 '15 at 14:41

score 1 · Answer 2 · edited May 23 '17 at 12:29

1

Use SqlCommand instead of odbc.

Just put the stored proc name in the CommandText, not a SQL statement to execute it. Adding the param values means the adapter will pass in the params in the right format. You don't need to do the string manipulation in CommandText.

If you need to use OdbcCommand then see this answer showing you need to use ? syntax for the parameters, so maybe change your CommandText back to including the 'call' or 'exec' command and parameter placeholders, then make sure you AddWithValue the params in the right order

edited May 23 '17 at 12:29

Community

1
1

answered Aug 18 '15 at 22:39

Rory

40,559
52
175
261

Rory I would love to see you give me an example? – Danrex Aug 18 '15 at 22:46

Error when adding parameters to stored procedure call

2 Answers2

Linked