CODEWITHSUNDEEP
c#.netwinformsms-accessoledb
TtT23
TtT23

Reputation: 7040

OLEDB Parameterized Query

public void LoadDB()
{
    string FileName = @"c:\asdf.accdb";
    string query = "SELECT ID, Field1 FROM Table1 WHERE ID=? AND Field1=?";
    string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName;

    OleDbConnection odc = new OleDbConnection(strConn);
    dAdapter = new OleDbDataAdapter();
    OleDbCommand cmd = new OleDbCommand(query,odc);

    cmd.Parameters.Add("?", OleDbType.Integer, 5).Value = 1234;
    cmd.Parameters.Add("?", OleDbType.BSTR, 5).Value ="asdf";

    dAdapter.SelectCommand = cmd;

    ds = new DataSet();
    dAdapter.Fill(ds);
    dataGridView1.DataSource = ds.Tables[0];
}

I'm trying to use parametrized query to bind the access file into the datagridview. It finds the column names fine, but the contents are empty.

How can I fix this issue?

Upvotes: 2

Views: 19366

Answers (3)

Hesein Burg
Hesein Burg

Reputation: 329

Here is an example of how the parameterized queries work with CSharp, OleDB.

try
{
    connw.Open();
    OleDbCommand command;
    command = new OleDbCommand(
        "Update Deliveries " +
        "SET Deliveries.EmployeeID = ?, Deliveries.FIN = ?, Deliveries.TodaysOrders = ? , connw);
    command.Parameters.Add(new OleDbParameter("@EMPID", Convert.ToDecimal(empsplitIt[1])));
    command.Parameters.Add(new OleDbParameter("@FIN", truckSplit[1].ToString()));
    command.Parameters.Add(new OleDbParameter("@TodaysOrder", "R"));
    catchReturnedRows = command.ExecuteNonQuery();//Commit   
    connw.Close();

}
catch (OleDbException exception)
{
    MessageBox.Show(exception.Message, "OleDb Exception");
}

This will work with any sql statement, you must assign the question mark "?" to each parameter, and then below you must create the parameters and add them in the order of how you laid out the question marks to get the right data into the right field.

Upvotes: 5

Miroslav Zadravec
Miroslav Zadravec

Reputation: 3740

Try without specifying column size in parameters:

cmd.Parameters.Add("?", OleDbType.Integer).Value = 1234;
cmd.Parameters.Add("?", OleDbType.BSTR).Value ="asdf";

Upvotes: 0

Rastus7
Rastus7

Reputation: 416

In my test program, the ds.Tables[0].Rows.Count datatable actually had 1 row returned (as there was one row in my test database that matched the query itself). If you put a break on this line you should be able to see whether or not data is getting into the datatable in the first place. Try this:

dataGridView1.DataSource = ds.Tables[0];

What does the front end binding of dataGridView1 look like? Running the query in Access could shed some light on the situation too.

Upvotes: 2

Related Questions