[VB.NET] Use a datagridview linked to a firebird db

please post the table definition

Sorry for the late answer I dind't see your post

here is the how the filed is defined in firebird

STK_PRESALES D_BOOLEAN NOT NULL

D_BOOLEAN DEFINED  AS DOMAIN > 

       CREATE DOMAIN D_BOOLEAN AS SMALLINT

        DEFAULT 0

        NOT NULL
CHECK ((VALUE = 0) OR (VALUE = 1));

your datatype is an integer, hence the vb behaviour, I suggest you put a pointer question to this one in the vb area as it's really a vb question rather than interbase/firebird

is the datatype set correct for you ?
 I serch in several forum and webpage and it was everywhere defined that firebird recognize the domain with boolean in its name as a boolean datatype and that it should be defined as smallint or varchar (false true).

Assuming that what is returned by your db for Boolean False is 0 and for Boolean True is -1 (or anything other than 0) there are a couple of things you might like to try.  One is to alter the datatable definition so that the DataType of the relevant column is read by VB as Boolean rather than Integer.  If your dataset/datatable is strongly typed you maybe able to do this in the dataset designer.  If not, you would have to do it in code before the table was loaded.  I've just done this successfully with the following code

        Dim dt As New DataTable
        Dim dc As New DataColumn
        dc.DataType = GetType(Int32)
        dc.ColumnName = "ID"
        dt.Columns.Add(dc)
        Dim dc1 As New DataColumn
        dc1.DataType = GetType(Boolean)
        dc1.ColumnName = "MyBool"
        dt.Columns.Add(dc1)
        da.Fill(dt)
        dg1.DataSource = dt

with an Access datatable consisting of two Fields ID (AutoNumber Primary Key) and MyBool (Integer) containing the following data

ID      MyBool
1      0
2      1
3      -1
4      0
5      5
6      0
7      -1

The datagrid showed the MyBool column as checkboxes with the 0 values unchecked and all other values checked.

The other would be to add an expression column to your table after the data was loaded, on the lines of MyBool <> 0, and set the datagrid to display that column rather than the MyBool column.

I did my test using VB.NET 2003 - hence the datagrid rather than the DataGridView that you refer to.  But the principles would be the same.

Roger

"it was everywhere defined that firebird recognize the domain with boolean in its name as a boolean datatype and that it should be defined as smallint or varchar (false true)."

I don't know where you found this but it's not true as firebird doesn't have a boolean datatype, you can 'pretend' to have a boolean datatype by defining a domain such as you have done, which will constrain it's contents but you logic in the database such as a stored procedure would still have to be

if (varname = 0) then ....

if (varname) will not work

Sancler -> Thx for you ansew I will try this whe  going home after work and keepyou posted tomorrow.

NickUpson ->  What I would like to say with this, is that Firebird recognize the domain name including %Boolean% as a boolean var type. When I check in the formview of any table containing a boolean domain EMS displays it as a checkbox field. I'm sure that the real outpout of that field is an integer and not a boolean.


 

EMS may consider it a boolean but firebird itself cannot, hence the confusion

How do I have to do insert the data of my firebird table in a datable?
i try to change the datatype of the field in question here but VSTZ005 returns and error msg explaining that it's not possible to change the datatype of a datatable that has value? dixit 'Cannot change DataType of a column once it has data.'
here how did I try to do

       Dim dastkpre As New FbDataAdapter()
       Dim dt As DataTable
       dastkpre.SelectCommand = New FbCommand("select * from PreSales", conn)
       dastkpre.Fill(ds, "Preview")
        dt = ds.Tables("Preview")
        dt.Columns("Reserve").DataType = GetType(Boolean)
        '    ds.Tables("Preview").Columns("RESERVE").DataType = GetType(Boolean)
        '    Me.DataGridView1.DataSource = ds.Tables("Preview")
        Me.DataGridView1.DataSource = dt
        Me.DataGridView1.Columns("STK_ID").Visible = False
        DataGridView1.Columns("MARQUE").ReadOnly = True
        DataGridView1.Columns("MODELE").ReadOnly = True
        DataGridView1.Columns("DESCRIPTION").ReadOnly = True
        DataGridView1.Columns("PRIX").ReadOnly = True
        DataGridView1.Columns("REMISE").ReadOnly = True

I'm not really sure of the right order to write the code

1st I declare my variable
     Dim dastkpre As New FbDataAdapter()
     Dim dt As New DataTable
     Dim dc As New DataColumn

2nd How to I fill the datatable with the dataadapter ?

3rd do I have to define the var type of each column and how ?

thx for your patience and support

HI,

I try to adapt your solution and after some constraints it works fine now.

I just have the problem when clcking again on the button that update the datagridview, VST said that the table already exists in the dataset
; I look forward to solved that

PS : If you have some advises to simplify the code here below do not hesitate

Hereunder the code after solution

 Dim dt As New DataTable
 dastkpre.SelectCommand = New FbCommand("select * from PreSales", conn)
        ds.Tables("Preview").
        dt.TableName = "Preview"
        ds.Tables.Add(dt)
        Dim dc As New DataColumn
        dc.DataType = GetType(Int32)
        dc.ColumnName = "STK_ID"
        dt.Columns.Add(dc)
        Dim dc1 As New DataColumn
        dc1.DataType = GetType(String)
        dc1.ColumnName = "MARQUE"
        dt.Columns.Add(dc1)
        Dim dc2 As New DataColumn
        dc2.DataType = GetType(String)
        dc2.ColumnName = "MODELE"
        dt.Columns.Add(dc2)
        Dim dc3 As New DataColumn
        dc3.DataType = GetType(String)
        dc3.ColumnName = "DESCRIPTION"
        dt.Columns.Add(dc3)
        Dim dc4 As New DataColumn
        dc4.DataType = GetType(Double)
        dc4.ColumnName = "PRIX"
        dt.Columns.Add(dc4)
        Dim dc5 As New DataColumn
        dc5.DataType = GetType(Double)
        dc5.ColumnName = "REMISE"
        dt.Columns.Add(dc5)
        Dim dc6 As New DataColumn
        dc6.DataType = GetType(Boolean)
        dc6.ColumnName = "RESERVE"
        dt.Columns.Add(dc6)

        dastkpre.Fill(dt)
        Me.DataGridView1.DataSource = dt

Thx for you support and great help.

SCVO

Your code looks fine except that the line

        ds.Tables("Preview").

means nothing and will do nothing - you should delete it.

I am not sure what "the button that update the datagridview" is.  Does it run ALL this code again?  That would give problems.  If that is the case, the best approach would be to put all this code - except for

        dastkpre.Fill(dt)

- in a separate sub that you call just once, BEFORE the first time you load your data, and then put just

        dastkpre.Fill(dt)

in that button's code.

Roger

.

T'hats exaclty what I deed and it wortk's fine now.

I put the whole code in the form_load sub and only the ds.table("Preview").clean, dastkpre.fill(dt).

On Click, the button will flag the field  "RESERVE" in the table stock, and update the datagridview with a view ("presales") representing all the flagged rows.  if I don't clean the dataset from the table "Preview", the dgv will display the old data added by the news ones each times I click on the button so I cannot delete the ds.tables("Preview").

Except for the filed "RESERVE", is there no way to insert the diffrents new columns in a single loop for each tablle field ?

I assume by "ds.table("Preview").clean" you mean "ds.table("Preview").clear".  On that assumption, you shouldn't need to redefine the table every time.  The DataTable.Clear method empties the datatable of data but does not affect what columns are defined for it.  So .Clear followed by .Fill should just re-fill the table you defined in the form_load sub with a fresh set of data.

Or am I misunderstanding the problem?

Roger

yest it's ds.tables("Preview").clear
and No that's exactly what I do. I clear the datable to refill it next, it's lonely way I found to refresh the datagridview wihout adding each time the new record to the old one.

To resume, the issue of refreshing the dgv is solved for me. May be will I learn more about how to work with classes and I will work on improving the perf of my code