how to save listbox multiple select values

Hi,

I have an ASP.NET form that stores it's data in MSDE but I just added a multi-select ListBox to the form and I'm having a hard time coming up with a way of writing that data to the database. Should I write the values into a column on the same table where I store the rest of the data from the form (values separated by a comma) or shouild I create another table (one to many) and store the data there. I like the second option, but I'm not sure how to loop through each value and write it to the database table.

I grab the values for the selection as follow:

foreach (ListItem lstItem in lbAttendees.Items)
{
if (lstItem.Selected == true)
{
grpList.Add(lstItem.Value.ToString());
}
}

but I'm not sure on what to do next and could use some help.

Thanks
Germanoshouldn't lbAttendees have .SelectedItems?

Next, you should be passing the values to the database through sql or other dataaccess means.|||>>Should I write the values into a column on the same table where I store the rest of the data from the form (values separated by a comma) or shouild I create another table (one to many) and store the data there.

I would strongly suggest option 2 (Normalize).

>>I'm not sure how to loop through each value and write it to the database table.

Well you have some options.

i) Loop through your items on the client and perform inserts (one row at a time).

ii) Package up the values as an xml chunk and use openxml to insert (set based method, less chatty)

ii) Package up the values as a delimited string and perform parsing and insert at the server.

And there are others (using OO: collections, persistance frameworks mechanisms, etc..)sql

how to return event description from stored proceedure?

I am using C# to insert the form details and passing event id (numeric) to the same stored procedure in my eror handler and need to retrieve the description from event_db to display in MessageBox..

can the stored proceedure send the text?

Could you describe this abit in detail and send your DDL over ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

How to retrieve results in predefined chunks

Hello,
Let's say I am planning retrieve thousands of records and load them in my
datagrid in a windows form. This is very slow and I want to retrieve the
result in small chunks. For example I want to retrieve first 100 rows and
load it and then when user scrolls down on the grid I retrieve the next 100
rows and so on.
Can anyone give me some hints? Can this be done by writing a SQL statement
or there are other things to take into consideration?
Thanks for your help.http://www.aspfaq.com/show.asp?id=2120
David Portas
SQL Server MVP
--|||Great article thank you very much!
"David Portas" wrote:

> http://www.aspfaq.com/show.asp?id=2120
> --
> David Portas
> SQL Server MVP
> --
>
>

How to retrieve primary key columns from db?

Hello all. I need to extract the column names that form the primary key group on a table in SQL Server. I have a table called Account and it contains ten columns. The primary key consists of two columns - MasterAccountID, AccountID. This primary key is a unique constraint and is clustered (it acts as an index as well as a primary key group). I have tried the following to no avail:

exec sp_pkeys Account -> returns no rows

exec sp_helpindex Account -> throws an error stating that the object 'Account' does not exist

If I run the following SQL statement, I can see all of the PK_* constraints in the database, so I know they are there:

select * from information_schema.table_constraints
where constraInt_type IN ('PRIMARY KEY','FOREIGN KEY')

Again, I need to be able to specify a table name and have it return the columns (don't care if it returns extra fields) that make up the primary key fields for that table. Thanks!

Perhaps something like this:

select

*fromsyscolumnswhere id=object_id('yourTable')and colidin(select colidfromsysindexkeyswhere id=object_id('yourTable'))

|||

declare

@.TableNamevarchar(255)

set

@.TableName='tblPersons'

SELECT

CCU.*

FROM

INFORMATION_SCHEMA.Table_Constraints TCJOININFORMATION_SCHEMA.Constraint_Column_Usage CCUON CCU.CONSTRAINT_NAME= TC.CONSTRAINT_NAME

WHERE

CONSTRAINT_TYPE

='PRIMARY KEY'AND TC.TABLE_NAME= @.TableName

but this also works: look at the quotes ''

execsp_pkeys'tblPersons'

How to Re-Structure Form After adding an Identity

I'd created a dataform from a table stored in a SQL database, after that I changed the primary key field to be an identity, refreshed the dataset but when I execute the program, it raises an exception when I try to save the record after adding a new one asking me for the value of this field. What can I do to be sure it will be created automatically?

Move the thread here in order to get more helpful answers.

|||

Hi Juan,

I'm not sure why this got moved here, it seems like a problem with the DataSet definition...

It would help to have the actual error you're getting, but my guess is that the field you added didn't acutally get the Identity proprty set correctly, so it is not generating new values automatically. When you try to add a new record, there is no value specified for your PK so you get the error. Verify that the field works correctly outside of your DataSet, if it doesn't, then you have a problem with the table, if it does, then there is something wrong with your DataSet.

Mike

|||Ok. I've got the answer. After change the datatype in the table as IDENTITY, and refresh the dataset, the identity atribute in the dataset field does not change automatically. Thanks Mike