locked
SQL Express - Visual C# - When error exception is caught it effects all records after that RRS feed

  • Question

  • When I run this program I get some unexpected results. I have a simple database (myDataBase) with one table (myTable) that has two columns (myColumn1 and myColumn2). myColumn1 is set as a primary key.

     

    The expected result would be that John-Tina-Joel-Ian would get wrote to myTable and that John (three) and John (five) would generate error message for duplicate key.

     

    What happens is that John-Tina get wrote to the database and the next four generate duplicate key error message.

     

    Could someone tell me what could be going wrong. Also, it doesn't matter what type of error - I could have done some thing with the field size being two large on the third item and it would have generated the same error message for all the records that follow that as well.

     

    using System;

    using System.Collections.Generic;

    using System.ComponentModel;

    using System.Data;

    using System.Drawing;

    using System.Linq;

    using System.Text;

    using System.Windows.Forms;

    using System.Data.SqlClient;

    namespace MyTestSql

    {

    public partial class Form1 : Form

    {

    public Form1()

    {

    InitializeComponent();

    }

    private void button1_Click(object sender, EventArgs e)

    {

    #region DefineVariables

    string[] s1 = new stringDevil { "John", "Tina", "John", "Joel", "John", "Ian" };

    string[] s2 = new stringDevil { "one", "two", "three", "four", "five", "six" };

    string cs = "Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Data\\myDataBase.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";

    string strSelect = "Select * from myTable";

    DataSet tcrDS = new DataSet();

    SqlDataAdapter tcrAdapter;

    SqlCommandBuilder tcrCommandBuilder;

    DataTable tcrDT = new DataTable();

    #endregion

    //

    #region LoopingThroughEachPerson

    for (int iArea = 0; iArea < 6;iArea++)

    {

    //counter += 1; //increment every time we successfully read a record

    //

    #region AddANewRecord

    try

    {

    using (SqlConnection tcrConn = new SqlConnection(cs))

    {

    tcrConn.Open();

    tcrAdapter = new SqlDataAdapter(strSelect, tcrConn);

    tcrCommandBuilder = new SqlCommandBuilder(tcrAdapter);

    tcrAdapter.Fill(tcrDS, "myTable");

    tcrDT = tcrDS.Tables["myTable"];

    DataRow tcrRow = tcrDT.NewRow();

    tcrRow["myColumn1"] = s1[iArea];

    tcrRow["myColumn2"] = s2[iArea];

    tcrDS.Tables["myTable"].Rows.Add(tcrRow);

    tcrCommandBuilder.GetUpdateCommand();

    tcrAdapter.Update(tcrDS, "myTable");

    tcrConn.Close();

    }

    }

    catch (SqlException ex)

    {

    //Log the exception

    string errorMessage;

    if (ex.Number.Equals(2627))

    {

    errorMessage = "Error Number: " + ex.Number.ToString() + "\n" +

    "Error Message: " + ex.Message + "\n" +

    s1[iArea] + "\n" +

    s2[iArea];

    MessageBox.Show(errorMessage);

    }

    else

    {

    errorMessage = "Error Number: " + ex.Number.ToString() + "\n" +

    "Error Message: " + ex.Message + "\n" +

    s1[iArea] + "\n" +

    s2[iArea];

    MessageBox.Show(errorMessage);

    }

    }

    #endregion //AddANewRecord

    }

    #endregion //LoopingThroughEachPerson

    }

    }

    }

     

    Wednesday, October 31, 2007 2:16 PM

All replies

  • I tried taking out the (using SqlConnection ....) statement and replacing with SqlConnection tcrConn = new .....

     

    but same result.

     

    I have now added this to my Catch routine -- tcrConn.Close() and then I added tcrConn.Dispose() because my if statement was showing that tcrConn was not null.

     

    catch (SqlException ex)

    {

    //Log the exception

    string errorMessage;

    if (ex.Number.Equals(2627))

    {

    errorMessage = "Error Number: " + ex.Number.ToString() + "\n" +

    "Error Message: " + ex.Message + "\n" +

    s1[iArea] + "\n" +

    s2[iArea];

    MessageBox.Show(errorMessage);

    tcrConn.Close();                                               ***** Inside the catch - I have closed the tcrConn

    Application.DoEvents();

    tcrConn.Dispose();

    Application.DoEvents();

    if (null != tcrConn)                                            **** But when I check that it is closed

    {

    MessageBox.Show("tcrConn not null");              ****  I find that it is not null

    }

    }

     

    Does anyone have any ideas why the SqlConnection tcrConn does not close and is this leading to the result where all of the records that are read after the exception error is thrown act as if they had the same error.

    Wednesday, October 31, 2007 6:28 PM
  • Deleted
    Wednesday, October 31, 2007 8:42 PM