[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 918
  • Last Modified:

How to set databound datagridview default column values to null

Software: vb.net & sql server 2005

I have a databound datagridview with row cells performing calculations.
Example: col1*(col2+col3) = col4

I need to set the default column values to ("0.00") so the values will perform as part
of the calculations if I enter numerical values in the one or some of the row cells.
Example: col1("1.00")*(col2("10.00")+col3("0.00")=Col4("10.00")

 This code will not work:
DataGridView1.Columns("DataGridViewTextBoxColumn5").DefaultCellStyle.NullValue = "0.00"

The (0.00") will only perform as part of the calculations if the value is keyed in.

Sample code will be appreciated.

Thanks!
0
dave_sky
Asked:
dave_sky
  • 9
  • 6
  • 5
1 Solution
 
nepaluzCommented:
you have to set the default value in the table that is bound to the datagridview
Here is an example of setting the default value of a column in code:
Dim xTable As New DataTable
xTable.Columns.Add(New DataColumn With {.DefaultValue = 0.0, .AllowDBNull = True, .ColumnName = "Category Headers", .DataType = System.Type.GetType("System.String")})

Open in new window


0
 
CodeCruiserCommented:
Or you can modify your sql query

Select IsNull(columnname, 0.00) ...

which will convert DBNulls to 0.00
0
 
dave_skyAuthor Commented:
I get the following errors:

With            Error "Comma, ')', or a valid expression continuation expected".      


.AllowDBNull       .ColumnName       .DataType           Errors "Leading '.' or '!' can only appear inside a 'With' statement".
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
nepaluzCommented:
if you are not using .NET 4.0 then you will get that error, try

        Dim xTable As New DataTable
        Dim xColumn As New DataColumn
        With xColumn
            .DefaultValue = 0.0
            .AllowDBNull = False
            .ColumnName = "YourColumnName"
            .DataType = System.Type.GetType("System.Double")
        End With
        xTable.Columns.Add(xColumn)

Open in new window

0
 
dave_skyAuthor Commented:
The code from nepaluz goes in Form Load with no errors - but does not work.

Should I use the code somewhere else?

I am using .Net 3.5...
0
 
nepaluzCommented:
can you show how you have applied the code? (I am hoping it not copy and paste!)
0
 
dave_skyAuthor Commented:
Dim TblBasicPlumbingBidDetail As New DataTable
        Dim BasePrice As New DataColumn
        With BasePrice
            .DefaultValue = 0.0
            .AllowDBNull = False
            .ColumnName = "BasePrice"
            .DataType = System.Type.GetType("System.Double")
        End With
        TblBasicPlumbingBidDetail.Columns.Add(BasePrice)
0
 
nepaluzCommented:
and your datagridview is bound to TblBasicPlumbingBidDetail?
ie
DataGridView1.DataSource = TblBasicPlumbingBidDetail

Open in new window

0
 
nepaluzCommented:
If you have your table autogenerated when executing your sql query, then my suggestion may not work. You are probably better of exploring Code-Cruiser's suggestion.

On the other hand, if you have a table inside a dataset added to the form from the designer(drag and drop), then you can simply set the columns' attributes as per my example code or through the dataset designer's interface (i.e to have a default value of 0.0)
0
 
dave_skyAuthor Commented:
I have a table inside a dataset added to the form from the designer(drag and drop), and I have set the columns' attributes as per your example code or through the dataset designer's interface (i.e to have a default value of (0.0)

I have tried setting the column default values to 0.00 in the 'Edit Columns' window. The default value of 0.00 show in the column in run time but the row will not calculate with the default 0.00 unless I type 0.00 over the default value.

Any more ideas?
0
 
CodeCruiserCommented:
Any problem trying my idea? http:#35501346
0
 
nepaluzCommented:
short of trying CodeCruiser's suggestion, you will have to show some more code on how you populate the dataset's table from your database. Otherwise, I think I have taken you as far as I can go.
0
 
dave_skyAuthor Commented:
The column "Sales" data type is set to Money in the database.
-----------------------------------------------------------------------------------
This sql query does not work in the dataset: (it works ok in SSMS)
SELECT     ISNULL(Sales, 0.00) AS Sales
FROM         tblIncomeStatementDetail
-----------------------------------------------------------------------------------
I put a sample app together that contains all of the code but I can't get it to set column "Sales" default values to 0.00

Public Class Form1

    Private Sub TblIncomeStatementBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TblIncomeStatementBindingNavigatorSaveItem.Click
        Me.Validate()
        Me.TblIncomeStatementBindingSource.EndEdit()
        Me.TblIncomeStatementDetailBindingSource.EndEdit()
        Me.TblIncomeStatementTableAdapter.Update(Me.MYDataSet.tblIncomeStatement)
        Me.TblIncomeStatementDetailTableAdapter.Update(Me.MYDataSet.tblIncomeStatementDetail)

    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'MYDataSet.tblIncomeStatementDetail' table. You can move, or remove it, as needed.
        Me.TblIncomeStatementDetailTableAdapter.Fill(Me.MYDataSet.tblIncomeStatementDetail)
        'TODO: This line of code loads data into the 'MYDataSet.tblIncomeStatement' table. You can move, or remove it, as needed.
        Me.TblIncomeStatementTableAdapter.Fill(Me.MYDataSet.tblIncomeStatement)

        Dim TblIncomeStatementDetail As New DataTable
        Dim Sales As New DataColumn
        With Sales
            .DefaultValue = 0.0
            .AllowDBNull = False
            .ColumnName = "Sales"
            .DataType = System.Type.GetType("System.Double")
        End With
        TblIncomeStatementDetail.Columns.Add(Sales)

    End Sub
End Class
0
 
dave_skyAuthor Commented:
I also tried using the code in an event with no luck:

Private Sub ToolStripButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton1.Click
        Dim TblIncomeStatementDetail As New DataTable
        Dim Sales As New DataColumn
        With Sales
            .DefaultValue = 0.0
            .AllowDBNull = False
            .ColumnName = "Sales"
            .DataType = System.Type.GetType("System.Double")
        End With
        TblIncomeStatementDetail.Columns.Add(Sales)
    End Sub
0
 
dave_skyAuthor Commented:
Anyone?
0
 
CodeCruiserCommented:
Why are you not willing to try my suggestion? and not replying to me? Any problem?
0
 
dave_skyAuthor Commented:
CodeCrusier:
I replied.....see above.

This sql query does not work in the dataset: (it works ok in SSMS)
SELECT     ISNULL(Sales, 0.00) AS Sales
FROM         tblIncomeStatementDetail
0
 
CodeCruiserCommented:
I did not see the reply. Are you not loading the dataset from sql? Is it in memory data only?
0
 
dave_skyAuthor Commented:
Yes, I am loading the dataset from sql.

Something must be wrong... maybe I am not explaining what I want correctly.

I am looking for code to populate a datagridview column with 0.00 in all cells.
0
 
CodeCruiserCommented:
>I am looking for code to populate a datagridview column with 0.00 in all cells

Select Column1, Column2, 0.00 As MyColumn From MyTable


0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 9
  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now