title: SQL Server 2017 Express randomly failed to insert
body: <p>We have a SQL Server 2017 Express instance installed on Win10, with cumulative update 18. The SQL insert is generated in a C# Winforms app on a client machine (Windows 7; just single query repeating).</p>
  
  <p>Recently we notice sometimes the insert fails without any error from either SQL Server or the client. Strangely from the same Winforms application, if we query the SQL Server for the number of records, just after the insert (within the same connection), the number is correct, as if every insert is successful. </p>
  
  <p>But checking the SQL Server table afterwards, we can see some records are missing. We can not recreate the problem using same query.</p>
  
  <p>The system was running fine initially with SQL Server 2005 (on a Windows 2003 server) + VS2013/2015. The problem only happened after we upgraded SQL Server from 2005 to 2008 and then to 2017 on a Windows 10 machine. </p>
  
  <p>I tried to upgrade the project from VS 2015 to VS 2017 but that doesn't help. </p>
  
  <p>The C# project is targetting the .NET Framework 4 with Windows 7.</p>
  
  <p>One of the column is a <code>varchar(128)</code> nullable, I can not see any data is longer than 128 characters.</p>
  
  <p>The connection string is </p>
  
  <pre><code>sqlCon.ConnectionString = "Data Source=" + dataSource + ";Integrated Security=SSPI;Connection Timeout=10;Initial Catalog=" + DatabaseName;
  </code></pre>
  
  <p>This is the code for the insert. Does anyone have a similar issue or a suggestion? Thanks</p>
  
  <pre><code>SqlTransaction transaction;
  
  // Start a local transaction.
  transaction = con.BeginTransaction();
  
  try
  {
      int rowsmodified;
  
      using (SqlCommand cmd = new SqlCommand("INSERT INTO Cards (JobStepID, Serial, CardNumber, DateProgrammed) " +
                   " VALUES (@JOBSTEPID, @CSN, @CARDNUMBER, @DATEPROGRAMMED); SELECT CAST(SCOPE_IDENTITY() AS int)", con, transaction)) 
      {
          cmd.Parameters.Add("@JOBSTEPID", SqlDbType.Int);
          cmd.Parameters.Add("@Serial", SqlDbType.VarChar);
          cmd.Parameters.Add("@CARDNUMBER", SqlDbType.Int);
          cmd.Parameters.Add("@DATEPROGRAMMED", SqlDbType.DateTime);
  
          cmd.Parameters["@JOBSTEPID"].Value = jobStepID;
          cmd.Parameters["@Serial"].Value = serial;
          cmd.Parameters["@CARDNUMBER"].Value = cardNumber;
          cmd.Parameters["@DATEPROGRAMMED"].Value = DateTime.Now;
  
          rowsmodified = (int)cmd.ExecuteNonQuery();
      }
  
      if (rowsmodified != 1)
      {
          transaction.Rollback();
          throw new Exception("Error add more than one row");
      }
      else
      {
          transaction.Commit();
      }
  
      return rowsmodified;
  }
  catch (Exception ex)
  {
      throw new Exception("Error add card record in database:", ex);
  }
  </code></pre>