none
How to use select with Data table column name with special characters and variable RRS feed

  • Question

  • Hallo ,

    I am facing a problem when using a column name with hyphen and also would like to know if i can use space in a column name.

    following code works fine when the name of the column does not contain any spaces or hyphen. how do i use it for the column name with space or hyphen.

    this is a small function and ColName is a string variable .

            Dim findRows() As DataRow = DT.[Select](ColName & "=" & "'" & Value2Find & "'")
    
            For Each row As DataRow In findRows
                row.Item(ColName) = Value2Change
            Next
    Thanks.

    Saturday, July 4, 2020 10:31 AM

Answers

  • Example 1

    Imports System.Data.SqlClient
    Public Class DataOperations
    
        Public Shared Function GetWithAlias() As DataTable
            Dim dt As New DataTable
            Dim selectStatement =
                    "SELECT Id, [First Name] AS FirstName, [Last Name] AS LastName, [Invoice-Discount] AS Discount FROM dbo.Table_1;"
    
            Using connection As New SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=ForumExample;Integrated Security=True")
                Using cmd As New SqlCommand(selectStatement, connection)
                    connection.Open()
                    dt.Load(cmd.ExecuteReader())
                    dt.Columns("id").ColumnMapping = MappingType.Hidden
                End Using
            End Using
    
            Return dt
        End Function
    
    End Class
    

    Form code

    Public Class Form2
        Private bindingSource As New BindingSource
        Private Sub Form2_Shown(sender As Object, e As EventArgs) Handles Me.Shown
            bindingSource.DataSource = DataOperations.GetWithAlias()
            DataGridView1.DataSource = bindingSource
        End Sub
        Private Sub CurrentButton_Click(sender As Object, e As EventArgs) Handles CurrentButton.Click
            Dim row As DataRow = CType(bindingSource.Current, DataRowView).Row
            MessageBox.Show($"Current id {row.Field(Of Integer)("id")}")
        End Sub
    End Class

    Public Class Form2
        Private bindingSource As New BindingSource
        Private Sub Form2_Shown(sender As Object, e As EventArgs) Handles Me.Shown
            bindingSource.DataSource = DataOperations.GetWithAlias()
            DataGridView1.DataSource = bindingSource
            DataGridView1.Columns("Firstname").HeaderText = "First"
            DataGridView1.Columns("LastName").HeaderText = "Last"
            DataGridView1.Columns("Discount").HeaderText = "Percent"
        End Sub
        Private Sub CurrentButton_Click(sender As Object, e As EventArgs) Handles CurrentButton.Click
            Dim row As DataRow = CType(bindingSource.Current, DataRowView).Row
            MessageBox.Show($"Current id {row.Field(Of Integer)("id")}")
        End Sub
    End Class


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by Shan1986 Saturday, July 4, 2020 6:45 PM
    Saturday, July 4, 2020 2:05 PM
    Moderator
  • According to documentation for DataTable.Select, try these adjustments too:

    Dim AdjustedColName = "[" & ColName.Replace("\", "\\").Replace("]", "\]") & "]"
    Dim AdjustedValue2Find = Value2Find.Replace("'", "''")
    
    Dim findRows() As DataRow = DT.Select(AdjustedColName & "='" & AdjustedValue2Find & "'")



    • Edited by Viorel_MVP Saturday, July 4, 2020 5:23 PM
    • Marked as answer by Shan1986 Saturday, July 4, 2020 6:45 PM
    Saturday, July 4, 2020 5:22 PM

All replies

  • Hello,

    You should not use column names with hyphen or spaces. Here is a bad example from a table.

    Here is a good example

    Read data in the good example into a DataTable, when there is a need to show the data e.g. in a DataGridView set the Column header text e.g. FirstName can be First or First Name etc.

    Going to a bad example again 

    You can always use an alias

    SELECT Id, 
           [First Name] AS FirstName, 
           [Last Name] AS LastName, 
           [Invoice-Discount] AS Discount
    FROM ForumExample.dbo.Table_1;


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Saturday, July 4, 2020 11:41 AM
    Moderator
  • Hello,

    You should not use column names with hyphen or spaces. Here is a bad example from a table.

    Here is a good example

    Read data in the good example into a DataTable, when there is a need to show the data e.g. in a DataGridView set the Column header text e.g. FirstName can be First or First Name etc.

    Going to a bad example again 

    You can always use an alias

    SELECT Id, 
           [First Name] AS FirstName, 
           [Last Name] AS LastName, 
           [Invoice-Discount] AS Discount
    FROM ForumExample.dbo.Table_1;


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Hallo Karen

    SELECT Id, 
           [First Name] AS FirstName, 
           [Last Name] AS LastName, 
           [Invoice-Discount] AS Discount
    FROM ForumExample.dbo.Table_1;

    Where do i declare these when i bind my data table to a datagridview using binding source? 

    can i assign alias when declaring data table column?

    thanks


    • Edited by Shan1986 Saturday, July 4, 2020 11:56 AM
    Saturday, July 4, 2020 11:55 AM
  • No it is not about the special character (as long as that in a normal ascii range).

    It is because you use the expression not right, (The expression is limited, that was why Linq was created). 

    That is not something which you can make a kind of scripting at runtime. It is fixed and build at compile time. 

    As well are those squared bracket strange although they probably don't hurt. Squared bracket in VB means tells something about the word used but here it is senseless (I mean in your code, not in what Karen created where is is used to tell about the column name with a white space). 

    Your code could be something as 

    Dim findRows() As DataRow = dt.Select("_Whatever = Value2Find")
    But that is probably not what you're up to. But remember, build programs are not the same as programs which are completely compiled at runtime (scripting languages). 


    Success
    Cor


    Saturday, July 4, 2020 12:03 PM
  • Example 1

    Imports System.Data.SqlClient
    Public Class DataOperations
    
        Public Shared Function GetWithAlias() As DataTable
            Dim dt As New DataTable
            Dim selectStatement =
                    "SELECT Id, [First Name] AS FirstName, [Last Name] AS LastName, [Invoice-Discount] AS Discount FROM dbo.Table_1;"
    
            Using connection As New SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=ForumExample;Integrated Security=True")
                Using cmd As New SqlCommand(selectStatement, connection)
                    connection.Open()
                    dt.Load(cmd.ExecuteReader())
                    dt.Columns("id").ColumnMapping = MappingType.Hidden
                End Using
            End Using
    
            Return dt
        End Function
    
    End Class
    

    Form code

    Public Class Form2
        Private bindingSource As New BindingSource
        Private Sub Form2_Shown(sender As Object, e As EventArgs) Handles Me.Shown
            bindingSource.DataSource = DataOperations.GetWithAlias()
            DataGridView1.DataSource = bindingSource
        End Sub
        Private Sub CurrentButton_Click(sender As Object, e As EventArgs) Handles CurrentButton.Click
            Dim row As DataRow = CType(bindingSource.Current, DataRowView).Row
            MessageBox.Show($"Current id {row.Field(Of Integer)("id")}")
        End Sub
    End Class

    Public Class Form2
        Private bindingSource As New BindingSource
        Private Sub Form2_Shown(sender As Object, e As EventArgs) Handles Me.Shown
            bindingSource.DataSource = DataOperations.GetWithAlias()
            DataGridView1.DataSource = bindingSource
            DataGridView1.Columns("Firstname").HeaderText = "First"
            DataGridView1.Columns("LastName").HeaderText = "Last"
            DataGridView1.Columns("Discount").HeaderText = "Percent"
        End Sub
        Private Sub CurrentButton_Click(sender As Object, e As EventArgs) Handles CurrentButton.Click
            Dim row As DataRow = CType(bindingSource.Current, DataRowView).Row
            MessageBox.Show($"Current id {row.Field(Of Integer)("id")}")
        End Sub
    End Class


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by Shan1986 Saturday, July 4, 2020 6:45 PM
    Saturday, July 4, 2020 2:05 PM
    Moderator
  • According to documentation for DataTable.Select, try these adjustments too:

    Dim AdjustedColName = "[" & ColName.Replace("\", "\\").Replace("]", "\]") & "]"
    Dim AdjustedValue2Find = Value2Find.Replace("'", "''")
    
    Dim findRows() As DataRow = DT.Select(AdjustedColName & "='" & AdjustedValue2Find & "'")



    • Edited by Viorel_MVP Saturday, July 4, 2020 5:23 PM
    • Marked as answer by Shan1986 Saturday, July 4, 2020 6:45 PM
    Saturday, July 4, 2020 5:22 PM
  • Hello,

    You should not use column names with hyphen or spaces. Here is a bad example from a table.

    Here is a good example

    Read data in the good example into a DataTable, when there is a need to show the data e.g. in a DataGridView set the Column header text e.g. FirstName can be First or First Name etc.

    Going to a bad example again 

    You can always use an alias

    SELECT Id, 
           [First Name] AS FirstName, 
           [Last Name] AS LastName, 
           [Invoice-Discount] AS Discount
    FROM ForumExample.dbo.Table_1;


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Hallo Karen

    SELECT Id, 
           [First Name] AS FirstName, 
           [Last Name] AS LastName, 
           [Invoice-Discount] AS Discount
    FROM ForumExample.dbo.Table_1;

    Where do i declare these when i bind my data table to a datagridview using binding source? 

    can i assign alias when declaring data table column?

    thanks




    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Saturday, July 4, 2020 7:55 PM
    Moderator