PureBasic Forums - English

• Hello, I am updating an SQLite table with data from an input screen. The first method works fine. The second, which involves a lot less work, does not.
  I would prefer to use the second method as there are fewer possibilites of a typo, which isn't that important but as there are nearly 30 fields to update there is clearly far less work involved and easier to notice any field omissions etc.
  
  The second method doesn't throw an error and gives the impression it has completed but does not update the table. The SQL code line is fine and checks out in DBBrowser. Is this an issue in PB or have I got some syntax wrong?
  
  Many thanks, C87

  ;SQLite  Database
     REPL$ = "UPDATE PWS10 SET "
     REPL$ = REPL$ + "GROUPBY= '" + fmGroupBy + "', "  
     REPL$ = REPL$ + "WEBSITE='" + fmWebsite + "', "    
     REPL$ = REPL$ + "EMAIL='" + fmEmail +"', "  
     REPL$ = REPL$ + "NAME= '" + fmName + "', "            
     REPL$ = REPL$ + "ACCTYPE='" + fmAccType + "'"
     REPL$ = REPL$ + " WHERE RECNO=?"
     ;
     If DatabaseUpdate(0,REPL$)   
          ;               
          MessageRequester("UPDATE COMPLETED","Your changes have been saved",#PB_MessageRequester_Ok)
          ;
      Else
           ;
           MessageRequester("ERROR UPDATING RECORD","Changes have not been saved, please try again."+Chr(10)+Chr(10)+"Error: "+ DatabaseError(), #PB_MessageRequester_Info)
      EndIf
        
;                ▲   ▲   ▲ the above works Ok

;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

;                ▼    ▼   ▼  the code below goes to UPDATE COMPLETED i.e., no error on update but the PWS10 table is not updated with the changes
     SetDatabaseLong(0,0,DBRECNO) 
     SetDatabaseString(0,1,fmGroupBy)
     SetDatabaseString(0,2,fmWebsite)
     SetDatabaseString(0,3,fmEmail)
     SetDatabaseString(0,4,fmName)
     SetDatabaseString(0,5,fmAccType)
     ;
     If DatabaseUpdate(0,"UPDATE PWS10 SET  GROUPBY=?, WEBSITE=?, EMAIL=?, NAME=?,ACCTYPE=?  WHERE RECNO=?" )  ;   this messages Saved but doesn't do the save
         ;               
         MessageRequester("UPDATE COMPLETED","Your changes have been saved",#PB_MessageRequester_Ok)
     Else
          ;
          MessageRequester("ERROR UPDATING RECORD","Changes have not been saved, please try again."+Chr(10)+Chr(10) + "Error: " +DatabaseError(), #PB_MessageRequester_Info)
      EndIf
     

WHERE RECNO=?"

REPL$ = "UPDATE PWS10 SET "
REPL$ + "GROUPBY= '" + fmGroupBy + "', " 
REPL$ + "WEBSITE='"   + fmWebsite + "', "   
REPL$ + "EMAIL='"       + fmEmail +"', " 
REPL$ + "NAME= '"      + fmName + "', "           
REPL$ + "ACCTYPE='"  + fmAccType + "'"
REPL$ + " WHERE RECNO=?"

REPL$ = "UPDATE PWS10 SET " + 
"GROUPBY= '"    + fmGroupBy + "', " + 
"WEBSITE='"     + fmWebsite + "', " + 
"EMAIL='"       + fmEmail   + "', " + 
"NAME= '"       + fmName    + "', " + 
"ACCTYPE='"     + fmAccType + "' " + 
"WHERE RECNO=?"

Fangbeast wrote:How does sqlite update a record if there was none specified?

spikey wrote:

Fangbeast wrote:How does sqlite update a record if there was none specified?

SQLite is loosely typed. 'WHERE RECNO=?' is syntactically correct SQL, in isolation at least, so it will do its best to bend the supplied arguments to fit and execute it.

In this case RECNO appears to be a long so at a guess I'd say ? ends up as null or maybe zero, assuming no SetDatabase* gives it a different value. At execution I'd expect no rows actually to match the supplied data and no updates will take place. An update with no results is perfectly acceptable too, at least as far as the engine is concerned, so no error gets reported.

Personally I'd regard this as a program bug but the sample code shown isn't complete...

Returns the number of rows affected by the last DatabaseUpdate() operation.

suggested changing the Index location numbers but they are correct as they are. DBRECNO is a Long memory var taken from the field RecNo on the 'current' record in the Table PWS10 where RecNo is a unique number that my system fils in when a new record is added
Its location at 0 is correct. I do not use the number SQLite generates. GROUPBY ihas to be 1, etc. It is confirmed if I Debug the results of the SetDatabaseString() SetDatabaseLong() functions.

RECNO=? : On the first example the RECNO=? works Ok. So why not on the other? Which begs the question if it works with RECNO=? why don't the rest get written?

I can certainly see the advantage of not doing REPL$ = REPL$ .... on each line but I read somewhere in Forums that PB didn't allow concatenating lines as you have shown. I'll certainly change to that.

Fangbeast suggested the following to reduce typing, which is better and works great.
REPL$ = "UPDATE PWS10 SET " +
"GROUPBY= '" + fmGroupBy + "', " +
"WEBSITE='" + fmWebsite + "', " +
"EMAIL='" + fmEmail + "', " +
"NAME= '" + fmName + "', " +
"ACCTYPE='" + fmAccType + "' " +
"WHERE RECNO=?"

Citystate: A record will always exist as I am preventing the user from getting to BOF or EOF. To get to EDIT the selected record it must exist and I don't want to allow a user to add another record when completing an edit. I'll only allow them to add a record after they click the [ADD] button.

#Null suggested using AffectedDatabaseRows() which I didn't know about AffectedDatabaseRows() and have incorporated it into the code :

Code: Select all

 Result = AffectedDatabaseRows(0)
 MessageRequester("UPDATE COMPLETED","Your changes have been saved  "+Chr(10)+"("+Str(DBRECNO)+" : " + Str(Result) + ")", #PB_MessageRequester_Ok)

This confirms the chosen record it should be updating and the number or rows updated. In my 1st example it returns both correctly eg 5 : 1
In the 2nd it returns 5 : 0 

It will now show me these values after UPDATE in the MsgRequester(). In every case the DBRECNO is always correct. The Result is 1 in the 1st example which works and 0 in the second, which doesn't. It is a huge step forward as I can now trap an update that may not happen by checking for a zero result. I'll fiddle about with the code later and using AffectedDatabaseRows() maybe locate why it isn't updating. In the meantime I've used Fangbeast's suggestion to at least reduce some typing by concatenating the lines. I wanted really to get away from typing all the +'" "'+ stuff. I'll probably end up writing a short program to write that code for me from a list of 30 field names and cut & paste it in in future applications. It will stick all those single and double speech marks in in a second!

As I say on my 1st example RECNO=? works fine but not on the second. In fact I am unable to amend just one field . If I sort it I'll post in on Forums.

Have to say many thanks to you all, most appreciated. C87

C87 wrote:Its location at 0 is correct.

UPDATE PWS10 SET  GROUPBY=?, WEBSITE=?, EMAIL=?, NAME=?,ACCTYPE=?  WHERE RECNO=?

C87 wrote:It is confirmed if I Debug the results of the SetDatabaseString() SetDatabaseLong() functions.

code suggested by Kiffi:
SetDatabaseString(0,0,fmGroupBy)
SetDatabaseString(0,1,fmWebsite)
SetDatabaseString(0,2,fmEmail)
SetDatabaseString(0,3,fmName)
SetDatabaseString(0,4,fmAccType)
SetDatabaseLong  (0,5,DBRECNO)

Spikey wrote earlier - which was right!
Personally I'd regard this as a program bug but the sample code shown isn't complete...

(Sorry about that Spikey but I thought at the time ; hey, there's nothing wrong with my code Spikey) But there was!

UPDATE PWS10 SET  GROUPBY=?, WEBSITE=?, EMAIL=?, NAME=?,ACCTYPE=?  WHERE RECNO=?

GROUPBY is the first parameter (index 0), WEBSITE is the second (index 1) ... RECNO is the sixth (index 5).

C87 wrote:Apologies for not testing Kiffi's code in the first place