none
SQL query works in in SSMS but not in C# program RRS feed

  • Question

  • Hello,

    I have a stored SQL procedure that takes XML data as an input, along with an email address. So, it looks like this when I use it SSMS

    exec pr_my_stored_procedure_name '<?xml version="1.0" encoding="UTF-8"?>
    <SIGNATURE>
      <LIST>
        <name>....more XML data' , 'email@address.com '

    When I run it in SSMS, it completes fine.

    When I run it in my  c# code (where data is the content of an XML file saved from a StreamReader)...

    using (SqlConnection con = new SqlConnection(@"Data Source= SERVER\DBaseName;Integrated Security=False;User ID=sa;Password=MySmSSPWord;Connect Timeout=30;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False;Initial Catalog=DBaseName;")) { using (SqlCommand cmd = new SqlCommand("pr_my_stored_procedure_name @xml, @from", con)) {

    cmd.Parameters.AddWithValue("@xml", data);

    cmd.Parameters.AddWithValue("@from", "email@address.com");

    var returnParameter = cmd.Parameters.Add("@ReturnVal", SqlDbType.Int); // check that the write process has completed but getting the return value returnParameter.Direction = ParameterDirection.ReturnValue; con.Open(); cmd.ExecuteNonQuery(); var returnedresult = returnParameter.Value; int returnedresultint = Convert.ToInt16(returnParameter.Value); if (returnedresultint != 0)

    { MessageBox.Show(string.Format("Error writing data to server. Please review & fix before Clicking OK.\n\nNB: CALL DATA HAS NOT BEEN WRITTEN TO THE DATABASE."), "SQL Server Communication Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } else { MessageBox.Show("Done");

    //figure out how to display the (x rows effected) output i see in SSMS } } }

    I get the exception

    System.Data.SqlClient.SqlException: 'XML parsing: line 1, character 38, unable to switch the encoding'

    No, I understand that this might be down to 'encoding="UTF-8"', but, it works fine in SSMS, hence my confusion.

    How can I get this to work fine in my code with changing the encoding= value?

    If I change the Parameter line to read

     cmd.Parameters.AddWithValue("@xml","N"+ data);

    I get a different exception

    System.Data.SqlClient.SqlException: 'XML parsing: line 1, character 7, text/xmldecl not at the beginning of input'

    so, I am not sure I have done this correctly

    Can someone please show me how I can get my code to run the SQL query correct, and show the "(x rows affected" output I get from SSMS in a richtextbox2?

    Thank you

    Friday, April 13, 2018 11:15 AM

Answers

  • HI, 

    if I replace

    cmd.Parameters.AddWithValue("@xml",data);

    with

    cmd.Parameters.AddWithValue("@xml","N" + data);

    I get a new exception

    System.Data.SqlClient.SqlException
      HResult=0x80131904
      Message=XML parsing: line 1, character 7, text/xmldecl not at the beginning of input
      Source=.Net SqlClient Data Provider
      StackTrace:
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
       at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
       at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption)
       at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
       at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
       at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
       at upsell.Form1.button3_Click(Object sender, EventArgs e) in C:\Users\MyName\documents\visual studio 2017\Projects\proj1\proj1\Form1.cs:line 276
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ButtonBase.WndProc(Message& m)
       at System.Windows.Forms.Button.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.Run(Form mainForm)
       at upsell.Program.Main() in C:\Users\MyName\documents\visual studio 2017\Projects\proj1\proj1\Program.cs:line 19


    the line it fails on is " cmd.ExecuteNonQuery();"

    NOW, I have changed my code ...and.... it <seems> to be working, as it gets to the "done" message box, but, what I would like to do is have the "messages" that you get when you run the query in SSMS 

    (9 row(s) affected)
    
    (11 row(s) affected)
    
    etc...

    My Code NOW looks like

    filename = @"C:\Apps\MYData.xml";
    
    using (FileStream fs = File.OpenRead(filename))
    {
    	StreamReader reader = new StreamReader(filename);
    	long x = reader.BaseStream.Length;
    	long y = x - 1;
    	int z = unchecked((int)y);
    
    	string text = reader.ReadToEnd();
    	data = (text.Substring(0, z));
    	
    	richTextBox1.Text = (data);
    	reader.Dispose(); 										// dispose the StreamReader if it's not null
    
    }
    
    System.IO.File.WriteAllText(@"C:\Apps\MYData.xml", data);
    
    using (SqlConnection con = new SqlConnection(@"Data Source= SERVER\DBaseName;Integrated Security=False;User ID=sa;Password=MySmSSPWord;Connect Timeout=30;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False;Initial Catalog=DBaseName;"))
    {
    	using (SqlCommand cmd = new SqlCommand("pr_insert_upsell_email_data @xml, @from", con))
    
    	{
    		XmlDocument xmlToSave = new XmlDocument();
    		xmlToSave.Load(@"C:\Apps\MYData.xml");
    		cmd.Parameters.Add(
    			new SqlParameter("@xml", SqlDbType.Xml)
    			{
    				Value = new SqlXml(new XmlTextReader(xmlToSave.InnerXml, XmlNodeType.Document, null))
    			});
    		cmd.Parameters.AddWithValue("@from", "email@address.com");
    
    		var returnParameter = cmd.Parameters.Add("@ReturnVal", SqlDbType.Int);  // check that the write process has completed by getting the return value
    		returnParameter.Direction = ParameterDirection.ReturnValue;
    
    		con.Open();
    		cmd.ExecuteNonQuery();
    
    		var returnedresult = returnParameter.Value;
    		int returnedresultint = Convert.ToInt16(returnParameter.Value);
    
      if (returnedresultint != 0)         
      {
    	MessageBox.Show(string.Format("Error writing data to server. Please review & fix before Clicking OK.\n\nNB: CALL DATA HAS NOT BEEN WRITTEN TO THE DATABASE."), "SQL Server Communication Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
      }
    	else
      {
       MessageBox.Show("Done");
    
    	}
    
    }

    Thanks

    NB: the file is read, and then overwritten with the data string as the original file as an invalid char at the end of the file, and I need to remove it before I can pass it tot he SPROC


    • Edited by G-Oker Monday, April 16, 2018 4:30 PM
    • Marked as answer by G-Oker Wednesday, April 18, 2018 8:29 AM
    Monday, April 16, 2018 4:27 PM
  • Adding N + data won't do what you think it does when working with parameters. All it does is string concat N to whatever string value you have and pass that as the parameter. That in no way translate to the SQL side as N'<data>'. That's the whole point of parameters, to prevent people from doing what you're trying to do.

    "NOW, I have changed my code ...and.... it <seems> to be working, as it gets to the "done" message box, but, what I would like to do is have the "messages" that you get when you run the query in SSMS "

    Good to hear. ExecuteNonQuery returns back an integral value which generally indicates the # of impacted rows. Try using it. There is a way to get back the underlying messages from the provider but it is rarely done.


    Michael Taylor http://www.michaeltaylorp3.net

    • Marked as answer by G-Oker Wednesday, April 18, 2018 8:29 AM
    Monday, April 16, 2018 5:04 PM
    Moderator
  • "does this run the query, or just return the rows the query effects?"

    Yes it runs the query. But you're already running the query to do the work so that is expected. You don't want to call it twice though. Just capture the results when you run it the first time.


    Michael Taylor http://www.michaeltaylorp3.net

    • Marked as answer by G-Oker Wednesday, April 18, 2018 8:29 AM
    Tuesday, April 17, 2018 8:08 PM
    Moderator

All replies

  • Try this.

    using (SqlCommand cmd = new SqlCommand("pr_my_stored_procedure_name", con))
    {
       cmd.CommandType = CommandType.StoredProcedure;
    
       //You have to use the real name of your procedure parameters here, refer to the sproc for their name
       cmd.Parameters.AddWithValue("@xml", data);
       cmd.Parameters.AddWithValue("@from", "email@address.com");
    
       con.Open();
       var result = (int)cmd.ExecuteScalar();   
    }
    
       

    " and show the "(x rows affected" output I get from SSMS in a richtextbox2"

    This forum is for C# specific questions only. Please post questions related to a specific UI in the appropriate forums based upon the Sticky thread at the top of the forums (Windows Forms for winforms).


    Michael Taylor http://www.michaeltaylorp3.net

    Friday, April 13, 2018 2:27 PM
    Moderator
  • Hi CoolDadTx, This still gives me the unable to switch the encoding'   if I use UTF-8 . If I use UTF-16, I get System.NullReferenceException: 'Object reference not set to an instance of an object.' 
    Friday, April 13, 2018 2:57 PM
  • I've not used XML in Stored Procedures, so I don't know for sure and haven't tried this, but maybe actually specifying the SQL type instead of using AddWithValue might do the trick:

    cmd.Parameters.Add("@xml", SqlDbType.Xml).Value = data;


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Friday, April 13, 2018 3:16 PM
    Moderator
  • Please post your updated code. As Bonnie said specifying the exact type may help. ADO.NET treats XML differently than strings.

    Michael Taylor http://www.michaeltaylorp3.net

    Friday, April 13, 2018 3:39 PM
    Moderator
  • Hello,

    Have you tried using SqlXml

    https://stackoverflow.com/questions/1698153/calling-a-stored-procedure-with-xml-datatype


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, April 13, 2018 4:31 PM
    Moderator
  • Hi BonnieB,

    This gives me the same  "unable to switch the encoding" exception at cmd.ExecuteNonQuery();

    Friday, April 13, 2018 4:32 PM
  • Hi, the code is still "as is" in my OP. 
    Friday, April 13, 2018 4:33 PM
  • Then it is wrong. You weren't calling the sproc correctly in the original code nor were you passing the parameters properly. Please use the code I posted and let us see ur updates for the correct parameter names

    Michael Taylor http://www.michaeltaylorp3.net

    Friday, April 13, 2018 4:41 PM
    Moderator
  • Hi, if I right mouse click on the stored procedure, and execute it, the parameter names are:

    Parameter    Datatype  Output Parameter Pass null value   Value
    
    @xml           xml     
    @from          varchar(100)

    Friday, April 13, 2018 5:07 PM
  • In that case the code I posted is using the correct parameter names. Please use that code and then post the exact exception message you're getting along with the exception type and callstack.

    Is the column in the DB an XML column or a VARCHAR? You might potentially have a culture issue in your DB. You might need to loop in your DBA to ensure the DB can handle Unicode characters.


    Michael Taylor http://www.michaeltaylorp3.net

    Friday, April 13, 2018 6:07 PM
    Moderator
  • Hi.

    Exception message is 

    System.Data.SqlClient.SqlException
      HResult=0x80131904
      Message=XML parsing: line 1, character 38, unable to switch the encoding
      Source=.Net SqlClient Data Provider
      StackTrace:
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
       at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
       at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
       at System.Data.SqlClient.SqlDataReader.get_MetaData()
       at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption)
       at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
       at System.Data.SqlClient.SqlCommand.ExecuteScalar()
       at upsell.Form1.button3_Click(Object sender, EventArgs e) in C:\Users\myname\documents\visual studio 2017\Projects\proj1\proj1\Form1.cs:line 280
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ButtonBase.WndProc(Message& m)
       at System.Windows.Forms.Button.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.Run(Form mainForm)
       at upsell.Program.Main() in C:\Users\myname\documents\visual studio 2017\Projects\proj1\proj1\Program.cs:line 19
    

    line 280 is " var result = (int)cmd.ExecuteScalar();"

    This is line 1 of the XML file 

    <?xml version="1.0" encoding="UTF-8"?>
    Char 38 is the ">"

    If I go into SSMS and modify the stored procedure, I can see

    ALTER PROCEDURE [dbj].[pr_my_stored_procedure_name] @xml xml, @from varchar(100)

    There is then a DECLARE @proj1 TABLE, which then lists an array . IE:

    DECLARE @proj1 TABLE

    ( sold char(1) null,

    code char(100) null,

    Res_is char (50) null,

    Conf varchar(50) null,

    etc )

    Thanks

    Monday, April 16, 2018 7:46 AM
  • Hi Karen, If I am following the Stack Overflow code correctly (and I don't think I am), I get

    System.ArgumentException
      HResult=0x80070057
      Message=Illegal characters in path.
      Source=mscorlib
      StackTrace:
       at System.IO.Path.LegacyNormalizePath(String path, Boolean fullCheck, Int32 maxPathLength, Boolean expandShortPaths)
       at System.IO.Path.NormalizePath(String path, Boolean fullCheck, Int32 maxPathLength, Boolean expandShortPaths)
       at System.IO.Path.GetFullPathInternal(String path)
       at System.Xml.XmlResolver.ResolveUri(Uri baseUri, String relativeUri)
       at System.Xml.XmlUrlResolver.ResolveUri(Uri baseUri, String relativeUri)
       at System.Xml.XmlTextReaderImpl..ctor(String url, XmlNameTable nt)
       at System.Xml.XmlTextReader..ctor(String url)
       at upsell.Form1.button3_Click(Object sender, EventArgs e) in C:\Users\myname\documents\visual studio 2017\Projects\proj1\proj1\Form1.cs:line 273
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ButtonBase.WndProc(Message& m)
       at System.Windows.Forms.Button.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.Run(Form mainForm)
       at upsell.Program.Main() in C:\Users\myname\documents\visual studio 2017\Projects\proj1\proj1\Program.cs:line 19
    at the line 
    XmlTextReader xmlReader = new XmlTextReader(transactionXml); 

    I'm getting my "data" field from an XML file

    filename = @"C:\Apps\MYData.xml";
    
    using (FileStream fs = File.OpenRead(filename))
     {
      StreamReader reader = new StreamReader(filename);
      long x = reader.BaseStream.Length;
      long y = x - 1;
      int z = unchecked((int)y);
    
      string text = reader.ReadToEnd();
      data = (text.Substring(0, z));
    
      richTextBox1.Text = (data);
     }

    then my " using (SqlCommand cmd = "statement begins.

    My code looked like this

    using (SqlCommand cmd = new SqlCommand("pr_my_stored_procedure_name", con))
    {
    	StringWriter sw = new StringWriter(); 
    	XmlTextWriter xw = new XmlTextWriter(sw); 
    	doc.WriteTo(xw); 
    	StringReader transactionXml = new StringReader(sw.ToString()); 
    	XmlTextReader xmlReader = new XmlTextReader(data); 
    	SqlXml sqlXml = new SqlXml(xmlReader); 
    	
    	cmd.Parameters.AddWithValue("@xml", data);
        cmd.Parameters.AddWithValue("@from", "email@address.com");
    
    	var returnParameter = cmd.Parameters.Add("@ReturnVal", SqlDbType.Int);  // check that the write process has completed bu getting the return value
        returnParameter.Direction = ParameterDirection.ReturnValue;
    
        con.Open();
        cmd.ExecuteNonQuery();
    	
    	var returnedresult = returnParameter.Value;
        int returnedresultint = Convert.ToInt16(returnParameter.Value);
    	
    	if (returnedresultint != 0)                                             // if the return value is 1 or Null, then process failed
         {
          MessageBox.Show(string.Format("Error writing data to server. Please review & fix before Clicking OK.\n\nNB: DATA HAS NOT BEEN WRITTEN TO THE DATABASE."), "SQL Server Communication Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
         }
    	 else
    	 {
    	 MessageBox.Show("done");
    	 }
    }


    • Edited by G-Oker Monday, April 16, 2018 1:34 PM
    Monday, April 16, 2018 1:33 PM
  • You've got a couple of things going on here. Firstly you're passing the data as XML to the sproc but at some point you'd be converting it to a VARCHAR. Why not just pass the parameter as VARCHAR. Also note that 100 characters isn't very long for XML. You'll likely truncate data as well.

    Since you are using XML though the encoding becomes important. Here's the list of encoding supported by SQL but note that it depends on the version of SQL you're running. UTF-8 is not a supported encoding for SQL, or at least it doesn't work correctly. Everyone recommends the same thing. Either use XML directly (params, columns, etc) and use UTF-16 (which is what XML requires) or use VARCHAR for everything and just treat it as a string. Some folks seem to have success with UTF-8 when using NVARCHAR but that is because it will convert from UTF-8 to UTF-16. 


    Michael Taylor http://www.michaeltaylorp3.net

    Monday, April 16, 2018 1:46 PM
    Moderator
  • HI, 

    if I replace

    cmd.Parameters.AddWithValue("@xml",data);

    with

    cmd.Parameters.AddWithValue("@xml","N" + data);

    I get a new exception

    System.Data.SqlClient.SqlException
      HResult=0x80131904
      Message=XML parsing: line 1, character 7, text/xmldecl not at the beginning of input
      Source=.Net SqlClient Data Provider
      StackTrace:
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
       at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
       at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption)
       at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
       at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
       at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
       at upsell.Form1.button3_Click(Object sender, EventArgs e) in C:\Users\MyName\documents\visual studio 2017\Projects\proj1\proj1\Form1.cs:line 276
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ButtonBase.WndProc(Message& m)
       at System.Windows.Forms.Button.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.Run(Form mainForm)
       at upsell.Program.Main() in C:\Users\MyName\documents\visual studio 2017\Projects\proj1\proj1\Program.cs:line 19


    the line it fails on is " cmd.ExecuteNonQuery();"

    NOW, I have changed my code ...and.... it <seems> to be working, as it gets to the "done" message box, but, what I would like to do is have the "messages" that you get when you run the query in SSMS 

    (9 row(s) affected)
    
    (11 row(s) affected)
    
    etc...

    My Code NOW looks like

    filename = @"C:\Apps\MYData.xml";
    
    using (FileStream fs = File.OpenRead(filename))
    {
    	StreamReader reader = new StreamReader(filename);
    	long x = reader.BaseStream.Length;
    	long y = x - 1;
    	int z = unchecked((int)y);
    
    	string text = reader.ReadToEnd();
    	data = (text.Substring(0, z));
    	
    	richTextBox1.Text = (data);
    	reader.Dispose(); 										// dispose the StreamReader if it's not null
    
    }
    
    System.IO.File.WriteAllText(@"C:\Apps\MYData.xml", data);
    
    using (SqlConnection con = new SqlConnection(@"Data Source= SERVER\DBaseName;Integrated Security=False;User ID=sa;Password=MySmSSPWord;Connect Timeout=30;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False;Initial Catalog=DBaseName;"))
    {
    	using (SqlCommand cmd = new SqlCommand("pr_insert_upsell_email_data @xml, @from", con))
    
    	{
    		XmlDocument xmlToSave = new XmlDocument();
    		xmlToSave.Load(@"C:\Apps\MYData.xml");
    		cmd.Parameters.Add(
    			new SqlParameter("@xml", SqlDbType.Xml)
    			{
    				Value = new SqlXml(new XmlTextReader(xmlToSave.InnerXml, XmlNodeType.Document, null))
    			});
    		cmd.Parameters.AddWithValue("@from", "email@address.com");
    
    		var returnParameter = cmd.Parameters.Add("@ReturnVal", SqlDbType.Int);  // check that the write process has completed by getting the return value
    		returnParameter.Direction = ParameterDirection.ReturnValue;
    
    		con.Open();
    		cmd.ExecuteNonQuery();
    
    		var returnedresult = returnParameter.Value;
    		int returnedresultint = Convert.ToInt16(returnParameter.Value);
    
      if (returnedresultint != 0)         
      {
    	MessageBox.Show(string.Format("Error writing data to server. Please review & fix before Clicking OK.\n\nNB: CALL DATA HAS NOT BEEN WRITTEN TO THE DATABASE."), "SQL Server Communication Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
      }
    	else
      {
       MessageBox.Show("Done");
    
    	}
    
    }

    Thanks

    NB: the file is read, and then overwritten with the data string as the original file as an invalid char at the end of the file, and I need to remove it before I can pass it tot he SPROC


    • Edited by G-Oker Monday, April 16, 2018 4:30 PM
    • Marked as answer by G-Oker Wednesday, April 18, 2018 8:29 AM
    Monday, April 16, 2018 4:27 PM
  • Adding N + data won't do what you think it does when working with parameters. All it does is string concat N to whatever string value you have and pass that as the parameter. That in no way translate to the SQL side as N'<data>'. That's the whole point of parameters, to prevent people from doing what you're trying to do.

    "NOW, I have changed my code ...and.... it <seems> to be working, as it gets to the "done" message box, but, what I would like to do is have the "messages" that you get when you run the query in SSMS "

    Good to hear. ExecuteNonQuery returns back an integral value which generally indicates the # of impacted rows. Try using it. There is a way to get back the underlying messages from the provider but it is rarely done.


    Michael Taylor http://www.michaeltaylorp3.net

    • Marked as answer by G-Oker Wednesday, April 18, 2018 8:29 AM
    Monday, April 16, 2018 5:04 PM
    Moderator
  • Hi,

    If I use 

    int affectedRows = cmd.ExecuteNonQuery();


    Then I get the number of affected rows in total back. cool, but, (silly question time) does this run the query, or just return the rows the query effects?

    Tuesday, April 17, 2018 7:21 PM
  • Hi,

    If I use 

    int affectedRows = cmd.ExecuteNonQuery();


    Then I get the number of affected rows in total back. cool, but, (silly question time) does this run the query, or just return the rows the query effects?

    Please read the documentation.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, April 17, 2018 7:57 PM
    Moderator
  • "does this run the query, or just return the rows the query effects?"

    Yes it runs the query. But you're already running the query to do the work so that is expected. You don't want to call it twice though. Just capture the results when you run it the first time.


    Michael Taylor http://www.michaeltaylorp3.net

    • Marked as answer by G-Oker Wednesday, April 18, 2018 8:29 AM
    Tuesday, April 17, 2018 8:08 PM
    Moderator
  • Hi Karen, thanks for this. I did read the documentation, but I was unsure what that line meant (hence my question).

    It might just be the way I am reading it, but it reads like it just returns the number of rows the command would effect, not the actual command.

    I think Michael has cleared this up for me. 

    thank you both

    Wednesday, April 18, 2018 8:29 AM