FoxPro Help and Support - Building SQL statements in runtime based on data in controls

Author

Building SQL statements in runtime based on data in controls

zlatan

2005-10-27, 8:34 am

Hi,

I am building a form where I would like to search in my table based on
various criterias. For example

I have 2 listboxes, the first one is filled with static values that are
selectable. From them I move the ones that I want to the other
listbox=B4, like this --> distrikt: 145, 147 and 148 displayed as
following:
145
147
148
in the listbox.

So my wish is that when I press the search button a SQL string should
perform the appropiate question to my table based on the values in my
listbox, I do not want to write 100 cases to cover all different
values.

Hope I am clear.

My SQL string looks like following:

SELECT Testar.*;
FROM ;
testar ;
WHERE distrikt =3D "145";
ORDER BY regnr INTO CURSOR muppen

Carsten Bonde

2005-10-27, 8:34 am

zlatan,

instead of putting them into another listbox, put them into a cursor.

Now your select would look like this:

SELECT Testar.*;
FROM ;
testar ;
WHERE distrikt IN (SELECT * FROM curDistrikt) ;
ORDER BY regnr INTO CURSOR muppen

--
Cheers
Carsten
____________________
___________

"zlatan" <michael.holm@bilia.se> schrieb im Newsbeitrag
news:1129283788.819878.317700@o13g2000cwo.googlegroups.com...
Hi,

I am building a form where I would like to search in my table based on
various criterias. For example

I have 2 listboxes, the first one is filled with static values that are
selectable. From them I move the ones that I want to the other
listbox´, like this --> distrikt: 145, 147 and 148 displayed as
following:
145
147
148
in the listbox.

So my wish is that when I press the search button a SQL string should
perform the appropiate question to my table based on the values in my
listbox, I do not want to write 100 cases to cover all different
values.

Hope I am clear.

My SQL string looks like following:

SELECT Testar.*;
FROM ;
testar ;
WHERE distrikt = "145";
ORDER BY regnr INTO CURSOR muppen

zlatan

2005-10-27, 8:34 am

Thanks Carsten, I will try this. Looks like it will cover my needs.

Is there a limit on how many Where ***** IN(SELECT ....) commands I can
have?

Later on I am going to add dates and other variables..

Paul

2005-10-27, 8:34 am

Carsten Bonde wrote:
> zlatan,
>
> instead of putting them into another listbox, put them into a cursor.
>
> Now your select would look like this:
>
> SELECT Testar.*;
> FROM ;
> testar ;
> WHERE distrikt IN (SELECT * FROM curDistrikt) ;
> ORDER BY regnr INTO CURSOR muppen

You could use a join for this:

SELECT Testar.*;
FROM ;
testar ;
INNER JOIN curDistrikt ON testar.curDistrikt == curDistrikt.distrikt ;
ORDER BY regnr INTO CURSOR muppen

Would this be faster?

Cindy Winegarden

2005-10-27, 8:34 am

Hi Zlatan,

I'm partial to the Inner Join method Paul suggested, although you'll have to
check that the other variables are given since an inner join on nothing
yields nothing.

Another way is to build your SQL dynamically and run it using macro
expansion.

cSQL = "Select * From MyTable " + ;
"Where Last Name = '" + ;
ThisForm.txtLastName.Value + '" + .....

&cSQL

If you've got a list in an array or table use code like the following:
cInList = ""
Select MyTable
Scan
cInlist = cInlist + ", " + Transform(CustomerID
)
EndScan
cInlist = Substring(cInlist, 2) && Strip the extra comma
cSQL = "Select * From MyTable " + ;
"Where CustomerID In (" + ;
cInlist + ")"
&cSQL

--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@msn
.com www.cindywinegarden.com
Blog: http://spaces.msn.com/members/cindywinegarden

"zlatan" <michael.holm@bilia.se> wrote in message
news:1129291860.170056.282720@g14g2000cwa.googlegroups.com...
> Thanks Carsten, I will try this. Looks like it will cover my needs.
>
> Is there a limit on how many Where ***** IN(SELECT ....) commands I can
> have?
>
> Later on I am going to add dates and other variables..
>

Paul

2005-10-27, 8:34 am

Cindy Winegarden wrote:
> Hi Zlatan,
>
> I'm partial to the Inner Join method Paul suggested, although you'll have to
> check that the other variables are given since an inner join on nothing
> yields nothing.
>
> Another way is to build your SQL dynamically and run it using macro
> expansion.
>
> cSQL = "Select * From MyTable " + ;
> "Where Last Name = '" + ;
> ThisForm.txtLastName.Value + '" + .....
>
> &cSQL

Use with caution - this kind of dynamic SQL allows hackers to inject
malicious code. e.g.

ThisForm.txtLastName.Value = "' DROP TABLE MyTable -- "

There's an excellent web resource on Dynamic SQL (and other things) here:
http://www.sommarskog.se/

--
Paul

Sponsored Links