Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Howto use multiple QueryStringParameters in a SelectCommand for a MySQL SqlDataSource

Posted on 2008-10-29
2
Medium Priority
?
1,029 Views
Last Modified: 2012-05-05
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
Comment
Question by:davecove
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 32

Assisted Solution

by:Daniel Wilson
Daniel Wilson earned 400 total points
ID: 22831528
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
 

Accepted Solution

by:
davecove earned 0 total points
ID: 22831809
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

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question