OleDbCommand, No value given

Question

With an Access table (Customers) and two fields (CustomerID, CustomerName):

string queryString = "INSERT INTO Customers (CustomerID, CompanyName)  VALUES (qqq, xyz)";
OleDbCommand command = new OleDbCommand(queryString, connection);
command.ExecuteNonQuery();

results in an error:

No value given for one or more required parameters

Both fields have a text Data Type

What are qqq and xyz? You need to use a parameterized query – Steve Dec 12 '14 at 15:04 — Steve, Dec 12 '14 at 15:04

score 1 · Answer 1 · answered Dec 12 '14 at 15:05

You should pass your values to the database engine using a parameterized query

string queryString = "INSERT INTO Customers (CustomerID, CompanyName)  VALUES (?,?)";
OleDbCommand command = new OleDbCommand(queryString, connection);
command.Parameters.AddWithValue("@p1", valueForCustomerIDFIeld)
command.Parameters.AddWithValue("@p2", valueForComapnyNameFIeld)
command.ExecuteNonQuery();

Actually I don't know what the qqq and xyz are. But embedding them inside the sql string cause them to be considered as parameter placeholders, thus the engine asks for two parameters.

Unless they are between single quotes, where they would be considered as values. Other than that, parameterized query is the way to go. — Will Marcouiller, Dec 12 '14 at 15:25

Soner Gönül · Accepted Answer · 2014-12-12T15:19:17.057

I strongly suspect since you used qqq and xyz without single quotes, OleDbCommand see them as a parameter. That's why it expects some parameter values for both.

Because if they were strings, they would be used with single quotes like;

INSERT INTO Customers (CustomerID, CompanyName)  VALUES ('qqq', 'xyz')

If they are some variables, and want to add them to your command, you should always use parameterized queries to prevent SQL Injection attacks.

using(OleDbConnection connection = new OleDbConnection(conString))
using(OleDbCommand command = _connection.CreateCommand())
{
    command.CommandText = "INSERT INTO Customers(CustomerID, CompanyName) VALUES(?, ?)";
    command.Parameters.Add("@p1", OleDbType.VarChar).Value = qqq;
    command.Parameters.Add("@p2", OleDbType.VarChar).Value = xyz;
    connection.Open();
    command.ExecuteNonQuery();
}

And CustomerID is a very bad name for a character column because as a general ID part used for numerical values.

OleDbCommand, No value given

2 Answers2