Insert textfield1.text into Sqlite

Databases
1

Sorry if this is a newbie question. I’m struggling to insert text field contents into an SQLite database via a prepared insert statement.

This works and puts the letters into the columns of a new DB record:
bmdb.SQLExecute (“INSERT INTO bm_customers (customer_id,title,first_name,last_name,company_name,number_name,street,town,city,county,postcode) VALUES “_
+”(‘A’,‘B’,‘C’,‘D’,‘E’,‘F’,‘G’,‘H’,‘I’,‘J’,‘K’)”)

But when I tried and put text fields in place of the characters, i.e. textfield1.text (using the relevant + symbols, i.e. I didn’t just put textfield1.text in single quotes) it wouldn’t work and gave a type miss match.

So I set up string variables, a through to k and tried to insert those as below; but it still doesn’t work (I figured if it did, all I’d have to do; was make my a through to k string variables equal to textfield1.text and textfield2.text etc…

bmdb.SQLExecute (“INSERT INTO bm_customers (customer_id,title,first_name,last_name,company_name,number_name,street,town,city,county,postcode) VALUES (”+a+","+b+","+c+","+d+","+e+","+f+","+g+","+h+","+i+","+j+","+k+")")

Ignore the fact neither are prepared statements, I just wanted to crack it in raw form before I assigned it to a string as a prepared statement.

2 
"('"+a+"','"+b+"','"+c+"','"+d+"','"+e+"','"+f+"','"+g+"','"+h+"','"+i+"','"+j+"','"+k+"')")
3

You Sir, Mr DS are a star!! Thank you… that’s been driving me mad to the point where I couldn’t focus anymore…

Thanks again :smiley:

4

In the future, to make this easier to debug, you might try first assigning your SQL to a variable (that you can view in the debugger) rather than attempting to get it right the first time as part of the SQLExecute command:

Dim sql As String = "INSERT INTO bm_customers " + _ "(customer_id,title,first_name,last_name,company_name,number_name,street,town,city,county,postcode) " + _ "VALUES ("+a+","+b+","+c+","+d+","+e+","+f+","+g+","+h+","+i+","+j+","+k+")"

5

Thanks Paul, I’ve taken your advice and adopted that; which I’ll continue to do. Thank you!

6

Sorry all,

I have a Data Control and text fields that are linked to my database table and columns. They browse left to right etc ok…

but I don’t know how to refresh the data linked to the Data Control when I add or delete a record. If I quit and relaunch the changes are present, but not until I do that.

This has become more important now I’m executing a search feature… my search statement is executing but I don’t know how to make the results visible via the Data Control

sql = “SELECT * FROM bm_customers WHERE (customer_id) LIKE “_
+”(’”+ser_id+"’)"

7

best suggestion … DO NOT use the “Data Control”… its antique, and was a bad idea in the days of VB6
and if you are binding controls to a database, don’t do that either

8

Thanks Dave. Do you mind me asking what the best approach is in theory to read and write sqlite data between textfields and databases? Shame, I’ve put a good few late nights into that element this week… but greatly appreciate the honesty before I’ve gone either further…

9

SQL and Recordsets… you have full control over you database this way,

SQL syntax might vary based on the database engine you are using… and Recordsets are explained in the Xojo docs.

10

Thanks Dave, I’ll stick with SQLite then as that’s installed and working… and will check out Recordsets now. Thanks.

11

@Adrian Bolton I use a method that I pass the recordset to that is designed to update the UI. Now if I am using a container control, I add the method to that container control itself. that way as I update the CC, I can update the method that “fills in the data” in it.

doing SQL and Recordsets (like what @Dave S suggested) it gives you the most control.

Now I am going to throw you a curve ball. I use ActiveRecord which is an ORM. I make a class that relates back to each table. Then that class (think recordset in the above example) is the object I work with to fill in the data in the control. I still use a method that takes the one ActiveRecord Class as a parameter and I just read the properties (database fields) and fill in the controls(s) that way. Basically the same thing but instead of recordsets, I am dealing with objects which is more OO for my liking.

and I agree with others at avoiding the data bindings.

12

True Scott! I’m using also an own created ActiveRecord variant… that translate properties of an object by introspection and attributes to SQL statements and visa versa. Much easier to work with and no mistakes in SQL code :slight_smile:

I don’t use also the default data bindings…
Let the base form iterate over the inherited form - controls and checks the the data field property value and verify @ runtime if the object has the same propertyName… and bind…

13

Thanks Scott, I understand the theory of that and will implement it.

I am currently converting my add, delete, edit and search functions from bound controls to recordset. I have add working…

How can I globally open the database and make the XX.recordset (the XX part) globally referable? I’m currently having to open and close my database and define a XX.recordset within each of my functions. Surely I can open it once, close it at the end of the application’s use and be able to refer to a one time defined XX.recordset pointer throughout various actions without redefining it etc. (hope that makes sense). As it stands if I don’t redefine it, it just says XX isn’t recognised in other actions.

14

using RecordSets:
defined the database at the App level.
Then generate recordset(s) at the various method(s) to access/update those particular records.
You cant define a record set once and keep using it. The recordset is a PIT (point in time) copy of the data. the recordset doesnt automagically update in memory if the database data changes.

using ActiveRecord:
you would instance the object(s) of the various record(s) at the time of using them.
then every time you make an instance of the record, it is always current.

15

Thanks Scott. That makes sense. I’ve done that now. Understood re. RecordSets.
Writing the methods you referred to now, starting to get my head around it all :slight_smile: