update database more than 1 rec ord (data redundancy)

[swathys]

hi,

i think there is a problem with my code below....it insert the data more than 1 record (data redundancy) in the server database....i think there is a problem at the place i highlight with red....
can any1 assist me on this....

If Not Update_ServerPOS() Then
Update_TempPOS()
End If

Public Function Update_ServerPOS() As Boolean
        Dim myConnection As SqlConnection
        Dim myCommand As SqlCommand
        Dim strSQL As String = ""
        Dim strSQL1 As String = ""
        Dim connStr As String
        Dim dr As SqlDataReader
        Dim cmdSerialNo, cmdPayment As SqlCommand
        Dim strSerialNo As String = "1"
        Dim drSerialNo As SqlDataReader
        Dim cmdTmp As SqlCommand
        Dim strPosID As String
        Dim strLoc As String
        Dim dr3 As SqlDataReader
        Dim myCommand1 As SqlCommand
        Try
            Update_ServerPOS = False
         
            myConnection = GetConnect("REMOTE")
            myConnection.Open()
          
            If myConnection.State = 0 Then
                myConnection.Open()
            End If
           
            ' Get LatestTransactionNo from Parameter table
            getLatestTransNo()
        
            ' Get SerialNo
            sSQL = "SELECT MAX(SerialNo) FROM PaymentDetails WHERE BoothID=" & _clsDBHelper.mQuotedStr(BoothNo)
            cmdSerialNo = POSconn.CreateCommand
            cmdSerialNo.CommandText = sSQL
            drSerialNo = cmdSerialNo.ExecuteReader
            drSerialNo.Read()
         
            If drSerialNo.HasRows Then
                ' Get SerialNo
                If Not drSerialNo.IsDBNull(0) Then ' old transaction exist
                    strSerialNo = drSerialNo(0) + 1
                Else ' new transaction
                    strSerialNo = 1
                End If
            End If
            drSerialNo.Close()
       
            CurrentTranxDateTime = Now
            CurrentTranxDate = System.DateTime.Now.ToString("dd-MMM-yyyy")
            CurrentTranxTime = System.DateTime.Now.ToString("hh:mm:ss tt")
          
            BoothNo = BoothNo
            StoreCity = StoreCity
            ostrTransactionNo = TransactionNo
            ostrSerialNo = strSerialNo
            oCurrentTranxDateTime = CurrentTranxDateTime
            oCurrentTranxDate = CurrentTranxDate
            oCurrentTranxTime = CurrentTranxTime
            oPOSTransCode = POSTransCode
            oSESSION_AGENCY_CODE = SESSION_AGENCY_CODE
          

            strSQL1 = "select POSID, BoothLocation from tblBoothMapping where BoothID = '" & BoothNo & "'"
       
            myCommand1 = New SqlCommand(strSQL1, myConnection)
            dr3 = myCommand1.ExecuteReader()
            If dr3.Read Then
                If Not dr3.IsDBNull(0) Then
                    strPosID = dr3("POSID")
                    strLoc = dr3("BoothLocation")
                    End If
            End If
            dr3.Close()
     
strSQL = "INSERT INTO dbo.PaymentDetails ([BoothID],[BoothLocation],[TransactionNo],[SerialNo],[TransactionDateTime],[TransactionDate],[TransactionTime],[TransactionCode],[AgencyCode],[AgencyName],[AccountNo],[BillNo],[BillAmount],[PaidAmount],[Remark]) " _
& "VALUES ('" & strPosID & "','" & strLoc & "'," & TransactionNo & "," & strSerialNo & ",'" & CurrentTranxDateTime & "','" & CurrentTranxDate & "','" & CurrentTranxTime & "','" & POSTransCode & "','" & SESSION_AGENCY_CODE & "','" & SESSION_AGENCY_NAME & "','" & SESSION_AGENCY_ACCNO & "','" & SESSION_AGENCY_BILLNO & "','" & SESSION_AGENCY_BILLAMO & "','" & PaymentToPOS & "','" & BoothNo & strLoc & "')"
 '& "SELECT DISTINCT [BoothID],[BoothLocation]," _
 '& "[TransactionNo],[SerialNo],[TransactionDateTime], " _
 '& "[TransactionDate],[TransactionTime],[TransactionCode]," _
 '& "[AgencyCode],[AgencyName],[AccountNo],[BillNo]," _
 '& "[BillAmount], [PaidAmount], [Remark]" _
 '& "from dbo.PaymentDetails " _
'& "WHERE BoothID= '" & BoothNo & "' ORDER BY TransactionNo ASC"
        
myCommand = New SqlCommand(strSQL, myConnection)

If myCommand.ExecuteNonQuery > 0 Then
Update_ServerPOS = True
End If
          
myConnection.Close()
           
Catch ex As Exception
WriteToLogFile("modGeneral|Update_ServerPOS()| " & ex.Message)
End Try

    End Function

[bLuEmEzzy]

use .executescalar

If mycommand.ExecuteScalar > 1 Then 
'Update
End If

[swathys]

hi,

its still the same even i modify the code...i think there is something wrong with my code....PLEASE HELP ME !!!!!!!!

If Not Update_ServerPOS() Then
      Update_TempPOS()
      End If

if Update_ServerPOS()(remote server) is fail it should be able to update at Update_TempPOS()(local server)

[bLuEmEzzy]

.executescalar does not add same record...

What's with this??

If Not Update_ServerPOS() Then
Update_TempPOS()
End If

Dim ReturnValue As Integer
            ReturnValue = mycommand.executescalar

            If ReturnValue = 1 Then '  existing
                'your code
            ElseIf ReturnValue > 1 Then
                'your code
            Else
            End If

[swathys]

hi,

i have no idea how to modify your code....please modify it with my code and guide me where to modify...tq

[bLuEmEzzy]

instead of using this one

If myCommand.ExecuteNonQuery > 0 Then
Update_ServerPOS = True
End If

use this... i already encountered this problem...
i hope it will help you...

If mycommand.ExecuteScalar > 0 Then
         Update_ServerPOS = True
         End If

[swathys]

i'm stil getting the same error....it goes back to

If Not Update_ServerPOS() Then
      Update_TempPOS()
      End If

and save again the record....what c an i do to avoid this

[bLuEmEzzy]

i do not know what's the alternative for it... i'll study your code... God bless u...

[swathys]

hi,
do you know on how to stop the code from save more than once in database..
TQ

[bLuEmEzzy]

Yeah,,, im using .ExecuteScalar instead of .ExecuteNonQuery... i really do not know what's the problem of .executeScalar in your code...