Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1039
  • Last Modified:

Howto use multiple QueryStringParameters in a SelectCommand for a MySQL SqlDataSource

I am trying to use multiple QueryStringParameters in a SelectCommand for a MySQL SqlDataSource. I can use 1 just fine like this:

[CODE]
<asp:SqlDataSource ID="db_faa" runat="server"
                ConnectionString="Dsn=my_faa;uid=root;pwd=root"
                ProviderName="System.Data.Odbc"
                SelectCommand="SELECT faa_part, faa_part_desc, faa_status FROM tbl_faa_tracking WHERE faa_pl_desc = ?">
                <SelectParameters>
                       <asp:QueryStringParameter Name="value" QueryStringField="value" />
                </SelectParameters>
            </asp:SqlDataSource>
[/CODE]

and a query string like this:  ?value=TE456

But I actually need to pass in the field name as well like this:  ?field=faa_pl_desc&value=TE456
so I tried changing the code to this:

[CODE]
<asp:SqlDataSource ID="db_faa" runat="server"
                ConnectionString="Dsn=my_faa;uid=root;pwd=root"
                ProviderName="System.Data.Odbc"
                SelectCommand="SELECT faa_part, faa_part_desc, faa_status FROM tbl_faa_tracking WHERE ? = ?">
                <SelectParameters>
                    <asp:QueryStringParameter Name="field" QueryStringField="field" />
                    <asp:QueryStringParameter Name="value" QueryStringField="value" />
                </SelectParameters>
            </asp:SqlDataSource>
[/CODE]

I get no error, I just get a blank data set. It is as if the Select is returning no data even tho it has the same field name and search value in both instances. I have tired reversing the order of the 2 QueryStringParameters just in case the parameters are in a stack instead of a FIFO. No joy...

I think I just must be passing the 2 Query strings with some syntax missing?

Dave
0
davecove
Asked:
davecove
2 Solutions
 
Daniel WilsonCommented:
You can't use a parameter value as a field name.  What you have is translating as:

SELECT faa_part, faa_part_desc, faa_status FROM tbl_faa_tracking WHERE 'faa_pl' = 'TE465'

And that is going to return no records.

The number of valid fields is going to be finite, so coding for each branch is reasonable:

SELECT faa_part, faa_part_desc, faa_status FROM tbl_faa_tracking
WHERE
 (@Field = 'faa_pl' and faa_pl = @Value)
OR   (@Field = 'Field2' and Field2 = @Value)
OR   (@Field = 'Field3' and Field3 = @Value)
0
 
davecoveAuthor Commented:
Or something like that...
When you use MySQL you don't get to use things like @Field and @Value... you can only use a ? and then put the QueryStringParameters in the order you want to use them in the Select.
So your suggestion turns into:
SELECT faa_part, faa_part_desc, faa_status FROM tbl_faa_tracking
WHERE
(? = 'faa_pl' and faa_pl = ?)
OR   (? = 'Field2' and Field2 = ?)
OR   (? = 'Field3' and Field3 = ?)
followed by 6 QueryStringParameters to take care of the 6 question marks. Like so:
<asp:QueryStringParameter Name="field" QueryStringField="field" />
<asp:QueryStringParameter Name="value" QueryStringField="value" />
<asp:QueryStringParameter Name="field" QueryStringField="field" />
<asp:QueryStringParameter Name="value" QueryStringField="value" />
<asp:QueryStringParameter Name="field" QueryStringField="field" />
<asp:QueryStringParameter Name="value" QueryStringField="value" />  
Which is just fine with me... because it works!
Dave
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now