SQL Write Errors - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SQL Write Errors

Author

Topic

jp2code
Posting Yak Master

175 Posts

Posted - 2009-04-14 : 14:41:05

I've got a program that creates a text string from a table of barcode values. If the value is not found in the table, it is not accepted. This is to ensure standardized input into the tables.

When I write to the table, it is a basic insert command that never throws an error in the try/catch routine.

Out of months worth of data, I can see small flaws in how the server is saving the text in the Test_Result varchar(255) field using this query:

select distinct test_result from final_check order by test_result

Here is a sample output of the test_result column showing the problem:

PASS AF%ER REWORKPASS AFTER REWORK
PASS AFT5R REWORKPASS AFTER REWORK
PASS AFTEK REWORKPASS AFTER REWORK
PASS AFTER DEPAIRLEAKFP1PASS AFTER REPAIR,1LEAKFP1
PASS AFTER HEPAIRPASS AFTER REPAIR,1LEAKFP2
PASS AFTER HEPAIRPASS AFTER REPAIR,1LEAKRB
PASS AFTER KEPAIRPASS AFTER REPAIR,1LEAKDC
PASS AFTER REPAIR
PASS AFTER REPAIR,1LEAKRB
PASS AFTER REPAIR,1LEAKFP2
PASS AFTER REPAIR,000320 override
PASS AFTER REPAIR,000358 override
PASS AFTER REPAIR,003468,1LEAKFP1,1LEAKFP2
PASS AFTER REPAIR,003468,1LEAKFP2
PASS AFTER REPAIR,003607 override
PASS AFTER REPAIR,0LEAKFP1,2LEAKFP2,1LEAKRB

These rows should look like this:


PASS AFTER REWORK
PASS AFTER REWORK
PASS AFTER REWORK
PASS AFTER REPAIR,1LEAKFP1
PASS AFTER REPAIR,1LEAKFP2
PASS AFTER REPAIR,1LEAKRB
PASS AFTER REPAIR,1LEAKDC
PASS AFTER REPAIR
PASS AFTER REPAIR,1LEAKRB
PASS AFTER REPAIR,1LEAKFP2
PASS AFTER REPAIR,000320 override
PASS AFTER REPAIR,000358 override
PASS AFTER REPAIR,003468,LEAKFP1,1LEAKFP2
PASS AFTER REPAIR,003468LEAKFP2
PASS AFTER REPAIR,003607 override
PASS AFTER REPAIR,0LEAKFP1,2LEAKFP2,1LEAKRB

It looks like SQL attempts to write one of the columns, has a problem that it detects when writing, then re-writes that data, appending the garbled text.

How would I detect such errors when they occur?

Am I writing to the database incorrectly?

---
Writing to the database:
For those wanting to know how I am writing to the database, I use this little piece of code that is executed hundreds of times a day:

string insert = "INSERT INTO Final_Check ([OP_ID], [Serial_Number], [Date_Time], [System_ID], [First_WT_Test], [Test_Result]) " + //, [WorkOrder_Number]) " +
       "VALUES (@NUM, @SERNO, @DATE2, @SYSID, @FIRST, @RESULT)";
using (SqlCommand cmd = new SqlCommand(insert, Connection)) {
  cmd.Parameters.Add("@NUM", SqlDbType.VarChar, 50).Value = objEmployee.Num;
  cmd.Parameters.Add("@SERNO", SqlDbType.Char, 20).Value = objCoil.SerialNumber;
  cmd.Parameters.Add("@DATE2", SqlDbType.DateTime, 0).Value = DateTime.Now.ToString();
  cmd.Parameters.Add("@SYSID", SqlDbType.VarChar, 50).Value = systemID;
  cmd.Parameters.Add("@RESULT", SqlDbType.VarChar, 255).Value = result;
  cmd.Parameters.Add("@FIRST", SqlDbType.Bit, 1).Value = (-1 < result.IndexOf(BarCode.PassFirstTime));
  try {
    cmd.Connection.Open();
    nRet = cmd.ExecuteNonQuery();
  } catch (SqlException err) {
    LogError(err);
  } finally {
    cmd.Connection.Close();
  }
}

Avoid Sears Home Improvement

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources