how to use 2 parameters in data environment?

[cherrydee]

I have the public category as strings which is determined by the dropdown value and I also have txtchoice.text as search field.
Now i made a data environment -> data report. I created command1 and used the sql

select * from table where variable1 = variable2

In my frmsearch, I have the following code for 'print search result':

Dim x As String
x = category
If (DataEnvironment1.rsCommand2.State = 1) Then
        DataEnvironment1.rsCommand2.Close
    Else
        DataEnvironment1.Command2 x, txtchoice.text
        Load DataReport2
        DataReport2.Show
End If

this gives me empty output.
===
I've tried to change the command string to

select * from table where name = variable1

and in my print button:

If (DataEnvironment1.rsCommand2.State = 1) Then
        DataEnvironment1.rsCommand2.Close
    Else
        DataEnvironment1.Command2 (txtchoice.text)
        Load DataReport2
        DataReport2.Show
End If

This gives me the correct output but I need the "where" parameter to be dynamic

Maybe I have wrong format for 2 parameters in the DataEnvironment1.Command2?

[dilettante]

Parameters passed to queries are simple values, you can't pass field or table names as parameters.

To do that you'd have to rewrite (replace) the Command.CommandText with dynamic SQL. If your field name is taken from user input you are back to a SQL Injection risk again. And at that point you may as well not bother using a parameter for the value to test for either.

[cherrydee]

I've tried passing a new sql string to the command.commantext of data environment. But as you've said, this will give me the risk of sql injection. So i guess printing output based on dynamic WHERE will be out of the picture for me. thanks mate!

[dilettante]

I see two possible workarounds:

If you just have a few different fields that it makes sense to use in your criteria expression you could make a separate Command for each and select among them using Select Case or something. There is very little overhead in defining several similar Commands in the DataEnvironment.

If that's too clunky, there are still only so many fields in a query that make sense within a criteria expression. That list of fields also shouldn't change during the life of the application very much. So instead of letting users type in whatever they want you could have them pick from a ListBox or something containing a fixed set of values. Then you have control over what you use to construct your query dynamically, and the risk of SQL Injection disappears. You can still use a parameter for any values used in your expression, since those are more likely to be too open-ended for a ListBox and you'd want to accept typed-in user input.