Avatar of dave_sky
Flag for United States of America asked on

Column2 = Column1 if Column1 is numerical value

Databound datagridview:

I want Column2 value to = Column1 value only if Column1 is a numerical value.

If column1 is a punctuation symbol, such as ")" or (>)  Column2 value would = "0.00"

Visual Basic.NET

Avatar of undefined
Last Comment

8/22/2022 - Mon

If decimal.isnumeric(column1) AndAlso Column2 = Column1 then

End if

Open in new window

Nasir Razzaq

Want a SQL approach?

Select Column1, Column2 = Case udf_IsNumeric(Column1)
                                             When 1 Then Column1
                                             Else 0.00
From Table1

Now you can use the built in IsNumeric function but it returns 1 for +, -, $ as well so I used a custom function found in below link


Is this what you had in mind....I am getting error messages.

        If Decimal.isnumeric(TblBasicPlumbingBidDetailDataGridView.Columns("ItemNumber")) AndAlso TblBasicPlumbingBidDetailDataGridView.Columns("DataGridViewTextBoxColumn4") = TblBasicPlumbingBidDetailDataGridView.Columns("ItemNumber") Then

        End If

Open in new window

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Shahan Ayyub


Are you looking for the result as mention in the image ??

If yes then you have to do one thing:

Here row(0) have column1 values:
            DataGridView1.Rows(i).Cells(0).Value = row(0) 

Open in new window

and here VAL() will see that if column1 value is numeric, then take numeric value else if string make it zero. ( which i hope so required to you) and Format () will make it like 6.00,10.00 etc...
            DataGridView1.Rows(i).Cells(1).Value =Format(Val(row(0)), "0.00")

Open in new window

Here is the full code: ( I used Form_Load Event you can change it)
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim connstr As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\TestDB.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True" '"Data Source=C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\TestDB.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
        Dim conn As New SqlConnection(connstr)
        Dim cmd As New SqlCommand("select column1,column2 from tbl_test", conn)
        Dim da As New SqlDataAdapter(cmd)
        Dim dt As New DataTable
        Dim i As Integer = 0
        DataGridView1.AllowUserToAddRows = False
        For Each row As DataRow In dt.Rows
            DataGridView1.Rows(i).Cells(0).Value = row(0)
            DataGridView1.Rows(i).Cells(1).Value =Format(Val(row(0)), "0.00")
            i += 1
    End Sub

Open in new window

you can see column1 has punctuation as well as numbers but Column2 have same value in front of numbers but 0 in front of non-Numeric
Shahan Ayyub

I have taken image before FORMAT() function used in my code if you will test it you will get 0.00, 6.00 20.00 etc....
Shahan Ayyub

For your consistency i have attached the image again. see the values.. and the above two posts
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.


I created a sample application and am having problems with your detailed code above.
Could I please ask you to apply your code to the following.

Public Class Form1

    Private Sub Tbl_testBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Tbl_testBindingNavigatorSaveItem.Click

    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 'LivePersonSQLDataSet.tbl_test' table. You can move, or remove it, as needed.
End Sub
End Class

Open in new window

Thank you!
Shahan Ayyub

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

What should column1 and Column2 data type be?
Shahan Ayyub

i 've used varchar for both but you can used varchar for column1 and int or decimal etc  for column2.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Nasir Razzaq

Have you ruled out the possibility of achieving this using SQL? http:#36151676


I am not that familiar with using SQL and making it work with a click event. Ideally I can make this work with a ColumnDoubleClick event.

Thanks for following up.