Parameterized query for inserting values

Question

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?

LarsTech · Accepted Answer · 2012-02-22 19:55:16Z

4

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.

answered Feb 22, 2012 at 19:55

LarsTech

81.9k14 gold badges161 silver badges237 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Paolo Falabella · Accepted Answer · 2012-02-22 19:58:52Z

0

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

answered Feb 22, 2012 at 19:58

Paolo Falabella

26k3 gold badges79 silver badges91 bronze badges

Collectives™ on Stack Overflow

Parameterized query for inserting values

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related