Solved

Howto use multiple QueryStringParameters in a SelectCommand for a MySQL SqlDataSource

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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL GROUP BY 6 76
SQL Query Syntax Assistance 2 34
SQL Query incorrect syntax near the keyword ON any help would be appreciated 18 30
compare date to getdate() 8 16
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

749 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