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

[FunkyDexter]

Two obvious possibilities present themselves:-
1. The product id you're using doesn't exist in tblproduct
2. Val(Text2) is zero. This would result in BalP1 being set to its existing value.

If neither of those is true then you probably should walk through the code and look at the sql statement that is actually built by this line of code:-

MyConn.Execute ("Update tblproduct set BalP1 = '" & BalPac2 & "' where Productid = '" & txtproduct.Text & "'")

Is it producing what you'd expect.

[Tobyy]

Two obvious possibilities present themselves:-
1. The product id you're using doesn't exist in tblproduct
2. Val(Text2) is zero. This would result in BalP1 being set to its existing value.

If neither of those is true then you probably should walk through the code and look at the sql statement that is actually built by this line of code:-

MyConn.Execute ("Update tblproduct set BalP1 = '" & BalPac2 & "' where Productid = '" & txtproduct.Text & "'")

Is it producing what you'd expect.

Thanks for your response.

I am sorry if my question is confusing.

None of the possibilities you mentioned above is true and the code is working perfectly fine. I think it has to do with SQL Server Lock properties or the likes. The code works perfectly fine, but sometimes and just randomly i notice an update statement would just skip or not just have any effect. But if i run same code again, it works.....I read about it and i think its about the SQL locking a row when i first select and then before i update.

I need help on how to make sure my UPDATE statements does not fail again, by handling the LOCK thing better.

[OptionBase1]

The code may function, but I wouldn't go so far as to say the code is perfectly fine, but that's another discussions I guess.

I'm assuming you've seen this or are familiar with the concepts presented: https://www.microsoftpressstore.com/...33327&seqNum=3

My only suggestion is to throw in an "Rs.Close" at the tail end of your CurrentBalance function. If the problem is that, as currently written, the Select is locking the row and that lock isn't consistently freed by the time the Update is executed, explicitly closing the recordset may help.

Good luck.

[FunkyDexter]

the code is working perfectly fine

Nope. Trust me, the problem is not is sql server, it's in your code, we just don't know where yet

A quick word on SQL Server's locking behaviour: if a record is locked, it won't silently prevent an update from happening. Instead the command will wait for a predetermined length of time (this is the CommandTimeOut) for the record to become unlocked. If the record is unlocked in that time the update will be applied. If it doesn't become unlocked in that time an exception will be thrown which you should catch in your code and deal with appropriately.

So that raises the next possibility: Are you swallowing errors in your code? It's a long time since I worked with VB6 but I think that would look like OnError Resume Next (one of our resident 6ers can correct that for me if I'm wrong). Make sure you're not swallowing errors.

Other than that I'll repeat my first suggestion: walk through your code line by line and check exactly what it's doing at each step. In particular Look at the sql statements that are being generated. Are they all what you'd expect them to be?

[GaryMazzone]

I would do as Funky suggest. Put a break point in the code. when you get to each SQL statement to be executed copy the full code and try it in SQL server SSMS. Did it work? Move to the next line

[Zvoni]

The only things i see are:
1) "CurrentBalance" has no return-Datatype
2) Inside CurrentBalance you grab the Field "BalP1" and assign it to a String-Variable, you then throw through a round-function. --> Implicit Datatype-Casting.
3) You check against EOF instead of Recordcount = 1
4) You check CurrentBalance against emptystring, if yes assigning 0 to it.
5) If i read it correctly, your ProductID (Primary Key?) is a string?

What we do not see, if there is an OERN in play somewhere.
There is probably even more.
That's just a first glance right now.

EDIT: Something else: You use the Execute-Method of the Connection-Object for your SELECT-Query
While it's perfectly legal to use it to return Recordsets, IIRC it's considered bad practice.
Use the Recordset's Open-Method instead