SQL Like and Access database

Report · Aug 28, 2009

i have to retrieve data from access based on Emp_ID extracted from Sql DB. the queries looks like the following:

Can i do 3 queries and do something like the following:

<cfquery datasource="MasterSQL" username="***" password="***" name="SQLQRY">
     Select Employee.Emp_Name, Salary.Emp_ID
     From Employee inner join Salary on (Employee.Emp_ID = Salary.Emp_ID) AND (Employee.SectionName = Salary.SectionName)
     Where Emp_Name like <cfqueryparam value="%#arguments.search#%" cfsqltype="Sql_VarChar">
</cfquery>

<cfquery datasource="MasterDB" name="AccessQRY">
     Select Emp_Pro.Emp_ID
     From Emp_Pro
     WHERE Emp_Pro.Emp_ID like ("%#ValueList(SQLQRY.Emp_ID)#%")
</cfquery>

<cfquery datasource="MasterDB" name="AccessQRY2">
     Select Emp_Info.Desc,
     From Emp_Info inner join Emp_Pro on (Emp_Info.Emp_ID = Emp_Pro.Emp_ID) AND (Emp_Info.Con = Emp_Pro.Con)
     WHERE Emp_Info.Emp_ID IN

this block will change too...
( <cfqueryparam
value="#ValueList(SQLQRY.Emp_ID)#"
cfsqltype="cf_sql_integer"
list="true">
)

</cfquery>

What do i have to change to make this query work...

Report · Aug 28, 2009

This line looks like a huge problem:

WHERE Emp_Pro.Emp_ID like ("%#ValueList(SQLQRY.Emp_ID)#%")

why do you have "like" instead of "in" ?

Report · Aug 28, 2009

i donot know what to put there exactly, I don't know the right syntax...

may be it is more like

WHERE Emp_Pro.Emp_ID like ("%#SQLQRY.Emp_ID#%")

need help determining the right syntax for this query...

Report · Aug 28, 2009

The first query is returning me Emp_ID from SQL Server.

I need to match these Emp_ID with Emp_ID in access database and extract the Emp_Desc from Access.

The problem is the Emp_ID looks like the following:

The Emp_ID from the first query (Sql Server)looks like the following:
A 1000
A 1123
A 23456
B 450674

The Emp_ID in Access DB looks like
1000**
1123***
23456**
450674***

If i just use WHERE IN it doesn't return anything so I have to use LIKE some how to match these Emp_ID...

That is what I was trying to do here in second query use like and extract the Emp_ID from Access DB to pass in the 3rd query to reterive Emp_Desc in the 3rd query...

Any help is appreciated...

Report · Aug 28, 2009

This is the correct syntax:

WHERE Emp_ID LIKE "%#SQLQRY.Emp_ID#%"

or if you prefer more

WHERE Emp_Pro.Emp_ID LIKE "%#SQLQRY.Emp_ID#%"

Report · Aug 28, 2009

The firts query returns me an ID

A 1000

but the second query won't match any ID

it should retun 1000** right ???

The Emp_ID from the first query (Sql Server)looks like the following:
A 1000
A 1123
A 23456
B 450674

The Emp_ID in Access DB looks like
1000**
1123***
23456**
450674***

If i just use WHERE IN it doesn't return anything so I have to use LIKE some how to match these Emp_ID...

That is what I was trying to do here in second query use like and extract the Emp_ID from Access DB to pass in the 3rd query to reterive Emp_Desc in the 3rd query...

Any help is appreciated...

Report · Aug 28, 2009

is it possible to use an escape charater in this case for example:

WHERE Emp_Pro.Emp_ID LIKE "' %' escape' '#SQLQRY.Emp_ID#%"

the idea here is to look after the space A 1000 so that it could be matched to Emp.ID in Access that looks like 1000**

Report · Aug 28, 2009

"is it possible to use an escape charater in this case for example:

WHERE Emp_Pro.Emp_ID LIKE "' %' escape' '#SQLQRY.Emp_ID#%"

the idea here is to look after the space A 1000 so that it could be matched to Emp.ID in Access that looks like 1000**"" "

yes it is. WHERE Emp_Pro.Emp_ID LIKE "%#Right(SQLQRY.Emp_ID,4)#%

If you can not make it work, i will type your code from start tomorrow! And why are you using stars(1000**) in Access database?

Report · Aug 28, 2009

Thanks...

the stars are there in the database already...

Report · Aug 28, 2009

you are welcome. Is everything ok now?

Report · Aug 28, 2009

somehow when I enter a new Emp_ID it still gives me the same desc over and over .

<cfquery datasource="MasterSQL" username="***" password="***" name="SQLQRY">
     Select Employee.Emp_Name, Salary.Emp_ID
     From Employee inner join Salary on (Employee.Emp_ID = Salary.Emp_ID) AND (Employee.SectionName = Salary.SectionName)
     Where Emp_Name like <cfqueryparam value="%#arguments.search#%" cfsqltype="Sql_VarChar">
</cfquery>

<cfquery datasource="MasterDB" name="AccessQRY">
     Select Emp_Pro.Emp_ID
     From Emp_Pro
     WHERE Emp_Pro.Emp_ID like '%#Right(SQLQRY.Emp_ID,4)#%'
</cfquery>

<cfquery datasource="MasterDB" name="AccessQRY2">
     Select Emp_Info.Desc,
     From Emp_Info inner join Emp_Pro on (Emp_Info.Emp_ID = Emp_Pro.Emp_ID) AND (Emp_Info.Con = Emp_Pro.Con)
     WHERE Emp_Info.Emp_ID IN( <cfqueryparam
value="#ValueList(SQLQRY.Emp_ID)#"
cfsqltype="cf_sql_integer"
list="true">
)

is something wrong in the above query

Report · Aug 28, 2009

In that last query...


<cfquery datasource="MasterDB" name="AccessQRY2">
     Select Emp_Info.Desc,
     From Emp_Info inner join Emp_Pro on (Emp_Info.Emp_ID = Emp_Pro.Emp_ID) AND (Emp_Info.Con = Emp_Pro.Con)
     WHERE Emp_Info.Emp_ID IN( <cfqueryparam
value="#ValueList(SQLQRY.Emp_ID)#"
cfsqltype="cf_sql_integer"
list="true">

You have an errant comma at the end of your SELECT statement, after Emp_Info.Desc

Report · Aug 29, 2009

it seems like a problem with default value(#arguments.search#). It can not recognize your input and on each run it will show desc for default ID..!

why did you use <cfqueryparam value..?

Report · Sep 01, 2009

<cfqueryparam is parameter for whatever the user wants to serach on...

Report · Sep 01, 2009

Yes i know that!!

Try this for test:

<cfquery datasource="MasterSQL" username="***" password="***" name="SQLQRY">
    Select Employee.Emp_Name, Salary.Emp_ID
    From Employee inner join Salary on (Employee.Emp_ID = Salary.Emp_ID) AND (Employee.SectionName = Salary.SectionName)
    Where Emp_Name like <cfqueryparam value="%#arguments.search#%" cfsqltype="Sql_VarChar">
</cfquery>

<cfquery datasource="MasterDB" name="AccessQRY">
     Select Emp_Pro.Emp_ID
     From Emp_Pro
     WHERE Emp_Pro.Emp_ID LIKE '%#Right(SQLQRY.Emp_ID,4)#%'
</cfquery>

<cfoutput>#AccessQRY.NAME(INSERT CORRECT NAME)#</cfoutput>

I still think it seems like a problem with default value(#arguments.search#). It can not recognize your input and on each run it will show description for default ID..!

How are you passing arguments.search?

Report · Aug 28, 2009

It's not as bad as I thought. You need to loop through the values from the first query. Something like this

query q1 gets your id's from mssql

<cfquery name ="q2"

select yourfields

from yourtables

where your_id_field like '#id#*%'

union

</cfloop>

select yourfields

from yourtables

where 1= 2

Report · Sep 01, 2009

I did the follwoing:

<cfquery datasource="MasterSQL" username="***" password="***" name="SQLQRY">
    Select Employee.Emp_Name, Salary.Emp_ID
    From Employee inner join Salary on (Employee.Emp_ID = Salary.Emp_ID) AND (Employee.SectionName = Salary.SectionName)
    Where Emp_Name like <cfqueryparam value="%#arguments.search#%" cfsqltype="Sql_VarChar">
</cfquery>

<cfquery name ="AcessQRY"

     Select Emp_Info.Desc, Emp_Pro.Emp_ID
     From Emp_Info inner join Emp_Pro on (Emp_Info.Emp_ID = Emp_Pro.Emp_ID) AND (Emp_Info.Con = Emp_Pro.Con)
     WHERE Emp_Pro.Emp_ID LIKE '%#Right(SQLQRY.Emp_ID,4)#%'

union

</cfloop>

select Emp_Info.Desc, Emp_Pro.Emp_ID
from Emp_Info inner join Emp_Pro on (Emp_Info.Emp_ID = Emp_Pro.Emp_ID) AND (Emp_Info.Con = Emp_Pro.Con)

where 1= 2

But this is not working either.

How can i put a like condition in the where clause so that it satisafy a condition like following where SQL returns A 1000 and it has to match with 1000** in Access DB
The form number from the first query looks like the following:
A 1000
A 1123
A 23456
B 450674

The form number in the second query looks like
1000**
1123***
23456**
450674***

Any help is appreciated...

Report · Sep 01, 2009

Did the query run without errors? If so, did it return any records?

the right function here:

WHERE Emp_Pro.Emp_ID LIKE '%#Right(SQLQRY.Emp_ID,4)#%

is probably messing you up.

Looking at your data, all you probably need is:

WHERE Emp_Pro.Emp_ID LIKE '%#SQLQRY.Emp_ID#%'

You may also have a problem with your data. MS Access uses asterisks as wildcards. If that turns out to be problematic, it's beyond my level of expertise.

Report · Sep 01, 2009

WHERE Emp_Pro.Emp_ID LIKE '%#Right(SQLQRY.Emp_ID,4)#%

Dan, RosieGp asked me for such a function because he want to compare last for characters from MySQL database! As you can see from previous posts

first i typed him syntax you are talking about WHERE Emp_Pro.Emp_ID LIKE '%#SQLQRY.Emp_ID#%' but he was unable to make it work!

RosieGp if you have problem with "MS Access uses asterisks as wildcards" i will not be able to help you.

Report · Sep 02, 2009

CAn you export data from Access as csv? If you can you can easily import data from Access to MySQL!!

Just type this code to your mysql query browser and execute operation:

load data local infile 'C:\yourfile.csv' into table yourtable
fields terminated by ','
enclosed by '"'
lines terminated by ' '
(column1,column2,column3,column4,column5)

Note that columns names in csv must be same as columns names in MySQL database.
Also you must have same number of columns.