Sqlite insert statement

Databases
1

Hi All.

This is making me crazy… so hopefully someone can help.

I am trying to insert into a database values in textboxes.

When I do this:

theInsertStringForTransactions = “INSERT INTO theTransactions (nameOfPayee, dateAndTime, categoryClass, memoOfTransaction, amountOfTransaction, totalBalance) VALUES (‘payee’,‘date’,‘class’,‘memo’,‘555’,‘98765.43’)”

And it works just fine.

If I do this:

theInsertStringForTransactions = “INSERT INTO theTransactions (nameOfPayee, dateAndTime, categoryClass, memoOfTransaction, amountOfTransaction, totalBalance) VALUES ('”+winTransactionWindow.textNameOfPayee.Text+“‘’,‘date’,‘class’,‘memo’,‘555’,‘98765.43’)”

The sqlExecute command fails. It even shows that when I step through that there is nothing in the winTransactionWindow.textNameOfPayee.text shows nothing in it. But I’m darn sure there is something in there.

Any ideas?

2

Is that two quotation marks after .Text+ ?

In any case, did you try this:
theInsertStringForTransactions = “INSERT INTO theTransactions (nameOfPayee, dateAndTime, categoryClass, memoOfTransaction, amountOfTransaction, totalBalance) VALUES (?, ?, ?, ?, ?, ?);", _ winTransactionWindow.textNameOfPayee.Text, "date", "class", "memo", 555, 98765.43)

3

Hi Christoph.

Yep there were two double quotes.
I have taken one out, but even when I run it now, I still get the same result.

I will try what you have shown and let you know the result… unless I get distracted…
squirrel…

4

Not sure if this is something the forum did, but your code has curly quotes:

theInsertStringForTransactions = “INSERT INTO theTransactions 
(nameOfPayee
, dateAndTime
, categoryClass
, memoOfTransaction
, amountOfTransaction
, totalBalance) VALUES 
(‘payee’
,‘date’
,‘class’
,‘memo’
,‘555’
,‘98765.43’)”

vs. straight quotes:

theInsertStringForTransactions = "INSERT INTO theTransactions
(nameOfPayee
, dateAndTime
, categoryClass
, memoOfTransaction
, amountOfTransaction
, totalBalance) VALUES 
('payee'
,'date'
,'class'
,'memo'
,'555'
,'98765.43')"

Curly quotes will cause all kinds of mischief with queries.

5

After the left-paren there should be a single-quote followed by a double-quote. Then after the second plus-sign in the fragment above, there should be a double-quote followed by a single-quote, followed by a comma.

And they need to be proper single-quotes.

And this should work too. You have to be careful when you type this stuff to get it right.

6

I do all my inserts with a Database Method using DatabaseRow. I put my fields in one array and values in another array. Everything is strings because the database knows its field types and will raise an exception if the data doesn’t match the field type.

Var MyFields As String
MyFields.Add("nameOfPayee")
MyFields.Add("dateAndTime")
MyFields.Add("categoryClass")
MyFields.Add("memoOfTransaction")
MyFields.Add("amountOfTransaction")
MyFields.Add("totalBalance")

Var MyValues  As String
MyValues.Add("payee")
MyValues.Add("date")
MyValues.Add("class")
MyValues.Add("memo")
MyValues.Add("555")
MyValues.Add("98765.43")

Var b As Boolean
b = DBInsertRecord("theTransactions",MyFields,MyValues)
If b Then
  MessageBox("It Worked")
Else
  MessageBox("It Failed")
End If

Public Function DBInsertRecord(tblname As String, flds() As String, fldvalues() As String) As Boolean
If flds.Lastindex = fldvalues.Lastindex Then
  Var dbr as DatabaseRow
  dbr = New DatabaseRow
  For i As Integer = 0 To flds.Lastindex
    dbr.Column(flds(i)) = fldvalues(i)
  Next
  
  Try
    MyDB.AddRow(tblname ,dbr)
    MyDB.Commit
    Return True
  Catch error As DatabaseException
    Var desc,err As String
    desc = "DatabaseMethods.DBInsertRecord.DatabaseError"
    err = ": " + Str(error.ErrorNumber) + " - " + error.Message
    // Log the Exception
    LogExceptions(desc + err)
    Return False
  End Try
  
Else
  Var desc,err As String
  desc = "DatabaseMethods.DBInsertRecord.DatabaseError"
  err = ": Fields and Values arrays are NOT EQUAL in length."
  // Log the Exception
  LogExceptions(desc + err)
  Return False
End If
  
End Function
7

Never used it. If I have to update a given row with some number of changes, I can do that in a single statement.

Horses for courses, of course.

8

Tim this method is for inserts only. It’s pretty foolproof and needing to know what needs to be single quoted isn’t necessary. I do something different for updates but still a method I call so my pattern for how I work with a database is consistent. All my databases use the same name for the Primary Key “PK_ID” A lot of my databases may have 10 or 20 columns to insert into or update so this method makes it a lot easier to do and to read the code.

// Call the Method like this
Var b As Boolean
b = DBUpdateRecord("theTransactions","nameOfPayee = 'payee', dateAndTime = 'date', "totalBalance = '98765.43'","1")
If b Then
  MessageBox("It Worked")
Else
  MessageBox("It Failed")
End If

// this is the Method

Public Function DBUpdateRecord(tblname As String, colvals As String, rcrdkey As String) As Boolean

Var sqlString As String
sqlString = "UPDATE " + tblname + " SET " + colvals  + " WHERE PK_ID = '" + rcrdkey + "'"

Try
  MyDB.SQLExecute(sqlstring)
  MyDB.Commit
  Return True
Catch error As DatabaseException
  Var desc,err As String
  desc = "DatabaseMethods.UpdateRecord.DatabaseError"
  err = ": " + Str(error.ErrorNumber) + " - " + error.Message
  // Log the Exception
  LogExceptions(desc + err)
  Return False
End Try
  
End Function
9

If you use the prepared statement form as in the second part of my first post upthread, there it doesn’t require quoting either. Only if you build a complete SQL statement using string concatenation is quoting needed.

10

I keep meaning to play with Prepared Statements but everything I’m doing works so it’s just laziness on my part for continuing to put that off. (makes mental note to be less lazy)

11

What’s nice with the API 2 versions of the database methods is that all the hassle of preparing and binding variables is no longer necessary. E.g.:

Var  str, sql as String, i as Integer, db as SQLiteDatabase

sql = "update mytab set strcol=?1 where id=?2"
db.ExecuteSQL (sql, str, i)

No quotes or string concatenation with extreme care as to where the quotes go and what they need to be.

12

Hi Tim.

I verified the ’ (single quote) " (double quote) + a number of times, and verified that the quotes I look are correct.

I’ll do some more checking to be sure, but that is what I got when I copied my text into notepad and blew it up to 24 points just to make sure.

Since I sometimes need a German keyboard, I figured that might be why, but so far all looks good.

When I do some of the checking I will be advise. Thanks for all of the help so far.

Stay tuned.

13

Beware when you use an external application with code, you never know if that application will or not change your text (quotes for example, but not only).

If your screen is > 96 dpi, make a screen shot (in memory) and paste it in Paint: you will be able to make the difference between quotes, etc.

Or, you can enlarge the IDE font size… if needed.

14

Hi Emile.

Oh yeah… I’ve been aware of this … the hard way… trying to log into a system.
Anyway, I increased the font to 24, so I can really see it even with my bad eyes.

Regards

1 Like
15

There are several errors here - more of a mishmash of two different styles. The parameters should not be concatenated with the query. And the comma should be outside the double-quotes. The query and the query parameters are all parameters for the ExecuteSQL() call:

db.ExecuteSQL("INSERT INTO theTransactions (nameOfPayee, dateAndTime, categoryClass, memoOfTransaction, amountOfTransaction, totalBalance) VALUES (?, ?, ?, ?, ?, ?)", _
  winTransactionWindow.textNameOfPayee.Text, "date", "class", "memo", 555, 98765.43)

Or if you were attempting to assign the query to a string first, then using it in ExecuteSQL, it would be:

sql = "INSERT INTO theTransactions (nameOfPayee, dateAndTime, categoryClass, memoOfTransaction, amountOfTransaction, totalBalance) VALUES (?, ?, ?, ?, ?, ?)"
db.ExecuteSQL(sql, winTransactionWindow.textNameOfPayee.Text, "date", "class", "memo", 555, 98765.43)

Just to be sure, I assume the OP understands what the _ character does in Xojo? (line continuation)

16

Hi All. Thanks for all of the help and suggestions so far.
Jay, I used version of the sql string, and got the same issue.

I did find a “work around” where IF I take the comboBox value (mortgage, salary, etc) and put it in a Shared Property in the app it works.

I have NO idea why. Can anyone explain this to THIS dolt?

Regards

17

Ha ha yes, copy and paste error. What you have is what I intended to put.

18

Yes as Jay points out this is wrong. You need his second example:

Which is what I’d intended to post.

19

So it looks like there were (at least) two issues all along. You seem to have the SQL issue fixed. But I missed this statement in your OP:

From this and your latest statement, I assume the code executing the SQL is on another window or in a module or the App itself. And you stated that in the debugger there was nothing in the textNameOfPayee control, so obviously something else is going on. I’d have to see more of your project to figure out what. Like, where is the SQL execution method, and how and from where is it being called?

20

I’m new to Xojo but reading this thread with a lot of interest. I’m curious why all this SQL is bandied about as part of Xojo. Why aren’t parameterized stored procedures used?