trying to input xml data type as string, get error "Incorrect syntax near '<'."

[abehm]

Hey everyone,
I'm trying to input a string as an xml data type into sql server, but i keep getting this error. Am I doing this incorrectly, or do i need to format the string?

XmlDocument mydoc = new XmlDocument();
SqlCommand sqlCommand;
 
mydoc.Load(xmlDir + "temp.xml");
 
string tempXML = mydoc.OuterXml;
 
string sqlcom = "INSERT INTO Config.TestBooth  (TestBoothID,TestBoothName,Config) "
+ "VALUES (" + testBoothID + ", " + testBoothName + ", " + tempXML + ")";
sqlCommand = new SqlCommand(sqlcom);
sqlCommand.Connection = sql;
sql.Open();
sqlCommand.ExecuteNonQuery();
sqlCommand.Connection.Close();
 

[jhardman]

the only problem is that there must be some special characters (for sql) in your string. a quote mark or parenthese or some such. print out your sqlcon to make sure. You may need to run some kind of replace function on the string before you pass it.

Let me know if this helps.

Jared

[abehm]

So I added a 'Response.Write(sqlcom);' line, and it took me to a new page that says "The XML page cannot be displayed "
"Invalid at the top level of the document. Error processing resource 'http://localhost:1322/WebSite3/Default.aspx'. Line 1,...

INSERT INTO Config.TestBooth (TestBoothID,TestBoothName,Config) VALUES (1, .326, <?xml version="1.0" encoding="utf-8"?>..."

[abehm]

if i type sqlcom in the Immediate Window it reads:

""INSERT INTO Config.TestBooth (TestBoothID,TestBoothName,Config) VALUES (1, .326, <?xml version=\"1.0\" encoding=\"utf-8\"?><Config><Testbooth> <TestBoothID>1</TestBoothID><Vertical>0.340999990701675</Vertical> <Horizontal>5777</Horizontal><Diagonal>193</Diagonal> <AreaIN>0.32600000500679</AreaIN><AreaFT>0.340999990701675</AreaFT> <ScreenGain>0</ScreenGain><MeasurementType>2</MeasurementType> </Testbooth></Config>)"
 

[jhardman]

OK, I often suggest that coders switch from "Insert" to "addnew" and I think that may help in this case, it looks like the quote marks in the xml string are messing up your insert statement. syntax for addnew is:

query="SELECT * FROM config.testBooth"
objRS.open query, objConn, adOpenDynamic, adLockOptimistic
objRS.addNew
objRS("TestBoothID") = 1
objRS("TestBoothName") = 0.326
objRS("config") = xmlConfigString
objRS.update

I know it looks longer, but it executes at the same speed. Let me know if this helps.

Jared

[abehm]

OK, I often suggest that coders switch from "Insert" to "addnew" and I think that may help in this case, it looks like the quote marks in the xml string are messing up your insert statement. syntax for addnew is:

query="SELECT * FROM config.testBooth"
objRS.open query, objConn, adOpenDynamic, adLockOptimistic
objRS.addNew
objRS("TestBoothID") = 1
objRS("TestBoothName") = 0.326
objRS("config") = xmlConfigString
objRS.update

I know it looks longer, but it executes at the same speed. Let me know if this helps.

Jared

Thanks for your help Jared, but I ended up solving the issue when I changed the sql statement to this (with the apostrophes):

 string sqlcom = "INSERT INTO Config.TestBooth (TestBoothID,TestBoothName,Config) " + "VALUES (" + testBoothID.ToString() + ", '" + testBoothName.ToString() + "', '" + tempXML + "')";
 

[syed rizwan iqbal shah]

try that :)

xml String
 
 
 
using System;
using System.Collections;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Data.ProviderBase;
using System.Web.Mail;
using System.Xml;
using System.Xml.Serialization;
using System.Xml.Schema;
using System.Text;
using System.Text.RegularExpressions;
using System.IO;
 
 
 
namespace WebApplication3
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
 
        }
 
 
        protected void Button1_Click1(object sender, EventArgs e)
        {
 
 
 
            //for List 1
            for (int i = 0; i < ListSecA.Items.Count; i++)
            {
                if (ListSecA.Items[i].Selected)
                {
                    ListSecAValues.Text += ListSecA.Items[i].Value.ToString() + "\n";
                }
            }
            // For List 2
            for (int x = 0; x < ListSecB.Items.Count; x++)
            {
                if (ListSecB.Items[x].Selected)
                {
                    ListSecBValues.Text += ListSecB.Items[x].Value.ToString() + "\n";
                }
            }
            // For list 3
            for (int z = 0; z < ListBoxHearAbout.Items.Count; z++)
            {
                if (ListBoxHearAbout.Items[z].Selected)
                {
                    ListHearValues.Text += ListBoxHearAbout.Items[z].Value.ToString() + "\n";
                }
            }
 
 
 
 
            string DetailData = "";
            DetailData += TextBoxDetail.Text + TextBoxAddress.Text + TextBoxAddress2.Text + TextBoxCity.Text + TextBoxProvince.Text + TextBoxPostalCode.Text + TextBoxFax.Text + TextBoxWebsite.Text + TextBoxAttendee.Text + ListSecAValues.Text + ListSecBValues.Text + TextBoxSecADate.Text + TextBoxSecBDate.Text + TextBoxNumberAtt.Text + TextBoxSubTotal.Text + TextBoxGst.Text + TextBoxTotalDue.Text + DropdownPayment.SelectedItem.Text + TextBoxCreditCardMonth.Text + TextBoxCreditCardYear.Text + TextBoxCreditCardName.Text + ListHearValues.Text + TextBoxComments.Text;
            System.Xml.Serialization.XmlSerializer serializ = new System.Xml.Serialization.XmlSerializer(DetailData.GetType());
            StringBuilder sb = new StringBuilder();
            TextWriter writer = new StringWriter(sb);
            serializ.Serialize(writer, DetailData);
 
 
                string str = "";
                SqlConnection conn = new SqlConnection(
                    "Data source=SYED-PC;Initial Catalog=baass;Integrated Security=True");
 
                conn.Open();
                str = "insert into Registrations (EventCode,EventDate,EventTime,EventLocation,Name,Company,Phone,Email,Details) values ('Event of Toronto','03/15/2008','09:30:00 AM','Toronto','" + TextBoxName.Text.ToString() + "','" + TextBoxCompany.Text.ToString() + "','" + TextBoxPhone.Text + "','" + TextBoxEmail.Text.ToString() + "','" + writer.ToString()+ "')";
 
 
                //sr = "insert into Registrations (EventCode,EventDate,EventTime,EventLocation,Name,Company,Phone,Email,Details) values ('Event of Toronto','03/15/2008','09:30:00 AM','Toronto','" + Request.Form.Get("TextBoxName.text").ToString() + "','" + Request.Form.Get("TextBoxCompany.text").ToString() + "','" + Request.Form.Get("TextBoxPhone.text") + "','" + Request.Form.Get("TextBoxEmail.text").ToString() + "','" + Request.Form.Get("TextBoxDetail.text").ToString() + "')";
 
 
 
                SqlCommand md = new SqlCommand(str, conn);
 
                //int y = md.ExecuteNonQuery();
                int g = md.ExecuteNonQuery();
 
 
                conn.Close();
                MailMessage mails = new MailMessage();
 
                mails.To = TextBoxEmail.Text;
                //mails.Bcc = "[e-mail]";
                mails.From = "[e-mail]";
                mails.Subject = "Welcome: " + TextBoxName;
 
                mails.Body = "Hello " + TextBoxName.Text +
                    "\n \n \n Thank you for your registration request. You have successfully registered \n for the following BAASS Training Course(s). \n \n Series A Course(s):"
                    + ListSecAValues.Text + "\n \n Date(s) for Series A Course(s): " + TextBoxSecADate.Text + " \n Time(s) for Series A Course(s): \n \n Series B Course(s):" 
                    + ListSecBValues.Text + " \n \n Date(s) for Series B Course(s):" + TextBoxSecBDate.Text + "\n Time(s) for Series B Course(s): \n \n \n Registrant Name(s):" 
                    + TextBoxName.Text + " \n \n \n Company Name:" + TextBoxCompany.Text + " \n Payment Method:" + DropdownPayment.SelectedItem.Text + " \n Total Amount:" 
                    + TextBoxTotalDue.Text + " \n Location: BAASS Training Centre,  1200 Centre Street, Thornhill, ON L4J 3M9 \n \n \n Please visit www.baass.com/baass01.nsf/pages/Contact.html#map "+
                    "for a map to the location. \n \n \n Please do not hesitate to contact us if you require further information. \n \n \n Thank you, \n \n \n Susie Pedota \n Consultant Office Manager "+
                    "\n BAASS Business Solutions Inc.\n Tel: [phone] Ext. 237 \n Toronto Tel: [phone] \n Fax: [phone] \n [e-mail] \n www.baass.com \n Bringing Technology to Business";
 
                SmtpMail.SmtpServer = "localhost";
                SmtpMail.Send(mails);
 
                MailMessage myself = new MailMessage();
                myself.To = "[e-mail]";
                myself.From = "[e-mail]";
                myself.Subject = "BAASS Thornhill Online Course Registration Confirmation";
                myself.Body = "Hello " + TextBoxName.Text +
                    "\n \n \n Thank you for your registration request. You have successfully" +
                    "registered \n for the following BAASS Training Course(s). \n \n Series A Course(s):" + ListSecAValues.Text +
                    "\n \n Date(s) for Series A Course(s): " + TextBoxSecADate.Text + 
                    " \n Time(s) for Series A Course(s): \n \n Series B Course(s):"
                    + ListSecBValues.Text + " \n \n Date(s) for Series B Course(s):" + TextBoxSecBDate.Text +
                    "\n Time(s) for Series B Course(s): \n \n \n Registrant Name(s):"
                    + TextBoxName.Text + " \n \n \n Company Name:" + TextBoxCompany.Text +
                    " \n Payment Method:" + DropdownPayment.SelectedItem.Text + " \n Total Amount:" + TextBoxTotalDue.Text +
                    " \n Location: BAASS Training Centre,  1200 Centre Street, Thornhill, ON L4J 3M9 \n \n \n Please visit "+
                    "www.baass.com/baass01.nsf/pages/Contact.html#map for a map to the location. \n \n \n Please do not hesitate"+
                    " to contact us if you require further information. \n \n \n Thank you, \n \n \n Susie Pedota \n Consultant Office Manager "+
                    "\n BAASS Business Solutions Inc.\n Tel: [phone] Ext. 237 \n Toronto Tel: [phone] \n Fax: [phone] \n [e-mail] "+
                    "\n www.baass.com \n Bringing Technology to Business \n \n \n \n ----------------------------------------------------------------------------------- "+
                    "\n The following message was sent as a Online Course Registration request. \n Clicking on reply or reply with history will allow you to respond to this \n request. \n \n Registrant Name(s):" 
                    + TextBoxName.Text + "\n \n \n Company Name: " + TextBoxCompany.Text + " \n Address 1: " + TextBoxAddress.Text + "Address 2: " + TextBoxAddress2.Text + " \n City: " + TextBoxCity.Text + " \n Province: " 
                    + TextBoxProvince.Text + " \n Postal Code: " + TextBoxPostalCode.Text +
                    " \n Phone Number: " + TextBoxPhone.Text + " \n Fax Number: " + TextBoxFax.Text + " \n Person's Email: " + TextBoxEmail.Text + " \n Website: " + TextBoxWebsite.Text +
                    " \n Number of Attendees: " + TextBoxNumberAtt.Text + " \n \n Series A Course(s):"
                    + ListSecAValues.Text + "\n \n Date(s) for Series A Course(s): " + TextBoxSecADate.Text + " \n \n Series B Course(s):" + ListSecBValues.Text + " \n \n Date(s) for Series B Course(s):" + TextBoxSecBDate.Text + " \n Subtotal Amount: " 
                    + TextBoxSubTotal.Text + " \n GST Amount: " + TextBoxGst.Text + " \n Total Due: " + TextBoxTotalDue.Text + " \n Payment Method: " + DropdownPayment.SelectedItem.Text + " \n Credit Card Number: " + TextBoxCreditCard.Text + " \n Expiry Date: MONTH: "
                    + TextBoxCreditCardMonth.Text + " Year: " + TextBoxCreditCardYear.Text + " \n Card Holder Name: " + TextBoxCreditCardName.Text + " \n How did you hear about this course: " + ListHearValues.Text + " \n Message: " + TextBoxComments.Text +
                    " \n -----------------------------------------------------------------------------------\n";
 
 
                SmtpMail.SmtpServer = "Exchange";
 
                SmtpMail.Send(myself);
                Response.Redirect("Thanks.aspx");