SQLitening Support Forum

SQLitening Support => You've got Questions? We've got Answers! => Topic started by: Gary Stout on January 30, 2011, 12:49:35 AM

Title: "Where" string in slBuildInsertUpdate
Post by: Gary Stout on January 30, 2011, 12:49:35 AM
Can someone give me an explanation of the "Where" string in the slBuildInsertUpdate command.  I understand that if it is omitted, then Insert is assumed. I guess I am confused on its meaning and how it relates to an Update.

Thanks,
Gary
Title: Re: "Where" string in slBuildInsertUpdate
Post by: Frank W. Kelley on January 30, 2011, 01:41:36 AM
The "where" string in the slBuildInsertOrUpdate function specifies the condition for the update (i.e.: which record(s) should be updated).

For example, if the string "RowID = 22" is supplied as the "where" argument, the record with RowID 22 will be updated. In a broader sense, you can use "Age > 20" to apply the update to all records containing an Age value greater than 20.

This is similar to "UPDATE tablename SET Fulltime = TRUE WHERE Age > 20" only slBuildInsertOrUpdate assembles all the conditionals for you (and also handles embedded single quotes -- a lifesaver!)

Hope this helps.
Title: Re: "Where" string in slBuildInsertUpdate
Post by: Gary Stout on January 30, 2011, 02:57:37 AM
Frank,

Thanks for your reply. That makes a little more sense. Currently, I am using the following code that I was able to experiment with and seems to work, even though I really didn't understand why or how.
103203 is a field value but not a row id number. So, can row id be a value within a record or would this normally be a row number?


wStr = "rowid = '103203'"


Thanks,
Gary
Title: Re: "Where" string in slBuildInsertUpdate
Post by: Rolf Brandt on January 30, 2011, 05:39:25 AM
Hi Gary,

check in the FireFly Forum for "SQLitening for Beginners".

Iput a minimal SQLitening Database project to show how to create a database, a table, add, update, and delete records, and display records in a listview control. That might help a little.
Title: Re: "Where" string in slBuildInsertUpdate
Post by: Frank W. Kelley on January 30, 2011, 09:08:37 AM
The "where" value can be anything that corresponds to a field name:

wstr = "Firstname = 'Bob' AND Age > 22"
wstr = "Year > 1999"

It does not necessarily have to be a Row ID, although (for me, at least), the Row ID is probably the most common "where" value that is used.
Title: Re: "Where" string in slBuildInsertUpdate
Post by: Gary Stout on January 30, 2011, 08:18:16 PM
Thanks guys! I think I now have a better understanding of how this works. From reading your description, I am still not quite sure why the code I am using works, but it seems to.  The number that I mentioned, (103203),  is not actually a row id number, but rather a member number within the record that is being edited. 

Rolf, I have downloaded your example also to look over and try to get a better grasp.

Once I get past the learning curve, I will probably use Sqlitening for all my data needs. I have already been able to eliminate alot of code from a current project. Things that took many many lines of code to do in
Tsunami can be done in just a few lines of code in SL.

Thanks again,
Gary
Title: Re: "Where" string in slBuildInsertUpdate
Post by: Bern Ertl on January 31, 2011, 12:12:42 PM
Gary, SQLite will at times report success even when nothing was updated.  For example:

- Delete non-existant row, SQLite returns %SQLite_OK
- Update non-existant row, SQLite returns %SQLite_OK

Use slGetChangeCount to verify that records were in fact updated.

RowID values are integer class and should not be quote enclosed in SQL statements.
Title: Re: "Where" string in slBuildInsertUpdate
Post by: Gary Stout on January 31, 2011, 12:16:26 PM
Bern,

Thanks for the info as well. I will check slGetChangeCount to verify. Basically, what I was doing..... I had SQLExpert open also and after I tried updating something, I would check SE to see if the changes were made. It appeared that the items were changed, but based on what I have learned in this thread, I don't see how they could have been.

Thanks again,
Gary