Violation of primary key issue

[firoz.raj]

Can anyone tell me ???.why i am getting the following issue ???.
Violation of PRIMARY KEY constraint 'PK_CNK_dailysales'. Cannot insert duplicate key in object 'CNK_dailysales'.
here is the following code what i have written

Public Sub InsertDailySales(rs As ADODB.Recordset)
Dim con As ADODB.Connection
Dim tempdate As Date
Dim strMsg$, StrMsgSales$
Dim TotalValue#
Dim totalQty%
If Not OpenConnection(con) Then
   MsgBox ("Connection is not open ")
   Set con = Nothing
   Exit Sub
End If
On Error GoTo errhnd
Dim TempSerial As String
TempSerial = ""
 rs.MoveFirst
 Do While Not rs.EOF
  If TempSerial <> rs!saleserial Then
     TempSerial = rs!saleserial
    If TempSerial <> "" Then
       con.Execute "Insert into interface.dbo.CNK_dailysales(Storeid,Saleserial,SaleInternalDate,dumpdate,nettotal) values('" & rs!StoreId & "','" & rs!saleserial & "','" & Format(rs!saleinternaldate, "YYYY-MM-DD") & "','" & Format(Now(), "YYYY-MM-DD HH:MM:SS") & "','" & "0" & "')"
    End If
End If
rs.MoveNext
Loop
strMsg = strMsg & rs!StoreId & " Total Sales" & vbTab & CDbl(TotalValue) & vbTab & " Total Qty" & vbTab & CInt(totalQty) & vbNewLine
            StrMsgSales = StrMsgSales & rs!StoreId & " Total Sales" & vbTab & CDbl(TotalValue) & vbTab & " Total Qty" & vbTab & CInt(totalQty) & vbNewLine
            ' Updating the Generated Files Date time Informations
            con.Execute "Update CNK_Stores set LastSaleDate='" & Format(DateAdd("d", 1, tempdate), "YYYY-MM-DD") & "' where INF_CODE='" & rs!StoreId & "'"
errhnd:
  Debug.Print Err.Description
'  Err.Clear
'  On Error GoTo 0
    
    
End Sub

[HanneSThEGreaT]

Cannot insert duplicate key in object 'CNK_dailysales'.

The error is quite self explanitory and very obvious. It means that you are trying to insert duplicate values inside a field in which you cannot

[firoz.raj]

The error is quite self explanitory and very obvious. It means that you are trying to insert duplicate values inside a field in which you cannot

NoOOOOOOOOOOOO . I Think problem in after finish loop .the Do-while loop before that line forces rs to reach Eof .so let me know .How Should i put the update Statement Inside the Loop. so that it updates together with insert .

Public Sub InsertDailySales(ByRef con As ADODB.Connection, ByRef rs As ADODB.Recordset)
Dim tempdate As Date
Dim strMsg$, StrMsgSales$
Dim TotalValue#
Dim totalQty%
On Error GoTo errhnd
Dim TempSerial As String
TempSerial = ""
 rs.MoveFirst
 Do While Not rs.EOF
  If TempSerial <> rs!saleserial Then
     TempSerial = rs!saleserial
    If TempSerial <> "" Then
       con.Execute "Insert into interface.dbo.CNK_dailysales(Storeid,Saleserial,SaleInternalDate,dumpdate,nettotal) values('" & rs!StoreId & "','" & rs!saleserial & "','" & Format(rs!saleinternaldate, "YYYY-MM-DD") & "','" & Format(Now(), "YYYY-MM-DD HH:MM:SS") & "','" & "0" & "')"
    End If
End If
rs.MoveNext
Loop
strMsg = strMsg & rs!StoreId & " Total Sales" & vbTab & CDbl(TotalValue) & vbTab & " Total Qty" & vbTab & CInt(totalQty) & vbNewLine
            StrMsgSales = StrMsgSales & rs!StoreId & " Total Sales" & vbTab & CDbl(TotalValue) & vbTab & " Total Qty" & vbTab & CInt(totalQty) & vbNewLine
            ' Updating the Generated Files Date time Informations
            con.Execute "Update CNK_Stores set LastSaleDate='" & Format(DateAdd("d", 1, tempdate), "YYYY-MM-DD") & "' where INF_CODE='" & rs!StoreId & "'"
errhnd:
  Debug.Print Err.Description
'    Err.Clear
'    On Error GoTo 0
End Sub

[HanneSThEGreaT]

NoOOOOOOOOOOOO . I Think problem in after finish loop .the Do-while loop before that line forces rs to reach Eof .so let me know .How Should i put the update Statement Inside the Loop. so that it updates together with insert .

If you know where your problem is why don't you fix it then?? Sheesh, just trying to help, then I get your attitude. You are not listening to my advice!!!!!! You are trying to store a duplicate value in your PK field which is not allowed. Yes, it may be in your loop. Is that clear now??

Fix it yourself, I'm tired of spoonfeeding you, and you never doing any effort yourself.

Hannes

[firoz.raj]

Sorry Hannes actually i need to implement the rsstores loop logic in my code .i need to loop through rsstores .actually i need to implement for the above code .each brands has so many stores .so we need to loop the stores at the following way .and i need to implement in insertdaily sales method .so let me know please .and oh yes sorry for any miscommunication .
CnLocal.BeginTrans
Do While Not RsStores.EOF
tempdate = RsStores!LASTSALEDATE
For tempdate = tempdate To DateAdd("d", -1, Date)
.....
....

Private Function GenerateSalesFromInfinity_Dump()
Dim rsSales                 As New ADODB.Recordset
Dim RsStores                As New ADODB.Recordset
Dim strSQL                  As String
Dim strBarCode              As String
Dim strPrice                As String
Dim intCountQty             As Integer
Dim strOutput               As String
Dim TotalQty                As Integer
Dim TotalValue              As Double
Dim tempdate                As Date
Dim SaleSerial              As String
    strMsg = strMsg & "Connecting To Local server" & vbNewLine
    CnLocal.ConnectionString = CnsLocal.ConnectionString
    CnLocal.CommandTimeout = 0
    CnLocal.ConnectionTimeout = 0
    CnLocal.Open
    strMsg = strMsg & "Connected To Local server" & vbNewLine
    
    strMsg = strMsg & "Connecting To Infinity server" & vbNewLine
    CnInfinity.ConnectionString = CnsInfinity.ConnectionString
    CnInfinity.CommandTimeout = 0
    CnInfinity.ConnectionTimeout = 0
    CnInfinity.Open
    strMsg = strMsg & "Connected To Infinity server" & vbNewLine
    
RsStores.Open "select INF_CODE as StoreID,CEL_CODE,RSG_CODE ,LASTSALEDATE,LASTSALEENDTIME " & vbNewLine & _
             " from CEL_STORES WHERE Closed=0 and InfFlag=1 ", CnLocal, adOpenStatic, adLockOptimistic
Open OutPutPath & "\Sales.txt" For Output As #1
        CnLocal.BeginTrans
Do While Not RsStores.EOF
        tempdate = RsStores!LASTSALEDATE
    For tempdate = tempdate To DateAdd("d", -1, Date)
strSQL = " Select M.SaleInternalDate, M.StoreID, M.SaleSerial, ReturnFlag, Qty, " & vbCrLf & _
         " (CASE RETURNFLAG   WHEN 0 THEN  Case M.DetailTotal - M.DetailDiscount   when 0 then   ((D.Price - D.Discount) * D.qty - D.ManualDiscount - D.PromotionDiscount )  " & vbCrLf & _
         " else   ((D.Price - D.Discount) * D.qty - D.ManualDiscount - D.PromotionDiscount -      ((M.totalDiscount - M.DetailDiscount)* ((D.Price - D.Discount) * D.qty - D.ManualDiscount - D.PromotionDiscount)  / (M.DetailTotal - M.DetailDiscount)))  " & vbCrLf & _
         " end  ELSE   Case M.DetailTotal - M.DetailDiscount   when 0 then   -((D.Price - D.Discount) * D.qty - D.ManualDiscount - D.PromotionDiscount )   else   -((D.Price - D.Discount) * D.qty - D.ManualDiscount - D.PromotionDiscount -  " & vbCrLf & _
         " ((M.totalDiscount - M.DetailDiscount)* ((D.Price - D.Discount) * D.qty - D.ManualDiscount - D.PromotionDiscount)  / (M.DetailTotal - M.DetailDiscount) ))  " & vbCrLf & _
         " end   END) / Qty As DiscPrice,d.pRICE, M.NetTotal,  " & vbCrLf & _
         " isnull((select top 1 b.Barcode from ic_itembarcode as b where  " & vbCrLf & _
         " D.ItemID = B.ItemID And D.Serial1 = B.Serial1 And D.Serial2 = B.Serial2),(select top 1 b.Barcode from ic_itembarcode as b where  " & vbCrLf & _
         " (CASE D.ItemID WHEN 'CELCSEBASTIEN020'THEN 'CELCSEBASTIEN002' WHEN 'CELCSEBASTIEN010' THEN 'CELCSEBASTIEN001' ELSE D.ITEMID END) = B.ItemID))  as Barcode  " & vbCrLf & _
         " from interface.dbo.CEL_DAILYSALES as DU right outer join  " & vbCrLf & _
         " PS_SalesMaster as M  " & vbCrLf & _
         " on DU.StoreId=M.Storeid and Du.SaleSerial=M.SaleSerial  " & vbCrLf & _
         " Inner Join      PS_SalesDetail As D  " & vbCrLf & _
         " ON M.SaleSerial = D.SaleSerial  " & vbCrLf & _
         " Where M.Storeid='" & RsStores!StoreId & "' AND M.saleinternaldate <='" & Format(tempdate, "YYYY-MM-DD") & "' and m.SaleinternalDate>'2005-09-18'  " & vbCrLf & _
         " ANd M.Finished = 1 And D.Error = 0 And M.Deleted = 0 And M.Parked = 0  " & vbCrLf & _
         " and Du.Storeid is null  " & vbCrLf & _
         " Order By M.StoreID,M.SaleInternalDate, M.SaleSerial, B.Barcode, ReturnFlag "
        
    If rsSales.State = 1 Then
        rsSales.Close
    End If
    rsSales.Open strSQL, CnInfinity, adOpenStatic, adLockOptimistic
    If Not (rsSales Is Nothing) Then
        If Not (rsSales.BOF And rsSales.EOF) Then
            TotalQty = 0
            TotalValue = 0
            'Set Date
            ' ********** Modification **********
            ' *** If some sales lines missing for day before tempdate then it will include
            ' as the sales for tempdate.
            If rsSales!SaleInternaldate <> tempdate Then
                strMsg = strMsg & " Previous day sales is included in this date " & Format(rsSales!SaleInternaldate, "YYYY-MM-DD") & vbNewLine
                StrMsgSales = StrMsgSales & " Previous day sales is included in this date " & vbNewLine
            End If
            Do Until rsSales.EOF
                ' Return Flag =1 Return else Sales
                TotalQty = TotalQty + (IIf(rsSales!returnFlag = 1, -1 * rsSales!Qty, rsSales!Qty))
                TotalValue = TotalValue + (IIf(rsSales!returnFlag = 1, -1 * rsSales!Qty * Abs(rsSales!DiscPrice), rsSales!DiscPrice * rsSales!Qty))
                strPrice = Abs(rsSales![DiscPrice]) & vbNullString
                strPrice = Format(strPrice, "##########")
                If IsNull(rsSales![Barcode]) Then
                    strMsg = strMsg & "Null Barcode - Sales Serial = " & rsSales!SaleSerial & " Store Id:" & RsStores!StoreId & "- Date : " & Format(tempdate, "DD-MM-YYYY") & vbNewLine
                    StrMsgSales = StrMsgSales & "Null Barcode - Sales Serial = " & rsSales!SaleSerial & "Store Id:" & RsStores!StoreId & "- Date : " & Format(tempdate, "DD-MM-YYYY") & vbNewLine
                    Close #1
                    Kill OutPutPath & "\Sales.txt"
                    CnLocal.RollbackTrans
                    GoTo ErrorTrap
                End If
                
                strBarCode = rsSales![Barcode] & vbNullString
                strBarCode = Replace(strBarCode, "CEL", "", , , vbTextCompare)
                strOutput = strBarCode & vbTab & Format(IIf(rsSales!returnFlag = 1, -rsSales!Qty, rsSales!Qty), "###") & vbTab & Format(tempdate, "DD/MM/YYYY") & vbTab & RsStores!CEL_Code & vbTab & Format(Abs(rsSales!Price), "######.00") & vbTab & Format(Abs(rsSales!DiscPrice), "######.00")
                Print #1, strOutput
                If SaleSerial <> rsSales!SaleSerial Then
                    CnLocal.Execute "Insert into CEL_DailySales(Storeid,Saleserial,saleinternaldate,dumpdate,nettotal) values('" & rsSales!StoreId & "','" & rsSales!SaleSerial & "','" & Format(rsSales!SaleInternaldate, "YYYY-MM-DD") & "','" & Format(Now(), "YYYY-MM-DD HH:MM:SS") & "','" & rsSales!NetTotal & "')"
                    SaleSerial = rsSales!SaleSerial
                End If
                rsSales.MoveNext
            Loop
            strMsg = strMsg & RsStores!StoreId & " Total Sales" & vbTab & CDbl(TotalValue) & vbTab & " Total Qty" & vbTab & CInt(TotalQty) & vbNewLine
            StrMsgSales = StrMsgSales & RsStores!StoreId & " Total Sales" & vbTab & CDbl(TotalValue) & vbTab & " Total Qty" & vbTab & CInt(TotalQty) & vbNewLine
            ' Updating the Generated Files Date time Informations
            CnLocal.Execute "Update CEL_Stores set LastSaleDate='" & Format(DateAdd("d", 1, tempdate), "YYYY-MM-DD") & "' where INF_CODE='" & RsStores!StoreId & "'"
            ' End of the Date time Updation
        Else
            strMsg = strMsg & "Missing Sales for the store " & RsStores!StoreId & " Date : " & Format(tempdate, "YYYY-MM-DD")
            StrMsgMissing = StrMsgMissing & "Missing store : " & RsStores!StoreId & " Date : " & Format(tempdate, "YYYY-MM-DD")
            GoTo LblNextStore
        End If
    Else
        strMsg = strMsg & "Missing Sales for the store " & RsStores!StoreId & " Date : " & Format(tempdate, "YYYY-MM-DD")
        StrMsgMissing = StrMsgMissing & "Missing store : " & RsStores!StoreId & " Date : " & Format(tempdate, "YYYY-MM-DD")
        GoTo LblNextStore
    End If
LBLNextDate:
    Next tempdate
LblNextStore:
    RsStores.MoveNext
Loop
Close #1
CnLocal.CommitTrans
RsStores.Close
Set RsStores = Nothing
CnInfinity.Close
Set CnInfinity = Nothing
CnLocal.Close
Set CnLocal = Nothing
ErrorTrap:
    If Err.Number <> 0 Then
        strMsg = strMsg & "Error In generating file " & Err.Description & vbNewLine
        Resume Next
    Else
        strMsg = strMsg & "Sales file generated" & vbNewLine
    End If
End Function

[Killa69]

Simple DIY method.....

either
(a) Temporarily comment out the error handler.
or
(b) In the VB6 environment select options from the tools menu and on the general tab select 'Break On All Errors'


Run the code from the VB6 environment. (F5)

When the error is encountered you will get a message box telling you the error and then click debug.

The line with the error will now be highlighted. Much better than trying to guess where the error actually is.

You can also use the immediate window to display the content of any variables (like strSQL)

If SQL server is telling you that it cannot insert a duplicate key then it is a fair guess that you are trying to add a duplicate value to a unique field.

[Leon Kennedy]

In the database tables, look at the fields you are trying to insert. Look at your primary key field. It should state that duplicate values aren't allowed. Then step through the code like killa suggested, and compare what you are trying to insert in the primary key field with values that already exist. You'll solve the problem in no time.

[dglienna]

It's a design problem most likely.

[HanneSThEGreaT]

It's a design problem most likely.

How do you get that it's a design problem?

[dglienna]

Trying to insert DUPLICATE KEYS into any production system.

[HanneSThEGreaT]

That is not what I meant David. I meant that the OP 's code is doing the insertion of the duplicate data, so the PK field is set up correctly at design time, if it produces an error.......