Sqlite

I am having problems understanding syntax of using sqlite.

INSERT INTO WorkTable ( Internal, ColdOd, ColdFp )
VALUES ( textbox1.text, textbox2.text, textbox3.text)

I think the above is laid out right but I don't understand how to use it in basic4ppc. I have gotten bits and pieces from examples to work but am still having trouble. Would appreciate any help with this. :sign0085:

dennishea :sign0148:

There is no special Basic4ppc SQL syntax.
Basic4ppc just passes the command string to the SQLite engine.
You should test your actual command string using msgbox(cmd.CommandText) or using a breakpoint.
In your example I guess you are adding strings and not numbers so you need:
cmd.CommandText = "INSERT ... VALUES('" & textBox1.Text & "','" & textbox2.Text & "','" & textBox3.Text & "')"
Notice the small apostrophes.

@Erel

I have tried to follow your example and the first problem was to get it to unremark the last half of the code, there is a double quote behind textbox1.text that doesn't belong there, but it still doesn't add my data to the db. I have converted a csv file to a sql database and have been able to read it into a table but that is as far as I have been able to get. I'm trying to learn how to add and delete records with no success. I have been to a few web sites that show the sql statements and I think I understand them and at this point I think it's basic4ppc that I'm confused with. I know that you have done an excellent job with basic4ppc. I guess it's one of those blond moments when I just can't see the forest for the trees. Please bare with me. :sign0085:

dennishea

edit:
Yay Got adding a record sorted out and working.

Thank you for the correction.
Can you show some segment of the code?

Yes
I've got the insert statement working and now I'm fighting with update. I pretty much tried to copy the demosql example. This seems to work all the way up to cmd.executenonquery then it kicks out

cmd.executenonquery
error description:
sqlite error
near "0000":snytax error

The textbox1 has AA 0000 in my where statement, I don't know if that's what it's refering to or what. I thought I would keep it simple to get my feet wet but in the end I would like to update the whole record not just one field.

Sub btnUpDate_Click
text="update WorkData set ColdFP = " & textbox2.Text
text=text & " where Internal = " & textbox1.Text
cmd.CommandText=text
Msgbox(cmd.CommandText)
cmd.ExecuteNonQuery
End Sub

Thanks for your support.

dennishea
edit:
@Erel
With a lot of reading I have a handle on the basics with sqlite, "creating, inserting, updating, and deleteing". Between your example above and your other example of northwind.sl3 I finally sorted it out. Thanks Again Erel for your support.

Well I have a new problem. My program runs fine on pc and ppc as long as I run them from the ide's but when I compile them device gives a nullreference error and pc says something about sqldesktop.dll format not being right.

:sign0085:

dennishea

Hi dennishea,

since the sql library needs .Net CF2, have you added a ApplicationName.exe.config (as a copy from Dbasic.exe.config) to your compiled application?


specci48

Hi specci48.
Yes, I checked and there is one there. I am new to these librarys. I tried to read as much as I can but you can rely on murphy's law, if something can go wrong it will. I hope it is just a simple over site on my part. Thanks for your suggestion.

dennishea :sign0148:

When distributing an SQL compiled application you should also distribute:
- A config file (for .Net CF 2.0)
- System.Data.SQLite.dll (there is a desktop and a device version files).

There is an example of a compiled sql application here:
http://www.b4x.com/files/SQLCompiled.zip
It includes a Device and a Desktop folders with the files required (TreeView.dll and Northwind.sl2 are not required).

Hi Erel, I checked the directorys and and tried to duplicate what I thought I was seeing. The only thing that wasn't in my directory was a file named like my DeskData.exe with config on the end. When I installed 5.50 I didn't uninstall the last version of basic4ppc. I wasn't sure what file to rename to my program name with config on the end so I tried dbasic.exe.config and when that didn't work there was a basic4ppc.exe.config and I tried that with no luck. The dbasic.exe.config gave way more error's then I can type about. The basic4ppc.exe.config version just says I don't have the right version of .net framework. My program compiled with no errors for both pc and ppc.
Both uncompiled versions work on pc & ppc. I know that it has to be something I am over looking.

dennishea :sign0148: :sign0085: :sign0104:

edit:

I did rename both config files one at a time to DeskData.exe.config. I think that's what I am suppose to do?
I need system.data.sqlite.dll , SQLDeskTop.dll , DeskData.exe , deskData.exe.config I think all in the same directory.

Did the SQLCompiled example (from the above post) work on your device?

Erel the sqlexample.exe works fine on device and on desktop. When you asked me that question I didn't even to try that. I copied the whole folder to my device and all ran well, that answers the question about net on the device also on my desktop so now am I doing wrong in my program. It works in the ide's on both but not after I compile it. Well back to searching.

dennishea :sign0148:

edit:
I have the desktop compiled version working on the desktop. I had some of the device files on the desktop and they don't work to well. Now that I understand the file system I believe that I have the device files right and now it gives error loading program nullreferenceexception. Now I'm lost again.

edit again:
Ok I recompiled my program after verifying that all the files were in place and wala it works. :sign0060: The program isn't finish by any means and the only thing at this point that got me baffled is this command,

Sub btnSort_Click
text = "Select Internal FROM WorkData ORDER BY Internal"
cmd.CommandText=text
' Msgbox(cmd.CommandText)
cmd.ExecuteNonQuery
cmd.CommandText = " select * from WorkData "
cmd.ExecuteTable("table2",500)
frmRecord.Close
frmTable.Show
End Sub

When I push the button it does everything in the sub without popping up a error except sorting the data base. Thanks ahead of time for any help pointing out what I'm doing wrong.

dennishea

Is Table2 empty before cmd.ExecuteTable?
If not then try adding Table2.Clear before this line.

I tried table2.clear before cmd.ExecuteTable("table2",500) and it still respondes the same. I am trying to sort the database mydata1.sl3 table WorkData so when I close it it will be sorted ascending on col Internal. Sqlite is suppose to be able to do this I think. I can get the table to sort but thats not what I want, when I close the program for this session I want the database to be sorted. I hope this explains what I am trying to do and hope it can be done.

dennishea

p.s. Please forgive me if I misinterpute what you are trying to tell me.

dennishea said:

I want the database to be sorted.

Click to expand...

I think you have an incorrect picture of how a SQL database works. The database is just a collection of data but it is not like a spreadsheet that you can sort and save sorted. The native order of data in a SQL database is not visible or relevant or alterable by users - the internal database engine may order it in some fashion for efficiency but you don't see this. If you want to see data from a SQL database then you need to SELECT it into a table and if you want it sorted then you must specify ORDER BY in that selection.

Thanks agraham. I didn't realize that you couldn't rearrange the database itself. Again thanks for your help.

dennishea