asked on

unable to insert multiple listbox items in to database

insertcommand.parameters.add("@packagemaster",sqldbtype.varchar,500).value=selectedlist.items;

here insertcommand is stored procedure,selected list is list box,packagemaster is column name

when i try to insert multiple list items in to pakagemaster column using items property it was not working.....so suggest me to handle this situation

ASKER CERTIFIED SOLUTION

Binuth

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Binuth

pass as xml parameter to Sp and use sp_xml_preparedocument sp to insert xml values to Tabel
(see sample code)

C# code building Xml parameter
---------------
 
System.Xml.XmlDocument objXMLDocument = null;
System.Xml.XmlElement objXMLElement = null;
System.Xml.XmlElement objXMLChildElement = null;
 
//Bulding XML Document for Statistical Ranges
objXMLDocument = new System.Xml.XmlDocument();
objXMLDocument.LoadXml("<root/>");
 
objXMLElement = objXMLDocument.DocumentElement;
 
foreach (string sItem in listBox1.SelectedItems)
{
	objXMLChildElement = objXMLDocument.CreateElement("packagemaster");
	objXMLChildElement.SetAttribute("Pkey" , sItem);
	objXMLElement.AppendChild(objXMLChildElement);
}
 
// pass objXMLDocument.OuterXml to Sp parameter
insertcommand.parameters.add("@vchXMLParms" , sqldbtype.varchar , 8000).value = objXMLDocument.OuterXml;	 
 
 
sample Sp
---------
 
 
ALTER proc InsertSp(@vchXMLParms VARCHAR(MAX))
AS
 
DECLARE @xmldocHandle AS INT
EXEC sp_xml_preparedocument @xmldocHandle OUTPUT, @vchXMLParms
 
 insert into YourTableName(packagemaster)
 SELECT Pkey FROM OPENXML(@xmldocHandle, '/root/packagemaster', 1)WITH(Pkey VARCHAR(MAX))
 
EXEC sp_xml_removedocument @xmldocHandle

Open in new window

life_j

ASKER

im using using sql server 2005 as DB...could u tell me d solution in c# coding