Solved

Howto use multiple QueryStringParameters in a SelectCommand for a MySQL SqlDataSource

Posted on 2008-10-29
2
1,012 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 100 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

752 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