SqlCommand ExecuteNonquery strange exception

Question

I want to insert a record to SQL Server Database but I got a strange exception while ExecuteNonQuery() process. I got this exception:

ExecuteNonQuery requires an open and available Connection. 
The connection's current state is closed.

However my problem is not related with this. I am definitely sure that my connection's current state is Open. My problem is one of my parameters in my query. Here is my query:

                  sql = "update EMAIL_CONNECTIONS " +
                             "set END_DATE=@EndDate, " +
                             "STATUS=@Status, " +
                             "STATUS_DATE=@StatusDate, " +
                             "CATEGORY_CODE=@CategoryCode, " +
                             "EMAILOUT_SENTDATE=@EmailOutSentDate, " +
                             "TO_ADDRESS=@ToAddress " +
                             "where SESSIONID=@SessionId " +
                             "and STATUS = 'Inprocess'; ";
                sql += "insert into ICS_EMAIL_CONNECTIONS_TRX(SESSIONID, AGENTID, STATUS, FIELD1) " +
                       "values(@SessionId, @AgentId, @Status, 'Sended this mail')";

Exception throw because of the @EmailOutSentDate parameter. It does not accept Datetime format or something I dont know. When I give DateTime.Now to this parameter query runs successfully. I also tried DBNull.Value and query runs perfectly. DateTime problem is not related with the Exception.

   SqlConnection _cnn = new SqlConnection();
   _cnn.ConnectionString = connectionString;
   _cnn.Open();
    SqlCommand cmd = new SqlCommand(sql, _cnn);                    
   cmd.Parameters.Add(new SqlParameter("@EndDate", DateTime.Now));
   cmd.Parameters.Add(new SqlParameter("@Status", "Sent"));
   DateTime result = DateTime.MinValue;
   result = DateTime.ParseExact(result.ToString("yyyy-mm-dd HH:mm:ss.fff"), "yyyy-mm-dd HH:mm:ss.fff", null);
                DateTime newdate = DateTime.SpecifyKind(result, DateTimeKind.Local);
   cmd.Parameters.Add(new SqlParameter("@EmailOutSentDate", newdate));                   
   cmd.Parameters.Add(new SqlParameter("@ToAddress", interaction.AllAttributes["To"]));
   cmd.Parameters.Add(new SqlParameter("@StatusDate", DateTime.Now));
   cmd.Parameters.Add(new SqlParameter("@CategoryCode", long.Parse(CategoryCode)));
   cmd.Parameters.Add(new SqlParameter("@SessionId", interaction.Id));
   cmd.Parameters.Add(new SqlParameter("@AgentId", Agent.AgentId));
   cmd.CommandType = System.Data.CommandType.Text;
   cmd.ExecuteNonQuery();
   cmd.Dispose();

As you can see I have also tried DateTime.Kind property but still same Exception thrown. Where is the problem? Any suggesstions?

does the stacktrace of the exception point back the the ExecuteNonQuery that is shown here? or is this exception perhaps coming from some exception handling that you aren't showing? Also: `using`. You should be `using(var _cnn = ...)` and `using(var cmd = ...)` — Marc Gravell, Nov 07 '13 at 08:19
"It does not accept Datetime format or something" - datetimes are not sent to sql server as text; this is not a formatting issue — Marc Gravell, Nov 07 '13 at 08:19
btw; unrelated - re the way you've built the `sql` value - did you know you can do that more simply using verbatim string literals? i.e. `sql = @"{your text here; can include line feeds etc just by pressing return}";` — Marc Gravell, Nov 07 '13 at 08:22
You should use a `using` statement for mthe connection and the command. Otherwise the connection keeps open on error. — Tim Schmelter, Nov 07 '13 at 08:23
What are you doing here? You are converting `DateTime.MinValue` to `string`, then back to `DateTime` with `DateTime.ParseExact`, finally you are using `DateTime.SpecifyKind` on it. The result is the same: `DateTime.MinValue` which is not supported in sql-server(starts with January 1, 1753). — Tim Schmelter, Nov 07 '13 at 08:31

score 1 · Answer 1 · edited May 23 '17 at 11:57

What exactly are you trying to do? It looks like you are trying to insert a dummy date instead of having a null value in the db? If this is the case, you should stick with inserting a null value instead of a date that has no actual meaning.

If you actually have a DateTime value that you wan't to insert as localized value, this might help you: How to produce localized date string with CultureInfo

Here is a update method we use in our old db layer when not working with EF and it will give you a safer code. Notice that you don't need to open the connection until you actually need to execute the query, and using a using statement will ensure that you dispose of your SqlCommand. The _connection property is private and used for all methods in this class, and is set in the constructor by DependencyInjection.

_connection = new SqlConnection(this._connectionString);

public int Update(string query, Dictionary<string, string> commandParams)
    {
        int affectedRows = 0;
        using (SqlCommand command = new SqlCommand(query, _connection))
        {
            command.CommandType = CommandType.Text;
            foreach (var param in commandParams)
            {
                if (param.Value == null)
                    command.Parameters.AddWithValue(param.Key, DBNull.Value);
                else
                    command.Parameters.AddWithValue(param.Key, param.Value);
            }

            try
            {
                _connection.Open();
                affectedRows = command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                _connection.Close();
                command.Parameters.Clear();
            }
        }

        return affectedRows;
    }

SqlCommand ExecuteNonquery strange exception

1 Answers1