CODEWITHSUNDEEP
c#ms-access
Jeff Brady
Jeff Brady

Reputation: 1498

Why is this query not returning a result?

I have an Access table named Table1, which has a field named FieldA. FieldA had a Data Type of Text.

FieldA does contain the value 010005.

When I run the query, it doesn't find a match. If I run a query within Access, it does find the value.

OleDbConnection conn = null;
OleDbDataReader reader = null;

conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=\\\\server\\folder\\mydatabase.accdb");
conn.Open();

OleDbCommand cmd = new OleDbCommand("Select * FROM Table1 WHERE FieldA = @p1", conn);
cmd.Parameters.Add("@p1", OleDbType.VarChar);
cmd.Parameters["@p1"].Value = "010005";
reader = cmd.ExecuteReader();

dataGridView1.DataSource = reader;

What am I missing here?

Thank you!

EDIT: This worked, and is what I ended up using:

OleDbConnection conn = null;
conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=\\\\server\\folder\\mydatabase.accdb");
conn.Open();

string theValue = "010005";
string sql = string.Format("SELECT * FROM Table 1 where FieldA = '{0}'", theValue);

OleDbDataAdapter ada = new OleDbDataAdapter(sql, conn);

ada.Fill(dataTable1);

dataGridView1.DataSource = dataTable1;

Upvotes: 0

Views: 180

Answers (4)

Nicholas Carey
Nicholas Carey

Reputation: 74187

I believe you need to Prepare() the command after you've add the parameters to the command for the first time and prior to executing it:

OleDbCommand command    = new OleDbCommand(null, rConn);

// Create and prepare an SQL statement.
command.CommandText = "insert into Region (RegionID, RegionDescription) values (@id, @desc)" ;
command.Parameters.Add ( "@id", id) ;
command.Parameters.Add ( "@desc", desc) ;
command.Prepare() ;  // Calling Prepare after having set the Commandtext and parameters.
command.ExecuteNonQuery();

// Change parameter values and call ExecuteNonQuery.
command.Parameters[0].Value = 21;
command.Parameters[1].Value = "mySecondRegion";
command.ExecuteNonQuery();

Once it's been properly Prepared(), you don't need to do that again (unless you change the parameters proper (rather than just their values).

Upvotes: 0

Quintium
Quintium

Reputation: 497

Probably because you are trying to use named parameter with OleDb which I do not believe is supported.

Detail on how to do it instead: http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbcommand.parameters.aspx

Upvotes: 0

Ulises
Ulises

Reputation: 13419

You are looking for @p1 since you quoted it

Use this instead:

OleDbCommand cmd = new OleDbCommand("Select * FROM Table1 WHERE FieldA = @p1", conn);

Upvotes: 0

Guffa
Guffa

Reputation: 700192

You are assigning a number to the parameter, so the value will be converted to the string "10005", not "010005".

Assign it as a string to the parameter:

cmd.Parameters["@p1"].Value = "010005";

Also, you need to remove the apostrophes around the parameter, otherwise it won't recognise it as a parameter and look for the string "@p1" instead:

OleDbCommand cmd = new OleDbCommand("Select * FROM Table1 WHERE FieldA = @p1", conn);

Upvotes: 3

Related Questions