SQLExample - Edit Data

[outlawz]

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

 

[Erel]

The connection is opened with read/write access by default.

 

[outlawz]

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?

 

[Erel]

It is r/w on both the desktop and the device. What error do you get?

 

[outlawz]

I just can´t edit data on the table.

 

[Erel]

The table control is readonly. If you like to use an editable table you can use this module: http://www.b4x.com/forum/code-samples-tips/2609-editable-table-device-desktop.html
However changing data in the table will not automatically change the data in the database.

 

[outlawz]

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.

 

[Erel]

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

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

 

[outlawz]

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]

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]

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]

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]

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]

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]

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]

Try to enclosure the Ubic and inventario words in single quotes...

 

[outlawz]

I can´t Paulo, could you take a look when you can? No rush. Thanks.

 

[Cableguy]

a very small Copy-Paste error from you...

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]

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]

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:

UPDATE table SET col1='value1', col2=... WHERE primaryfield = 'rowid'

primaryfield is the primary key column and rowid is the value of that specific row.