Avatar of dave_sky
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"

Thanks!
Visual Basic.NET

Avatar of undefined
Last Comment
dave_sky

8/22/2022 - Mon
PagodNaUtak


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
                                             End
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

http://blog.sqlauthority.com/2007/08/11/sql-server-udf-validate-integer-function/
dave_sky

ASKER
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

Hi!

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)
        conn.Open()
        Dim dt As New DataTable
        da.Fill(dt)
        conn.Close()
        Dim i As Integer = 0
        DataGridView1.AllowUserToAddRows = False
        For Each row As DataRow In dt.Rows
            DataGridView1.Rows.Add()
            DataGridView1.Rows(i).Cells(0).Value = row(0)
            DataGridView1.Rows(i).Cells(1).Value =Format(Val(row(0)), "0.00")
            i += 1
        Next
    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.
dave_sky

ASKER
Shahan_Developer:

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
        Me.Validate()
        Me.Tbl_testBindingSource.EndEdit()
        Me.Tbl_testTableAdapter.Update(Me.LivePersonSQLDataSet.tbl_test)

    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.
        Me.Tbl_testTableAdapter.Fill(Me.LivePersonSQLDataSet.tbl_test)
End Sub
End Class

Open in new window


Thank you!
ASKER CERTIFIED SOLUTION
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
or
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
dave_sky

ASKER
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
dave_sky

ASKER
CodeCruiser,

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.