capaho wrote:
J.Hansen wrote:
According to RS documentation link:
http://docs.realsoftware.com/index.php/RecordSet.EditIt says: "Be sure the SQL incudes the
primary key column (or columns) so that you can update the table after making changes."
Have you check that. ?
Also which version of RS are you using, as in the past there was some issues with the MySql plugin.
RecordSet.Edit/RecordSet.Update appears to be broken in the MySQL plugin. I tried everything I could think of but never could get more than the first record to update. It looks to me like RecordSet.MoveNext doesn't function with RecordSet.Edit. The only way I can update multiple records in MySQL is to use IndexSchema or TableSchema to find an index column and then use the index with the SQL update command to update the necessary records.
I have tried following with RS version: RS2012R2,1, RS2012R2 and RS2011R4.3 and all of them gives same problem.
Created a MySql database:
db.SQLExecute("CREATE table Items ( id INT NOT NULL AUTO_INCREMENT, name TEXT, number INTEGER, PRIMARY KEY(id) )")
if db.Error Then
MsgBox("DB Error: " + db.ErrorMessage)
Else
db.SQLExecute("INSERT INTO Items(name, number) VALUES ( 'Car', 20)")
db.SQLExecute("INSERT INTO Items(name, number) VALUES ( 'House', 21)")
db.SQLExecute("INSERT INTO Items(name, number) VALUES ( 'Notebook', 22)")
db.SQLExecute("INSERT INTO Items(name, number) VALUES ( 'Realsoftware', 23)")
db.SQLExecute("INSERT INTO Items(name, number) VALUES ( 'Realsoftware', 24)")
db.SQLExecute("INSERT INTO Items(name, number) VALUES ( 'Realsoftware', 25)")
db.SQLExecute( "COMMIT" )
End If
Tried to update name='Realsoftware' with Code:
If Not db1.Connect Then
Msgbox "Could not connect to database"
Else
rs = db1.SQLSelect("SELECT * FROM Items WHERE Name = 'Realsoftware' ")
If rs <> Nil Then
Listbox1.AddRow "Update Recordset "+"Number of Records: " + str(rs.RecordCount)
While Not rs.EOF
rs.Edit
if Not db1.Error Then
rs.Field("number").IntegerValue = 30
rs.Update
Listbox1.AddRow ""
ListBox1.Cell(Listbox1.LastIndex,0) = rs.Field("id").StringValue
ListBox1.Cell(Listbox1.LastIndex,1) = rs.Field("name").StringValue
ListBox1.Cell(Listbox1.LastIndex,2) = rs.Field("number").StringValue
rs.MoveNext
End if
Wend
db1.Commit
Else
If db1.Error Then MsgBox(db1.ErrorMessage)
End If
db1.Close
End If
And I only manage to update the first record.
In the MySql log file shows the Primary key is the same for all three records:
Quote:
29840 Query SELECT * FROM Items WHERE Name = 'Realsoftware'
29840 Query SHOW COLUMNS FROM Items
29840 Query UPDATE Items SET number='30' WHERE id='4'
29840 Query UPDATE Items SET number='30' WHERE id='4'
29840 Query UPDATE Items SET number='30' WHERE id='4'
29840 Query commit
I will report it to RS.