data redundancy

• • Reply

  10 years ago

  by swathys

  swathy s

  kl, Malaysia

  Joined 10 years ago

  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....

  [CODE] If Not UpdateServerPOS() Then UpdateTempPOS() End If[/CODE]

  [CODE]Public Function UpdateServerPOS() 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 UpdateServerPOS = 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 & "','" & SESSIONAGENCYCODE & "','" & SESSIONAGENCYNAME & "','" & SESSIONAGENCYACCNO & "','" & SESSIONAGENCYBILLNO & "','" & SESSIONAGENCYBILLAMO & "','" & PaymentToPOS & "','" & BoothNo & strLoc & "')" '& "SELECT DISTINCT [BoothID],[BoothLocation]," _ '& "[TransactionNo],[SerialNo],[TransactionDateTime], " _ '& "[TransactionDate],[TransactionTime],[TransactionCode]," _ '& "[AgencyCode],[AgencyName

  Report abuse