Using a control as a parameter in two places in an SQL data source

Question

4.00/5 (1 vote)

See more:

I have an SQL data source on my aspx page that connects to the database and brings back data correctly. The issue I'm having is I'm trying to use a control on the aspx page to filter the data but I need to use the control in an SQL "OR" statement. The data is only picking up the first where condition of the "OR" statement. I also tried to union the Select statements and got the same results.

I've looked online and haven't found anything that pertains to my situation. Does anyone know if this can be done? Does anyone know how to accomplish this task or know where to find the answer?

I've included a sample of the connection string:

XML

<asp:SqlDataSource ID="sdsFiles" runat="server"
        ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
        ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>"
        SelectCommand="SELECT List.* FROM List WHERE (Name = ?) OR (Name LIKE '%Admin%') AND (Path LIKE '%' + ? + '\%')
                        ORDER BY Skipped"
        <SelectParameters>
            <asp:ControlParameter ControlID="lblUName" Name="?" PropertyName="Text" />
        </SelectParameters>

I also tried:

XML

<asp:SqlDataSource ID="sdsFiles" runat="server"
        ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
        ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>"
        SelectCommand="SELECT List.* FROM List WHERE (Name = ?)
        Union
         SELECT List.* FROM List WHERE (Name LIKE '%Admin%') AND (Path LIKE '%' + ? + '\%')
                        ORDER BY Skipped"
        <SelectParameters>
            <asp:ControlParameter ControlID="lblUName" Name="?" PropertyName="Text" />
        </SelectParameters>

Both give me the same results in that they only bring back the results for the first where condition.

Thanks,

Carolyn

Posted 12-Apr-11 7:20am

CARisk3

Updated 12-Apr-11 7:23am

v3

Add a Solution

Comments

web works 12-Apr-11 13:28pm

Are you sure your query returns perfect result of your expectation. Please try to execute your query in SQL server and see it gives you the correct result. See if this helps you. Let me know.

CARisk3 12-Apr-11 13:31pm

If I run the query with each individual where condition I get the results I expect. When I try to return it as one result with an "OR" or "Union" then I don't get what I expect. I even run the sql statement with the "OR" statement to figure out how many records it should be returning and it ran fine.

Carolyn

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

web works · Answer 1 · 2011-04-12T07:48:00

Solution 1

Try

SQL

SelectCommand="SELECT List.* FROM List WHERE (Name = ? OR Name LIKE '%Admin%') AND (Path LIKE '%' + ? + '\%')
 ORDER BY Skipped"

Posted 12-Apr-11 7:48am

web works

Comments

CARisk3 12-Apr-11 13:52pm

That doesn't work. And the "Or" really needs to be grouped as I originally had it as I'm looking for the name only or I'm looking for where the name is like admin and the path as the name in it.

Thanks,
Carolyn

web works 12-Apr-11 14:09pm

try this
"SELECT List.* FROM List WHERE (Name = ?) OR (Name LIKE '%Admin%') AND (Path LIKE '%" + ? + "\%') ORDER BY Skipped";

Wendelius · Answer 2 · 2011-04-12T07:55:00

Solution 2

It depends on what you need, if you need:
- name like admin or name is the text in the control and if any of those is true, and the path is given then:

SQL

SelectCommand="SELECT List.* FROM List WHERE ((Name = ?) OR (Name LIKE '%Admin%')) AND (Path LIKE '%' + ? + '\%')
                        ORDER BY Skipped"

- name like admin and path is the text in control then return results or if those don't match then if user is the text from control then:

SQL

SelectCommand="SELECT List.* FROM List WHERE (Name = ?) OR ((Name LIKE '%Admin%') AND (Path LIKE '%' + ? + '\%'))
                        ORDER BY Skipped"

Posted 12-Apr-11 7:55am

Wendelius

Comments

CARisk3 12-Apr-11 13:59pm

I need to know if the Name field contains the same data as the control, if not I then need to check if the name field is like Admin and if the path field contains the same data as the control.

Neither of the above solutions work within the aspx page. It's only evaluating the first where statement.

Wendelius 12-Apr-11 14:01pm

Ok, sorry, I misunderstood you. Have you tried adding the parameter twice?
<SelectParameters>
<asp:ControlParameter ControlID="lblUName" Name="?" PropertyName="Text" />
<asp:ControlParameter ControlID="lblUName" Name="?" PropertyName="Text" />
</SelectParameters>

CARisk3 12-Apr-11 14:03pm

If I do that it says I can only have one parameter with the name.

Carolyn

Wendelius 12-Apr-11 14:06pm

If you try replacing the question mark with parameter names. Something like:
...((Name = @Name1) OR (Name LIKE '%Admin%')) AND (Path LIKE '%' + @Name2 + '\%')

and

<SelectParameters>
<asp:ControlParameter ControlID="lblUName" Name="Name1" PropertyName="Text" />
<asp:ControlParameter ControlID="lblUName" Name="Name2" PropertyName="Text" />
</SelectParameters>

CARisk3 12-Apr-11 14:10pm

No, it's still only evaluating the first where condition.

Wendelius 12-Apr-11 14:18pm

And you're sure that the last portion returns rows. If you try using Management Studio and run a query for for example user 'BS_Admin15', you would have a sql:
SELECT List.* FROM List WHERE (Name LIKE '%Admin%') AND (Path LIKE '%BS_Admin15\%')
ORDER BY Skipped
And that returns a row?

CARisk3 12-Apr-11 14:21pm

Yes, it returns 2 rows. And the first portion returns 66 rows. When my data is returned to the grid there are only 66 rows and the 2 rows that should be returned for the second where condition are not listed. I know what path should be showing up for those two records and they are not in the grid.

Wendelius 12-Apr-11 14:24pm

And the 2 rows are not any of those rows in the set of 66? What I mean is that UNION operator includes a DISTINCT operation so duplicates will be removed.

CARisk3 12-Apr-11 14:26pm

No they are not because the name for those two records is admin and not the data in the control. They are two different records from the original 66 returned which is why I'm trying to accomplish this because I need to give the user the completed list of 68 not just the original 66.

Wendelius 12-Apr-11 14:44pm

Just can't see the problem in the query and especially if you have correct results in management studio.

One problem source could be if the database is case sensitive so upper and lower case would make a difference, but I take it you would have noticed this when you ran the query in management studio.

The only thing that comes in mind is that you debug the code and see what goes to SQL Server and how many rows are returned. Just in case that you don't have any kind of filtering mechanism in the application.

CARisk3 12-Apr-11 15:33pm

I figured this out. The data getting saved to the control has the domain name in front of the name. I need to strip this and then it works.

Thanks so much for you help though.

Carolyn

Wendelius 12-Apr-11 15:37pm

No problem, glad it worked out :)

CARisk3 12-Apr-11 15:42pm

Well that still didn't work. I tested it in the server explorer and it worked but when I run it on the aspx page it still only evaluates the first where clause. Really getting frustrated with this. But still thanks for your help.

Wendelius 12-Apr-11 15:46pm

One tool you could also use is Sql Profiler. Just to see the the statement is really going correctly to the server. Also one test could be that you use only the last portion of the select:

SelectCommand="SELECT List.* FROM List WHERE (Name LIKE '%Admin%') AND (Path LIKE '%' + ? + '\%') ORDER BY Skipped"

Do you get the two rows mentioned earlier (weren't they the problem)?

CARisk3 12-Apr-11 16:03pm

Well this time I did get it fixed and I feel really stupid. I was working with the wrong connection string. That's why it wasn't showing the values. The first one I came up with works but I do need two parameters. I forgot I ended up creating a view because I needed to filter the records based on a value in another table's field.

The final connection string looks like the following:

<asp:SqlDataSource ID="sdsUnresolvedList" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>"

SelectCommand="SELECT vw_UnresovedList.* FROM vw_UnresolvedList WHERE (Name = 'ads\' + ?) OR (Name LIKE '%BUILTIN\%') AND (Path LIKE '%' + ? + '\%') ORDER BY Skipped">
<SelectParameters>
<asp:ControlParameter ControlID="lblUserName" Name="?" PropertyName="Text" />
<asp:ControlParameter ControlID="lblUserName" Name="?" PropertyName="Text" />
</SelectParameters>

Yeah :). And Thanks for all you suggestions.

Carolyn

Wendelius 12-Apr-11 16:28pm

No problem, as said glad you got it resolved. One note though. If possible you could use named parameters. I'd think that would make the code clearer and changing the order of parameters wouldn't affect the statement anymore (since now you have to have the parameters in same exact order as the question marks)

CARisk3 12-Apr-11 16:33pm

I usually do but because this one is using the same control field I'm not worried about it. And the system automatically turned them into questions marks. It seems like when I try to change them it ends up with an error. So it's working for now and I'm leaving it alone.

Carolyn