MS SQL not updating but does add

Databases
1

Hi All,

Any Ideas why my MS SQL Server does not want to update anything … the code runs but fails to update (I get no errors …it add a row at the end but NO Update???)

Dim db As New MSSQLServerDatabase

db.Host = LaptopNAME\SQLEXPRESS" // or just the IP if using the default instance
db.DatabaseName = “Database_001”

If db.Connect Then
Dim rs As RecordSet

rs = db.SQLSelect("SELECT F1, F2, mem_num, store FROM tblTesting123_TRANS")
    
If db.Error Then
  MsgBox("Error: " + db.ErrorMessage)
  Return
End If

rem If rs <> Nil Then
While Not rs.EOF
  rs.Edit
  rs.Field("F1").StringValue = rs.Field("mem_num").Value
  rs.Field("F2").StringValue = rs.Field("store").Value
  rs.IdxField(2).StringValue = "333"
  rs.Field("F4").StringValue = "444"
  
  rs.Update()
  rs.MoveNext
  MsgBox("UPDATED RECORD")
Wend





Dim row As New DatabaseRecord
// ID will be updated automatically
row.Column("F4") = "Penguins"


DB.InsertRecord("tblTesting123_TRANS", row)

If DB.Error Then
  MsgBox("DB Error: " + DB.ErrorMessage)
End If

MsgBox("ROW ADDED")






rs.Close
rem End If
db.Close

Else
If db.Error Then
MsgBox("Error: " + db.ErrorMessage)
Return
End If
End If

MsgBox(“Finished”)

2

Include the primary key in your original SELECT statement.
Without it, the recordset may not know for certain which row to update.
(It does in Access, but the Xojo implementation / usage does seem to need more certainty)

3

Actually, you’re NOT checking for an error after the update. I’m sure it will give you a proper error message. I’m with Jeff, it’s probably that you’ve not brought back the Primary Key in the SQL statement so it won’t know how to update the Recordset.

rs.Update() if db.error then MsgBox("Error: " + db.ErrorMessage) return end

4

Yes, Thankyou … yes for the record using Microsoft SQL Server I can only update records if you have the Primary Key within the select statement

e.g. the following now DO work :-

rs = db.SQLSelect(“SELECT ID, F1, F2, F4, mem_num, store FROM tblTesting123_TRANS”)

rs = db.SQLSelect(“SELECT * FROM tblTesting123_TRANS”)

Regards
MDUK