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

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".

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

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.

    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];
    }

Edited 3 Years Ago by Nick Evan: Fixed formatting

This question has already been answered. Start a new discussion instead.