MSSQL 2008 Update Statement Fails

[Tobyy]

Hello,
I have this creepy issue with my VB6 APP.
I have discovered that sometimes, the update statements inside my VB6B Code like the following does not work, despite the code been perfectly fine. I think it has to do with a LOCK property on MSSQL when you Select and then Update:

MyConn.Execute ("insert into Productledger values ('" & Replace(lblname.Caption, "'", "''") & "','" & UCase(txtcustomerid.Text) & "','" & Replace(lbltype.Caption, "'", "''") & "','" & Year(Date) & "','" & Date & "','" & Daba & "','" & Replace(NewRef, "'", "''") & "')")

BalPac = Round(CurrentBalance(txtproduct.Text), 2)
BalPac2 = Round(Val(BalPac) - Val(Text2), 2)
MyConn.Execute ("Update tblproduct set BalP1 = '" & BalPac2 & "' where Productid = '" & txtproduct.Text & "'")

The Procedure CurrentBalance has this code :

Function CurrentBalance(ArgAcctb As String)
  Dim Rs As New ADODB.Recordset
  Dim strb As String
  
  Set Rs = MyConn.Execute("select * from tblproduct where Productid ='" & ArgAcctb & "' and Status <> 'DELETED'")
  If Not Rs.EOF Then
    strb = Rs("BalP1")
    CurrentBalance = Round(strb, 2)
  End If

  If CurrentBalance = "" Then
     CurrentBalance = 0
  End If
End Function

Most times it works and a very few times it doesn't. There no errors at all but the Update would not be done at this few times and i think it has to do with the LOCK thing.

This issue has been giving me headache and please i need help on how best to handle this, without having my Update statements fail at anytime again, no matter how many users use the software at the same time over the LAN.

I currently use MSSQL Server 2008