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
– Marc Gravell, Commented Nov 7, 2013 at 8: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
– Marc Gravell, Commented Nov 7, 2013 at 8: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
– Marc Gravell, Commented Nov 7, 2013 at 8:22
You should use a using statement for mthe connection and the command. Otherwise the connection keeps open on error. — Tim Schmelter
– Tim Schmelter, Commented Nov 7, 2013 at 8: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
– Tim Schmelter, Commented Nov 7, 2013 at 8:31

Community · Accepted Answer · 2017-05-23 11:57:31Z

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;
    }

Collectives™ on Stack Overflow

SqlCommand ExecuteNonquery strange exception

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related