xml file export to sql database

See more:

C#3.5

I am fetching the data from a machine and sending it to my database to display it on the website. Following is the code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Security.Permissions;
using Microsoft.Win32;
using System.Threading;
using System.Xml;
using System.Xml.Linq;
using System.Configuration;

using System.Data.SqlClient;
namespace CashManWeb_Import
{
    public partial class Form1 : Form
    {
        private string uwRegistryPath = "SOFTWARE\\MoneyPoint\\CashMan Web";

        string srcurl = "";
        string desturl = "";
        string logurl = "";
        RegistryKey registryKey = null;
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            RegistryKey registryKey = Registry.LocalMachine.OpenSubKey(uwRegistryPath, true);
            try
            {
                var p = registryKey.GetValue("srcfile");
            }
            catch (Exception er)
            {
                registryKey = Registry.LocalMachine.CreateSubKey(uwRegistryPath + "\\srcfile");
                registryKey = Registry.LocalMachine.CreateSubKey(uwRegistryPath + "\\destfile");
                registryKey = Registry.LocalMachine.CreateSubKey(uwRegistryPath + "\\logfile");
                registryKey = Registry.LocalMachine.OpenSubKey(uwRegistryPath, true);
                registryKey.SetValue("srcfile", "C:\\CashMan Web");
                registryKey.SetValue("destfile", "C:\\CashMan WebImp");
                registryKey.SetValue("logfile", "C:\\Logs");
            }
            fsw.Created += new FileSystemEventHandler(Start);
            registryKey = Registry.LocalMachine.OpenSubKey(uwRegistryPath, true);
            srcurl = (string)registryKey.GetValue("srcfile");
            desturl = (string)registryKey.GetValue("destfile");
            logurl = (string)registryKey.GetValue("logfile");
            textBox1.Text = srcurl;
            textBox2.Text = desturl;
            textBox3.Text = logurl;
            fsw.Path = @srcurl;
            fsw.EnableRaisingEvents = true;
        }

        public void Start(object source, FileSystemEventArgs e)
        {
            System.Threading.Thread.Sleep(2000);
            FileInfo f = new FileInfo(e.FullPath);
            //MessageBox.Show(f.Name + "@" + f.FullName);
            /*Console.WriteLine("File: " + e.FullPath + " " + e.ChangeType);
            label1.Text = e.FullPath;*/
            par p = new par();
            p.Parse(srcurl + "\\" + f.Name, desturl, logurl);
            //MessageBox.Show("done");
        }

        private void Browse_Click(object sender, EventArgs e)
        {
            DialogResult result = folderBrowserDialog1.ShowDialog();
            if ((result == DialogResult.OK) && (desturl != @folderBrowserDialog1.SelectedPath) && (logurl != @folderBrowserDialog1.SelectedPath))
            {
                RegistryKey registryKey = Registry.LocalMachine.OpenSubKey(uwRegistryPath, true);
                textBox1.Text = folderBrowserDialog1.SelectedPath;
                fsw.Path = @folderBrowserDialog1.SelectedPath;
                srcurl = @folderBrowserDialog1.SelectedPath;
                registryKey.SetValue("srcfile", @folderBrowserDialog1.SelectedPath);
                fsw.Path = @srcurl;
                fsw.EnableRaisingEvents = true;
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            RegistryKey registryKey = Registry.LocalMachine.OpenSubKey(uwRegistryPath, true);
            DialogResult result = folderBrowserDialog2.ShowDialog();
            if ((result == DialogResult.OK) && (srcurl != @folderBrowserDialog2.SelectedPath) && (logurl != @folderBrowserDialog2.SelectedPath))
            {
                textBox2.Text = folderBrowserDialog2.SelectedPath;
                desturl = @folderBrowserDialog2.SelectedPath;
                registryKey.SetValue("destfile", @folderBrowserDialog2.SelectedPath);
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            RegistryKey registryKey = Registry.LocalMachine.OpenSubKey(uwRegistryPath, true);
            DialogResult result = folderBrowserDialog3.ShowDialog();
            if ((result == DialogResult.OK) && (srcurl != @folderBrowserDialog3.SelectedPath) && (desturl != @folderBrowserDialog3.SelectedPath))
            {
                textBox3.Text = folderBrowserDialog3.SelectedPath;
                logurl = @folderBrowserDialog3.SelectedPath;
                registryKey.SetValue("logfile", @folderBrowserDialog3.SelectedPath);
            }
        }
    }

    class par
    {

        /* public void FiveMinuteWiatFun(decimal Header)
         {
             string cashmanwebstring = ConfigurationManager.ConnectionStrings["CashManWebDBString"].ConnectionString;
             string dnnstring = ConfigurationManager.ConnectionStrings["DnnDBString"].ConnectionString;
             try
             {
                 System.Threading.Thread.Sleep(300000);
                 SqlConnection con = new SqlConnection(cashmanwebstring);
                 if (con.State != System.Data.ConnectionState.Open)
                 {
                     con.Open();
                 }

                 SqlCommand m1 = new SqlCommand("UPDATE [CashManWebDB].[dbo].[Transaction_header]SET [Received] = 1  WHERE Cassette = (select Cassette from transaction_header where Transaction_Header_id = @h)", con);
                 SqlCommand m2 = new SqlCommand("INSERT INTO [CashManWebDB].[dbo].[Bank_received] VALUES (@p1 ,(select Cassette from transaction_header where Transaction_Header_id = @p2),getdate(),getdate(),'Processed',0.0)", con);
                 m1.Parameters.Add("@h", Header);
                 m2.Parameters.Add("@p1", Header);
                 m2.Parameters.Add("@p2", Header);
                 m1.ExecuteNonQuery();
                 m2.ExecuteNonQuery();
                 con.Close();
             }
             catch (Exception er)
             {
             }
         }
         */

        [PermissionSet(SecurityAction.Demand, Name = "FullTrust")]
        public string Parse(string xmlUrl, string b_url, string logurl)
        {
            string cashmanwebstring = ConfigurationManager.ConnectionStrings["CashManWebDBString"].ConnectionString;
            string dnnstring = ConfigurationManager.ConnectionStrings["DnnDBString"].ConnectionString;
            SqlConnection con = null;
            try
            {
                con = new SqlConnection(cashmanwebstring);
                if (con.State != System.Data.ConnectionState.Open)
                {
                    con.Open();
                }
                string date = "";
                string time = "";
                string currency = "";
                string trans_name = "";
                string trans_quan = "";
                string trans_val = "";
                string device = "";
                string result = "";
                string symbol = "";
                string type = "";
                string file_name = "";
                string user = "";
                string cassette = "";
                string filesaved = "";
               // string Till_Number = "";
                XmlDocument doc = new XmlDocument();
                System.Threading.Thread.Sleep(8000);
                doc.Load(@xmlUrl);

                XmlNode tempnode = doc.SelectSingleNode("/transaction");
                if (tempnode.SelectSingleNode("status").InnerText == "out")
                {
                    type = "collected";
                }
                else if (tempnode.SelectSingleNode("status").InnerText == "in")
                {
                    type = "deposit";
                }
                else if (tempnode.SelectSingleNode("status").InnerText == "outb")
                {
                    type = "withdrawal";
                }
                else
                {
                }

                result = result + "Device:" + tempnode.SelectSingleNode("device").InnerText + "\n";
                device = tempnode.SelectSingleNode("device").InnerText;
                result = result + "Currency:" + tempnode.SelectSingleNode("currency").InnerText + "\n";
                XmlNode temp = tempnode.SelectSingleNode("currency");
                currency = temp.SelectSingleNode("description").InnerText;
                symbol = temp.SelectSingleNode("symbol").InnerText;
                temp = tempnode.SelectSingleNode("date_time");
                date = temp.SelectSingleNode("date").InnerText;
                time = temp.SelectSingleNode("time").InnerText;

                cassette = tempnode.SelectSingleNode("cassette").InnerText;
                user = tempnode.SelectSingleNode("operator").InnerText;
                filesaved = tempnode.SelectSingleNode("filename").InnerText;
                doc.Save("Data.xml");
                


                //SqlCommand m1 = new SqlCommand("BEGIN TRAN T1;", con);
                //m1.ExecuteNonQuery();`
                SqlCommand m2 = null;
                if (type == "collected")
                {
                    m2 = new SqlCommand("INSERT INTO [Transaction_header]([Obls_id],[Currency],[Transaction_status],[Date_time_created],[Date_time_modified],[Symbol],CIT_id,XML_Status,[Operator],[Cassette],[Filename], Received,[Time_received],[Processed],[Time_Processed], Receiver_id) VALUES ((select Obls_id from dbo.Organisation_Branch_Location_Sources where Source_device_id = (select Source_device_id from Source_Devices where Source_device_name = '" + @device + "') and Active_flag = 1),'" + @currency + "','" + @type + "',@date1+ ' '+ @time1,  @date2+ ' '+ @time2,'" + @symbol + "',(select br.CIT_id from Organisation_Branch_Locations br, Organisation_Branch_Location_Sources obs, Source_Devices where br.Organisation_branch_location_id  = obs.Organisation_branch_location_id  and obs.Source_device_id =  (select Source_device_id from Source_Devices where Source_device_name = @p1) and obs.Active_flag = 1 group by br.Organisation_branch_location_id, br.Organisation_id, br.CIT_id)'Not sent',@o,@c,@f,0,null,0,null(select Receiver_id from Organisation_Branch_Locations where Organisation_branch_location_id = (select Organisation_branch_location_id from Organisation_Branch_Location_Sources where Source_device_id = (select Source_device_id from Source_Devices where Source_device_name = '" + @device + "') and Active_flag = 1)))", con);
                }
                else if (type == "deposit")
                {
                    m2 = new SqlCommand("INSERT INTO [Transaction_header]([Obls_id],[Currency],[Transaction_status],[Date_time_created],[Date_time_modified],[Symbol],CIT_id,XML_Status,[Operator],[Cassette],[Filename], Received,[Time_received],[Processed],[Time_Processed], Receiver_id) VALUES ((select Obls_id from dbo.Organisation_Branch_Location_Sources where Source_device_id = (select Source_device_id from Source_Devices where Source_device_name = '" + @device + "') and Active_flag = 1),'" + @currency + "','" + @type + "',@date1+ ' '+ @time1,@date2+ ' '+ @time2,'" + @symbol + "',1,'Not sent',@o,@c,@f,0,null,0,null(select Receiver_id from Organisation_Branch_Locations where Organisation_branch_location_id = (select Organisation_branch_location_id from dbo.Organisation_Branch_Location_Sources where Source_device_id = (select Source_device_id from Source_Devices where Source_device_name = '" + @device + "') and Active_flag = 1)))", con);
                }
                else if (type =="withdrawal")
                {
                  m2 = new SqlCommand("INSERT INTO [Transaction_header]([Obls_id],[Currency],[Transaction_status],[Date_time_created],[Date_time_modified],[Symbol],CIT_id,XML_Status,[Operator],[Cassette],[Filename], Received,[Time_received],[Processed],[Time_Processed], Receiver_id) VALUES ((select Obls_id from dbo.Organisation_Branch_Location_Sources where Source_device_id = (select Source_device_id from Source_Devices where Source_device_name = '" + @device + "') and Active_flag = 1),'" + @currency + "','" + @type + "',@date1+ ' '+ @time1,@date2+ ' '+ @time2,'" + @symbol + "',3,'Not sent',@o,@c,@f,0,null,0,null(select Receiver_id from Organisation_Branch_Locations where Organisation_branch_location_id = (select Organisation_branch_location_id from dbo.Organisation_Branch_Location_Sources where Source_device_id = (select Source_device_id from Source_Devices where Source_device_name = '" + @device + "') and Active_flag = 1)))", con);
                }
                
                m2.Parameters.Add("@p1", device);
                m2.Parameters.Add("@o", user);
                m2.Parameters.Add("@f", filesaved);
                m2.Parameters.Add("@c", cassette);
                m2.Parameters.Add("@date1", date);
                m2.Parameters.Add("@time1", time);
                m2.Parameters.Add("@date2", date);
                m2.Parameters.Add("@time2", time);
                string command = m2.CommandText;
                //MessageBox.Show(command);
                try
                {
                    m2.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                   // SqlCommand me = new SqlCommand("COMMIT", con);
                    //me.ExecuteNonQuery();
                    throw (new ArgumentNullException(e.ToString()));
                    MessageBox.Show(e.ToString());
                    
                }
                SqlCommand m3 = new SqlCommand("SELECT @@IDENTITY ", con);
                decimal head_id = 0;

                SqlDataReader rdr = null;
                /*try
                {*/
                rdr = m3.ExecuteReader();
                while (rdr.Read())
                {
                    // var g = rdr.GetDecimal(0);
                    head_id = rdr.GetDecimal(0);
                }
                rdr.Close();
                /* }
                catch(Exception e)
                 {
                     Console.WriteLine(e.ToString());
                     rdr.Close();
                 }0*/
                tempnode = doc.SelectSingleNode("/transaction");

                XmlNodeList transacationlist = tempnode.SelectNodes("transactionline");
                int line = 1;
                foreach (XmlNode node in transacationlist)
                {
                    result = result + node.Attributes["type"].Value + ": \n";
                    trans_name = node.Attributes["type"].Value;
                    result = result + "quantity:" + node.SelectSingleNode("quantity").InnerText + "\n";
                    trans_quan = node.SelectSingleNode("quantity").InnerText;
                    result = result + "value:" + node.SelectSingleNode("value").InnerText + "\n";
                    trans_val = node.SelectSingleNode("value").InnerText;
                    SqlCommand ml = new SqlCommand("INSERT INTO [Transaction_line]([Transaction_header_id],[Transaction_line_id],[Product_name],[Value],[Quantity]) VALUES (" + @head_id + "," + @line + " ,'" + @trans_name + "'," + @trans_val + "," + @trans_quan + ")", con);
                    ml.ExecuteNonQuery();
                    line++;
                }

                SqlCommand mr = new SqlCommand("commit TRAN T1;", con);
                mr.ExecuteNonQuery();
                con.Close();


                try
                {
                    file_name = @b_url + "\\" + DateTime.Now.ToString("ddMMyy") + DateTime.Now.ToString("HHmmss") + "imported.xml";
                    File.Copy(@xmlUrl, @b_url + "\\" + DateTime.Now.ToString("ddMMyy") + DateTime.Now.ToString("HHmmss") + "imported.xml");
                    // System.Threading.Thread.Sleep(5000);
                }

           // Catch exception if the file was already copied. 
                catch (IOException copyError)
                {
                    Console.WriteLine(copyError.Message);
                }
                File.Delete(xmlUrl);


                // The FIVE MINUTE PROCESS
                /*Thread five_minute = new Thread(() => this.FiveMinuteWiatFun(head_id));
                if (type == "collected")
                {
                    five_minute.Start();
                }
                */

                // The FIVE MINUTE PROCESS





                System.IO.StreamWriter file = new System.IO.StreamWriter(@logurl + "\\log.txt", true);
                file.WriteLine(result);
                file.WriteLine();
                file.WriteLine("File : " + file_name);
                file.WriteLine();
                file.WriteLine("Successfully completed");
                file.WriteLine("==================================================================================================");
                file.WriteLine("==================================================================================================");
                file.WriteLine();
                file.Close();
                //Five Minute Process
                /*
                if (type == "collected")
                {
                    five_minute.Join();
                }
                 */
                return result;


            }
            catch (Exception e)
            {

                //SqlCommand m1 = new SqlCommand("COMMIT", con);
                //m1.ExecuteNonQuery();
                string err = DateTime.Now.ToString() + "  " + e.ToString();

                System.IO.StreamWriter file = new System.IO.StreamWriter(@logurl + "\\log.txt", true);
                file.WriteLine(err);
                file.WriteLine();
                file.WriteLine("==================================================================================================");
                file.WriteLine("==================================================================================================");
                file.WriteLine();
                file.Close();
                MessageBox.Show(err);
                return err;
            }
        }

    }

}

Everything is ok when there is a deposit file coming and collected file coming but when withdrawal file is coming it is giving me error that is as follows:

I am not getting where I am getting this error
Incorrect syntax near ')'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at CashManWeb_Import.par.Parse(String xmlUrl, String b_url, String logurl) in C:\Users\gmanan\Desktop\CashMan Web Folder\CashMan Web Versions\Version 2\My modules\CashManWeb Import\Form1.cs:line 239
at CashManWeb_Import.par.Parse(String xmlUrl, String b_url, String logurl) in C:\Users\gmanan\Desktop\CashMan Web Folder\CashMan Web Versions\Version 2\My modules\CashManWeb Import\Form1.cs:line 245