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()

I think the problem here is probably that you are just supplying values to your parameters, and you should be supplying parameter name, data type and value? Here's a link to the MSDN article for a "normal" SQL parameter: https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters(v=vs.110).aspx and I assume a CtreeSqlParameter works the same way? — Richard Hansell, Jul 25 '18 at 12:39
Normally parameters (the things in the `VALUES()` part) use some kind of placeholder. Are you certain that's the format that CTree expects? — Nick.Mc, Jul 25 '18 at 12:57
If you aren't getting any exceptions, then the data must be going some place. or you aren't executing the instructions. First make sure you are getting to the INSERT Query. then check your connection string and see where the data is really going. — jdweng, Jul 25 '18 at 12:58
@Nick.McDermaid I'm not entirely sure I saw a document that showed "VALUES(?, ?, ?, ?)" which could mean it does it automatically? — Bartholomew Allen, Jul 25 '18 at 13:02
This is the wrong time to be saying you are getting exceptions. You need to capture the exception properly, and post the message in your original question. This is the second question I've seen tonight with completely incorrect SQL syntax hidden by an exception handler that ignored it — Nick.Mc, Jul 25 '18 at 13:04
Page 211 of this has a sample insert using ? as a parameter placeholder. https://docs.faircom.com/doc/sqlref/sqlref.pdf maybe you should try that. — Nick.Mc, Jul 25 '18 at 13:10
Slight detour...if you care at all about accuracy you should stop splattering that nolock hint everywhere. It can and will randomly return missing and/or duplicate rows along with a whole laundry list of other "features". https://blogs.sentryone.com/aaronbertrand/bad-habits-nolock-everywhere/ — Sean Lange, Jul 25 '18 at 13:10
@SeanLange I know the risks of using it I have tried using the Query without it and doesn't make much of a difference when pulling data from the Database — Bartholomew Allen, Jul 25 '18 at 13:25

score 0 · Accepted Answer · answered Jul 25 '18 at 12:51

0

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.

answered Jul 25 '18 at 12:51

Richard Hansell

5,315
1
16
35

and if that doesn't work, try `?` as a placeholder. Page 211 of this document seems to use it https://docs.faircom.com/doc/sqlref/sqlref.pdf – Nick.Mc Jul 25 '18 at 13:11
Thank you both. @RichardHansell your solution helped with the format greatly! – Bartholomew Allen Jul 25 '18 at 13:26
Please don't mark an answer correct if it is not 100% correct. Please post the actual correct syntax. This answer is of no use to anyone if they plug it in and it doesn't work. – Nick.Mc Jul 25 '18 at 22:03

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?

1 Answers1