Reading data from an access database

[Martin310]

Hi guys,

I've created a program to write form data to an access database. Currently I am extending it so that I can read the data back from the database.

I have created a new dialog with a datagridview to view the table data. I wish to be able to search for a specific User ID, the User ID can have multiple instances within the database. So I want the search to display all of the rows for that ID.

I thought I had it sorted but am getting:
syntax error (missing operator)
In the line:

Adapter.Fill(ds);

Here is the code I have for the search button:

string ConString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Database.mdb";
            OleDbConnection Con = new OleDbConnection(ConString);
            OleDbCommand Command = Con.CreateCommand();

            // create the DataSet
            DataSet ds = new DataSet();

            // clear the grids data source
            dtaSearchID.DataSource = null;

            // open the connection
            Con.Open();

            // run the query
            Command.CommandText = "Select * From Codes Where User ID = " + txtSearchID.Text;
            OleDbDataAdapter Adapter = new OleDbDataAdapter(Command);
            Adapter.Fill(ds);

            // close the connection
            Con.Close();

            // set the grid's data source
            dtaSearchID.DataSource = ds.Tables[0];

In the end I would like to be able to load the selected row data back into my main dialog. But I will deal with that when I get to it.

Any help would be greatly appreciated.
Many thanks
Martin

[alc6379]

looks like you could have an error in your query syntax:

"Select * From Codes Where User ID = " +

probably needs to look like

"Select * From Codes Where [User ID] = " +

The "missing operator is probably the runtime complaining that you don't have an "=" that it expects after the token "User".

[Martin310]

Hey, thanks for the reply.

I changed the User ID to [User ID] but got the same message.

I'm really stumped with this, any ideas?

Thanks

[dotyell]

Check the type definition of "User ID" and make sure it is int, number...(not text). If it accepts text you'll have to restructure your statement; "Select * From Codes Where User ID = '" + txtSearchID.Text + "'". Jus restructure your sql statement like I just did. It must work.

[Martin310]

Hey,

thats got it working.

Thank you very much

[vbr]

    private void button1_Click(object sender, EventArgs e)
    {
        string ConString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Database.mdb";
        OleDbConnection Con = new OleDbConnection(ConString);
        OleDbCommand Command = Con.CreateCommand();
        DataSet ds = new DataSet();

        dtaSearchID.DataSource = null;

        Con.Open();            
            Command.CommandText = "Select * From Codes Where [User ID] = '" + txtSearch.Text + "'";
            OleDbDataAdapter Adapter = new OleDbDataAdapter(Command);
            Adapter.Fill(ds);
        Con.Close();

        if (ds.Tables[0].Rows.Count > 0)
        {
            lblResult.Text = "YES";
        }
        else
        {
            lblResult.Text = "NO";
        }

        dtaSearchID.DataSource = ds.Tables[0];
    }