Stored procedure problem

[dpreznik]

Dear friends,

I faced a strange problem.

I have a stored procedure, and I call it using ADO.Net and ODBC. And it used to work until I had to add one parameter. Now it gives me a strange error.
Here is the code:

odbcCommand.Parameters.Clear();
OdbcParameter odbcParameter = odbcCommand.Parameters.Add("pBTFirstPrinting", OdbcType.BigInt);
odbcParameter.Value = sheetEntries[rowCounter, 0] == "" ? (object)DBNull.Value : Convert.ToInt64(sheetEntries[rowCounter, 0]);
odbcParameter = odbcCommand.Parameters.Add("pFirstPrinting", OdbcType.BigInt);
odbcParameter.Value = sheetEntries[rowCounter, 1] == "" ? (object)DBNull.Value : Convert.ToInt64(sheetEntries[rowCounter, 1]);
odbcParameter = odbcCommand.Parameters.Add("pIngram", OdbcType.VarChar, 45);
odbcParameter.Value = sheetEntries[rowCounter, 2] == "" ? (object)DBNull.Value : sheetEntries[rowCounter, 2];
odbcParameter = odbcCommand.Parameters.Add("pLicensor", OdbcType.VarChar, 50);
odbcParameter.Value = sheetEntries[rowCounter, 3] == "" ? (object)DBNull.Value : sheetEntries[rowCounter, 3];
odbcParameter = odbcCommand.Parameters.Add("pOfferDate", OdbcType.SmallDateTime);
odbcParameter.Value = sheetEntries[rowCounter, 4] == "" ? (object)DBNull.Value : Convert.ToDateTime(sheetEntries[rowCounter, 4]);
odbcParameter = odbcCommand.Parameters.Add("pLastDate", OdbcType.SmallDateTime);
odbcParameter.Value = sheetEntries[rowCounter, 5] == "" ? (object)DBNull.Value : Convert.ToDateTime(sheetEntries[rowCounter, 5]);
odbcParameter = odbcCommand.Parameters.Add("pComments", OdbcType.VarChar, 255);
odbcParameter.Value = sheetEntries[rowCounter, 6] == "" ? (object)DBNull.Value : sheetEntries[rowCounter, 6];
odbcParameter = odbcCommand.Parameters.Add("pNote", OdbcType.VarChar, 255);
odbcParameter.Value = sheetEntries[rowCounter, 7] == "" ? (object)DBNull.Value : sheetEntries[rowCounter, 7];
odbcParameter = odbcCommand.Parameters.Add("pMaterial", OdbcType.VarChar, 10);
odbcParameter.Value = sheetEntries[rowCounter, 8] == "" ? (object)DBNull.Value : sheetEntries[rowCounter, 8];
odbcParameter = odbcCommand.Parameters.Add("FirstAppearance", OdbcType.SmallDateTime);
odbcParameter.Value = sheetEntries[rowCounter, 9] == "" ? (object)DBNull.Value : Convert.ToDateTime(sheetEntries[rowCounter, 9]);
odbcParameter = odbcCommand.Parameters.Add("pEAN", OdbcType.VarChar, 13);
odbcParameter.Value = sheetEntries[rowCounter, 10] == "" ? (object)DBNull.Value : sheetEntries[rowCounter, 10];

odbcParameter = odbcCommand.Parameters.Add("pColor", OdbcType.Int);
odbcParameter.Value = sheetEntries[rowCounter, 11] == "" ? (object)DBNull.Value : sheetEntries[rowCounter, 11];


if (odbcConnection.State == ConnectionState.Closed)
odbcConnection.Open();
updated += odbcCommand.ExecuteNonQuery();

As you can see, I have 12 parameters. Before my change, I used to have only 11. But after I added pBTFirstPrinting, I have 12, and odbcCommand.Parameters.Count shows 12.
And yet, I am receiving "ERROR [HY000] [MySQL][ODBC 3.51 Driver][mysqld-5.0.81-community-nt]Incorrect number of arguments for PROCEDURE MyProcedure; expected 12, got 11.
How can it be explained?
Thanks,
Dmitriy

[nelo]

Sorry to ask an obvious question but does the stored procedure include the new parameter as well? It's not clear from your post so it warrants the question.

[dpreznik]

Yes, of course the stored procedure has been updated, that's why 12 parameters are now required instead of 11. I just don't understand why it doesn't see the 12th parameter passed. I don't know if it is important that I am adding a parameter at the beginning of the parameters list and not at the end.

[dpreznik]

Never mind, I found my problem. I forgot to update number of question marks:

OdbcCommand odbcCommand = new OdbcCommand("{ CALL FrontlistUpdate(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) }", odbcConnection);

[nelo]

Never mind, I found my problem. I forgot to update number of question marks:

OdbcCommand odbcCommand = new OdbcCommand("{ CALL FrontlistUpdate(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) }", odbcConnection);

I could be wrong about this as I don't normally use ODBC with ADO.NET but I think you could have avoided the problem by setting up your command as a stored procedure...

OdbcCommand odbcCommand = new OdbcCommand("FrontlistUpdate", odbcConnection);
odbcCommand.CommandType = CommandType.StoredProcedure;