Violation of primary key issue
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
Code:
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
Re: Violation of primary key issue
Quote:
Originally Posted by
firoz.raj
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
Re: Violation of primary key issue
Quote:
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 .
Code:
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
:(
Re: Violation of primary key issue
Quote:
Originally Posted by
firoz.raj
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
Re: Violation of primary key issue
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)
.....
....
Code:
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
Re: Violation of primary key issue
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.
Re: Violation of primary key issue
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.
Re: Violation of primary key issue
It's a design problem most likely.
Re: Violation of primary key issue
Quote:
Originally Posted by
dglienna
It's a design problem most likely.
How do you get that it's a design problem? :confused:
Re: Violation of primary key issue
Trying to insert DUPLICATE KEYS into any production system.
Re: Violation of primary key issue
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.......
