ADO Data Control

kf4rrm · March 18th, 2005, 12:26 PM

Greetings!

The problem I am having seems like it should be a simple task. However, I am not making much progress with it at all. I am using an ADO
Data control on my form to connect to an AS/400. When I set its Record source equal to a SQL statement it works fine if it is a non-text column I am querying against. For example, if I state:

Adodc1.RecordSource = "select * from chgtracker where chgdate = " & prmTest
Where prmTest is a string containing 20050114

Then all works well.

However, if the table column I am searching against contains text/string data I get an error. For example:

Adodc1.RecordSource="select * from chgtracker where approveby = " & prmTest2

Where prmTest2 is a string containing the literal âTESTERâ (including quotes as seen in debug) and Approveby is a field containing string data, I get an error stating that âThe column TESTER does not exist in the specified tablesâ.
I am not sure why this is taking place or why the quotes have been stripped off but the need to get past this is high. Thanks for your help!!!

Chris

dhay1999 · April 5th, 2005, 08:24 AM

I don't know why the first one is working, but each should be contained in SINGLE quotes IE '20050114' or 'tester'

Double quotes are a VB string thing

In debug your source string should look like

select * from chgtracker where approveby = 'tester'
or
select * from chgtracker where approveby = '20050114'

BrianWren · April 5th, 2005, 11:58 AM

It is helpfull to think about what your statements will evaluate to.
If prmTest = 45 and prmTest2 = "Dilly", then your first statement evaluates to

Code:

    select * from chgtracker where chgdate = 45

Your second statement evaluates to

Code:

    select * from chgtracker where approveby = Dilly

In that case, Dilly would be initially thought to be a field/column, and if that could not be resolved it would be evaluated as a variable, since Dilly is not really a value.

dhay1999 is mistaken regarding the first statement. A string of digits is regarded as a literal value. Enclosing that in quotes would result in failure to fly.

But he is right regarding the second statement. When the value on one side of an equality operator is a literal string, it needs to have a single quote on each side of the characters. Your statement should be

Code:

    Adodc1.RecordSource="select * from chgtracker where approveby = '" & prmTest2 & "'"

or the contents of prmTest2 should be like 'Dilly'