Parameterized query for inserting values

问题:

I was trying to insert values into an Access database using a parameterized query:

private void button1_Click(object sender, EventArgs e)
        {
            if (validationcontrol())
            {
                MessageBox.Show(cmbjobcode.SelectedValue.ToString());
                OleDbConnection oleDbConnection1 = new System.Data.OleDb.OleDbConnection(connString);
                oleDbConnection1.Open();
                OleDbCommand oleDbCommand1 = new System.Data.OleDb.OleDbCommand("INSERT INTO quotationmastertable (quotationcode ,jobcode , jobpk , sillabordercharges , battabordercharges , driverpayment , rent , extra , total , discount , remark ,amount ) Values (?,?,?,?,?,?,?,?,?,?,?,?) ", oleDbConnection1);
                oleDbCommand1.Parameters.Add(txtquotationno.Text);
                oleDbCommand1.Parameters.Add(cmbjobcode.Text);
                oleDbCommand1.Parameters.Add(cmbjobcode.SelectedValue);
                oleDbCommand1.Parameters.Add(int.Parse(txtsilabordercharges.Text));
                oleDbCommand1.Parameters.Add(int.Parse(txtbattacharges.Text));
                oleDbCommand1.Parameters.Add(int.Parse(txtdriverpayment.Text));
                oleDbCommand1.Parameters.Add(int.Parse(txtrent.Text));
                oleDbCommand1.Parameters.Add(int.Parse(txtextra.Text));
                oleDbCommand1.Parameters.Add(int.Parse(txttotal.Text));
                oleDbCommand1.Parameters.Add(int.Parse(txtdiscount.Text));
                oleDbCommand1.Parameters.Add(txtremark.Text);
                oleDbCommand1.Parameters.Add(int.Parse(txtamount.Text));
                oleDbCommand1.CommandType = CommandType.Text;
                oleDbCommand1.ExecuteNonQuery();
                oleDbConnection1.Close();
                MessageBox.Show(txtquotationno.Text);

            }
        }

but I am getting an exception at the first line itself:

oleDbCommand1.Parameters.Add(txtquotationno.Text);

The exception is

The OleDbParameterCollection only accepts non-null OleDbParameter type objects, not String objects.

I am new to programming; can anyone help in pointing out my mistakes?

回答1:

A single parameter for the Add object is expecting an OleDBParameter object. You are just passing strings and data.

A simple fix would be to use the AddWithValue method:

oleDbCommand1.Parameters.AddWithValue("?", txtquotationno.Text);
oleDbCommand1.Parameters.AddWithValue("?", cmbjobcode.Text);

OleDB does not really use parameter names, it's index based, which is why you can pass the question mark for each one of your parameters as the name. You do have to make sure your parameters are in the same order as your query statement.

回答2:

You are tryng to add a string to a collection of parameters. Try this (changing OleDbType.VarChar, 50 to the actual type of the data column in your db.

oleDbCommand1.Parameters.Add("@quot", OleDbType.VarChar, 50).Value =  txtquotationno.Text;

See the msdn for an example: http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbparameter.aspx