Why can I save the error information into the table of SQL server ?

[dongtrien]

Why I can not save the error information in the table: TABERRORINFO. Suppose I have two: TABCUSTOMER and TABERRORINFO.

private void cmdSave_Click(object sender, EventArgs e)
{
strSQL = "INSERT INTO TABCUSTOMER(IDCUSTOMER, FULLNAME, ADDRESS, IMAGE) " +
         "VALUES (@IDCUSTOME, @FULLNAME, @ADDRESS, @ImageData);"; 

SqlCommand cmd = new SqlCommand(strSQL, ClsConnection.objConnect);
        try
        {
            cmd.Parameters.AddWithValue("@IDCUSTOMER", txtIDCustomer.Text);
            cmd.Parameters.AddWithValue("@FULLNAME", txtFullName.Text);
            cmd.Parameters.AddWithValue("@ADDRESS", txtAddress.Text);            
            //byte[] ImageData = ReadFile(sPath); 
            cmd.Parameters.Add(new SqlParameter("@ImageData", (object)ImageData));            

            if (ClsConnection.objConnect.State != System.Data.ConnectionState.Open)
            {
                ClsConnection.objConnect.Open();
            }      
            cmd.CommandType = CommandType.Text;            
            cmd.ExecuteNonQuery();            
            // Close database
            ClsConnection.objConnect.Close();
        }
        catch (SqlException ex)
        {
            ClsConnection.objConnect.Close();
            string sConError = "Save Messager Customer 1: " + ex.Message;
            SaveMessageErrSQL(sConError, strSQL);            
        }
}

private void SaveMessageErrSQL(string MessErr, string SqlErr)
        {            
            string sSQL = "UPDATE TABERRORINFO SET ";
            sSQL = sSQL + " ERRDATE = @NgayErr";
            sSQL = sSQL + ", ERRMESSAGE = @MesaErr";
            sSQL = sSQL + ", ERRSQL = @SQLErr";
            sSQL = sSQL + " WHERE USERNAME = N'" + sUSERNAME + "';";
            Debug.Print(sSQL);            

            SqlCommand cmd = new SqlCommand(sSQL, objConnect);
            try
            {
                cmd.Parameters.AddWithValue("@NgayErr", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                cmd.Parameters.AddWithValue("@MesaErr", MessErr);
                cmd.Parameters.AddWithValue("@SQLErr", SqlErr);
                cmd.CommandType = CommandType.Text;
                cmd.ExecuteNonQuery();
                objConnect.Close();
                DevExpress.XtraEditors.XtraMessageBox.Show("Saved" + MessErr);
            }
            catch (SqlException ex)
            {                
                objConnect.Close();
                //MessageBox.Show("SaveMessageErrSQL lỗi: " + sSQL);
                MessageBox.Show("No Save Message Errer into SQL: " + ex.Message);
            }

        }

[Tron]

Is your DB Connection closed?

[dongtrien]

DB is opening. I intentionally create/caused the error by adding IMAGE and cmd.Parameters.Add(new SqlParameter("@ImageData", (object)ImageData)) to catch (SqlException ex) call SaveMessageErrSQL(...)

[Tron]

But I think you are closing the DB before you are writing to the error table.

            ClsConnection.objConnect.Close();
            string sConError = "Save Messager Customer 1: " + ex.Message;
            SaveMessageErrSQL(sConError, strSQL);

[dongtrien]

private void cmdSave_Click(object sender, EventArgs e)
{
    try
   {
      ...
      int rowsAffected = cmd.ExecuteNonQuery();//Error here
      Debug.Print(rowsAffected); // Debug I saw rowsAffected = 0     
     ...
   }
   catch (SqlException ex) // Debug I saw ex {"Operand type clash: nvarchar is incompatible with image"} 
   {
       ...
      SaveMessageErrSQL(sConError, strSQL); // I wrote the wrong code this place

   }
}

Debug I saw :
I see the variable rowsAffected = 0
+ ex {"Operand type clash: nvarchar is incompatible with image"} System.Data.SqlClient.SqlException

what do I do now ?

[Tron]

The datatype for an image in SQL Server is

varbinary(max)

Can you change the DB table schema?

[dongtrien]

The datatype in My SQL Server is an image. I know error at cmd.Parameters.Add(new SqlParameter("@ImageData", (object)ImageData)) (I intentionally caused the error to catch (SqlException ex) runtime), when errors occur procedure SaveMessageErrSQL(..) runtime but the process SaveMessageErrSQL(..) has not save scored in the Table error. if I run independent procedures SaveMessageErrSQL(..), the procedures SaveMessageErrSQL(...) is the good run

[Tron]

Try something like this in your SaveMessageErrSQL() method.

           if (objConnect.State != System.Data.ConnectionState.Open)
            {
                objConnect.Open();
            }

[dongtrien]

I mentioned above, not faulty connection, I deliberately make errors in SQL stored image to stored procedures and queries run SaveMessageErrSQL image file into SQL Server error, I have experimented with all encountered errors stored separately SQL error saving the image to the left SaveMessageErrSQL bypass procedures are not saved, I do not know why, I want to switch in C # error messages, you have to know how to code in C # error messages in this case?

[Arjay]

Let the Sql provider do its job and manage the connection state for you. Internally it has connection pooling so you aren't gaining anything by attempting to keep a connection opened and reusing it.

So using this pattern that uses a couple of using blocks and live a happy life. Resources will be automatically cleaned up if any exceptions occur.

And the SQL provider will internally keep a pool of connections available (so you aren't really opening new connections when you do a cn.Open().

using(var cn = new SqlConnection(someConnectionStr))
{
  cn.Open();

  using(var cmd = new SqlCommand("some command string or sproc", cn))
  {
      // use the cmd here
  }
}

[dongtrien]

I no faulty connections, I faulty image