El_Chonis SQLite lib HELP

[BackupUser]

Restored from previous forum. Originally posted by ricardo.

Im triyin to save into a database the values that i have in a listicon
using El Chonis lib for SQLite.

This is the TABLE:
Result = SQLiteExec("CREATE TABLE Mp3s (Id, Track, Song, Artist, Album, Year, Genre, Comment, Path)")

And when i try to store it it Crash:

Result = SQLiteExec("INSERT INTO Mp3s VALUES(" + Str(Con) + ", '" + GetGadgetItemText(#List,Val(Contador$),1) + "', '" + GetGadgetItemText(#List,Val(Contador$),2) + "', '" + GetGadgetItemText(#List,Val(Contador$),3) + "', '" + GetGadgetItemText(#List,Val(Contador$),4) + "', '" + GetGadgetItemText(#List,Val(Contador$),5) + "', '" + GetGadgetItemText(#List,Val(Contador$),6) + "', '" + GetGadgetItemText(#List,Val(Contador$),7) + "', '" + GetGadgetItemText(#List,Val(Contador$),8)+ "')")

Im having troubles to use variables to write and then querry the SQLite

Any idea?

Best Regards

Ricardo

Dont cry for me Argentina...

[BackupUser]

Restored from previous forum. Originally posted by dmoc.

Just got up so excuse the obvious questions:

- Is #List set?
- Is "Cont" a string? If so why?
- Any non-ascii characters in list text
- Can you do a sample dump of all GetGadgetItemText(#List,Val(Cont),???)

[BackupUser]

Restored from previous forum. Originally posted by ricardo.

Originally posted by dmoc

Just got up so excuse the obvious questions:

- Is #List set?
- Is "Cont" a string? If so why?
- Any non-ascii characters in list text
- Can you do a sample dump of all GetGadgetItemText(#List,Val(Cont),???)

#List is a ListIconGadget

Cont is long that why is use Str(Cont). Cont is the item number of the ListIcon.

I know i made a typo when putting here the code, but its not a string. The Contador$ is an string and its because i extract it from a file. But its should have the number of the item

No non-ascii characters

The data on the ListIcon is just the ID data of an Mp3, i mean

The name of the song, artist, album, year, track, genre (like 'Vocal' or 'Rock'), comments and the path and name of the mp3 ("c:\mymp3.mp3").

Its just a serie of columns in a ListIcon what im trying to save in a database, item by item.

My projects is exacylt this one:

Having a ListIcon with some info (artis, song, etc) and a checkbos on every line, then i want to store in a database the info only of the lines with the checkbox checked.
I already do all this and works fine, my only problem is that cant store properly info contained on variables.
Even making querries with variables has been a litle difficult.







Best Regards

Ricardo

Dont cry for me Argentina...

[BackupUser]

Restored from previous forum. Originally posted by Manolo.

Hi Ricardo,
You don't need variables for store the contents. You code is correct and work perfectly, but... you code have one bug.
You don't can build one string with one strig. You id is bad because only return one value and this is the Crash.

Code: Select all

Contador$="1"
b$=Str(Contador$) 
Debug b$

Regards,

Manolo

[BackupUser]

Restored from previous forum. Originally posted by ricardo.

Code: Select all

Contador$="1"
b$=Str(Contador$) 
Debug b$

Hi Manolo,

No, it was a typo here, now i correct it here.
In the code there was 2 different variables: Cont & Contador$

The one that uses Str(Cont) is a long, the string is the one that is used insed Val(Contador$).
I mean, that was a typo here in the forum, but not on my code and it crash.



Best Regards

Ricardo

Dont cry for me Argentina...

[BackupUser]

Restored from previous forum. Originally posted by El_Choni.

Hi, ricardo, could you send me some working code so I try to find out the problem? Sorry for the inconveniences.

El_Choni

[BackupUser]

Restored from previous forum. Originally posted by Saboteur.

Could you create the sql sentence in a string, and show the final SQL string with Debug or MessageRequester? It would be easy to find if there are some bug. I had a similar problem, but my SQL was longer 64k...

Win98 Athlon 1200 256DDR ATI RADEON 9000

[BackupUser]

Restored from previous forum. Originally posted by Manolo.

Hi Ricardo,
Ok. I think that the next code is solving, but....
You code:
SQLiteExec("INSERT INTO Mp3s VALUES(" + Str(Con) + ", '"

My Code:
SQLiteExec("INSERT INTO Mp3s VALUES('" + Str(Con) + "', '"

The code is more readable the next form:

sep.s="','" ; separator of variables and free errors in the SQL

SQLiteExec("INSERT INTO Mp3s VALUES('" + Str(Con)+sep+A$+sep+etc,etc

I prefer work with composed sql commands, for example:
....
Dim Codigo.s(8)

SQL.s="INSERT INTO Mp3s VALUES('"+Codigo(0)+"

Codigo(0)=Str(Cont)
For i=1 to 8
Codigo(i)=GetGadgetItemText(#List,Cont,i)
SQL+sep+Codigo(i)
Next
SQL+ "')"

MessageRequester("",SQL,0)
;-------------

This is minus code, more readable and permit to you see you SQL and sintax.

The next step in only

Result = SQLiteExec(SQL)

Regards,
Manolo

[BackupUser]

Restored from previous forum. Originally posted by ricardo.

Hi Manolo

Thanks!! With just a few changes it works 100%!!!!!!

Just for the recods i put here the code to store using variables and the code to querry using variables in SQLite.

This one let save variables (in this case the content of several subitems on a ListIcon) on a database using SQLite and EL_Chonis great lib.

Code: Select all

        sep.s="', '"
        Dim Codigo.s(8)
        Codigo(0)=Str(CCont)
        SQL.s="INSERT INTO Mp3s VALUES('"+Codigo(0)
        
        For i=1 To 8
        Codigo(i)=GetGadgetItemText(#List,Val(CheckBoxed),i)
        SQL = SQL+sep+Codigo(i)
        Next
        SQL = SQL+ "')"
        MessageRequester("",SQL,0)
        Result = SQLiteExec(SQL)

Querry:
This one let make a querry from a string inserted by the user on a StringGadget.
For making the querry to several columns try:

WHERE Artist LIKE " + Busqueda + " OR Song LIKE " + Busqueda

Here is the way to do it with out errors:

Code: Select all

  Querry.s = GetGadgetText(#String1)
  Busqueda.s = Chr(39) + Querry + "%"  + Chr(39)
  SQLQueery.s = "SELECT Song, Artist, Path FROM Mp3s WHERE Artist LIKE " + Busqueda
  Result = SQLiteGetTable(SQLQueery)
  If Result=#SQLITE_OK
      Rows = SQLiteRows()
      Cols = SQLiteCols()
      For i = 1 To Rows
        ;In this case will show me the 3th Cols that i ask that was Path
        MessageRequester("DATA " + Str(i) + " : " + Str(Rows),SQLiteData(i,2),0)
      Next i
  EndIf

Best Regards

Ricardo

Dont cry for me Argentina...

[BackupUser]

Restored from previous forum. Originally posted by Manolo.