Database Update Issue

Databases
#1

Hi All,

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
      
      DatabaseEditSet.Close

When i debug sql3 seems to be adding the passwordbox.text to the string.

It fails on DatabaseEditSet.Edit

#2

What is the error?

#3

Hey Paul

all i have to go on is : NilObjectException

#4

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.

Jim

#5

Hey Jim

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?

#6

You need to select the primary key (or, assuming SQLiteDatabase, rowid) for Edit/Update to work.

#7

Hey Brad,

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?

#8

Don’t worry have resolved it. Missing the “’” at the end to close the query (Facepalm)

#9

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:

http://www.realsoftwareblog.com/2012/12/10-tips-to-improve-password-security.html

#10

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) End If

The above would have given you a message telling you about a malformed SQL statement.

#11