|
Subject:
|
Binding Gridview with variable number of records
|
|
Posted By:
|
KamalRaturi
|
Post Date:
|
8/24/2006 4:08:21 PM
|
hi ,
I have a variable string e.g. ("25,3,45,32") which is stored in a field of Table.The String is of variable length, it could be "1,4,22" or "456,54,32,21,56,89,57" in datbase(SQL 2005)
The problem is that I have to access the records from anathor table where the comma separated values of string is a field (i.e ID)
Table1 Table2 Result(I Want)
------------ ------------ ----------------------
id Name strID Name
------------ ------------ -----------------------
1 a 2,3,11 b,c,f
2 b 2 b
3 c 1,3,6,9,11,31 a,c,d,e,f,g
6 d 1,3,32 ---and so on---
9 e 11
11 f 6,9
31 g 1
32 h 31,32
I want a single Function or Procedure that will access the values strID from begining to end of Table2 and join the records in Table1
with the IDs that are in the String.When all Records will be accessed by all rows of Table2 from Table1, then the Resultent Data has to be bind with DataBound Control(Gridview)
if you know How to do this ,please tell me how .
-----Kamal
Thanks.
|
|
Reply By:
|
thenoseknows
|
Reply Date:
|
8/24/2006 6:48:40 PM
|
The problem is you have data stored like that. What you should do is normalize the data.
You said those are ID's to the primary key to another table. So what you have then is either a one-to-many or a many-to-many relationship.
You need to normalize this data and then you won't even have the problem because you will pull the correct number of rows that you can bind to a gridview in the first place. I know how to do it but describing it here in words is very difficult.
If you can not, or won't normalize the data, then you can use the field of comma separated FK ID's in an SQL Query using the "IN" clause. Do something like this:
SELECT CommaSeparatedField
FROM TableWithCommaField
WHERE [whatever constraints you want here]
Then take that comma separated field you got from the above query and do:
SELECT *
FROM OtherTable
WHERE OtherTableId IN ( [field of comma separated values here] )
You know, and of course implement that however, with SQL statements you build as strings or with stored procedures or whatever you want.
Hope that helps.
Neil
Neil Timmerman
Programmer
Veris Consulting
|
|
Reply By:
|
KamalRaturi
|
Reply Date:
|
8/26/2006 2:05:29 PM
|
hi thenOSEKnows,
as u told me , i used Normallization for the solution ,
and simply it works.
Thanks friend!
|
|
