OLEDB "select, where, like" Query

[Mike Askew]

I am currently creating a product lookup system for a project of mine, however from reading around it has come to my attention that an OLEDB connection will not handle the SQL query below as it will not handle more than a simple "SELECT, FROM".

Table: tbl_ProductInformation
Field: Product_Name
CurrentEntry = Variable storing the users entry

"SELECT Product_Name FROM tbl_ProductInformation WHERE Product_Name LIKE '*" & CurrentEntry & "*'"

The outcome of this query is then put into a listbox.

Is there anyway to make the OLEDB SQL query work so that the returned entries will correctly narrow down to the entered text.

[Netcode]

first, the variable has to get its value from an input source, maybe a text box
so why not do something like this

"SELECT Product_Name FROM tb_ProductInformation" & _
WHERE Product_Name Like "'+ textbox1.text+'"

[Mike Askew]

The variable does pull from a text box, the issue is getting the full SQL query to work using OLEDB connection

[crapulency]

Hi Mikey. Use % instead of * for your wildcard

"SELECT Product_Name FROM tbl_ProductInformation WHERE Product_Name LIKE '%" & CurrentEntry & "%'"

Just a quirk of oledb

[Mike Askew]

Even when using the % wildcard this will still not solve the handling issue by the OLEDB connector as it simply wont handle complex SQL queries

[Momerath]

OleDB doesn't handle the SQL query at all, it just passes it to the database. So I don't understand why you think a where clause won't work, unless your database doesn't support them.

[Mike Askew]

Well momerath, it isn't supporting the clause. Even when i write the SQL in access before swapping it into my code

[Momerath]

So writing the SQL code into Access doesn't work, and you blame the OleDB connection? I'm not sure what you are doing, as Access works fine when I use the WHERE clause.

Can you post an example of data that you believe should be selected (exactly as it appears in the database) and an example of a query that doesn't select it (but you think it should).

[Mike Askew]

tbl_ProductInformation

Field: Product_Name

Entries:
Milk
Milkshake
Mushroom

SQL:
SELECT Product_Name FROM tbl_ProductInformation WHERE Product_Name LIKE '%M%'

I believe this should list all three entries in the database.

[Momerath]

Your problem is based on the version of access you are using. Use the * character as the wildcard character instead of the % and see if that solves it.

[Mike Askew]

Ok the SQL returns as it should using the * character instead of a %.

However when this SQL is now executed through my OLEDB connection I get a negative response.

Below is the entire connection code and the textbox check im currently using at the bottom to check for a positive return

CurrentEntry = txt_SearchInput.Text

'Sets the database connection information
        'DBP = Provider
        'DBS = Source
        DBP = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
        DBS = "Data Source = H:\Computing\Project\ProjectDatabase.mdb"

        'Sets the connection
        Connection.ConnectionString = DBP & DBS
        'Opens the connection
        Connection.Open()
        'Writes the SQL query for product searching
        SQL = "SELECT Product_Name FROM tbl_ProductInformation WHERE Product_Name LIKE '*" & CurrentEntry & "*'"
        'Exectutes the SQL product searching and fills data adapter
        DA = New OleDb.OleDbDataAdapter(SQL, Connection)
        'Fills data sheet from the data adaptor and names the information 'Products'
        DA.Fill(DS, "Products")
        'Counts how many possible products match the search
        MaxRows = DS.Tables("Products").Rows.Count
        'Shuts the database connection
        Connection.Close()

        TextBox1.text = MaxRows

The textbox1.text currently displays as "0" when the connection is run. This is what is confusing me.

[crapulency]

Using the Access application, you should use * as your wildcard.
When accessing an Access database file using oledb, you should use %

In the code in your last post, replace this line:

SQL = "SELECT Product_Name FROM tbl_ProductInformation WHERE Product_Name LIKE '*" & CurrentEntry & "*'"

with this one:

SQL = "SELECT Product_Name FROM tbl_ProductInformation WHERE Product_Name LIKE '%" & CurrentEntry & "%'"

[Mike Askew]

Aha this solved my issue. The search is now working as i would like it too.

Thank you very much guys :)

[rajkumar8910]

I want to select sum of fiels value between two Dates
so here i using this command
SELECT SUM (TotalCost) FROM SiteExpense where ToDate between @dtp and @dtp1
But it give Error

so plz Resolve It

[poojavb]

what error are u getting???
things u can check
1. check the datatype ...it shud be number related datatype
2. check the values u r passing to the date column....
3. the value for date shud be in the same format as it is there in database....

it worked for me but I manuallly passed values....

SELECT SUM ([Total]) FROM Tablename where [dtpDate] between '2012-05-22' and '2012-05-22'

total - int datatype
dtpDate - date datatype....

I tried this in mssql....what database r u using??? check that too...

[Carmelo_1]

Hi.
I resume this old post to ask:
I tried "CurrentEntry" equal to "?? R" to extract, for example, the "CAR" record, but the result is equal to ZERO ...

Why ?
Thanks

[Carmelo_1]

I resolved.
The jolly isn't "?" but "_".

So, it's go !!