Parameters in SQL-statement

[tsunami100]

Hi,

Does anybody know how i can use a fieldnaam in an sql-statement as a parameter.

Ex. select Field1 ,Field2 From Database
where param1 = param2

The user can choose Field1 or Field2 as param1
Is this possible ?
The statement above doens't work but gives you an idea what i mean.

Thanks !!

 

[chrissie1]

I think what you are looking for is dynamic sql. if you have sql-server look it up in BOL or ask in this forum forum183


Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting

 

[tsunami100]

Thanks chrissie.
I'm retrieving the data from an access-db.
So do you think it will be be a major problem.
I'll take a look at the forum.

Lode
Limburg-Belgie


I choose my username Tsunami so that everytime somebody see this, he or she will remember this disaster where i lost a good friend.

 

[compdrmt]

this is reasonably simple to do. Do check out the SQL forum because excepting minor implementation specific tweaks, SQL is SQL whether against SQL server or Access. This is why you should use it, so that if you change you app to a different database it should work without problems.


As to your specific issue

SQL queries are merely srtrings, So you simply glue a string together with the desired terms and variables forming a correct query and send it off to the interpreter. For example

dim ssql as string
ssql = "Select field1,field2 from table_name where " param1 & "=" & param2

then send ssql off to be interpreted by the SQL engine. Keep in mind that if the data mentioned in param one is a string then you must add single quotes to the mix.

For example if you wanted the query to be

Select field1, field2 from some_table where custname = 'smith'

This should work

dim param1 as string
dim param2 as string
dim ssql as string

param1 = "custname" (could be based on a choice made by user)
param2 = textbox1.text
ssql = "select field1,field2 from some_table where " & param1 & "='" & param2 & "'"

I would reccomend highly a good SQL book if you plan on using sqL commmands much. Mine has payed or itself many times over. SQL is a very powerful command language.

Debugging is the process of removing bugs. Programming is the process of putting them in.

 

[tsunami100]

Hi compdrmt,

Thank you so much for your reply.
I understand what you said here but there is another problem.
I tried to construct these sql-statements in the querybuilder and i think it's a little bit different than to do it just in code.
Do you have any idea ?
Thanks again, i appreciate it very much.

Lode.


I choose my username Tsunami so that everytime somebody see this, he or she will remember this disaster where i lost a good friend.