[Gambas-user] Can't exec an sql string

[Benoit Minisini]

On Thursday 26 October 2006 15:14, Fabricio Silva wrote:
> I was trying this when saving data:
>
> txtprice.Text = Replace$(txtprice.Text, ",", ".")
> then executing the sql to save. ok.
> So when the user types 1,25 it's saves 1.25 into a float type field.
> Perfect.
>
> But my problem is when showing the saved data. I tryed a tableview.
> ===============================================
> PUBLIC SUB show_data()
> DIM sql AS String
>   WITH tbvcadprodutos
>     .rows.count = 0
>     .columns.count = 4
>     .columns[0].text = "Código"
>     .columns[0].Width = 50
>     .columns[1].text = "Descrição"
>     .columns[1].Width = 250
>     .Columns[2].Text = "Quantidade"
>     .columns[2].Width = 80
>     .columns[3].text = "Preço"
>     .columns[3].Width = 70
>
>   END WITH
>   sql = "SELECT codigo, descricao, quantidade, preco FROM `produto` ORDER
> BY `codigo` ASC"
>   global.preencher(tbvcadprodutos, sql)
>
> =====================================================
> Procedure preencher
> =====================================================
> PUBLIC SUB preencher(tbv AS tableview, qry AS String)
>   DIM i AS Integer
>   i = 0
>   rs1 = db.exec(qry)
>   DO WHILE i <= rs1.Fields.Count
>     tbv.rows.count = 0
>     IF rs1.count <> 0 THEN
>       tbv.columns.count = rs1.fields.count
>       tbv.rows.count = rs1.count
>     END IF
>   i = i + 1
>   rs1.MoveNext
>   LOOP
> END
> ========================================================
> and to show the data
> ========================================================
> PUBLIC SUB tbvcadprodutos_Data(Row AS Integer, Column AS Integer)
>
>   arrtable[0] = "codigo"
>   arrtable[1] = "descricao"
>   arrtable[2] = "quantidade"
>   arrtable[3] = "preco"
>   global.rs1.MoveTo(Row)
>
>   tbvcadprodutos.data.Text = Str(global.rs1[arrtable[Column]])
>
> END
> ==================================================
>
> And the 'preco' values are shown wrongly.
> 34,00 typed by the user is displayed 34.
> and 89,56 is displayed 89.56.
>
> So I tried another way of showing the data:
>
> tbvcadprodutos.data.Text = Format(global.rs1[arrtable[Column]], "#.00")
>
> "Type mismatch: Wanted Float got String instead" error appears.
> ===================================================
>
> I'm stuck in this. I tryed datasource and databrowser pair too... but I
> can't manipulated this can I?
>
> Plz help me out in this. I'm desesperated. :)
>
> Best Reards
>
> Fabricio

You are putting numeric values inside a database string field. So Gambas 
automatically converts the numeric value into a string by using the CStr() 
function. CStr() uses US localization.

Then, when you fill your TableView, you get a string from the database: the 
result of CStr() applied to the number you wanted to store. And of course, if 
you use Format(), the function complains because it gets a string instead of 
a floating point value.

There are two solutions:

1) Using a database numeric field, and Str() or Format() to convert the value 
in TableView_Data.

2) Keeping with your string field, but then you must use Str() or Format() to 
store the data (so that you use your localization, not the US one). Then you 
display the data in TableView_Data as it is stored.

Regards,

-- 
Benoit Minisini