Prev: c# and "Select ID,Data FROM [Data$]"
Next: Connections and ##temp tables
From: scott on 9 May 2010 15:09
I've never had to deal with multiple items in a listbox control. Would it
best to store the comma-seperated id integers within 1 varchar field? The
other thought was possibly build a junction table and have multiple rows
for each comma-seperated integer.

What's the best way to store multiple items collected from a listbox? In my
example, it would like a product id belonging to multiple category id's.

Any guidance?


From: Erland Sommarskog on 9 May 2010 15:56
scott (sbailey(a)mileslumber.com) writes:
> I've never had to deal with multiple items in a listbox control. Would it
> best to store the comma-seperated id integers within 1 varchar field? The
> other thought was possibly build a junction table and have multiple rows
> for each comma-seperated integer.
>
> What's the best way to store multiple items collected from a listbox? In
> my example, it would like a product id belonging to multiple category
> id's.

I can't speak for the realm of list boxes, but storing comma-separated
lists in a table column is very rarely a good idea. They can be very
painful to work with

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Tony Rogerson on 9 May 2010 16:31
Pass the CSV via a varchar() parameter or preferably a Table Valued
Parameter if you have 2008 to a stored procedure.

If CSV then split the CSV into rows and store those.

Relationally (theory) speaking values need to be Atomic - CSV in your case
isn't; also, SQL only likes scalar data types - a limitation of the language
unfortunately.

If you get stuck get back and I'll pass you some VB.NET example code I use.

Tony.

"scott" <sbailey(a)mileslumber.com> wrote in message
news:uKLEws67KHA.4508(a)TK2MSFTNGP06.phx.gbl...
> I've never had to deal with multiple items in a listbox control. Would it
> best to store the comma-seperated id integers within 1 varchar field? The
> other thought was possibly build a junction table and have multiple rows
> for each comma-seperated integer.
>
> What's the best way to store multiple items collected from a listbox? In
> my example, it would like a product id belonging to multiple category
> id's.
>
> Any guidance?
>
From: Uri Dimant on 10 May 2010 01:58
Hi Tony
This question we often ask people on the interview. Now they can read the
answer here :-)



"Tony Rogerson" <tonyrogerson(a)torver.net> wrote in message
news:eyz1Oa77KHA.3964(a)TK2MSFTNGP05.phx.gbl...
> Pass the CSV via a varchar() parameter or preferably a Table Valued
> Parameter if you have 2008 to a stored procedure.
>
> If CSV then split the CSV into rows and store those.
>
> Relationally (theory) speaking values need to be Atomic - CSV in your case
> isn't; also, SQL only likes scalar data types - a limitation of the
> language unfortunately.
>
> If you get stuck get back and I'll pass you some VB.NET example code I
> use.
>
> Tony.
>
> "scott" <sbailey(a)mileslumber.com> wrote in message
> news:uKLEws67KHA.4508(a)TK2MSFTNGP06.phx.gbl...
>> I've never had to deal with multiple items in a listbox control. Would it
>> best to store the comma-seperated id integers within 1 varchar field? The
>> other thought was possibly build a junction table and have multiple rows
>> for each comma-seperated integer.
>>
>> What's the best way to store multiple items collected from a listbox? In
>> my example, it would like a product id belonging to multiple category
>> id's.
>>
>> Any guidance?
>>


 | 
Pages: 1
Prev: c# and "Select ID,Data FROM [Data$]"
Next: Connections and ##temp tables