UPDATING RECORD

Databases
1

is it correct for updating record ??

'//THIS IS FOR MSSQL
dim db as new MSSQLServerDatabase

'//SET NEEDED PARAMETER
db.Host = "(local)"
db.DatabaseName = "raf"
db.UserName = "sa"
db.Password = "Pastilan123"

'//OPENING NOW
if db.connect  then
  
  '//use this to start the update/insert/delete
  db.SQLExecute("START TRANSACTION")
  '//
  
  '//to inform datbase to save immediately
  db.SQLExecute("AUTO COMMIT")
  
  '//for transaction
  db.SQLExecute("BEGIN TRANSACTION")
  
  
  
  
  
  
  db.SQLExecute("UPDATE tblCustomer SET Firstname = '" + txtFname.text + "', lastname = '" + txtLname.text + "', Age= " + txtAge.text + ", Gender = '" + txtGender.text + "', Birthdate= '" + txtBdate.text + "' Where ID ="+ txtid.Text)
  
  
  
  '// record physically
  db.commit
  
  
  'done SAVING
  
END IF

end if

2

I would use a prepared statement rather than SQL injection, and unless there is more than one insert/update a transaction is an unnecessary overhead.

3

You MUST check for a database error after the SQLExecute. Otherwise you’ll never know if there was an error.

if db.error then msgbox db.errormessage return end

4

Please help me to resolve the rs.Update issue. Nothing is happening in the database after rs.Update
Dim db As New MySQLCommunityServer
db.Host = “localhost”
db.Port = 3306
db.DatabaseName = “new”
db.UserName = “root”
db.Password = “admin”
If db.Connect Then
Dim d As New Date
Dim ps As PreparedSQLStatement=db.Prepare("SELECT * FROM repgen WHERE dates = ? ")
ps.BindType(0, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.Bind(0,str(d.SQLDate))
Dim rs As RecordSet = ps.SQLSelect
MsgBox(str(rs.Field(“dates”).StringValue))
If rs <> Nil Then
rs.Edit
rs.Field(“ev1”).StringValue =“2”
MsgBox(rs.Field(“ev1”).StringValue)
rs.Update
db.Commit
Else
If db.Error Then MsgBox(db.ErrorMessage)
End If
Else
If db.Error Then MsgBox(db.ErrorMessage)
End If

this is my code . I’m getting the values in the MsgBox but its not getting updated in the database .

5

posting the same question in two different topics will not get you a better or quicker answer

6

also check for rs.recordcount. if there are multiple records, which one do you update ?