For my data insertion method how do I check that the values from the pulled data from the database are being passed correctly or if at all?

Question

Okay so from my previous question this is one iteration of how I am pulling data from the SQL Server and then inserting that same data into CtreeACE where the table is already setup for the values to be stored in there. When I run the code I get

Things I have tried:

• rewriting the method
• Refining how the items are stored after being pulled
• Making sure the names matched the correct call names
• Debugged my code multiple times and still can't wrap my head around it

Here is the code

 class Program
    {
        static CtreeSqlConnection _conn;
        static CtreeSqlCommand cmd;
        static CtreeSqlDataReader reader;



    static void Main(string[] args)
    {  //Creating the connection to the SQL server 
        SqlConnection conn = new SqlConnection("Server=bldg-db-pri.MDHUN.us.ups.com\\p001;Database=D90;Integrated Security=true");
        //Open the connection 
        conn.Open();
        SqlCommand cmd = new SqlCommand("SELECT TOP(100) l.tracingID, u.Sch_dt, p.address, p.city, p.state, u.zip, m.Time " +
            "FROM D490AD0.dbo.TUWUOW1 u WITH (nolock) " +
            "INNER JOIN D90.dbo.TUW p WITH (nolock) ON p.UOW = u.UOW " +
            "INNER JOIN D90.dbo.TUW2 l  WITH (nolock) ON l.UOW = u.UOW " +
            "CROSS JOIN  D90.dbo.tTN m " +
            "WHERE " +
            "u.Sch_dt = '2018-07-23' ", conn); //query that intializes after the connection is opened 


        using (SqlDataReader reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                Console.WriteLine($"{reader["tracingID"]} |" +
                    $"{reader["Time"]} |" +
                     $"{reader["state"]} |" +
                    $"{reader["address1"]} |" +
                    $"{reader["address1"]} |" +
                    $"{reader["address3"]} |" +
                    $"{reader["city"]} |" +
                    $"{reader["zip"]} |" +
                    $"{reader["Sch_dt"]}"
                    );
                try
                {

                    DataInsertion($"{reader["tracingID"]} " ,
                    $"{reader["Time"]} " ,
                     $"{reader["state"]} " ,
                    $"{reader["address1"]} ",
                    $"{reader["address1"]} " ,
                    $"{reader["address3"]} ",
                    $"{reader["city"]} " ,
                    $"{reader["zip"]} " ,
                    $"{reader["Sch_dt"]}"
                    );
                }
                catch (CtreeSqlException e)
                {
                    Console.WriteLine(e + " couldn't run method");
                }
            }
        }
         reader.Close();
         conn.Close();

        if(Debugger.IsAttached)
        {
            Console.ReadLine();
        }


    }


    public static void DataInsertion(string tracingID, string Time, string state, string address1, string address2, string address3, string city, string zip, string Sch_dt)
    {
        _conn = new CtreeSqlConnection();
        _conn.ConnectionString = "UID=ADMIN;PWD=ADMIN;Database=AttributeDB;Server=localhost;Service=6597;";
        _conn.Open();
        if (_conn == null)
        {
            Console.WriteLine("Could not connect to Ctree");
        }
        try
        {

            cmd.CommandText = "INSERT INTO tbl6(tracingID, Time, state, address, city, zipcode, dates) VALUES(tracingID, Time, state ,address,  city, zip, Sch_dt)";
            cmd.Parameters.Clear();
            cmd.Parameters.Add(new CtreeSqlParameter(tracingID));
            cmd.Parameters.Add(new CtreeSqlParameter(Time));
            cmd.Parameters.Add(new CtreeSqlParameter(state));
            cmd.Parameters.Add(new CtreeSqlParameter(address));
            cmd.Parameters.Add(new CtreeSqlParameter(city));
            cmd.Parameters.Add(new CtreeSqlParameter(zip));
            cmd.Parameters.Add(new CtreeSqlParameter(Sch_dt));

            cmd.ExecuteNonQuery();
        }
        catch (CtreeSqlException ctsqlEx)
        {
            Console.WriteLine("Something went wrong with the command script");
        }



    }

}

This is where the method inserts the pulled data into the Ctreedatabase

           CtreeSqlCommand cmd = new CtreeSqlCommand("INSERT INTO tbl6(tracingID, Time, state, address, city, zipcode, dates) VALUES(tracingID, Time, state ,address,  city, zip, Sch_dt)", _conn);
            cmd.Parameters.Clear();
            cmd.Parameters.Add(new CtreeSqlParameter(tracingID));
            cmd.Parameters.Add(new CtreeSqlParameter(Time));
            cmd.Parameters.Add(new CtreeSqlParameter(state));
            cmd.Parameters.Add(new CtreeSqlParameter(address));
            cmd.Parameters.Add(new CtreeSqlParameter(city));
            cmd.Parameters.Add(new CtreeSqlParameter(zip));
            cmd.Parameters.Add(new CtreeSqlParameter(Sch_dt));

            cmd.ExecuteNonQuery();
        }

I assume the values aren't being passed in? Everytime I run the program none of the values show up within the ctree database

Exceptions after code runs

Ctree.Data.SqlClient.CtreeSqlException (0x7FFFB1DD): Syntax error ---> Ctree.SqlClient.Common.FcSqlException: Syntax error
   at Ctree.SqlClient.FcSqlXApi.SQLExec(FcStatement stmt, Int32 InStatementType, FcSqlDA ida, FcSqlDA oda, FcSqlCA sqlca)
   at Ctree.SqlClient.FcSqlXApi.Prepare(FcStatement stmt, FcSqlDA input_sqlda, FcSqlDA output_sqlda, Int32 fetchSize)
   at Ctree.SqlClient.FcConnection.Prepare(FcStatement statement, FcSqlDA inputDA, FcSqlDA outputDA, Int32 fetchSize)
   at Ctree.SqlClient.FcPreparedStatement..ctor(FcConnection connexion, String sql, Int32 fetchSize, Int32 timeout)
   at Ctree.Data.SqlClient.CtreeSqlCommand.InternalPrepare(Boolean resultSet)
   at Ctree.Data.SqlClient.CtreeSqlCommand.ExecuteNonQuery()
   at Ctree.Data.SqlClient.CtreeSqlCommand.ExecuteNonQuery() 

Answer 1

Could you try replacing:

CtreeSqlCommand cmd = new CtreeSqlCommand(@"INSERT INTO tbl6(tracingID, Time, state, address, city, zipcode, dates) 
VALUES(tracingID, Time, state ,address,  city, zip, Sch_dt)", _conn);
cmd.Parameters.Clear();
cmd.Parameters.Add(new CtreeSqlParameter(tracingID));

With something like this:

CtreeSqlCommand cmd = new CtreeSqlCommand(@"INSERT INTO tbl6(tracingID, Time, state, address, city, zipcode, dates) 
VALUES(@tracingID, @Time, @state, @address, @city, @zip, @Sch_dt)", _conn);
cmd.Parameters.Clear();
cmd.Parameters.Add(new CtreeSqlParameter("@tracingID", tracingId));

etc. i.e. do the same for all the other parameters.

Note I split your command onto two lines, just to make it more obvious that I added an "@" before each parameter value, so this was just for readability.