Sqlite

[dennishea]

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.

dennishea

[Erel]

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.

[dennishea]

@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.

dennishea

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

[Erel]

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

[dennishea]

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.

[dennishea]

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.



dennishea

[specci48]

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

[dennishea]

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

[Erel]

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.basic4ppc.com/files/SQLCompiled.zip
It includes a Device and a Desktop folders with the files required (TreeView.dll and Northwind.sl2 are not required).

[dennishea]

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

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.