?
Solved

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

Posted on 2006-05-16
16
Medium Priority
?
8,005 Views
Last Modified: 2013-12-09
Hi,

I use a datagridview linked to a dataset loaded by a table stored on a firebird db.
The table contains a coluimn deifne as boolean but in the datagridview vb.net displays it as textbox and not as a checkbox column.

I thik the datagridview considers the firebird boolean type as a single integer

How do I have to do to convert that column as checkbox

Regards

SCVO
0
Comment
Question by:SCVO
  • 8
  • 4
  • 4
16 Comments
 
LVL 19

Expert Comment

by:NickUpson
ID: 16692724
please post the table definition
0
 

Author Comment

by:SCVO
ID: 16768721
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));
0
 
LVL 19

Expert Comment

by:NickUpson
ID: 16769145
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
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:SCVO
ID: 16769266
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).



0
 
LVL 34

Expert Comment

by:Sancler
ID: 16783833
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
0
 
LVL 19

Expert Comment

by:NickUpson
ID: 16784186
"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
0
 

Author Comment

by:SCVO
ID: 16784620
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.


 
0
 
LVL 19

Expert Comment

by:NickUpson
ID: 16784654
EMS may consider it a boolean but firebird itself cannot, hence the confusion
0
 

Author Comment

by:SCVO
ID: 16786116
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
0
 
LVL 34

Accepted Solution

by:
Sancler earned 2000 total points
ID: 16786325
You will have to define the table BEFORE you fill it to use the first method I described.  As my example said

        Dim dt As New DataTable
        Dim dc As New DataColumn
        dc.DataType = GetType(Int32)
        dc.ColumnName = "ID"
        dt.Columns.Add(dc)
        'and so on for each column
        'until
        da.Fill(dt)
        dg1.DataSource = dt

In your case it looks like you would have to do this for (at least) columns for "RESERVE", "STK_ID", "MARQUE", "MODELE", "DESCRIPTION", "PRIX" and "REMISE".  Also, if you want to use your dataadapter to fill the table you have defined you would need, besides creating and adding the columns as above, to add

        dt.TableName = "Preview"
        ds.Tables.Add(dt)

to the code before

        dastkpre.Fill(ds, "Preview")

Roger
0
 

Author Comment

by:SCVO
ID: 16792526
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
0
 

Author Comment

by:SCVO
ID: 16793343
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
0
 
LVL 34

Expert Comment

by:Sancler
ID: 16794019
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

.
0
 

Author Comment

by:SCVO
ID: 16797166
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 ?
0
 
LVL 34

Expert Comment

by:Sancler
ID: 16798731
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
0
 

Author Comment

by:SCVO
ID: 16798943
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

0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question