Solved

VB.Net: Adding a Checkbox

Posted on 2011-09-29
11
454 Views
Last Modified: 2012-08-14
I have little to no experience with VB.net. I have been given a program written in VS2003 and asked to add a checkbox to a form. The form retrieves data from a table in the SQL database. I know how to ad the column to the table. That's easy. I know how to add the control to the form. That's easy.

Where I'm puzzled is how to add the new column to the data set and bind the new column to the checkbox. Is this done through code, or is there a way to do this through properties? The form has a function to look up a record, edit a record and save the record.

I'm hoping someone can point me in the right directions. I want the checkbox to be a part oft he data set.
0
Comment
Question by:jczander
11 Comments
 
LVL 17

Expert Comment

by:nepaluz
Comment Utility
you mean you want a checkboxcolumn in the datagridview? in that case, if you have a bool (true / false) or similar variable in your data then you can set the checkbox(es)'s value in code on loading. what code do you use to retrieve the data and bind to the datagridview?
0
 
LVL 40

Expert Comment

by:Jacques Bourgeois (James Burger)
Comment Utility
This can be done in different ways. You should try to stay in tune with the code that is already there, because mixing the different ways can make things hard to debug should you have problems.

Do a search for one of the other controls (over the project, not only the Current Document) that already displays a field from the table. If it ends up in your own application code, simply duplicate what you see there. You might have to change a few things, such as use CBool instead of ToString, because the CheckBox uses a Boolean for its Checked property while most other controls use a String.

If you find nothing, or if the search ends up giving results in the designer.vb file that comes with every form, they they probably binded everything through the properties of the control. Take one of the controls that is already binded and look at its (DataBindings) property, usually the second one in the property Windows when sorted alphabetically. Reproduce what you see there for your CheckBox/Field combination.
0
 

Author Comment

by:jczander
Comment Utility
The form itself has a few field, a lookup button and a drop down box. The user click the look up button to select a record from the database table. They can edit a few of the fields and use the drop down to select from a few options. They want a check box added. Here's an image:

 form

I'm looking for some direction as to how I edit the data query for inserts/updates/deletes and how to bind that check box to the data.
0
 
LVL 15

Expert Comment

by:x77
Comment Utility
Are you working with typed Dataset ?
Are you building datatables before get data from DB ?

You says - I know how to ad the column to the table -

You can add columns to a DataTable when DataTable has no rows.
After that, you can only add computed columns.

Typed DataTables are based on DataTables, but to modify a Typed DataTable, you need modify the .xsd file, then rebuild the dataset.

Note that you can put aditional columns on a datatable (columns out of list of columns from Select clause).

To bind a control (Textox, CheckBox ...) to a DataSource you need create a binding object and add this binding to Control.Bindings collection.
You can see how Microsoft does on autogenerated bindings.

   ChkExtDur.DataBindings.Add("Checked", TndBs, "ExtDur")

   This Binds a CheckBox to a boolean DataColumn ("ExtDur").
   Here the DataSource is a BindingSource.
   Note that DataTable is not a DataSource. When you use a DataTable as DataSource you use DataTable.DefaultView (DataView) as Datasource.


       Me.tbLhFecha.DataBindings.Add(New Binding("Text", Me, "lhFecha", True, DataSourceUpdateMode.OnValidation, Nothing, "g"))
     Here the dataSource is the form - Property lhFecha - Formating Data with Date "g" format.
0
 

Author Comment

by:jczander
Comment Utility
You says - I know how to ad the column to the table -

You can add columns to a DataTable when DataTable has no rows.
After that, you can only add computed columns.

I went to the SQL table and added the column. I rebuilt the project and it runs without error.

I went to the .xsd file and added the new column.

I'm not sure what you mean by "You can add columns to a DataTable when DataTable has no rows.
After that, you can only add computed columns."
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 40

Expert Comment

by:Jacques Bourgeois (James Burger)
Comment Utility
He means that the DataTable has to be empty in order for you to add columns. We did not know that you were working with a .xsd, he assumes something else. There are many ways to do those things in .NET.

This is no concern to you, since we know now that you are working through a .xsd. The DataTable is created automatically this way, so you do not have to care about it. Adding the row to the .xsd will automatically add the column to the DataTable, so you do not need to do so.

All you need to do now is look through the code that is already there, as I explained sooner, to see which technique they use to fill the controls with the data, and use the same yourself.
0
 

Author Comment

by:jczander
Comment Utility
OK. I did a search on the form or the word "binding" and found where to start.

    '** Field bindings
    txtCompanyName.Text = Globals.AppUser.CompanyName
    txtID.MaxLength = ds.PayrollForm.PayForm_IDColumn.MaxLength
    txtCustomer_ID.DataBindings.Add("Text", dv, ds.PayrollForm.Customer_IDColumn.ColumnName)
    txtDescription.DataBindings.Add("Text", dv, ds.PayrollForm.DescriptionColumn.ColumnName)
        cbActive.DataBindings.Add("Checked", dv, ds.PayrollForm.StatusColumn.ColumnName)

Open in new window


It compiles, but I have a question about the syntax. After "Checked" should the next parameter be "dv" or something else?

One last question. I'm assuming there is some code for update/delete/insert. Is that correct?


0
 
LVL 40

Accepted Solution

by:
Jacques Bourgeois (James Burger) earned 500 total points
Comment Utility
Assuming that the StatusColumn comes from the same source as the other fields, yes, dv is the right thing.

If the line you added there does not give you an error upon execution (compiling is easy, executing can be something else :-)), and if the data was updated correctly in the original form, then registering your CheckBox with dv as you do there will automatically enter it in a process that let's you lose a bit of control, but handles most of the routine jobs for you underneath: DataBinding.

Once a control is data binded to a data source (dv), the value in the current record of the source automatically sets the specified (Checked) when you display a new record. And when the user makes a change in the control, it is automatically recorded in the source. When an update is called on the source, the changes are posted to the database. The difference between update/delete/insert is usually handled automatically, although it can also be done "manually" through more sophisticated code that you write yourself.

If you are curious, search for .Update in the form. It might be somewhere else than in the form itself and might be done with something else than an Update method. But 60% of chances what you will find a .Update in the form. If it is so, the system knows which lines were added, which were deleted and which were modified and will issue the proper command for the state (RowStatus property) of each line. No matter that the method is called only Update, it will handle the 3 types of commands.

-----

If my assumptions are right. So do not look to hard if you do not see Update, another technique might have been used.

A nice feature of ADO.NET is that there are almost always many different ways to do the same thing. Some are better than others in specific conditions. Some are a matter of style. Once you understand them, it gives you confidence and enable to work as you like.

A bad feature of ADO.NET is that there are almost always many different ways to do the same thing. This makes the thing confusing for somebody who is learning. And harder for us, supposed experts, to guide you, because unless we see a lot of code, we are never sure of the technique that was used.

Without seeing more of the code, it's impossible to tell exactly what method was used to fetch the data, and then to return the changes to the database. The 6 lines you have provided so far let me suppose that the data was retrieved in a DataSet (ds) and filtered through a DataView (dv).

But those variable names might be misleading. Data can be accessed through a DataSet or a DataTable, filtered or not on a DataView. 80% of the code we see everywhere uses a DataSet where a DataTable would be more appropriate. And that is if the programmer is found of basic ADO.NET. Other programmers would rather use one of those supposed "code 100 lines in 1" tools, a Typed DataSet, LINQ, or the little darling of the moment, Data Entities (this one seems more serious than the other ones, so I might start using it myself eventually).

All this to say that if it works and the performance is not a pain, its OK.
0
 

Author Comment

by:jczander
Comment Utility
The small change I made compiles and runs. It does not result in an update of the column. I can tick the box, but nothing happens to the table. It appears there is some SQL code for updating, inserting and deleting records in that table. Here's what looks like the update script:

Me.SqlUpdateCommand1.CommandText = "UPDATE PayrollForm SET Company_ID = @Company_ID, PayForm_ID = @PayForm_ID, Divisi" & _
        "on_ID = @Division_ID, Customer_ID = @Customer_ID, CC_ID = @CC_ID, ShipOrTerminal" & _
        " = @ShipOrTerminal, Ship_ID = @Ship_ID, Pier_ID = @Pier_ID, Super_ID = @Super_ID" & _
        ", Description = @Description, Modified = @Modified, ModifiedBy = @ModifiedBy, Cr" & _
        "eated = @Created, CreatedBy = @CreatedBy WHERE (Company_ID = @Original_Company_I" & _
        "D) AND (PayForm_ID = @Original_PayForm_ID) AND (CC_ID = @Original_CC_ID OR @Orig" & _
        "inal_CC_ID IS NULL AND CC_ID IS NULL) AND (Created = @Original_Created OR @Origi" & _
        "nal_Created IS NULL AND Created IS NULL) AND (CreatedBy = @Original_CreatedBy OR" & _
        " @Original_CreatedBy IS NULL AND CreatedBy IS NULL) AND (Customer_ID = @Original" & _
        "_Customer_ID OR @Original_Customer_ID IS NULL AND Customer_ID IS NULL) AND (Desc" & _
        "ription = @Original_Description OR @Original_Description IS NULL AND Description" & _
        " IS NULL) AND (Division_ID = @Original_Division_ID OR @Original_Division_ID IS N" & _
        "ULL AND Division_ID IS NULL) AND (Modified = @Original_Modified OR @Original_Mod" & _
        "ified IS NULL AND Modified IS NULL) AND (ModifiedBy = @Original_ModifiedBy OR @O" & _
        "riginal_ModifiedBy IS NULL AND ModifiedBy IS NULL) AND (Pier_ID = @Original_Pier" & _
        "_ID OR @Original_Pier_ID IS NULL AND Pier_ID IS NULL) AND (ShipOrTerminal = @Ori" & _
        "ginal_ShipOrTerminal OR @Original_ShipOrTerminal IS NULL AND ShipOrTerminal IS N" & _
        "ULL) AND (Ship_ID = @Original_Ship_ID OR @Original_Ship_ID IS NULL AND Ship_ID I" & _
        "S NULL) AND (Super_ID = @Original_Super_ID OR @Original_Super_ID IS NULL AND Sup" & _
        "er_ID IS NULL); SELECT Company_ID, PayForm_ID, Division_ID, Customer_ID, (SELECT" & _
        " CustomerName FROM Customer WHERE Customer.Customer_ID = PayrollForm.Customer_ID" & _
        " AND Company_ID = PayrollForm.Company_ID) AS CustomerName, CC_ID, ShipOrTerminal" & _
        ", Ship_ID, Pier_ID, Super_ID, Description, Modified, ModifiedBy, Created, Create" & _
        "dBy FROM PayrollForm WHERE (Company_ID = @Company_ID) AND (PayForm_ID = @PayForm" & _
        "_ID)"
        Me.SqlUpdateCommand1.Connection = Me.sqlConn
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Company_ID", System.Data.SqlDbType.VarChar, 5, "Company_ID"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@PayForm_ID", System.Data.SqlDbType.VarChar, 8, "PayForm_ID"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Division_ID", System.Data.SqlDbType.VarChar, 8, "Division_ID"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Customer_ID", System.Data.SqlDbType.VarChar, 5, "Customer_ID"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@CC_ID", System.Data.SqlDbType.VarChar, 4, "CC_ID"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@ShipOrTerminal", System.Data.SqlDbType.Bit, 1, "ShipOrTerminal"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Ship_ID", System.Data.SqlDbType.VarChar, 10, "Ship_ID"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Pier_ID", System.Data.SqlDbType.VarChar, 4, "Pier_ID"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Super_ID", System.Data.SqlDbType.VarChar, 8, "Super_ID"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Description", System.Data.SqlDbType.VarChar, 20, "Description"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Modified", System.Data.SqlDbType.DateTime, 8, "Modified"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@ModifiedBy", System.Data.SqlDbType.VarChar, 15, "ModifiedBy"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Created", System.Data.SqlDbType.DateTime, 8, "Created"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@CreatedBy", System.Data.SqlDbType.VarChar, 15, "CreatedBy"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_Company_ID", System.Data.SqlDbType.VarChar, 5, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Company_ID", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_PayForm_ID", System.Data.SqlDbType.VarChar, 8, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "PayForm_ID", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_CC_ID", System.Data.SqlDbType.VarChar, 4, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "CC_ID", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_Created", System.Data.SqlDbType.DateTime, 8, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Created", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_CreatedBy", System.Data.SqlDbType.VarChar, 15, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "CreatedBy", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_Customer_ID", System.Data.SqlDbType.VarChar, 5, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Customer_ID", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_Description", System.Data.SqlDbType.VarChar, 20, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Description", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_Division_ID", System.Data.SqlDbType.VarChar, 8, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Division_ID", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_Modified", System.Data.SqlDbType.DateTime, 8, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Modified", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_ModifiedBy", System.Data.SqlDbType.VarChar, 15, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "ModifiedBy", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_Pier_ID", System.Data.SqlDbType.VarChar, 4, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Pier_ID", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_ShipOrTerminal", System.Data.SqlDbType.Bit, 1, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "ShipOrTerminal", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_Ship_ID", System.Data.SqlDbType.VarChar, 10, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Ship_ID", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_Super_ID", System.Data.SqlDbType.VarChar, 8, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Super_ID", System.Data.DataRowVersion.Original, Nothing))
        '

Open in new window

0
 

Author Closing Comment

by:jczander
Comment Utility
Great help that got me looking in the right place
0
 

Author Comment

by:jczander
Comment Utility
Thanks James!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video discusses moving either the default database or any database to a new volume.

763 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now