C# SQL query not producing data

Question

I have a section of code in my program that is supposed to pull the most recent time for a batch at a specific event. All I'm getting however is a blank result. I know there's data in the table for the batch in question and SQL Profiler shows it is being executed.

 public void UpdateBeginStir()
    {
        string beginStir = null;
        string connectionString = "(Omitted)";
        string commandLine = "SELECT MAX(Event_Time) AS Time " +
                             "FROM dbo.Custom_EventLog " +
                             "WHERE Container_ID = @LOTNUMBR " +
                             "AND Event_ID = 1 " +
                             "GROUP BY BadgeNo, Container_ID, Event_ID";
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            using (SqlCommand command = new SqlCommand(commandLine, connection))
            {
                command.Parameters.Add("@LOTNUMBR", SqlDbType.NChar, 50).Value = TextBoxLot.Text;
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    beginStir = reader["Time"] as string;
                    break;
                }
            }
            connection.Close();
        }
        MessageBox.Show(beginStir, "test", MessageBoxButton.OK);
        LabelBeginStir.Content = beginStir;
    }

so when you execute "SELECT MAX(Event_Time) AS Time FROM dbo.Custom_EventLog WHERE Container_ID = 'whatever LOTNUMBR is' AND Event_ID = 1 GROUP BY BadgeNo, Container_ID, Event_ID" , it gives the expected result? What is the value of TextBoxLot.Text? — NP3
– NP3, Commented May 18, 2017 at 16:40
When I execute the query in SSMS, I get the expected result of the most recent time. When I run it in the code, I get a blank result as in the message box is blank. — Ryan Fauser
– Ryan Fauser, Commented May 18, 2017 at 16:42
If you only expect 1 result with 1 value, I'd just beginStir = command.ExecuteScalar() as string; and simplify to command.Parameters.AdWithValue("@LOTNUMBR", TextBoxLot.Text); while @NemanjaPerovic's answer will likely fix your issue and explains why it doesn't work as it is now. — Filburt
– Filburt, Commented May 18, 2017 at 16:46

NP3 · Accepted Answer · 2017-05-18 23:41:53Z

4

SqlDbType.NChar will pad right with blank spaces up to 50 characters, and Container_ID won't match. Use SqlDbType.NVarChar instead

UPDATE: the correct answer ended up being

beginStir = Convert.ToDateTime(reader["Time"]).ToString();

edited May 18, 2017 at 23:41

answered May 18, 2017 at 16:42

NP3

1,2741 gold badge10 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

SS_DBA Over a year ago

Good catch... If this is the reason, that's great! @Ryan Fauser. But shouldn't LOTNUMBR be an int or at least a number? Assuming Container_ID is an int.

NP3 Over a year ago

@WEI_DBA Great point. Also, Filburt made a valid point. There are a few things to improve in this code.

Ryan Fauser Over a year ago

I get "System.FormatException: 'Input string was not in a correct format.'" on the Convert.ToInt32 line. I query the lot number in other parts of the program and it works just fine. Could it be something with how I'm entering the Event_ID?

NP3 Over a year ago

What is the TextBoxLot.Text (container_ID)? Is it an integer? Also, does my original suggestion of changing NChar to NVarChar solve the issue?

Ryan Fauser Over a year ago

Container_ID is a NChar on the SQL table. It's a series of numbers like 17080387-002. The original suggestion did not work either.

|

Collectives™ on Stack Overflow

C# SQL query not producing data

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related