Newbie Question about DB and SQL

Hi all,

Really new to XOJO, SQL and Database

I have created 2 methods

One to save to my DB (RouterInfo) and one to edit:

Save Method:

Dim SaveRouterInfoToDB as new DatabaseRecord  // Creating Variable name
  
  SaveRouterInfoToDB.Column("DeviceName") = Trim(DeviceNameBox.Text) // Colounm in Database
  SaveRouterInfoToDB.Column("MAC") = Trim(MACAddressBox.Text) // Colounm in Database
  RouterDB.InsertRecord "Devices", SaveRouterInfoToDB  //Sending Data to the Table in MainDB
  
  RouterDB.Commit

Edit Method:

[code] Dim DBRecordEditCall as new DatabaseRecord // This is calling the Databse
Dim sql 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
sql = "select DeviceName,MAC,RouterUp,RouterDown,DateAdded from Devices where name = " + RouterListBox.Cell(RouterListBox.ListIndex,0) + " and MAC = " + RouterListBox.Cell(RouterListBox.ListIndex,1)

Dim DatabaseEditSet as RecordSet = RouterDB.SQLSelect(sql) // Passing the SQL query on the Main Database

DatabaseEditSet.Edit // Setting the start to EDIT the DATABASE

DatabaseEditSet.Field(“DeviceName”).StringValue = trim(DeviceNameBox.Text) // DeviceName
DatabaseEditSet.Field(“MAC”).StringValue = trim(MACAddressBox.Text) // MAC

DatabaseEditSet.Update // Telling the DB That The Above Info is needed to be updated on select item

RouterDB.Commit // Database SAVES changes

DatabaseEditSet.Close
[/code]

When ever i then action this method my app fails and throws a NilObjectException on

  DatabaseEditSet.Edit // Setting the start to EDIT the DATABASE

Can anyone see what i’m doing wrong?

Any help is greatly appreciated

You should check RouterDB.ErrorCode or RouterDB.ErrorMessage before edit. It will give you a hint as to what is going wrong.

I think most people would suggest you always check for errors.

A simple example would be

if RouterDB.ErrorCode = 0 then
     // Do your Edit
else
    // There was an error
     msgbox RouterDB.ErrorMessage
end 

Looking at your sql string in the edit method I see a couple of problems. You’re using two columns in your where clause, name and MAC. I don’t know what name is as it’s not referenced anywhere else. Is this supposed to be DeviceName? Then, for both name (DeviceName?) and MAC, which appear to be string or text values, you need to enclose the criteria in single quotes. So, your sql string should be assigned as follows:

sql = "select DeviceName,MAC,RouterUp,RouterDown,DateAdded from Devices where DeviceName = '" + RouterListBox.Cell(RouterListBox.ListIndex,0) + "' and MAC = '" +  RouterListBox.Cell(RouterListBox.ListIndex,1) + "'"

Notice how I added single quotes so that the resulting string should look something like:

select DeviceName,MAC,RouterUp,RouterDown,DateAdded from Devices where DeviceName = 'some value' and MAC = 'some other value'

Also, to help make sure you’re getting a correct sql string, you could output the sql string either in a msgbox or in a “debug” field on the window. Then you could copy that sql string into a database query tool to verify that it retrieves the correct record.

Thanks both for your help

@ Johnny - I am going to go read up on .ErrorCode now

@ Jay - Yep my SQL code was out, now i’ve used your SQL everything updates correctly.

Really appreciate your guidance here

@Johnny Harris

Where would you put the error code statement?

[quote]if RouterDB.ErrorCode = 0 then
// Do your Edit
else
// There was an error
msgbox RouterDB.ErrorMessage
end [/quote]


Dim DBRecordEditCall as new DatabaseRecord  
  
Dim sql as String  

  sql = "select DeviceName,MAC,RouterUp,RouterDown,DateAdded from Devices where name = " + RouterListBox.Cell(RouterListBox.ListIndex,0)
  
Dim DatabaseEditSet as RecordSet = RouterDB.SQLSelect(sql)

/////////IF HERE////////////

if RouterDB.ErrorCode = 0 then

  DatabaseEditSet.Edit // Setting the start to EDIT the DATABASE
  
  DatabaseEditSet.Field("DeviceName").StringValue = trim(DeviceNameBox.Text)
 
  DatabaseEditSet.Update
  
  RouterDB.Commit 
  
  DatabaseEditSet.Close

/////////ELSE HERE////////////
else
     msgbox RouterDB.ErrorMessage
end

Yes, you have it correct. But you should check for database errors after each operation, so in your case also after the .Edit and .Update calls. And you probably want to add information to the error message msgbox that tells you where the error occurred, something like

msgbox "Error occurred on recordset edit: " + RouterDB.ErrorMessage

I very rarely use the edit and update methods. I prefer to just write SQL.
Probably something like the code below would work.

As Jay mentioned I also always use single quotes. Not sure if it is required of all databases.

Dim DBRecordEditCall as new DatabaseRecord  
  
Dim sql as String  

  sql = "select DeviceName,MAC,RouterUp,RouterDown,DateAdded from Devices where name = " + RouterListBox.Cell(RouterListBox.ListIndex,0)
  
Dim DatabaseEditSet as RecordSet = RouterDB.SQLSelect(sql)

/////////IF HERE////////////

if RouterDB.ErrorCode = 0 then

  DatabaseEditSet.Edit // Setting the start to EDIT the DATABASE
  
  DatabaseEditSet.Field("DeviceName").StringValue = trim(DeviceNameBox.Text)
 
  DatabaseEditSet.Update
  
//  ---->    Test again after trying to  Update. This would also give you a chance to rollback if needed.
if RouterDB.ErrorCode = 0 then
       RouterDB.Commit 
else
      msgbox RouterDB.ErrorMessage
end

DatabaseEditSet.Close

/////////ELSE HERE////////////
else
     msgbox RouterDB.ErrorMessage
end

Same here. If I have some bulk operation that I want to update a bunch of records I will use the Update command, but for changes to the database on a single record, I just use SQL to pull the record in a recordset and update as Johnny Harris described.