We help IT Professionals succeed at work.

Insert statement error in VB.Net code

Definit1
Definit1 asked
on
146 Views
Last Modified: 2013-11-05
I am trying to debug a Insert SQL statement in my VB.Net code that is trying to loop through two different database connections (Conn2 = Trades Mngmt db) and (ConnSett = EODSettlements). It seems to handle looping through the first SqlCommand (CmdExpDate) in the initial database (Trades Mngmt) fine but, when it gets to the second 'For Next' statement it seems to not be recognizing it is looping through a second SqlCommand (CmdExpDate2) hence forth a different database (EODSettlements). At the command line...
CmdExpDate2.ExecuteNonQuery()
My code triggers this error '
Violation of Primary key constraint 'PK_Commodity_Contract_Detail'. Cannot insert dupilcate key in object 'Commodity_Contract_Detail'. Statement has been terminated. it seems to me that when it gets to this command line, the program is apparently not recognizing that it is inserting values into a different database (EOD Settlements) from the intial database (Trades Mngmt)...henceforth the value duplication error.
Can someone please help me with a work around solution to this problem. The code was written by someone else and from my analysis got a little convoluted with the effort to insert the same values essentially into two different databases with the way the looping structure was created.
I have attached the code below.




Private Sub btnEntDate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEntDate.Click
        Dim Sql As String = "insert into Commodity_Contract_Detail(Commodity,Commodity_Month,Commodity_year,Contract_Type,Expiration_Date,ExchangeID) values (@Commodity,@Month,@Year,@Type,@ExpDate,@ExchangeID)"
        Dim ExpDateTrans As SqlTransaction
        Dim ExpDateTrans2 As SqlTransaction
        Dim Cn As New SqlConnection(MainForm.Conn2)
        Dim Cn2 As New SqlConnection(MainForm.ConnSett)
        Dim CmdExpDate As New SqlCommand(Sql, Cn)
        Dim CmdExpDate2 As New SqlCommand(Sql, Cn2)

        CmdExpDate.Parameters.Add("@Commodity", SqlDbType.VarChar)
        CmdExpDate.Parameters.Add("@Month", SqlDbType.VarChar)
        CmdExpDate.Parameters.Add("@year", SqlDbType.Int)
        CmdExpDate.Parameters.Add("@Type", SqlDbType.Char)
        CmdExpDate.Parameters.Add("@ExpDate", SqlDbType.SmallDateTime)
        CmdExpDate.Parameters.Add("@ExchangeID", SqlDbType.Int)

        CmdExpDate2.Parameters.Add("@Commodity", SqlDbType.VarChar)
        CmdExpDate2.Parameters.Add("@Month", SqlDbType.VarChar)
        CmdExpDate2.Parameters.Add("@year", SqlDbType.Int)
        CmdExpDate2.Parameters.Add("@Type", SqlDbType.Char)
        CmdExpDate2.Parameters.Add("@ExpDate", SqlDbType.SmallDateTime)
        CmdExpDate2.Parameters.Add("@ExchangeID", SqlDbType.Int)

        Try
            Cn.Open()
            Cn2.Open()
            ExpDateTrans = Cn.BeginTransaction
            ExpDateTrans2 = Cn2.BeginTransaction
            CmdExpDate.Transaction = ExpDateTrans
            CmdExpDate2.Transaction = ExpDateTrans2
            If Trade_Type = "F" Then
                CmdExpDate.Parameters(0).Value = Cmdty
                CmdExpDate.Parameters(1).Value = Mnth
                CmdExpDate.Parameters(2).Value = yr
                CmdExpDate.Parameters(3).Value = Trade_Type
                CmdExpDate.Parameters(4).Value = dtExpDate.Text
                CmdExpDate.Parameters(5).Value = Exchange_ID
                CmdExpDate.ExecuteNonQuery()

                CmdExpDate2.Parameters(0).Value = Cmdty
                CmdExpDate2.Parameters(1).Value = Mnth
                CmdExpDate2.Parameters(2).Value = yr
                CmdExpDate2.Parameters(3).Value = Trade_Type
                CmdExpDate2.Parameters(4).Value = dtExpDate.Text
                CmdExpDate2.Parameters(5).Value = Exchange_ID
                CmdExpDate2.ExecuteNonQuery()
            ElseIf Trade_Type = "C" Or Trade_Type = "P" Then
                Dim i As Integer
                For i = 1 To 2
                    CmdExpDate.Parameters(0).Value = Cmdty
                    CmdExpDate.Parameters(1).Value = Mnth
                    CmdExpDate.Parameters(2).Value = yr
                    CmdExpDate.Parameters(3).Value = Trade_Type
                    If i = 1 Then
                        CmdExpDate.Parameters(3).Value = "C"
                    ElseIf i = 2 Then
                        CmdExpDate.Parameters(3).Value = "P"
                    End If
                    CmdExpDate.Parameters(4).Value = dtExpDate.Text
                    CmdExpDate.Parameters(5).Value = Exchange_ID
                    CmdExpDate.ExecuteNonQuery()
                Next
                For i = 1 To 2
                    CmdExpDate2.Parameters(0).Value = Cmdty
                    CmdExpDate2.Parameters(1).Value = Mnth
                    CmdExpDate2.Parameters(2).Value = yr
                    CmdExpDate2.Parameters(3).Value = Trade_Type
                    If i = 1 Then
                        CmdExpDate2.Parameters(3).Value = "C"
                    Else
                        CmdExpDate2.Parameters(3).Value = "P"
                    End If
                    CmdExpDate2.Parameters(4).Value = dtExpDate.Text
                    CmdExpDate2.Parameters(5).Value = Exchange_ID
                    CmdExpDate2.ExecuteNonQuery()
                Next
            End If
            ExpDateTrans.Commit()
            ExpDateTrans2.Commit()
        Catch ex As Exception
            ExpDateTrans.Rollback()
            ExpDateTrans2.Rollback()
            MessageBox.Show(ex.Message)
        Finally
            Cn.Close()
        End Try

        RetStatus = True
        Me.Close()
    End Sub
Comment
Watch Question

Are the primary key fields the same on both Databases?

Author

Commented:
Yes
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
I don't know what connection pooling is. The primary fields are
Commodity
Commodity_Month
Commodity_year
Contract_Type

Author

Commented:
Did some reading as you know the point is to learn this stuff...lol! leave it to experts exchange to increase my intelligence quotient...lol! Well apparently connection pooling is on by default in VB.Net right? Well if your asking about any explicit references in the code there are none. I don't have any Pooling = True statements in it. But from my recent review of connection pooling methodologies, it looks like my predecessor did not help matters by opening the "Cn2" database connection so early in the code (before it was actually needed). So I retooled the code and is now working perfectly. This is what I did...
CmdoProg2...Thanks for leading me in the right direction...GOT IT!!!

    Private Sub btnEntDate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEntDate.Click
        Dim Sql As String = "insert into Commodity_Contract_Detail(Commodity,Commodity_Month,Commodity_year,Contract_Type,Expiration_Date,ExchangeID) values (@Commodity,@Month,@Year,@Type,@ExpDate,@ExchangeID)"
        Dim ExpDateTrans As SqlTransaction
        Dim ExpDateTrans2 As SqlTransaction
        Dim Cn As New SqlConnection(MainForm.Conn2)
        Dim Cn2 As New SqlConnection(MainForm.ConnSett)
        Dim CmdExpDate As New SqlCommand(Sql, Cn)
        Dim CmdExpDate2 As New SqlCommand(Sql, Cn2)

        CmdExpDate.Parameters.Add("@Commodity", SqlDbType.VarChar)
        CmdExpDate.Parameters.Add("@Month", SqlDbType.VarChar)
        CmdExpDate.Parameters.Add("@year", SqlDbType.Int)
        CmdExpDate.Parameters.Add("@Type", SqlDbType.Char)
        CmdExpDate.Parameters.Add("@ExpDate", SqlDbType.SmallDateTime)
        CmdExpDate.Parameters.Add("@ExchangeID", SqlDbType.Int)

        CmdExpDate2.Parameters.Add("@Commodity", SqlDbType.VarChar)
        CmdExpDate2.Parameters.Add("@Month", SqlDbType.VarChar)
        CmdExpDate2.Parameters.Add("@year", SqlDbType.Int)
        CmdExpDate2.Parameters.Add("@Type", SqlDbType.Char)
        CmdExpDate2.Parameters.Add("@ExpDate", SqlDbType.SmallDateTime)
        CmdExpDate2.Parameters.Add("@ExchangeID", SqlDbType.Int)

        Try
            Cn.Open()
            'Cn2.Open()
            ExpDateTrans = Cn.BeginTransaction
            'ExpDateTrans2 = Cn2.BeginTransaction
            CmdExpDate.Transaction = ExpDateTrans
            'CmdExpDate2.Transaction = ExpDateTrans2
            If Trade_Type = "F" Then
                CmdExpDate.Parameters(0).Value = Cmdty
                CmdExpDate.Parameters(1).Value = Mnth
                CmdExpDate.Parameters(2).Value = yr
                CmdExpDate.Parameters(3).Value = Trade_Type
                CmdExpDate.Parameters(4).Value = dtExpDate.Text
                CmdExpDate.Parameters(5).Value = Exchange_ID
                CmdExpDate.ExecuteNonQuery()

                'CmdExpDate2.Parameters(0).Value = Cmdty
                'CmdExpDate2.Parameters(1).Value = Mnth
                'CmdExpDate2.Parameters(2).Value = yr
                'CmdExpDate2.Parameters(3).Value = Trade_Type
                'CmdExpDate2.Parameters(4).Value = dtExpDate.Text
                'CmdExpDate2.Parameters(5).Value = Exchange_ID
                'CmdExpDate2.ExecuteNonQuery()
            ElseIf Trade_Type = "C" Or Trade_Type = "P" Then
                Dim i As Integer
                For i = 1 To 2
                    CmdExpDate.Parameters(0).Value = Cmdty
                    CmdExpDate.Parameters(1).Value = Mnth
                    CmdExpDate.Parameters(2).Value = yr
                    CmdExpDate.Parameters(3).Value = Trade_Type
                    If i = 1 Then
                        CmdExpDate.Parameters(3).Value = "C"
                    ElseIf i = 2 Then
                        CmdExpDate.Parameters(3).Value = "P"
                    End If
                    CmdExpDate.Parameters(4).Value = dtExpDate.Text
                    CmdExpDate.Parameters(5).Value = Exchange_ID
                    CmdExpDate.ExecuteNonQuery()
                Next
                'For i = 1 To 2
                'CmdExpDate2.Parameters(0).Value = Cmdty
                'CmdExpDate2.Parameters(1).Value = Mnth
                'CmdExpDate2.Parameters(2).Value = yr
                'CmdExpDate2.Parameters(3).Value = Trade_Type
                'If i = 1 Then
                'CmdExpDate2.Parameters(3).Value = "C"
                'Else
                'CmdExpDate2.Parameters(3).Value = "P"
                'End If
                'CmdExpDate2.Parameters(4).Value = dtExpDate.Text
                'CmdExpDate2.Parameters(5).Value = Exchange_ID
                'CmdExpDate2.ExecuteNonQuery()
                'Next
            End If
            ExpDateTrans.Commit()


            'Testing new code - Chris Jasabe - 8/22/2007
            Cn2.Open()
            ExpDateTrans2 = Cn2.BeginTransaction
            CmdExpDate2.Transaction = ExpDateTrans2


            If Trade_Type = "F" Then
                CmdExpDate2.Parameters(0).Value = Cmdty
                CmdExpDate2.Parameters(1).Value = Mnth
                CmdExpDate2.Parameters(2).Value = yr
                CmdExpDate2.Parameters(3).Value = Trade_Type
                CmdExpDate2.Parameters(4).Value = dtExpDate.Text
                CmdExpDate2.Parameters(5).Value = Exchange_ID
                CmdExpDate2.ExecuteNonQuery()

            ElseIf Trade_Type = "C" Or Trade_Type = "P" Then
                Dim i As Integer
                For i = 1 To 2
                    CmdExpDate2.Parameters(0).Value = Cmdty
                    CmdExpDate2.Parameters(1).Value = Mnth
                    CmdExpDate2.Parameters(2).Value = yr
                    CmdExpDate2.Parameters(3).Value = Trade_Type
                    If i = 1 Then
                        CmdExpDate2.Parameters(3).Value = "C"
                    Else
                        CmdExpDate2.Parameters(3).Value = "P"
                    End If
                    CmdExpDate2.Parameters(4).Value = dtExpDate.Text
                    CmdExpDate2.Parameters(5).Value = Exchange_ID
                    CmdExpDate2.ExecuteNonQuery()
                Next
            End If

            ExpDateTrans2.Commit()
        Catch ex As Exception
            ExpDateTrans.Rollback()
            ExpDateTrans2.Rollback()
            MessageBox.Show(ex.Message)
        Finally
            Cn.Close()
        End Try

        RetStatus = True
        Me.Close()
    End Sub
Connection pooling is where a SQL connection is reused between the SQL server and an application which stays around until the garbage collected.  Pooling is generally keyed by the database name or by settings which I don't think you would set.   I would run a test to verify the database in your loop...

   ' ***************************************
   ' Debug only to verify the Database or written to the event log or ...
   MessageBox.Show(Cn2.Database(), "Connection To")
   ' ***************************************
  CmdExpDate2.ExecuteNonQuery()

  Finally
    Cn.Close()
    Cn2.Close()
  End Try
Glad you got it.  Please ignore my last suggestion.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.