I have a bit of DB Code that update columns in my DB. But it for some reason seems to break when starting the edit. Can anyone see the issue
Dim DBRecordEditCall as new DatabaseRecord // This is calling the Databse
Dim sql3 as String // This is setting the SQL Code Needed to bring back data from the saved table
// The SQL CODE is trying to match row in Database with the infomaiton taken from the RouterListbox
sql3 = "select Mins,Password from LockdownStore where Password = '" + PasswordBox.Text
Dim DatabaseEditSet as RecordSet = LockdownDB.SQLSelect(sql3) // Passing the SQL query on the Main Database
DatabaseEditSet.Edit // Setting the start to EDIT the DATABASE
DatabaseEditSet.Field("Mins").StringValue = trim(MinutesInputBox.Text) // Mins
DatabaseEditSet.Field("Password").StringValue = trim(PasswordBox.Text) // Password
DatabaseEditSet.Update // Telling the DB That The Above Info is needed to be updated on select item
LockdownDB.Commit // Database SAVES changes
When i debug sql3 seems to be adding the passwordbox.text to the string.
It fails on DatabaseEditSet.Edit
all i have to go on is : NilObjectException
Place a breakpoint and look at sql3 in the debugger. I think you will find a missing ’
You should check for errors after each database operation.
A nil object means your sql was malformed.
as i walk into the Code the String returned is:
select Mins,Password from LockdownStore where Password = 'passy
Passy is the value in the box. Does the string look malformed?
You need to select the primary key (or, assuming SQLiteDatabase, rowid) for Edit/Update to work.
The above has worked for me for sometime now as all i wanted to do was match a row. Can you see why my code is not adding an additional ’ to the end to complete the SQL?
Don’t worry have resolved it. Missing the “’” at the end to close the query (Facepalm)
Try this. Be sure to escape the password value.
sql3 = “select Mins,Password from LockdownStore where Password = '” + ReplaceAll(PasswordBox.Text, “’”) + “’”
But still, if you want to edit records, you need to select a primary key.
Not sure what you’re doing with passwords in database, but depending on the real security needs of your app, you may want to familiarize yourself with this:
And for the record, you should be checking for a DB error after DB command such as SQLSelect:
If LockdownDB.Error Then
MsgBox("DBError: " + LockdownDB.ErrorMessage)
The above would have given you a message telling you about a malformed SQL statement.