473,326 Members | 2,099 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

sqldatasource using WHERE IN problem

12
Using Studio 2010 C# - with sqldatasource querying DB2 UDB database.

I have a gridview sqldatasource that uses a WHERE condition as follows:
Select values WHERE groupname IN ('groupa','groupb',etc.)

Works fine hardcoded in the sqldatasource but I want to create the group list in the code-behind and plug it into the SelectParameters.

So far - I have changed my sqldatasource select statement to: Select values WHERE groupname IN ?

Next, I am building a string in the code behind as follows:
string gList = "('groupa', 'groupb')";
This is a variable list of groups from another data source.

Next, I moved the string to a Session string so that it's available throughout my session as follows:
Session["aGrps"] = gList;

Then I use the session string in my selectparameters of the sqldatasource as follows:
<SelectParameters> <asp:SessionParameter Name="aGrps" Type="String" <SessionField="aGrps" /> </SelectParameters>

The sql statment does not work.
I get data if gList is only one group with no quote or paren characters as follows:
string gList = "groupa";

But when I try to add more groups in the list with or without any special characters like quotes, parens, or commas - I get no data. Not sure that's causing the problem - but then again...?

Is there a problem with using WHERE IN ? Or am I going about this all wrong? Any assistance in building this WHERE claue would be appreciated.

Thanks
Mar 12 '13 #1

✓ answered by dev99

Solution found elsewhere. thanks everyone

6 4306
r035198x
13,262 8TB
Determine how many parameters you are going to add, say N, before hand and add N amount of ? to the query parameters programatically. Then add the values and you should be good.
Mar 12 '13 #2
dev99
12
Thanks for the reply. I was not quite able to get your suggestion to work. I was however trying to set the Select paramters in the code behind as follows and got it partially to work:

I added onSelecting="Grid01_Selecting" to the SqlDataSource and my WHERE clause is WHERE GROUP_NAM = @Grps

and my select parms are as follows:
<SelectParameters><asp:Parameter Name="Grps" Type="String" /></SelectParameters>

In the code-behind I have:
Expand|Select|Wrap|Line Numbers
  1. protected void Grid01_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
  2. {
  3.  e.Command.Parameters[0].Value = "groupA";
  4. }
This works and the sql will return data for groupA. But I can't figure out how to have 1-n groups in the Command.Parameter list. Is there a way to do this?
Mar 13 '13 #3
dev99
12
Sorry for not using the code tags. I will do better! Does anyone know how I can pass a string of parms from my code behind to the WHERE IN clause of my sqldatasource Select statement? thanks
Mar 14 '13 #4
dev99
12
Solution found elsewhere. thanks everyone
Mar 20 '13 #5
Rabbit
12,516 Expert Mod 8TB
Can you post the solution you found for the benefit of the next person that reads this thread?
Mar 20 '13 #6
dev99
12
A solution I found for this issue is overriding the SqlDataSource SELECT statement so that the WHERE IN clause works.

In my case, I used a String.Format to build a Select statement with a formatted string of group names in my gridview_DataBound() as follows:

SqlDataSource1.SelectCommand = String.Format("SELECT mycolumns FROM mytables WHERE GROUP_NAME IN ({0}),Get_Groups(logged_in_user));

Get_Groups receives the logged in user id and returns a formatted string of groups. In my case I retrieved group names from Active Directory and the returned string looked like this: "'groupa','groupb','groupc'".

As the gridview bind takes place - the new Select statement is executed.
Mar 20 '13 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: Steven Blair | last post by:
I am looking for some advice on the following problem: I am using a GridView and a SQLDataSource. The problem is based around a Room Booking app I am currently writing. When the user makes a...
2
by: Steven Blair | last post by:
When I hit a button inside my FormView which has Insert as its Command Name, my Insert statement is executed with the correct parameters (the events Inserting & Inserted are fired). But I need...
0
by: Giovanni | last post by:
I was wondering if someone would be able to help me with the following: Simply put, I have built an ASP.NET 2.0 WebUserControl (UC1). UC1 contains: A GridView which is bound to an SQLDataSource...
2
by: Ned Balzer | last post by:
I'm trying to create a formview bound to a sqldatasource, and use a stored procedure to insert data from the formview into several tables. I have some simplified code below, the real code is much...
0
by: Sean | last post by:
Using SqlDataSource controller with sprocs and its been working fairly well. I changed a table by removing a foreign key, removing 2 columns and adding 3. I rewrote the update sproc and works fine...
2
by: Brad Isaacs | last post by:
Good evening friends, I have added a Drop Down List box control to my web form. I am using the web.config connection string to access my SQL Server 2000 db. Inside that db I have a table named...
0
by: Lenny | last post by:
Hi. I've a problem with an update from the sqldatasource the table cannot be modified because it is from a third party sw table fields are: codice, fcli_inizio, fcli_fine. keys are codice and...
2
by: gwtech | last post by:
Sending with: http://www.catahouligans.com/Message%20Board/MBUpload.aspx?MessageID=78&Subject=hhh&TopicID=1 SQLdatasource <asp:QueryStringParameter DefaultValue="0" Name="MessageID"...
1
by: David C | last post by:
I have aspx web page with a GridView that displays data from a SqlDataSource view that combines 2 tables to show information. It has an Edit feature and the SqlDataSource has an UpdateCommand text...
1
by: renatois | last post by:
DeleteCommand in SqlDataSource using DetailsView Hi guys, Figure that: - I have two related tables by IDName: 1 - table Name (fields: IDName, Name) 2 - table Products (fields: IDProduct,...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.