jczander
asked on
VB.Net: Adding a Checkbox
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.
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.
nepaluz
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?
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.
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.
ASKER
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:
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.
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.
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
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.
Here the dataSource is the form - Property lhFecha - Formating Data with Date "g" format.
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
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.
Here the dataSource is the form - Property lhFecha - Formating Data with Date "g" format.
ASKER
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."
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.
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.
ASKER
OK. I did a search on the form or the word "binding" and found where to start.
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?
'** 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)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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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))
'ASKER
Great help that got me looking in the right place
ASKER
Thanks James!