Sign in to follow this  
Followers 0
Edifice

Getting SQLite ROWID's and adding them to combobox or string?

10 posts in this topic

#1 ·  Posted (edited)

Hello

I'm looking to write a rutine to go through my SQLite database and return the 1st colum of each row as a string (or the ROWID if you will), seperated by a |

I searched the forums and helpfiles, and if this info already exists, I know I can't find it ^_^ - If you can please post a link :)

I'm kinda stuck, and hoping for a little help - thx :)

Edited by Edifice

Share this post


Link to post
Share on other sites



Hello

I'm looking to write a rutine to go through my SQLite database and return the 1st colum of each row as a string (or the ROWID if you will), seperated by a |

I searched the forums and helpfiles, and if this info already exists, I know I can't find it ^_^ - If you can please post a link :)

I'm kinda stuck, and hoping for a little help - thx :)

Do you already have a [rowid] for each row? If not, then just count the number of rows, and create your own ID using a loop.

Thanks,

Jarvis


AutoIt Links

File-String Hash Plugin Updated! 04-02-2008 Plugins have been discontinued. I just found out.

ComputerGetInfo UDF's Updated! 11-23-2006

External Links

Vortex Revolutions Engineer / Inventor (Web, Desktop, and Mobile Applications, Hardware Gizmos, Consulting, and more)

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

Do you already have a [rowid] for each row? If not, then just count the number of rows, and create your own ID using a loop.

Thanks,

Jarvis

Well I use this for creating new entries:

Func dataadd($id)
    MsgBox(0, "Test", $color & $accesory)
    Local $retarr
    $str1 = GUICtrlRead($combo_modtager)
    $str2 = "Time: " & $time & " - " & "Date: " & $date
    $str3 = GUICtrlRead($input_navn)
    $str4 = GUICtrlRead($input_phonenumber)
    $str5 = GUICtrlRead($input_email)
    $str6 = GUICtrlRead($combo_indleveret)
    $str7 = GUICtrlRead($chkbx_garanti)
    $str8 = GUICtrlRead($combo_faknr)
    $str9 = $color
    $str10 = GUICtrlRead($combo_brand)
    $str11 = GUICtrlRead($diversebskrvls)
    $str12 = $accesory
    $str13 = GUICtrlRead($diversetlbhr)
    $str14 = GUICtrlRead($fejlbeskrivelse)
    $str15 = GUICtrlRead($input_antaltimer)
    $str16 = GUICtrlRead($input_hrdwr1)
    $str17 = GUICtrlRead($input_hrdwr1price)
    $str18 = GUICtrlRead($input_hrdwr2)
    $str19 = GUICtrlRead($input_hrdwr2price)
    $str20 = GUICtrlRead($input_hrdwr3)
    $str21 = GUICtrlRead($input_hrdwr3price)
    $str22 = GUICtrlRead($input_hrdwr4)
    $str23 = GUICtrlRead($input_hrdwr4price)
    $str24 = GUICtrlRead($input_hrdwr5)
    $str25 = GUICtrlRead($input_hrdwr5price)
    $str26 = GUICtrlRead($input_ialtprice)
    _SQLite_QuerySingleRow($dbn,"SELECT id FROM datas WHERE id='"&$id&"'",$retarr)
    If $retarr[0] <> "" Then
        _SQLite_Exec($dbn,"UPDATE datas SET modtager='"&$str1&"', timeanddate='"&$str2&"',navn='"&$str3&"',telefonnummer='"&$str4&"',email='"&$str5&"',indleveret='"&$str6&"',garanti='"&$str7&"',fakturanummer='"&$str8&"',color='"&$str9&"',brand='"&$str10&"',diversebeskrivelse='"&$str11&"',accesory='"&$str12&"',diversetlbhr='"&$str13&"',fejlbeskrivelse='"&$str14&"',antaltimer='"&$str15&"',reservedel1='"&$str16&"',pris1='"&$str17&"',reservedel2='"&$str18&"',pris2='"&$str19&"',reservedel3='"&$str20&"',pris3='"&$str21&"',reservedel4='"&$str22&"',pris4='"&$str23&"',reservedel5='"&$str24&"',pris5='"&$str25&"',ialtprice='"&$str26&"' WHERE id='"&$id&"'")
    Else
        _SQLite_Exec($dbn,"INSERT INTO datas (id,modtager,timeanddate,navn,telefonnummer,email,indleveret,garanti,fakturanummer,color,brand,di

ver
sebeskrivelse,accesory,diversetlbhr,fejlbeskrivelse,antaltimer,reservedel1,pris1,reservedel2,pris2,r


eservedel3,pris3,reservedel4,pris4,reservedel5,pris5,ialtprice) VALUES ('"&$form_num&"','"&$str1&"','"&$str2&"','"&$str3&"','"&$str4&"','"&$str5&"','"&$str6&"','"&$str7&"','"&$str8&"','"&$str9&"','"&$str10&"','"&$str11&"','"&$str12&"','"&$str13&"','"&$str14&"','"&$str15&"','"&$str16&"','"&$str17&"','"&$str18&"','"&$str19&"','"&$str20&"','"&$str21&"','"&$str22&"','"&$str23&"','"&$str24&"','"&$str25&"','"&$str26&"');")
    EndIf
EndFunc

So I guess the answer to your question would be: yes :)

Edited by Edifice

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

Edifice,

Why aren't you using AutoIncrement (that's a function of most databases)?

Edit01: Here's a good link... http://www.sqlite.org/autoinc.html

Thanks,

Jarvis

Edited by JSThePatriot

AutoIt Links

File-String Hash Plugin Updated! 04-02-2008 Plugins have been discontinued. I just found out.

ComputerGetInfo UDF's Updated! 11-23-2006

External Links

Vortex Revolutions Engineer / Inventor (Web, Desktop, and Mobile Applications, Hardware Gizmos, Consulting, and more)

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

Edifice,

Why aren't you using AutoIncrement (that's a function of most databases)?

Edit01: Here's a good link... http://www.sqlite.org/autoinc.html

Thanks,

Jarvis

After reading a bit about AutoIncrement (:)), the first answer that pop into my head is that I didn't know it ever existed.

That said, I really like the fact that I choose the ROWID myself (As this is still my first SQLite App - I just assume that is the case, correct me if I'm wrong.

Would the usage of AutoIncrement make this any easier?

Edit: I did read that exact link. However as far as I understood that is for creating ROWID's - not for fetching them? (again, correct me if I'm wrong)

Edited by Edifice

Share this post


Link to post
Share on other sites

Edifice,

Maybe provide me a text version of what exactly you're trying to do, and I can make some database recommendations.

I would highly recommend a Primary Key AutoIncrement. You can then retrieve it as whatever you want to call that column of data. I usually call it something as would follow...

tblPages
    PageID = Primary Key AutoIncrement
    Name
    Description
    ColorID = Foreign Key

tblColors
    ColorID = Primary Key AutoIncrement
    Name
    Value

The above would give you the ability to have a table (list) of colors that are associated with the page simply by it's ID. That's usually what unique row identifiers are used for. Not to mention being able to look up the data more easily.

I hope this helps some,

Jarvis


AutoIt Links

File-String Hash Plugin Updated! 04-02-2008 Plugins have been discontinued. I just found out.

ComputerGetInfo UDF's Updated! 11-23-2006

External Links

Vortex Revolutions Engineer / Inventor (Web, Desktop, and Mobile Applications, Hardware Gizmos, Consulting, and more)

Share this post


Link to post
Share on other sites

Edifice,

Maybe provide me a text version of what exactly you're trying to do, and I can make some database recommendations.

I would highly recommend a Primary Key AutoIncrement. You can then retrieve it as whatever you want to call that column of data. I usually call it something as would follow...

tblPages
    PageID = Primary Key AutoIncrement
    Name
    Description
    ColorID = Foreign Key

tblColors
    ColorID = Primary Key AutoIncrement
    Name
    Value

The above would give you the ability to have a table (list) of colors that are associated with the page simply by it's ID. That's usually what unique row identifiers are used for. Not to mention being able to look up the data more easily.

I hope this helps some,

Jarvis

The reason I did what I did was to be able to sort all the forms easily, as I have phonenumber & date & time as my id-string. The aim was to be able to look through these id's in a combo - easily distinguishing identical phonenumbers.

However, if you feel that there is an easier way to do it I will be happy to learn of it! :)

The program has quit a bit of lines, so I just attached it. I hope that's what you ment by "provide me a text version of what exactly you're trying to do".

What I want to achive is to be able to chose the data entries by phonenumber, followed by date & time. - maybe in a combobox, but again if you have a better idea I'm all ears! :)

By the way, sorry for all the danish Dim $names - I try to do it all in english by sometimes I just forget :huh2:

Thanks for all the help! - This is really what makes auto-it the greatest! ^_^

Main_new.au3

Share this post


Link to post
Share on other sites

Edifice,

What I meant by a "text" description was just describe in detail what you would like to accomplish. Providing the source code may help as I try to help you implement a solution so not a bad idea. I'm not quite sure what you're after, but I will try to spell out for you what I feel you're trying to accomplish. We'll narrow it down till I have a good understanding then I may be able to offer some suggestions as to what would help or hurt in this situation.

It would seem you're trying to easily find duplicate phone numbers in your database. What is your purpose for finding these duplicates? Do you realize that some recursive SQL queries would allow you to know whether or not you have duplicate entries? SQL is quite a powerful language. It should be able to almost if not be able to put the data into the proper ordering and smallest set of usable data possible.

Don't worry about using non-English in your scripts :). The important parts are mostly the functions and such anyways. You are supposed to understand your script, but I appreciate any English :).

Let me know if I have your problem narrowed down, and I'll work towards a good solution.

Thanks,

Jarvis


AutoIt Links

File-String Hash Plugin Updated! 04-02-2008 Plugins have been discontinued. I just found out.

ComputerGetInfo UDF's Updated! 11-23-2006

External Links

Vortex Revolutions Engineer / Inventor (Web, Desktop, and Mobile Applications, Hardware Gizmos, Consulting, and more)

Share this post


Link to post
Share on other sites

Edifice,

What I meant by a "text" description was just describe in detail what you would like to accomplish. Providing the source code may help as I try to help you implement a solution so not a bad idea. I'm not quite sure what you're after, but I will try to spell out for you what I feel you're trying to accomplish. We'll narrow it down till I have a good understanding then I may be able to offer some suggestions as to what would help or hurt in this situation.

It would seem you're trying to easily find duplicate phone numbers in your database. What is your purpose for finding these duplicates? Do you realize that some recursive SQL queries would allow you to know whether or not you have duplicate entries? SQL is quite a powerful language. It should be able to almost if not be able to put the data into the proper ordering and smallest set of usable data possible.

Don't worry about using non-English in your scripts :). The important parts are mostly the functions and such anyways. You are supposed to understand your script, but I appreciate any English :).

Let me know if I have your problem narrowed down, and I'll work towards a good solution.

Thanks,

Jarvis

I properly have duplicate phonenumbers but it doesn't really matter as my ID will be unique coz of the date and time (assuming i'm still using that)

Here's my ultimate goal:

I've created that GUI (dunno if you looked at it) to input data. Then I've saved that data into my SQLite database (may not be optimal way to do so, but what the heck - it works).

Now I'm trying to get the data back on-screen and I just need an easy way to select a row in the database to open and display. However - we are not talking about 5 entries - more like 5-10 each day for all eternity :huh2:. As it is today we use the phonenumber as ID as we scamble it on a piece of paper, and to make this transition (from analog to digital ^_^) easier I thought I'd do the same thing. Putting it, sorted by number, into a combobox just seemed natually.

So basically I'm just trying to find the best possible way to sort my entries in the GUI, to make it easier to select them.

I was kinda hoping you could just run a rutine on the datebase to get a string containing all of the id's and then put it into a combo, although it would be a very long string when I come to think it through :lmao:

Share this post


Link to post
Share on other sites

Just one last thing: It's important that you don't actually have to type the whole IDnumber to find the row containing data. Although it would be okay to have to type the entire phonenumber, however not optimal.

Did a search before this thread was started, and found this: http://www.autoitscript.com/forum/index.ph...56&hl=ROWID

However I'm not sure how to use SQL Match, and not sure how to get the match's into the GUI to select from. SQL Match would also, so it seems anyway, allow me to search by name, which would also be an advantage, but not necessary

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0