VB6 + MySql: Row cannot be located for updating.

[mastertorr]

When trying to update a table i get the following error on the .update command:
Row cannot be located for updating. Some values may have been changed since it was last read.


I've tried changing the option in the connection string to option=2;....and it didn't work. I also tried checking "row update" checkbox under the connection it self (under datasources) as others have suggested, but no luck there either.

What are some things i can do/check to resolve this issue??

Thanks

 

[r937]

this is not a mysql question

r937.com | rudy.ca

 

[mastertorr]

Hows this not an issue with mysql? what else would it be...its updating a record in the table from a mysql database

 

[johnwm]

It looks like your VB part is faulty - you should be in forum222. Try reading faq222-2244 to learn how these forums work, and how to get the best from them. Read the FAQ carefully and take note of its contents before rephrasing your question and posting it in forum222

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints

 

[mastertorr]

My apologies....thanks for your help/direction

 

[sbotu]

Hi I am new to this site but I am experiencing a similar problem on a VB 6 project that has been running for a few years. I only started getting this row update error when I upgraded Mysql from 4.0.23 to 4.1.22 - which tells me the problem must lie with how I have set up Mysql.
I have two other separate user servers - one in SA and one in the UK and both are using Mysql 4.1.20. The other main difference is that these are Linux whereas my development computer is using Mysql for windows XP. The same code is running on all three servers yet it is only on mine where this row update error occurs. If the cause is in the code then why is the problem not happening in all three places?

 

[johnwm]

Show us your MySQL update statement

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints

 

[sbotu]

I have since changed all my float fields to double - which seemed to resolve the problem - but then it came back - but only for specific records. i have tried to find what is different about the records that won't save but cannot see any. Note that this is not the only table where I am getting this problem. I rolled back Mysql to version 4.0 and now my problem has gone away. Weird. Anyway - here is my piece of update code :

UpdatePSet.Open "Select * from payments where data_ID = " & DataID, Conn, adOpenDynamic, adLockOptimistic
If DataID = -1 Then

NewRec = True
UpdatePSet.AddNew
DataID = GetLastDataID("Select max(data_id) as last from payments")
PaymentObject.PaymentNumber = GetLastDataID("Select max(payment_Number) as Last from payments where company = " & PaymentObject.Company & " and payment_type = '" & PaymentObject.PaymentType & "'")

End If



'update table
UpdatePSet("data_id") = DataID
UpdatePSet("payment_Number") = PaymentObject.PaymentNumber
UpdatePSet("company") = PaymentObject.Company
If PaymentObject.Account <> -1 Then
UpdatePSet("account") = PaymentObject.Account
Else
PaymentObject.Account = UpdatePSet("account")
End If
UpdatePSet("date") = PaymentObject.PaymentDate
UpdatePSet("payment_type") = PaymentObject.PaymentType
UpdatePSet("trx_type") = PaymentObject.TrxType
If PaymentObject.OwnerID <> -1 Then
UpdatePSet("owner") = PaymentObject.OwnerID
Else
If PaymentObject.PaymentType = "Dividend Pmt" Then
PaymentObject.OwnerID = UpdatePSet("owner")
End If
End If
If PaymentObject.ProjectID <> -1 Then
UpdatePSet("project_id") = PaymentObject.ProjectID
End If
If PaymentObject.ContraOwner <> 0 Then
UpdatePSet("Contra_owner") = PaymentObject.ContraOwner
End If
If PaymentObject.Department <> 0 Then
UpdatePSet("department") = PaymentObject.Department
End If
UpdatePSet("amount") = PaymentObject.TotalAmount
UpdatePSet("gross_salary") = IIf(PaymentObject.PaymentType = "Salary", PaymentObject.GrossSalary, 0)
UpdatePSet("salary_adjustment") = IIf(PaymentObject.PaymentType = "Salary", PaymentObject.SalaryAdjustment, 0)
UpdatePSet("tax_code") = IIf(PaymentObject.PaymentType = "Salary", PaymentObject.TaxCode, 0)
UpdatePSet("tax_code_letter") = IIf(PaymentObject.PaymentType = "Salary", PaymentObject.TaxCodeLetter, 0)
If PaymentObject.PaymentMethod <> -1 Then
UpdatePSet("payment_method") = PaymentObject.PaymentMethod
End If
UpdatePSet("Cheque_no") = PaymentObject.ChequeNo
UpdatePSet("Memo") = PaymentObject.Memo
UpdatePSet("Remittance_Hyperlink") = PaymentObject.RemittanceFromClient
UpdatePSet("modified") = DateTime
UpdatePSet("modified_by") = CurrentUser.Number
UpdatePSet("Paid_Sick_Days") = PaymentObject.PaidSickDays
UpdatePSet("Total_Sick_Days") = PaymentObject.TakensickDays
UpdatePSet("Total_Maternity_Days") = PaymentObject.TakenMaternityDays
UpdatePSet("asset_Number") = PaymentObject.AssetNo
UpdatePSet("Owner_Type") = IIf(PaymentObject.OwnerType = "", "NA", PaymentObject.OwnerType)
If PaymentObject.SPType <> -1 Then
UpdatePSet("SP_type") = PaymentObject.SPType
End If
UpdatePSet.Update 'row cannot be located for updating error occurs on this line

---------------------------------------------------
Below is the table structure of the table I am updating:

CREATE TABLE `payments` (
`Data_ID` int(11) NOT NULL default '0',
`Company` tinyint(4) NOT NULL default '0',
`Account` int(11) default NULL,
`Payment_Number` int(11) NOT NULL default '0',
`Payment_Type` enum('Made','Received','Deposit','Vat Pmt','Vat Refund','Transfer','Salary','PAYE Pmt','Contra Bill','Contra Inv','Corporate Tax Pmt','Made-Sundry','Received-Sundry','Dividend Pmt','AdHoc','Corporate Tax Liability','Special Pmt') default NULL,
`Date` date NOT NULL default '0000-00-00',
`Trx_Type` varchar(60) NOT NULL default '',
`Transaction_id` int(11) default NULL,
`Payment_Method` tinyint(4) default NULL,
`Owner` int(11) NOT NULL default '0',
`Project_id` int(11) default NULL,
`Contra_Owner` int(11) NOT NULL default '0',
`Department` smallint(6) default NULL,
`Amount` double(15,3) default NULL,
`Gross_Salary` double(15,3) default NULL,
`Salary_Adjustment` double(15,3) default NULL,
`Tax_Code` varchar(10) default NULL,
`Tax_Code_Letter` varchar(4) default NULL,
`Cheque_No` varchar(20) default NULL,
`Memo` mediumtext,
`Remittance_Hyperlink` varchar(150) default NULL,
`Modified_By` int(11) default NULL,
`Modified` timestamp(14) NOT NULL,
`Paid_Sick_Days` smallint(4) NOT NULL default '0',
`Total_Sick_Days` smallint(4) NOT NULL default '0',
`Total_Maternity_Days` smallint(4) NOT NULL default '0',
`Asset_Number` int(11) NOT NULL default '0',
`Owner_Type` enum('Client','Supplier','NA') NOT NULL default 'NA',
`SP_Type` smallint(4) NOT NULL default '0',
PRIMARY KEY (`Data_ID`),
UNIQUE KEY `Payment_ID` (`Payment_Number`,`Company`,`Trx_Type`,`Payment_Type`)
) TYPE=MyISAM;