Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MS Access SQL LIKE query from C#

I have this query for Ms Access and im using C# and Ole DB commands. It works on Ms Access but when I'm passing the query from C# using OleDB, nothing happened. Anyway here's my code:

SQL query

SELECT * FROM tblIssue WHERE  id LIKE '*2*' AND dateChecque LIKE '**'AND +
issueTo LIKE '**' AND byTheName LIKE '**' AND bankName LIKE '**' AND accountNo LIKE '**' +
AND checqueNo LIKE '**' AND amount LIKE '**' AND being LIKE '**'   AND whoDeleted LIKE '**' +
AND whyDeleted LIKE '**' AND dateCreated LIKE '**';

C# code

try
{
    DataTable newDt = new DataTable();
    OleDbDataAdapter newSda = new OleDbDataAdapter(sqlQuery , conn);
    newSda.Fill(newDt);

    if (newDt.Rows.Count > 0)
    {
        dataGridView1.DataSource = newDt.DefaultView;
        _hasData = true;
    }
    else
    {
        _hasData = false;
    }
}
catch (Exception error)
{
    MessageBox.Show(error.ToString()); conn.Close();
}
like image 314
ViFer Avatar asked Jun 08 '13 13:06

ViFer


People also ask

How do you write a like query in Access?

In an expression, you can use the Like operator to compare a field value to a string expression. For example, if you enter Like “C*” in an SQL query, the query returns all field values beginning with the letter C. In a parameter query, you can prompt the user for a pattern to search for.

Is Microsoft Access like SQL?

Microsoft Access is full-fledged application which is used for managing the databases. SQL is not application i.e. database management programming language.

Can I use like in select SQL?

The SQL LIKE condition allows you to use wildcards to perform pattern matching in a query. The LIKE condition is used in the WHERE clause of a SELECT, INSERT, UPDATE, or DELETE statement.

Can you code SQL in Access?

In MS Access, you can create a SQL query by either writing the code directly (SELECT field FROM table) or you can use a special interface to help construct the code for you.


2 Answers

Queries performed from within the Microsoft Access application itself normally use * and ? as wildcard characters for the LIKE operator. OleDb connections to an Access database from an external application should use the % and _ wildcard characters instead. (The latter are actually the more commonly-used wildcard characters in other SQL dialects.)

like image 100
Gord Thompson Avatar answered Sep 23 '22 15:09

Gord Thompson


From http://technet.microsoft.com/en-us/library/cc966377.aspx :

Microsoft Jet uses partial match (or "wildcard") characters with the Like operator that are different from those used in most SQL dialects. The asterisk (*) character matches zero or more characters and is equivalent to the percent (%) character in ANSI SQL. The other Microsoft Jet partial match characters are the question mark (?), which matches any character in a single field, and the number sign (#), which matches any digit in a single field.

like image 28
StockB Avatar answered Sep 25 '22 15:09

StockB