SQL with variables via DataEnvironment

[gEielts]

Hi all there,

Every time I use SQL in cooperation with the DataEnvironment in VB6, I have troubles. Need your help mostly:

I have created a connection in the DataEnvironment and by the SQL-builder i made the commandtext.

e.g.

DataEnvironmentname = DE
Connectionname = CRMDATABASE
Commandname = AllCust


The syntax of the sql =
SELECT KUNNR, NAME1, STRAS, PSTLZ, ORT01, LAND1, LOEVM FROM ZAN_KNA1 WHERE (NAME1 LIKE '"*" & `m_NAME` & "*"') AND (ORT01 LIKE '"*" & `m_CITY` & "*"') AND (LAND1 LIKE '"*" & [m_COUNTRY] & "*"')

As you see m_NAME, m_CITY and m_COUNTRY are variables, defined in the code. As you can also see, I use the * in front of
and behind the variable, so the user can type a few characters and the sql must populate the recordset

In code i use the next syntax for opening the recordset

DE.rsAllCust.open and the result is DE.rsAllCust.recordcount = 0

The problem is everytime, what do i use around the variables:
is that {} or [] or () or '' or "" or whatever???????

Help would be appreciated..


Ger

[ralph]

Try % instead of *. (% is used with ado)

[WizardLMB]

Sorry

SQL like that will never work with DE. If you are trying to create a very simple Data Report. Connect using Code, Connection like

Dim CNN as New ADODB.Connection

Then open the Recordset

Set DataReport.DataSource=Recordset

Then

DataReport.Show

Try this one it will work for you. This is the reason why many run to using Crysteal report and not Datareports.

If the report is Hierychal, you must stick to DE, I do not have the answer for you.

I hope you come right.

Wizard
SA

[techgnome]

Originally posted by ralph
Try % instead of *. (% is used with ado)

Actually it's an Access/SQL issue, not an ado one... Access uses '*' as it's wildcard, while SQL uses '%'....

[gEielts]

Thx guys,

So SQL % and ACCESS * as wildcard

But when i tried it , the result seemed to be:

SQL % and ACCESS ? as wildcard

Where can I find documentation about this ?

Greetings, Ger

[ralph]

quote by techgnome:
Actually it's an Access/SQL issue, not an ado one... Access uses '*' as it's wildcard, while SQL uses '%'....

It's also an ado issue, oledb provider dependent (jet4.0 vs odbc driver)
http://support.microsoft.com/default...NoWebContent=1
http://support.microsoft.com/default...NoWebContent=1