The OdbcParameterCollection only accepts non-null OdbcParameter type objects

Question

Just as a little heads up, I'm completely new to this whole "coding" thing in general, so forgive me if I don't make myself clear and just let me know if I can tell you anything else. I've been Googling for days and couldn't find anything, I'm not sure if that's just bad on my part, or just because I'm really new and wouldn't know the solution if it were right in front of my face.

When I go to test the application (Visual Studio 2010), everything shows up and I can put in information in my text boxes, but once I press submit, this error pops up:

"The OdbcParameterCollection only accepts non-null OdbcParameter type objects. Parameter name: value"

and it points to this line of code:

cmd.Parameters.Add(pram[i]);

I don't know if I'm setting the parameters wrong or INSERT INTO statement wrong or what. I can show you the ASP.net code also if needed. Let me know if I can give you any more info! Thank you in advanced!

My C# code is this:

 private void execution(string eventspecialist, string phone, string phone2, string firstname, string lastname, string besttime, string companyname, string nonprofit, string requesteddate, string requestedtime, string attendance, string eventtype, string other, string leadsource, string notes, string catering, string bar, string damagedeposit, string dancefloor)
{


    OdbcConnection conn = new OdbcConnection(GetConnectionString());

    string sql = "INSERT INTO tblcontacts (eventspecialist, phone, phone2, firstname, lastname, besttime, companyname, nonprofit, requesteddate, requestedtime, attendance, eventtype, other, leadsource, notes, catering, bar, damagedeposit, dancefloor) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

    try
    {
        conn.Open();
        OdbcCommand cmd = new OdbcCommand(sql, conn);

        cmd.Parameters.Add("@SPECIALIST", OdbcType.NVarChar, 50).Value = eventspecialist;
        cmd.Parameters.Add("@CUST_PHONE1", OdbcType.NVarChar, 50).Value = phone;
        cmd.Parameters.Add("@CUST_PHONE2", OdbcType.NVarChar, 50).Value = phone2;
        cmd.Parameters.Add("@CUST_FNAME", OdbcType.NVarChar, 50).Value = firstname;
        cmd.Parameters.Add("@CUST_LNAME", OdbcType.NVarChar, 50).Value = lastname;
        cmd.Parameters.Add("@BEST_TIME", OdbcType.NVarChar, 50).Value = besttime;
        cmd.Parameters.Add("@COMPANY_NAME", OdbcType.NVarChar, 225).Value = companyname;
        cmd.Parameters.Add("@NONPROFIT", OdbcType.NVarChar, 1).Value = nonprofit;
        cmd.Parameters.Add("@REQ_DATE", OdbcType.NVarChar, 10).Value = requesteddate;
        cmd.Parameters.Add("@REQ_TIME", OdbcType.NVarChar, 20).Value = requestedtime;
        cmd.Parameters.Add("@ATTENDANCE", OdbcType.NVarChar, 50).Value = attendance;
        cmd.Parameters.Add("@EVENT_TYPE", OdbcType.NVarChar, 50).Value = eventtype;
        cmd.Parameters.Add("@OTHER_DESC", OdbcType.NVarChar, 225).Value = other;
        cmd.Parameters.Add("@LEAD_SOURCE", OdbcType.NVarChar, 50).Value = leadsource;
        cmd.Parameters.Add("@NOTES", OdbcType.NVarChar, 225).Value = notes;
        cmd.Parameters.Add("@CATERING", OdbcType.NVarChar, 1).Value = catering;
        cmd.Parameters.Add("@BAR", OdbcType.NVarChar, 1).Value = bar;
        cmd.Parameters.Add("@DAMAGE_DEPOSIT", OdbcType.NVarChar, 19).Value = damagedeposit;
        cmd.Parameters.Add("@DANCE_FLOOR", OdbcType.NVarChar, 19).Value = dancefloor;



        cmd.CommandType = CommandType.Text;
        cmd.ExecuteNonQuery();
    }
    catch (System.Data.Odbc.OdbcException ex_msg)
    {

        string msg = "Error occured while inserting";
        msg += ex_msg.Message;
        throw new Exception(msg);
    }
    finally
    {

        conn.Close();
    }
}
protected void Page_Load(object sender, EventArgs e)
{
}
protected void submit_Click(object sender, EventArgs e)
{
    execution(eventspecialist.Text, phone.Text, phone2.Text, firstname.Text, lastname.Text, besttime.SelectedItem.Text, companyname.Text, nonprofit.Text, requesteddate.Text, requestedtime.Text, attendance.Text, eventtype.SelectedItem.Text, other.Text, leadsource.SelectedItem.Text, notes.Text, catering.Text, bar.Text, damagedeposit.Text, dancefloor.SelectedItem.Text);

    conform.Visible = true;
    Control frm = this.FindControl("form1");
    foreach (Control ctrl in frm.Controls)
    {
        if (ctrl is TextBox)
        {
            ((TextBox)ctrl).Text = "";
        }
        else if (ctrl is CheckBox)
        {
            ((CheckBox)ctrl).Checked = false;
        }
        else if (ctrl is DropDownList)
        {
            ((DropDownList)ctrl).SelectedIndex = 0;
        }
    }
}

Answer 1

You're getting a null reference because you've created and set the size of pram but have not added any objects to it. So, what you have is an array filled with nulls. It looks like you can eliminate the for loop and the pram array altogether, because you're adding the parameters to cmd already, outside the loop.

The following code is unnecessary:

OdbcParameter[] pram = new OdbcParameter[19];

and also

for (int i = 0; i < pram.Length; i++)
{
    cmd.Parameters.Add(pram[i]);
}

Additionally, you should set the values of each parameter in this fashion:

cmd.Parameters.Add("@SPECIALIST", OdbcType.NVarChar, 50).Value = eventspecialist;

So, your try block will look like this:

try
{
    conn.Open();
    OdbcCommand cmd = new OdbcCommand(sql, conn);

    cmd.Parameters.Add("@SPECIALIST", OdbcType.NVarChar, 50).Value = eventspecialist;
    cmd.Parameters.Add("@CUST_PHONE1", OdbcType.NVarChar, 50).Value = phone;
    cmd.Parameters.Add("@CUST_PHONE2", OdbcType.NVarChar, 50).Value = phone2;
    cmd.Parameters.Add("@CUST_FNAME", OdbcType.NVarChar, 50).Value = firstname;
    cmd.Parameters.Add("@CUST_LNAME", OdbcType.NVarChar, 50).Value = lastname;
    cmd.Parameters.Add("@BEST_TIME", OdbcType.NVarChar, 50).Value = besttime;
    cmd.Parameters.Add("@COMPANY_NAME", OdbcType.NVarChar, 225).Value = companyname;
    cmd.Parameters.Add("@NONPROFIT", OdbcType.NVarChar, 1).Value = nonprofit;
    cmd.Parameters.Add("@REQ_DATE", OdbcType.Date, 10).Value = requesteddate;
    cmd.Parameters.Add("@REQ_TIME", OdbcType.Time, 20).Value = requestedtime;
    cmd.Parameters.Add("@ATTENDANCE", OdbcType.NVarChar, 50).Value = attendance;
    cmd.Parameters.Add("@EVENT_TYPE", OdbcType.NVarChar, 50).Value = eventtype;
    cmd.Parameters.Add("@OTHER_DESC", OdbcType.NVarChar, 225).Value = other;
    cmd.Parameters.Add("@LEAD_SOURCE", OdbcType.NVarChar, 50).Value = leadsource;
    cmd.Parameters.Add("@NOTES", OdbcType.NVarChar, 225).Value = notes;
    cmd.Parameters.Add("@CATERING", OdbcType.NVarChar, 1).Value = catering;
    cmd.Parameters.Add("@BAR", OdbcType.NVarChar, 1).Value = bar;
    cmd.Parameters.Add("@DAMAGE_DEPOSIT", OdbcType.NVarChar, 19).Value = damagedeposit;
    cmd.Parameters.Add("@DANCE_FLOOR", OdbcType.NVarChar, 19).Value = dancefloor;

    cmd.CommandType = CommandType.Text;
    cmd.ExecuteNonQuery();

}

From your question's edit, I see this is the code you're using, and I take it you're still getting the same error? This most likely means that one of the values you're adding to the parameters collection is null. If you inspect them with the debugger, do they all have values set?

Edit, again:

There's another error which I missed before. The SQL command you've written for ODBC is using parameters incorrectly, as indicated by the new error.

string sql = "INSERT INTO tblcontacts (eventspecialist, phone, phone2, firstname, lastname, besttime, companyname, nonprofit, requesteddate, requestedtime, attendance, eventtype, other, leadsource, notes, catering, bar, damagedeposit, dancefloor) 
VALUES (@SPECIALIST, @CUST_PHONE1, @CUST_PHONE2, @CUST_FNAME, @CUST_LNAME, @BEST_TIME, @COMPANY_NAME, @NONPROFIT, @REQ_DATE, @REQ_TIME, @ATTENDANCE, @EVENT_TYPE, @OTHER_DESC, @LEAD_SOURCE, @NOTES, @CATERING, @BAR, @DAMAGE_DEPOSIT, @DANCE_FLOOR)";

Should be:

string sql = "INSERT INTO tblcontacts (eventspecialist, phone, phone2, firstname, lastname, besttime, companyname, nonprofit, requesteddate, requestedtime, attendance, eventtype, other, leadsource, notes, catering, bar, damagedeposit, dancefloor) 
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

Please note that I've broken up the line of code for readability. Also, the order you add your parameters to the collection matters. They need to be in the order that matches the columns (The value for eventspecialist must be first, the one for phon second, etc.) It may also be the case that you need to name your parameters the same as the column names, as @shahkalpesh suggests.

ODBC commands use ? to access parameters, rather than the parameter name as SQL commands do. A couple links on the subject:

http://msdn.microsoft.com/en-us/library/yy6y35y8.aspx

http://msdn.microsoft.com/en-us/library/8dcw81x5.aspx

Edit

I notice you're passing all the values to the method as string. You'll need to convert those to the correct type, and use the correct ODBC data type, when adding parameters. Please see this link for ODBC parameter types.

Use whatever type the column is (varchar, money, etc), and if your variables (eventspecialist, etc) are not of the correct type, convert the values to the correct type (as indicated in the link) before adding them. For example, cmd.Parameters.Add("@ATTENDANCE", OdbcType.Int).Value = Int32.Parse(attendance);. Please note that Int32.Parse may throw an error if attendance is not a string which represents an integer. I suggest changing the method signature to require the correct types, then validating and converting them before the call to execution. This will avoid type conversion errors in this method. That way, if you do get an error here, you know it's related to the interaction with the DB, rather than setting up the command incorrectly.

Answer 2

The error is because you've defined an array (OdbcParameter[] pram = new OdbcParameter[19];). But, you are not initializing elements of it.

Looking at your code, the following lines in your code is not required.

OdbcParameter[] pram = new OdbcParameter[19];

and

for (int i = 0; i < pram.Length; i++)
{
    cmd.Parameters.Add(pram[i]);
}

EDIT: I have't used Odbc* classes in the past.
Looking at the specific example here, your parameter name should be same as your column name

cmd.Parameters.Add("eventspecialist", OdbcType.NVarChar, 50);
cmd.Parameters.Add("phone", OdbcType.NVarChar, 50);
cmd.Parameters.Add("phone2", OdbcType.NVarChar, 50);
cmd.Parameters.Add("firstname", OdbcType.NVarChar, 50);
cmd.Parameters.Add("lastname", OdbcType.NVarChar, 50);
cmd.Parameters.Add("besttime", OdbcType.NVarChar, 50);

and so on for the rest of the parameters.

Answer 3

Add the Parameter with a value defined, presuming the values you are passing are not null where not specified.

Depending on the Database your parameters names in yoru query must match the parameters names defined in the OdbcParameters collection, and in some instances be in the correct order or application.

As per your update, specify your parameter names in your Query.

string sql = "INSERT INTO tblcontacts (eventspecialist, phone, phone2, firstname, lastname, besttime, companyname, nonprofit, requesteddate, requestedtime, attendance, eventtype, other, leadsource, notes, catering, bar, damagedeposit, dancefloor) VALUES (@EVENTSPECIALIST, @CUST_PHONE1, ETC ETC ETC)"; 


// Remove this code.
OdbcParameter[] pram = new OdbcParameter[19]; 

for (int i = 0; i < pram.Length; i++) 
    { 
        cmd.Parameters.Add(pram[i]); 
    } 


// Add your parameters with a value.
cmd.Parameters.Add("@SPECIALIST", OdbcType.NVarChar, 50).Value = eventSpecialist;

I would also recommend using one of the Native Database connection classes where applicable, System.Data.SqlClient, MySql.Data.Client or the Oracle client.

Answer 4

cmd.Parameters.Add("@SPECIALIST", OdbcType.NVarChar, 50).Value = eventspecialist;
cmd.Parameters.Add("@CUST_PHONE1", OdbcType.NVarChar, 50).Value = phone;
cmd.Parameters.Add("@CUST_PHONE2", OdbcType.NVarChar, 50).Value = phone2;
cmd.Parameters.Add("@CUST_FNAME", OdbcType.NVarChar, 50).Value = firstname;
cmd.Parameters.Add("@CUST_LNAME", OdbcType.NVarChar, 50).Value = lastname;
cmd.Parameters.Add("@BEST_TIME", OdbcType.NVarChar, 50).Value = besttime;
cmd.Parameters.Add("@COMPANY_NAME", OdbcType.NVarChar, 225).Value = companyname;
cmd.Parameters.Add("@NONPROFIT", OdbcType.NVarChar, 1).Value = nonprofit;
cmd.Parameters.Add("@REQ_DATE", OdbcType.Date, 10).Value = requesteddate;
cmd.Parameters.Add("@REQ_TIME", OdbcType.Time, 20).Value = requestedtime;
cmd.Parameters.Add("@ATTENDANCE", OdbcType.NVarChar, 50).Value = attendance;
cmd.Parameters.Add("@EVENT_TYPE", OdbcType.NVarChar, 50).Value = eventtype;
cmd.Parameters.Add("@OTHER_DESC", OdbcType.NVarChar, 225).Value = other;
cmd.Parameters.Add("@LEAD_SOURCE", OdbcType.NVarChar, 50).Value = leadsource;
cmd.Parameters.Add("@NOTES", OdbcType.NVarChar, 225).Value = notes;
cmd.Parameters.Add("@CATERING", OdbcType.NVarChar, 1).Value = catering;
cmd.Parameters.Add("@BAR", OdbcType.NVarChar, 1).Value = bar;
cmd.Parameters.Add("@DAMAGE_DEPOSIT", OdbcType.NVarChar, 19).Value = damagedeposit;
cmd.Parameters.Add("@DANCE_FLOOR", OdbcType.NVarChar, 19).Value = dancefloor;

Answer 5

why are you doing this if you already added parameters in command object.

for (int i = 0; i < pram.Length; i++)
{
    cmd.Parameters.Add(pram[i]);
}

no need of this to do again and also change the

string sql = "INSERT INTO tblcontacts (eventspecialist) VALUES (@SPECIALIST)"

in this manner.