Syntax Error in FROM Clause

[web24]

Hi Guys,

I am very new to Databases and keep getting this error in VB6 : "Syntax Error in FROM Clause"
I'm using a MS Access database connected to my project via the Adodc control which has it's recordsource properties set to my table (Clients), I am trying to filter my clients table to only show client records that match the SQL query for the field (username)

my code:
=============================================================================
Dim strSql As String

strSql = "SELECT * FROM clients WHERE username LIKE '*abc*'"
Adodc1.RecordSource = strSql
Adodc1.Refresh
=============================================================================

All help will be highly appreciated, many thanks, Devan

[kinwang2009]

Hi web24,
Use

strSQL = "SELECT * FROM clients WHERE username LIKE '" & txtUsername.Text & "'"

instead of

strSql = "SELECT * FROM clients WHERE username LIKE '*abc*'"

Here txtUsername is your field(username).

Hope this helps

[web24]

hi kinwang2009,

Still no go, I can't believe that we're actually doing something wrong here, I have tried over 20 times to no avail.

As mentioned ( might be the problem ) I am populating the datagrid control via Adodc control, maybe I should try to open the database via code not control? Will that make a difference at all?

what do you think?

regards

devan

[web24]

Hi,

OK, I nailed it down to the Adodc control Refresh method, everything runs fine if I remove the ado2.refresh code, but once I put it back, I get the Syntax error, followed by the Adodc Refresh Failed error, any help on this?

[CreativeLightni]

Can you post all of the code from from this routine? It works for me but one thing you will want to do if you are using the like statement instead of = you need wildcards around the value. % is the wildcard symbol. Like this:

SQL = "SELECT * FROM clients WHERE username LIKE '%" & txtUsername.text & "%'"

You can have the wildcard symbol before after or both. With both you can type tev and return username steven with the % only at the end ste would return steven etc.

I'm just making sure your username field in the table clients is text right?

[CreativeLightni]

Also make sure your adodc1 command type is set to 8 so the from clause will work.

[omoridi]

use % not * (like '%' & &'%')

[omoridi]

don't forgot in ur string if u have ' sign u must replace it with ''