CODEWITHSUNDEEP
c#asp.netexceptioninformixparameterized-query
Anyname Donotcare
Anyname Donotcare

Reputation: 11403

Wrong number of parameters (parametrized query)

Q:

When i try to execute the following parametrized query:

INSERT INTO days (day,short,name,depcode,studycode,batchnum) values (?,?,?,?,?,?);SELECT SCOPE_IDENTITY();

through command.ExecuteScalar();

throws the following exception:

ERROR [07001] [Informix .NET provider]Wrong number of parameters.

Where is the problem?

EDIT:

 public static int InsertDays(List<Day> days)
        {

            int affectedRow = -1;
            Dictionary<string, string> daysParameter = new Dictionary<string, string>();
            try
            {
                foreach (Day a in days)
                {
                    daysParameter.Add("day", a.DayId.ToString());
                    daysParameter.Add("short", a.ShortName);
                    daysParameter.Add("name", a.Name);
                    daysParameter.Add("depcode", a.DepCode.ToString());
                    daysParameter.Add("studycode", a.StudyCode.ToString());
                    daysParameter.Add("batchnum", a.BatchNum.ToString());

                    affectedRow = DBUtilities.InsertEntity_Return_ID("days", daysParameter);
                    daysParameter.Clear();
                    if (affectedRow < 0)
                    {
                        break;
                    }
                }
            }
            catch (Exception ee)
            {
                string message = ee.Message;
            }

            return affectedRow;

        }

public static int InsertEntity_Return_ID(string tblName, Dictionary<string, string> dtParams)
        {
            int Result = -1;
            DBConnectionForInformix DAL_Helper = new DBConnectionForInformix("");
            string[] field_names = new string[dtParams.Count];
            dtParams.Keys.CopyTo(field_names, 0);
            string[] field_values = new string[dtParams.Count];
            string[] field_valuesParam = new string[dtParams.Count];
            dtParams.Values.CopyTo(field_values, 0);
            for (int i = 0; i < field_names.Length; i++)
            {
                field_valuesParam[i] = "?";
            }
            string insertCmd = @"INSERT INTO " + tblName + " (" + string.Join(",", field_names) + ") values (" + string.Join(",", field_valuesParam) + ");SELECT SCOPE_IDENTITY();";

        Result = int.Parse(DAL_Helper.Return_Scalar(insertCmd));
        return Result;
        }

Upvotes: 1

Views: 2376

Answers (2)

Eben Roux
Eben Roux

Reputation: 13256

Ensure that where you are providing the parameters that one of the values is not null. That may cause the provider to ignore the parameter. If this is your issue pass DBNull.

EDIT

As Jon stated you need to use command.Parameters to give the command the parameters to use in the query.

Upvotes: 1

Jon Skeet
Jon Skeet

Reputation: 1501976

You haven't shown where you're actually populating the parameter values. Given that you've got the right number of question marks, I suspect that's where the problem lies.

EDIT: Okay, now you've posted more code, it's obvious what's going wrong: your Return_Scalar method isn't accepting any actual values! You're not using field_values anywhere after populating it. You need to set the parameters in the command.

(You should also look at .NET naming conventions, by the way...)

Upvotes: 3

Related Questions