quote:

---

Originally posted by bluestar

I have a store procedure to insert multiple data from the datagrid which has 2 columns.


ALTER PROCEDURE [dbo].[sp_InsertCounterStyle]
(
@TOCHSCounterStyleID int,
@xmlList XML
)


AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Begin tran

Insert into TOCHSCounterStyle(TOCHSCounterStyleID,TOCHSCounterNumber,TOCHSCounterLabelText)

Select
@TOCHSCounterStyleID,
path.xl.value('@TOCHSCounterNumber','int') as TOCHSCounterNumber,
path.xl.value('@TOCHSCounterLabelText','varchar(20)') as TOCHSCounterLabelText

from @xmlList.nodes('/xmlList') as path(xl)

--commit tran

END

when I am executing this ...
I am entering data in 2 places

@TOCHSCounterStyleID int :::: 11
@xmlList ::::<xmlList><TOCHSCounterNumber=1></TOCHSCounterNumber><TOCHSCounterLabelText=''i''></TOCHSCounterLabelText></xmlList>


then its giving me error
Msg 9455, Level 16, State 1, Procedure sp_InsertCounterStyle, Line 0
XML parsing: line 1, character 29, illegal qualified name character


please help


Thank You

---

quote:

---

Originally posted by bluestar

is my store procedure wrong or something else ...

please help

---

quote:

---

Originally posted by bluestar

okay 1st of all thanks for reply.
I am getting an error when I am executing the SP
Msg 8152, Level 16, State 14, Procedure sp_InsertCounterStyle, Line 20
String or binary data would be truncated.
The statement has been terminated.

(1 row(s) affected)


Please do reply

Thnak You

---

quote:

---

Originally posted by bluestar

hello visakh16

please help me in this,
your stored procedure is working fine,but I want the input xml string in this form
<xmlList>
<xl>1</xl>
<xl>i</xl>
<xl>2</xl>
<xl>ii</xl>
<xl>3</xl>
<xl>iii</xl>
</xmlList>

this is because in my C# code this xml string is generated,but my SP is excepting this string


<xmlList><TOCHSCounterNumber value="1"></TOCHSCounterNumber><TOCHSCounterLabelText value="i"></TOCHSCounterLabelText></xmlList><xmlList><TOCHSCounterNumber value="12123"></TOCHSCounterNumber><TOCHSCounterLabelText value="ifqewferf"></TOCHSCounterLabelText></xmlList><xmlList><TOCHSCounterNumber value="9986"></TOCHSCounterNumber><TOCHSCounterLabelText value="wqrwertetggrti"></TOCHSCounterLabelText></xmlList>

please help me

this is my C# code

protected void btnsubmit_Click(object sender, EventArgs e) {

int schemaid;

schemaid = tocheaderstyle.InsertTOCNumberingSchema(txtname.Text);

String xmlList = GetList();

tocheaderstyle.InsertTOCCounterStyle(schemaid,xmlList);



}
private String GetList() {
XmlDocument doc = new XmlDocument();
XmlElement elem = doc.CreateElement("xmlList");
doc.AppendChild(elem);

XmlElement root = doc.DocumentElement;
for (int i = 0; i < dgItem.Rows.Count; i++)
{
XmlElement infoEle = doc.CreateElement("xl");
XmlElement infoEle1 = doc.CreateElement("xl");

DataRowView drv = (DataRowView)dgItem.Rows[i].DataItem;
TextBox tb = (TextBox)dgItem.Rows[i].FindControl("txtdrgno");
TextBox tb1 = (TextBox)dgItem.Rows[i].FindControl("txtdrgno1");
if((tb.Text != "") && (tb1.Text != ""))
{
//infoEle.SetAttribute("TOCHSCounterNumber", TOCHSCounterNumber);
infoEle.InnerText = tb.Text;
root.AppendChild(infoEle);
//infoEle.SetAttribute("TOCHSCounterLabelText", TOCHSCounterLabelText);
infoEle1.InnerText = tb1.Text;
root.AppendChild(infoEle1);
//infoEle.InnerText = tb.Text;
//infoEle1.InnerText = tb1.Text;
}
}
string s = doc.InnerXml;
return s;
}

please do reply

thank you

---