CODEWITHSUNDEEP
c#ms-accessoledbdataadapter
MaylorTaylor
MaylorTaylor

Reputation: 5041

OleDB Like query using DataAdapter

Below is a function that goes to a database and pulls a controlNumber that is similar to one in the database. As you can see by the commented out section near the top, I want to replace the last 3 digits of the controlNumber with wildcard indicators () that way it will only search the database for controlnumbers similar to the one below that line ("1289**")

Inside the database, I can use the query "SELECT * FROM Orders WHERE Control_Number LIKE '1298***';" and I am rewarded with the correct information. But when I try it in the code using this method I am returned 0 rows.

Any help?

NOTE: I am going to an Access Database, not a mysql

public string ABSCheckControlNumberForAccuracy(string _controlNumber,string _zip,string _state, string _city)
        {
            //_controlNumber = _controlNumber.Remove(_controlNumber.Length - 3) + "***";
            _controlNumber = "1298***";
            OleDbConnection conn = new OleDbConnection(strAccessConnTaxCert);

            string query = "SELECT * FROM Orders WHERE Control_Number LIKE @_controlNumber;";


            OleDbCommand cmd = new OleDbCommand(query, conn);

            cmd.CommandText = query;
             OleDbDataAdapter adapter = new OleDbDataAdapter(query, conn);
            adapter.SelectCommand.Parameters.Add("@_controlNumber", OleDbType.VarChar).Value = "%" + _controlNumber + "%";
                      System.Data.DataTable dt = new System.Data.DataTable();
            try
            {
                conn.Open();
                adapter.Fill(dt);
            }

            catch (System.Exception ex)
            {
                               throw;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
            return dt.Rows[0][0].ToString();            
        }

Upvotes: 0

Views: 1817

Answers (1)

Frederik Gheysels
Frederik Gheysels

Reputation: 56934

Use % as the wildcard character instead of *. % is the defacto wildcard. The oledb driver will translate it into the wildcard character that the target database expects.

edit: i have reread the question. You shouldn't use % but you should use ? instead. ? is the wildcard that you should use for exactly one character.

this means your query should look like this

select * from table where foo like '567???'

Also, try to use parameters instead of string concatenation in your query.

Upvotes: 4

Related Questions