SQLitening Support Forum

Hello,

Been playing around SQLitening.

I want to update (Modify) a record based on the value of a textbox. In this case sUsername.

What is the correct syntax for updating a records using slBuildInsertOrUpdate and Update. I used the following:


slExe slBuildInsertOrUpdate("tblCreateUsers", sUsername & $Nul & sPassword & $Nul & sStatus, "Username = '" + sUsername + " ' ")


I got syntax error.in "="


slExe "Update tblCreateUsers Set Username = sUsername, Password = sPassword, Status = sStatus Where Username='" + sUsername + "'"


I got column not found in table error.

I guess I am missing something? 

Hi Frederick,

These are two small examples to save a new record and an existing modified record.

'Create a new record
Function SaveNewRec()As Long
Local fstr As String 'the fields string
Local vstr As String 'the values string

fStr = "MANUF,REDREF,PRODUCT,TYPE"
vStr = "MyCompany" & $NUL & "'003214523'" & $NUL & _
"MyProduct" & $NUL & "MyType"

slExe slBuildInsertOrUpdate ("Parts", vstr, fStr)
End Function   

'Update an existing record
Function UpdateRec()As Long
Local fstr As String 'the fields string
Local vstr As String 'the values string
Local wstr As String 'the where string

fStr = "MANUF,REDREF,PRODUCT,TYPE"
vStr = "MyCompany" & $NUL & "'003214523'" & $NUL & _
"MyProduct" & $NUL & "MyType"
wStr = "rowid = 123"

slExe slBuildInsertOrUpdate ("Parts", vstr, fStr, wStr)
End Function     


Regards
Rolf

Hello Rolf,

That was fast.

Umm... what is really wrong with my codes?

Thank you.

Rolf,

I see in your example you are using the rowid.
You know in the real world you might, after a search operation want to use the value of a textbox to modify the record Like:



sUsername = vd_gettext(ncbhndl, TextBoxID)
slExe slBuildInsertOrUpdate("tblCreateUsers", sUsername & $Nul & sPassword & $Nul & sStatus, "Username = '" + sUsername + " ' ")



The syntax for using the rowid would be different.

Kind regards,

Frederick,

there is no problem, you can do that. Important is that if you want to update an existing record you must use the WhereSring. If you omit the WhereString a new record will be inserted.

Also if you are not putting values into all fields of the table then you must use the ColumnsString, and it must match the ValuesString. If you are using all fields then you can omit the ColumnsString, but you must put a comma as placeholder. (see example)

See extra comma!!! Very important!

slExe slBuildInsertOrUpdate("tblCreateUsers", sUsername & $Nul & sPassword & $Nul & sStatus, , "Username = '" + sUsername + " ' ")

Fredrick,

The slBuildInsertOrUpdate line you posted doesn't have the column names for the fields you are specifying.
Excerpt from the SQLitening doc:

QuoteColumn names are optional for Insert but required for Update

So, I think the syntax error is from the "=" in the next parameter (the rsWhere string in the documentation).
Here is how I think it should be:

slExe slBuildInsertOrUpdate("tblCreateUsers", sUsername & $Nul & sPassword & $Nul & sStatus, "UserName,Password,Status", "Username = '" + sUsername + " ' ")

In the second code block you are sending three variable names in the SQL statement, when you should be sending the contents of the variables.  Just as you correctly inserted the sUsername into the where clause, you should do the same for each of the columns as such:
slExe "Update tblCreateUsers Set Username = '" + sUsername + "', Password = '" + sPassword + "', Status = '" + sStatus + "' Where Username='" + sUsername + "'"

Now, in both cases, you are using the where clause to make sure the update only happens to records where the Username field is equal to the one from your Textbox.  So, the 'set Username = sUsername is not necessary.  It shouldn't fail however.  Here is how I think both should look:
slExe slBuildInsertOrUpdate("tblCreateUsers", sPassword & $Nul & sStatus, "Password,Status", "Username = '" + sUsername + " ' ")

slExe "Update tblCreateUsers Set Password = '" + sPassword + "', Status = '" + sStatus + "' Where Username='" + sUsername + "'"

Regards,

David

Ah, I see I cross posted with Rolf.  Rolf, you are correct if he is doing an Insert.  But I believe his two different code blocks were attempts to solve the same problem, and the second code block is clearly an update. Therefore, the parameter cannot be omitted.

You are right, David. It says in Fred's documentation:
"Column names are optional for Insert but required for Update."

Rolf

Hello,

Thank you Rolf and David.

>>Column names are optional for Insert but required for Update

This makes all the difference when I am updating all the fields. I need to read the documentation more.

>>See extra comma!!! Very important!

Noted. This would work if I am updating just a field.

Best regards,

OK, I need some help here, and I can't quite grasp what the manual is saying.

I have 4 different CSV files, all in the format of

DateTime, Real.

They contain data every second for Tension, Pitch Roll and Heave, so each idividual file would be a series of entries as such-


Tension.CSV

RecordTime	Tension
2009-01-01 00:00:00	73
2009-01-01 00:00:01	67
2009-01-01 00:00:03	53
2009-01-01 00:00:04	72
2009-01-01 00:00:05	90
2009-01-01 00:00:08	77
2009-01-01 00:00:09	47
2009-01-01 00:00:10	19
2009-01-01 00:00:11	90
2009-01-01 00:00:13	78
2009-01-01 00:00:14	82
2009-01-01 00:00:15	50
2009-01-01 00:00:17	21
2009-01-01 00:00:18	84

   

Pitch.CSV

RecordTime	Pitch
39814.0000115741	0.635480334372697
39814.0000347222	0.651912825162737
39814.0000462963	0.960518554952992
39814.0000578704	0.0918474916201155
39814.0000694445	0.319918163823353
39814.0000810185	0.941540053614541
39814.0000925926	0.746957004452512
39814.0001157408	0.907714781997423
39814.0001273148	0.825096233881558
39814.0001388889	0.623002920037419
39814.000150463	0.0988242513928097
39814.0001851852	0.423528689520101
39814.0001967593	0.164773031247083

Roll.CSV

RecordTime	Roll
39814	0.226449782983676
39814.0000347222	0.508779557441737
39814.0000578704	0.0975702516256709
39814.0000810185	0.695856147544947
39814.0001041667	0.554725874769032
39814.0001157408	0.0694166052752716
39814.0001388889	0.0500894191504688
39814.0001736112	0.686395584500251
39814.0001851852	0.106952609077364
39814.0001967593	0.7232638244345
39814.0002083334	0.874211940270543

Heave.CSV

RecordTime	Heave
39814	0.533813121206404
39814.0000115741	0.550086162941062
39814.0000231482	0.824738836166552
39814.0000347222	0.328173923893668
39814.0000462963	0.390095363264371
39814.0000810185	0.752438962289054
39814.0000925926	0.192342174956707
39814.0001041667	0.601769796791728
39814.0001157408	0.444666057148828
39814.0001273148	0.310802052318191
39814.0001388889	0.0131939787769038
39814.0001620371	0.285008667883267
39814.0001736112	0.526707486478498
39814.0001967593	0.887415995622519
39814.0002083334	0.432657835526106

For a combined table which would look like

RecordTime	Tension	Pitch	Roll	Heave
39814	73	$NULL	0.226449782983676	0.533813121206404
39814.0000115741	67	0.635480334372697	$NULL	0.550086162941062
39814.0000231482	$NULL	$NULL	$NULL	0.824738836166552
39814.0000347222	53	0.651912825162737	0.508779557441737	0.328173923893668
39814.0000462963	72	0.960518554952992	$NULL	0.390095363264371
39814.0000578704	90	0.0918474916201155	0.0975702516256709	$NULL
39814.0000694445	$NULL	0.319918163823353	$NULL	$NULL
39814.0000810185	$NULL	0.941540053614541	0.695856147544947	0.752438962289054
39814.0000925926	77	0.746957004452512	$NULL	0.192342174956707
39814.0001041667	47	$NULL	0.554725874769032	0.601769796791728
39814.0001157408	19	0.907714781997423	0.0694166052752716	0.444666057148828
39814.0001273148	90	0.825096233881558	$NULL	0.310802052318191
39814.0001388889	$NULL	0.623002920037419	0.0500894191504688	0.0131939787769038
39814.000150463	78	0.0988242513928097	$NULL	$NULL
39814.0001620371	82	$NULL	$NULL	0.285008667883267
39814.0001736112	50	$NULL	0.686395584500251	0.526707486478498
39814.0001851852	$NULL	0.423528689520101	0.106952609077364	$NULL
39814.0001967593	21	0.164773031247083	0.7232638244345	0.887415995622519
39814.0002083334	84	$NULL	0.874211940270543	0.432657835526106

Any assistance would be appreciated.

Regards

Andrew

Below are the slBuildInsertOrUpdate statements for your first two records.

slExe slBuildInsertOrUpdate("TableName", "39814" & $NUL & _
                                         "73" & $NUL & _
                                         "Null"  & $NUL & _
                                         ".226449782983676"  & $NUL & _
                                         ".533813121206404")

slExe slBuildInsertOrUpdate("TableName", "39814.0000115741" & $NUL & _
                                         "67" & $NUL & _
                                         ".635480334372697"  & $NUL & _
                                         "Null"  & $NUL & _
                                         ".550086162941062")

Of course you would want to user variables and some kind of loop as shown below.
The GetTension, GetPitch, GetRoll, and GetHeave functions would return the proper
value for the passed RecordTime or "Null" if none.


   Local lsRecordTime as String

   do
      lsRecordTime = GetRecordTime
      if isfalse len(lsRecordTime) then exit do
      slExe slBuildInsertOrUpdate("TableName", lsRecordTime & $NUL & _
                                               GetTension(lsRecordTime) "67" & $NUL & _
                                               GetPitch(lsRecordTime)  & $NUL & _
                                               GetRoll(lsRecordTime)  & $NUL & _
                                               GetHeave(lsRecordTime))

   loop

Fred,

Do you have an example of a prepared SQLite statement, using 'placeholders'?

Regards

Andrew

Fred,

Is it slBuildInsertOrUpdate or slBuildInsertOrDelete?

May be a new function?

Andrew

Not sure what you mean by 'placeholders'?  If you are referring
to the ability to insert/update binary data then Example C has sample code
using slExeBind along with slBuildBindDat. 

Note that I discovered a doc error in the Sub BindAndQuery in Example C
(will be fixed in next release).  It should be as follows:

Sub BindAndQuery
' The boss has decided to store a memo in the customer table. This memo
' can be very long and is confidential so we must compress and encrypt.
' This example will use the Alter Table and Bind statements.

' The syntax and doc for slExeBind is:
'  slExeBind  (rsStatement String, rsBindDats String, [rsModChars String]) Long
'     Statement contains one (no multiples like slExe) Insert or Update
'     statement.  Will replace all of the '?' expressions in Statement with the
'     corresponding BindDat entry passed in BindDats.  A BindDat entry is the
'     specially formated string returned by the slBuildBindDat function.  Pass
'     BindDats as multiply concatenated BindDat entries to handle multiple '?'
'     expressions.  The first '?' will be replaced with the first BindDat entry,
'     the second '?' with the second BindDat entry, etc.  Excess '?' expressions
'     will be set to Null while excess BindDat entries will rais an error.  The
'     following example will insert three columns as follows:
'       ColName1 = Blob, ColName2 = Compressed Text, ColName3 = Compressed Encrypted Blob.
'         slExeBind slBuildInsertOrUpdate("TableName", _
'                                         "?" & $NUL & "?" & $NUL & "?" & $NUL, _
'                                          "ColName1, ColName2, ColName3"), _
'                                          slBuildBindDat("This is some Blob data") & _
'                                          slBuildBindDat("This is some copressed Text", "TC") & _
'                                          slBuildBindDat("This is a copressed and encrypted Blob", "CE")
'     ModChars:
'        Em = Return errors. This will override the global return errors flag.
'             m is the optional message display modifier and can be:
'                0 = No message is displayed.  This is the default.
'                1 = Display a warning message with OK button.  Error is
'                    returned when OK pressed.
'                2 = Display a question message with OK and Cancel buttons.
'                    If they press OK, error is returned.  If they press
'                    Cancel, will exit process.
'        e  = Do not return errors, display message and exit process. This
'             will override the global return errors flag.
'     Returns zero if processed OK. Else, depending on ModChars and the global
'     return errors flag, will either display error and exit or will return
'     the error number.

' The syntax and doc for slBuildBindDat is:
'  slBuildBindDat             (rsData String, [rsModChars String]) String
'     Returns a BindDat entry which is a specially formatted string required
'     by slExeBind.  Data contains the value you want converted into a BindDat.
'     A BindDat(s) is required to be passed to slExeBind.  The returned data
'     may also be compressed and/or encrypted.  If an error occurs then the
'     return value will be an empty string.  Use slGetError or slGetErrorNumber
'     to determine the error.
'     ModChars:
'        B = Bind as Blob.  This is the default.
'        C = Compress the data.
'        N = Encrypt the data.
'        T = Bind as Text.  Default is to bind as Blob.
'        Note: If both C and N then will first compress and then encrypt.

Fredrick

My mistake,  I have corrected to slBuildInsertOrUpdate,  thanks for catching.