Using In Line SQL in VB

[Robeen]

I usually work with PowerBuilder.
I am used to using in line SQL like the following to get values from my Databases & process them:

Select SomeFieldValue
Into :SomeVariable
From SomeTable
Where Some[Other?]FieldValue = Some[Other?]Variable;

Then I can go back to PowerBuilder Code and process depending on the contents of 'SomeVariable'.

Or I can do a Do While (True) . . . Loop
and keep looping through all the returned records, processing each record as I go till there are no more records that match the criteria.

What is the best way to do this in VB - Version 6.0 with an MS Access Database?

Thanks,

Robeen

 

[DonQuichote]

Use MS-Access' query designer to build the SQL. Then copy it and paste it as a string into VB.

Note that you can use parameterqueries in Access. Using a parameterquery instead of rebuilding (and therefore re-interpreting) SQL can gain you a huge lot of performance.

Best regards

 

[Robeen]

Thanks DonQuichote,
What I need, specifically, however, is to be able to retrieve a particular value from the Database into a VB Variable.

For instance, I might want to:
Select EmployeeAge From EmployeeTable
Into MyVariable
Where EmployeeID = SomeEmployeeID

I'm comfortable with writing SQL - it's the quickest way of getting database values into VB variables that I'm looking for code for.

Thanks!

Robin

 

[bmoyer23]

Search for help on created Recordsets...

 

[chiph]

Robeen -

There is no inline SQL in VB, as the VB "compiler" doesn't know how to parse it. Back in the old days, I used to use inline SQL with Watcom SQLAnywhere on OS/2.. ;-) It had a precompiler you had to run before running your (now modified) source through the C/C++ compiler & linker. Nothing like that in VB.

In VB you write your SQL and store it in a string variable. You then pass that to the ADO library functions which execute it for you. You probably know this as being Dynamic SQL.

There are a couple of examples here - use the Keyword Seach facility, and look for "ADO Parameter" or "ADO Command".

Chip H.