SQLExample - Edit Data

outlawz

Member
Licensed User
Hi to you all. If i pick up SQLExample here on forum, i run it but i can´t edit data on database, even if i put my own DB. Is there any way i can open the connection with the database with rw previleges?

Thanks
 

outlawz

Member
Licensed User
Hi Erel, could be a error I make exporting from CSV to S3DB? I din´t try it on device, only on desktop. Could it permit rw on the device?
 

outlawz

Member
Licensed User
What can i do to change it on the database Erel?

I understand what you mean. I just changed the X with an V and that´s fine. Thanks Erel.
 
Last edited:

outlawz

Member
Licensed User
You will need to make an UPDATE query (with the right values) after each table update.

Thanks Erel. Can i use a connection between the table and the database (like on SQLExample) to work with your method Editable or is not compatible?
 

Cableguy

Expert
Licensed User
Longtime User
Olá Pedro,

Na ajuda online sobre a dll do SQL, encontrarás um link para a syntax do sql...
Usando a aplicação que te falei poderas testar os comando antes de os passar para o B4PPc...

Hi Pedro,

In the Online Help about the SQL Librarie, you will find a link to the sql sattement syntax...
using the app I told you about the other day, you can pre-test your commands before passing them to B4Ppc code..
 

outlawz

Member
Licensed User
Erel i figure it out, i investigate a little and everything is OK. My next problem is when you told me i need to make an UPDATE query (with the right values) after each table update, could i make only an update for the particulay row user changed?

Paulo então, long time no see.. Já consegui descobrir como compatibilizar a BD em SQL com a rotina do Erel para editar o conteúdo da tabela. No entanto todos os dados que mudo na tabela não são actualizados na BD, por isso tenho de fazer uma consulta de UPDATE sempre que alterar. Estava a pensar se, a cada update de campo, bastava apenas fazer o UPDATE de todos os campos dessa linha (do registo). Achas que dá?

Thanks/Obrigado
 

Cableguy

Expert
Licensed User
Longtime User
Pedro. estou a desenvolver para a minha empresa uma aplicação que vai trabalhar em cima de uma base de dados dedicada...
O que fiz foi criar as tabelas, em branco, e enviar comandas com o sqladmin para ver se estavam correctos..
Basicamente o que faço é o seguinte...
ccarrego a base de dados e leio a(s) tabela(s) que me interessam, e respectivo(s) campo(s)..
depois edito essa informação...
Depois disso passo a info para a base de dados...
A minha vantagem é que estou a trabalhar com tabelas pequenas...
Sim penso que poderas actualizar apenas os campos que editaste, mas terás que saber quais foram...
Poderás ter que fazer algumas comparações para saber se houve mudanças...
ou se souberes exactamente os campo a actualizar, melhor...e mais rapido...
N´~o te esqueças de fazer um VACUUM antes de fechar a conecção á base dados para reduzir o tamanho apeanas par o que esta ocupado...


Cutting a long story short, yes you can update specific fiels, as long as you know their names...
Sql syntax is very complex but also very versatile...
Be sure to VACUUM the bd before closing the conection in order to shrink the bd size to the real size, as it tends to keep enpty spaces from deleted values...
 

outlawz

Member
Licensed User
I will only write in English as all the persons envolved understand it. If any Portuguese does´t understand just write "Traduz" - Se algum Português precisar de tradução por favor apenas pedir que traduzo.

Paulo i will have a long way to became good. There is a sub on Erel routine very usefull:

'This sub will be called before setting any value in table1.
'The value will only be set if Validate returns true.
Sub Validate(column, value)
If column = "Amount" AND Not(IsNumber(value)) Then
Msgbox("Value must be a number!")
Return False
Else
Return True
End If
End Sub


By this way i will always known where to update. The VACUUM part (is the first time i ear about it) is not my priority righ now, because i will not delete anything, just insert stock counting. I will look for it later. Right now i must understand how everything works. Next fase i must populate a combobox and learn how to make a SQL command based on the choice from the combo. I will get back to you soon i guess.

Thanks to you all, your help is fantastic. Regards.
 

Cableguy

Expert
Licensed User
Longtime User
I will only write in English as all the persons envolved understand it. If any Portuguese does´t understand just write "Traduz" - Se algum Português precisar de tradução por favor apenas pedir que traduzo.

Paulo i will have a long way to became good. There is a sub on Erel routine very usefull:

'This sub will be called before setting any value in table1.
'The value will only be set if Validate returns true.
Sub Validate(column, value)
If column = "Amount" AND Not(IsNumber(value)) Then
Msgbox("Value must be a number!")
Return False
Else
Return True
End If
End Sub


By this way i will always known where to update. The VACUUM part (is the first time i ear about it) is not my priority righ now, because i will not delete anything, just insert stock counting. I will look for it later. Right now i must understand how everything works. Next fase i must populate a combobox and learn how to make a SQL command based on the choice from the combo. I will get back to you soon i guess.

Thanks to you all, your help is fantastic. Regards.

You have my e-mail, I'll be here untill at least midnight...
 

outlawz

Member
Licensed User
Thanks again Paulo for availability. Why do you think that this doenst work for me:

cmd.CommandText="Select ubic From inventario Group By ubic Order By ubic Asc"
reader.Value=cmd.ExecuteReader
do while reader.ReadNextRow=true
combo.add(reader.GetValue(0))
loop

And this is my code:

Sub App_Start
Form1.Show
Con.New1
Reader.New1
Tree.New1("Form2", 5, 5, Form2.Width - 10, Form2.Height - 10)
Node.New1
Cmd.New1("",con.Value)
Con.Open("Data Source = " & AppPath & "\inventario.s3db") 'Opens a connection with the database.
AddEvent("btnExecute",Click,"mnuExecute_Click") 'The button and the menu use the same sub.
EditableTable.SetEdit("Main.Table1","Main.Validate","")
End Sub

Should i create a new reader with other name? I did that and nothing. Where should i find out what is a reader?

Thanks
 

Cableguy

Expert
Licensed User
Longtime User
Try to enclosure the Ubic and inventario words in single quotes...
 

Cableguy

Expert
Licensed User
Longtime User
a very small Copy-Paste error from you...

B4X:
cmd.CommandText="Select 'ubic' From 'inventario' Group By 'ubic' Order By 'ubic' Asc"
   [COLOR="Blue"]Reader.Value=Cmd.ExecuteReader[/COLOR][COLOR="SeaGreen"] 'You forgot this part[/COLOR]
   Do While reader.ReadNextRow=True
   ComboBox1.add(reader.GetValue(0))
    Loop
 

outlawz

Member
Licensed User
Hi Paulo, it is not a copy/paste error. I tried the command (Reader.Value=Cmd.ExecuteReader) on other parts of the code. It works however only populates the combo with the column name "ubic" and not with the fiels values. I tried, as you recommend, the SQL instruction first on the sqliteadmin and works. I need to try harder.

Thanks
 

Erel

B4X founder
Staff member
Licensed User
Longtime User
You should have a primary key in your table. This field will identify each row and will allow you to make an update to that specific row.
Your query will look like:
B4X:
UPDATE table SET col1='value1', col2=... WHERE primaryfield = 'rowid'
primaryfield is the primary key column and rowid is the value of that specific row.
 
Top