SQL SELECT in VB6 DataEnvironment?

[KarloZ]

i'm using the dataenvironment in vb6 to connect to the Access database. i have no problems but creating a query using the SELECT Statement to display records in the datagrid. i want to pass multiple parameters in that query.
for example getting all records from rsClients (ID,Name,Status,City)table in the dataenvironment DB1 with these parameters STATUS is "NEW" and CITY is "TORONTO"

Any suggestions is warmly appreciated. Thnak you..

 

[johnwm]

In your Dataenvironment properties window select SQL staement and SQL builder
Select you fields and criteria, using '= ?' or 'LIKE ?' or whatever
When you go to parameters tag, set a parameter name for each parameter.
Back in VB, when you type in your

Dataenvironment1.Command1(

Intellisense will now show you the parameter variables to insert as normal arguments
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[KarloZ]

hi johnwm, thank you for your reply. i have used this method already before, but i find it difficult with my query. because i have three fields to be included in the query and sometimes only ONE parameter of the three may have a value and the rest are blank. in this case it gives me no record because the SQL SELECT includes in the condition, in the WHERE clause, the other two parameters as blank entries.

for example if i want just to display the clients records only with "NEW" STATUS and the rest of the parameter as blank, it gives me no records in the datagrid coz SQL EVALUTES it as
"WHERE STATUS = "NEW" AND ID = " " AND CITY = " "

i want my sql to give me records of all "NEW" clients regardless of the ID and CITY.

your suggestion is much apprecialed, thanks in advance.

 

[johnwm]

Why not set up 2 or 3 commands with the appropriate criteria and select programmatically, based on length of the text strings for instance.

Pseudocode:
If len(txtCity) = 0 then
Dataenvironment1.Command1( "NEW&quot
endif
If len(ID) = 0 then
Dataenvironment1.Command2( txtCity, "NEW&quot
endif

etc.
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[KarloZ]

hi johnwm, it really makes sense. i was thinking before that all parameters are required. now, i'm gonna try it. thank you. but one more thing, i'm gonna create another program, i am thinking of using the adodb connection through vbcodes to connect to my database rather than using the dataenvironment. would you recommend that? in that way i can have options changing the path of the database if there's a need and less limitations?

your a great help. thank you.

 

[johnwm]

Using ADO will give you much better control over Connections, Queries and Recordsets.
There have been loads of good threads on this site regarding ADO. Another starting point is:

http://msdn.microsoft.com/library/default.asp?URL=/library/psdk/dasdk/ados4piv.htm

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[KarloZ]

hi johnwm, i had a chance to try your suggestion but it didn't work. i tried to enter only one parameter from the "two variable sql" that i have. it's giving errors. do u have other suggestions in mind? thnx

 

[johnwm]

Sorry I didn't make myself clear
The pseudocode I gave was intended to give the idea, not the full logic.
In the example above Dataenvironment1.Command1 would expect only the Status parameter, Command2 would expect City and Status, but not ID etc etc
Depending on your requirements you could have any number of commands in the same dataenvironment, and you will select amongst them according to user input
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[KarloZ]

ok thanks, i had that in mind and tried it, which has still some weird problems not displaying the records but i know its gonna work. but i find it ridiculous having all these commands just because i have these multiple parameters. is there any other way of doing this. thank you for your reply.

 

[johnwm]

You could generate the SQL on the fly. Along the lines of:

(Pseudocode only!)
strSQL = "Select * FROM tblData WHERE "
If len(txtID.text) > 0 then
strSQL = strSQL & "ID = " & cint(txtID.text) & " AND "
end if
If len(txtCity.text) > 0 then
strSQL = strSQL & "City = '" & txtCity.txt & " AND "
end if

etc etc for all interested fields, then:
strSQL = left(strSQL, len(strSQL) - 4)

then use strSQL for your command.

There is still some error checking required of course!
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'