Click here to Skip to main content
15,891,951 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi sir ,
I am uday satardekar,

I am inserting 12000 rows using xml
and i am binding data using xml;
i am binging using StringBuilder

but when i am passing it to storded procedure it throws exception
Failed to convert parameter value from string builder to string

C#
StringBuilder XML_TRANSACTIONDETAILS = new StringBuilder();

                    long maxid = getMaxId();
                    
                    int status;

                    //XML_TRANSACTIONDETAILS = "<master>";
                    XML_TRANSACTIONDETAILS.Append("<master>");

                    for (int i = 0; i < excelRows; i++)
                    {
                             maxid = maxid + 1;

                           // XML_TRANSACTIONDETAILS = XML_TRANSACTIONDETAILS + "<TRANSACTION>";
                            //XML_TRANSACTIONDETAILS = XML_TRANSACTIONDETAILS + "<maxid>" + maxid + "</maxid>";
                             XML_TRANSACTIONDETAILS.Append("<TRANSACTION>");
                             XML_TRANSACTIONDETAILS.Append("<maxid>" + maxid + "</maxid>");

                            for (int j = 0; j < ExcelColumnCount; j++)
                            {
                                string xmlTag = cleanedEmailTbl.Columns[j].ToString();

                               
                                XML_TRANSACTIONDETAILS.Append("<" + xmlTag + ">" + cleanedEmailTbl.Rows[i][j].ToString() + "</" + xmlTag + ">");
                           }
                           
                            XML_TRANSACTIONDETAILS.Append("</TRANSACTION>");
                    }
                    
                    XML_TRANSACTIONDETAILS.Append("</master>");

                    SqlParameter prm = new SqlParameter("@XML_TRANSACTIONDETAILS", SqlDbType.NText);
                    SqlParameter prm1 = new SqlParameter("@status", cmbAllStatus.SelectedValue);
                    SqlParameter prm2 = new SqlParameter("@username", currentLoggedUserId);
                    SqlParameter prm3 = new SqlParameter("@urlorcatalog", tboxUrlCat.Text);
                    SqlParameter prm4 = new SqlParameter("@dataentrytype", cmbDataEntryType.Text);
                 

                 
                    prm.Value = XML_TRANSACTIONDETAILS;

                    try
                    {
                        command.Connection = conn;
                        command.CommandText = "InsertExcelUsingXml";
                        command.CommandType = CommandType.StoredProcedure;

                        command.Parameters.Add(prm);
                        command.Parameters.Add(prm1);
                        command.Parameters.Add(prm2);
                        command.Parameters.Add(prm3);
                        command.Parameters.Add(prm4);

                        adapter = new SqlDataAdapter(command);
                        adapter.Fill(ds1, "rowsinserted");
                       
                        //after this lines exception comes
                       

                        int rows1 = command.ExecuteNonQuery();
                        transaction.Commit();
Posted
Updated 18-Aug-11 0:02am
v2

1 solution

you need to convert it to a string first
C#
prm.Value = XML_TRANSACTIONDETAILS.ToString();
 
Share this answer
 
v2
Comments
udusat13 18-Aug-11 6:26am    
Thanks its really working

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900