How to convert entire DataTable column into a string?

How to convert entire DataTable column into a string? - VB.Net

In this project I'm working on, I have to create a bunch of pie charts using SQL queries.

There are 2 phases of SQL

First, I want to collect the names of Machines operating during the time frame the user has selected and place it in a DataTable.

    Dim query1 As String = String.Format("SELECT DISTINCT A.MacID FROM dbo.tblMachine A LEFT JOIN  dbo.tblDataHdr b on a.MacID = b.MacID where DayID between '" & TextBox1.Text & "' AND '" & TextBox3.Text & "' ORDER BY A.MACID")
    Dim dt1 As DataTable = GetData(query1)

For this example the machine names I collected is

--------------
|    MacId   |
--------------
|    A01     |
|    A02     |
|    C01     |
--------------

For the second phase I want to use the data collected in the first phase and reference it into another SQL query. My idea is to have the content in the table to convert into a string, like so:

'A01', 'A02', 'C01'

That way I'll only need to run the 2nd SQL query once instead of having to rely on For - Next statement to get the data, which was what I used the last time resulting in long loading times, expecially when there are 30+ machines.

How can I get this result? I've been thinking about it for 2 days now and I have no idea how to start.

Edit:

For some context here is my current code. Please ignore the mess.

Sub draw_chart1() 'All Machines

    Dim check1, check2, fi, cnt
    Dim seperator As String = ", "
    Dim columnindex As Integer = 0

    Dim myConnectionString As String = "Provider=SQLOLEDB;" & SQLDB_pp.ConnectionString
    fi = 0
    cnt = 0

    PlaceHolder1.Dispose()
    PlaceHolder2.Dispose()

    'Get all machines
    Dim query1 As String = String.Format("SELECT DISTINCT A.MacID FROM dbo.tblMachine A LEFT JOIN  dbo.tblDataHdr b on a.MacID = b.MacID where DayID between '" & TextBox1.Text & "' AND '" & TextBox3.Text & "' ORDER BY A.MACID")
    Dim dt1 As DataTable = GetData(query1)

    Dim result As String = String.Join(seperator, dt1.AsEnumerable.Select(Function(r) "'" + r(columnindex).ToString() + "'")).TrimEnd(seperator.ToCharArray())

    Dim dt As DataTable
    Dim query As String


    'Dim query As String = String.Format("SELECT Z.MacID, Z.EventName, ISNULL(DIFF,0) AS DIFF FROM (select distinct MacID, EventName from dbo.tblMachine a " _
    '                                        & "join (SELECT DISTINCT EVENTNAME FROM dbo.tblEvtDur where EventName <> 'ON'  ) b on b.EventName <> ''  and MacID in ('" & check1 & "') ) Z " _
    '                                        & "LEFT JOIN (SELECT A.MacID, A.EventName, SUM(DATEDIFF(SECOND, A.STARTdt, A.eNDdt)) as diff,round(SUM(DATEDIFF(SECOND, A.STARTdt, A.eNDdt)) / cast(aVG(Tdiff) " _
    '                                        & "as decimal(30,8)),4) * 100 AS PER FROM dbo.tblEvtDur  A LEFT JOIN ( SELECT MacID, SUM(DATEDIFF(SECOND, STARTdt, eNDdt)) as Tdiff FROM  dbo.tblEvtDur " _
    '                                        & "WHERE DayID between '" & TextBox1.Text & "' and '" & TextBox3.Text & "'  GROUP BY MacID ) B ON  A.MacID = B.MacID WHERE DayID " _
    '                                        & "between '" & TextBox1.Text & "' and '" & TextBox3.Text & "'  AND A.MacID in ('" & check1 & "') group by A.MacID, A.EventName) a " _
    '                                        & "ON A.EVENTNAME = Z.EVENTNAME  and Z.MacID  = a.MacID order by Z.MacID, Z.EventName")
    'Dim dt As DataTable = GetData(query)

    'For q As Integer = 0 To dt1.Rows.Count - 1

    'check1 = dt1.DataSet.ToString
    'check1 = dt1.Rows(q)(0).ToString() '(0) means column, since query1 only search for 1 column, it uses 0 = 1ST COLUMN

    query = String.Format("SELECT Z.MacID, Z.EventName, ISNULL(DIFF,0) AS DIFF FROM (select distinct MacID, EventName from dbo.tblMachine a " _
            & "join (SELECT DISTINCT EVENTNAME FROM dbo.tblEvtDur where EventName <> 'ON'  ) b on b.EventName <> ''  and MacID in ({0}) ) Z " _
            & "LEFT JOIN (SELECT A.MacID, A.EventName, SUM(DATEDIFF(SECOND, A.STARTdt, A.eNDdt)) as diff,round(SUM(DATEDIFF(SECOND, A.STARTdt, A.eNDdt)) / cast(aVG(Tdiff) " _
            & "as decimal(30,8)),4) * 100 AS PER FROM dbo.tblEvtDur  A LEFT JOIN ( SELECT MacID, SUM(DATEDIFF(SECOND, STARTdt, eNDdt)) as Tdiff FROM  dbo.tblEvtDur " _
            & "WHERE DayID between '" & TextBox1.Text & "' and '" & TextBox3.Text & "'  GROUP BY MacID ) B ON  A.MacID = B.MacID WHERE DayID " _
            & "between '" & TextBox1.Text & "' and '" & TextBox3.Text & "'  AND A.MacID in ({0}) group by A.MacID, A.EventName) a " _
            & "ON A.EVENTNAME = Z.EVENTNAME  and Z.MacID  = a.MacID order by Z.MacID, Z.EventName", result)

    dt = GetData(query)

    'Next

    'dt = GetData(query)

    For a As Integer = 0 To dt.Rows.Count - 1

        check2 = dt.Rows(a)(0).ToString()

        Dim mychart As Chart = New Chart
        Dim ChartArea1 As ChartArea = New ChartArea
        Dim Legend1 As Legend = New Legend

        'Dim dt As DataTable = GetData(query) '30 sec to process
        Dim x As String() = New String(dt.Rows.Count - 1) {}
        Dim y As Integer() = New Integer(dt.Rows.Count - 1) {}
        For i As Integer = 0 To dt.Rows.Count - 1

            x(i) = dt.Rows(i)(1).ToString()
            y(i) = Convert.ToInt32(dt.Rows(i)(2))
        Next

        mychart.Width = 600
        mychart.Height = 400

        mychart.ChartAreas.Clear()
        mychart.ChartAreas.Add("ChartArea2")

        mychart.Series.Clear()
        mychart.Series.Add(0)
        mychart.Series(0).Points.DataBindXY(x, y)

        mychart.Titles.Clear()
        mychart.Titles.Add("[" & a + 1 & "] " & check2.ToString.ToUpper)
        mychart.Titles(0).Font = New System.Drawing.Font("Tahoma", 12, System.Drawing.FontStyle.Bold)
        mychart.Titles(0).BackColor = Color.PaleTurquoise
        mychart.Titles(0).ForeColor = Color.Black

        mychart.Series(0).ChartType = SeriesChartType.Pie
        mychart.Series(0).LegendText = "#VALX"
        mychart.Series(0)("BarLabelStyle") = "Center"
        mychart.Series(0)("pointWidth") = "1"
        mychart.Series(0).BorderDashStyle = ChartDashStyle.Solid
        mychart.Series(0).BorderWidth = 2
        mychart.Series(0).Label = "#PERCENT"
        mychart.Series(0).ShadowColor = Color.Gray
        mychart.Series(0).ShadowOffset = 10

        mychart.Series(0).LabelBackColor = Drawing.Color.Cornsilk
        mychart.Series(0).Font = New Font("Tahoma", 9, FontStyle.Bold)

        mychart.Series(0).LegendToolTip = "#VALX - #PERCENT"
        mychart.Series(0).ToolTip = "#VALX - #PERCENT"

        mychart.ChartAreas("ChartArea1").Area3DStyle.Enable3D = True

        mychart.Series(0).CustomProperties = "DrawingStyle=LightToDark"
        'new
        Chart1.Series(0).CustomProperties = "PieLabelStyle=Outside"

        mychart.ChartAreas("ChartArea1").BorderDashStyle = BorderStyle.Solid
        mychart.Palette = ChartColorPalette.None
        mychart.Series(0).BorderDashStyle = ChartDashStyle.Solid
        mychart.Series(0).BorderWidth = 2
        mychart.Series(0).BorderColor = Color.Black

        mychart.PaletteCustomColors = {Drawing.Color.Black, Drawing.Color.White, Drawing.Color.Blue, Drawing.Color.Yellow, Drawing.Color.Red, Drawing.Color.Orange, Drawing.Color.Green}

        mychart.Legends.Clear()
        mychart.Legends.Add(0)
        mychart.Legends(0).Font = New Font("Tahoma", 10, FontStyle.Bold)
        mychart.Legends(0).Docking = System.Web.UI.DataVisualization.Charting.Docking.Bottom

        mychart.DataBind()

        If (a + 1) Mod 2 <> 0 Then

            PlaceHolder1.Controls.Add(mychart)

        End If

        If (a + 1) Mod 2 = 0 Then

            PlaceHolder2.Controls.Add(mychart)

        End If

    Next
End Sub

 Private Shared Function GetData(ByVal query As String) As DataTable

    Dim dt As New DataTable()
    Dim cmd As New SqlCommand(query)
    '  Dim constr As [String] = ConfigurationManager.ConnectionStrings("SQLDB_pp").ConnectionString
    'Dim con As New SqlConnection(SQLDB_pp)
    Dim sda As New SqlDataAdapter()
    cmd.CommandType = CommandType.Text
    cmd.Connection = SQLDB_pp
    sda.SelectCommand = cmd
    sda.Fill(dt)
    Return dt

End Function

Edit 2:

Dim sb As New StringBuilder()

    sb.Append("SELECT Z.MacID, Z.EventName, ISNULL(DIFF,0) AS DIFF FROM (select distinct MacID, EventName from dbo.tblMachine a " _
            & "join (SELECT DISTINCT EVENTNAME FROM dbo.tblEvtDur where EventName <> 'ON'  ) b on b.EventName <> ''  and MacID in (")

    Dim params As New List(Of SqlParameter)()

    Dim max As Integer = result.Length - 1

    For i As Integer = 0 To max

        If i = max Then
            sb.Append("MacID" & i.ToString())
        Else
            sb.Append("MacID" & i.ToString() & ", ")
        End If
        params.Add(New SqlParameter("MacID" & i.ToString(), result(i)))
    Next

    sb.Append(") ) Z " _
            & "LEFT JOIN (SELECT A.MacID, A.EventName, SUM(DATEDIFF(SECOND, A.STARTdt, A.eNDdt)) as diff,round(SUM(DATEDIFF(SECOND, A.STARTdt, A.eNDdt)) / cast(aVG(Tdiff) " _
            & "as decimal(30,8)),4) * 100 AS PER FROM dbo.tblEvtDur  A LEFT JOIN ( SELECT MacID, SUM(DATEDIFF(SECOND, STARTdt, eNDdt)) as Tdiff FROM  dbo.tblEvtDur " _
            & "WHERE DayID between '" & TextBox1.Text & "' and '" & TextBox3.Text & "'  GROUP BY MacID ) B ON  A.MacID = B.MacID WHERE DayID " _
            & "between '" & TextBox1.Text & "' and '" & TextBox3.Text & "'  AND A.MacID in (")

    Dim params2 As New List(Of SqlParameter)()

    Dim max2 As Integer = result.Length - 1

    For j As Integer = 0 To max2

        If j = max2 Then
            sb.Append("MacID" & j.ToString())
        Else
            sb.Append("MacID" & j.ToString() & ", ")
        End If
        params2.Add(New SqlParameter("MacID" & j.ToString(), result(j)))
    Next

    sb.Append(") group by A.MacID, A.EventName) a " _
            & "ON A.EVENTNAME = Z.EVENTNAME  and Z.MacID  = a.MacID order by Z.MacID, Z.EventName")

    Dim q As String = sb.ToString()

Edit 4:

This is the full code for the sub I'm having problems with. This is the original code before any modifications with slow load times during the 2nd SQL.

Sub draw_chart1() 'All Machines

    Dim check1, check2, fi, cnt

    Dim sql
    Dim myConnectionString As String = "Provider=SQLOLEDB;" & SQLDB_pp.ConnectionString
    fi = 0
    cnt = 0

    PlaceHolder1.Dispose()
    PlaceHolder2.Dispose()
    'Get all machines
    Dim query1 As String = String.Format("SELECT DISTINCT A.MacID FROM dbo.tblMachine A LEFT JOIN  dbo.tblDataHdr b on a.MacID = b.MacID where DayID between '" & TextBox1.Text & "' AND '" & TextBox3.Text & "' ORDER BY A.MACID")
    Dim dt1 As DataTable = GetData(query1)


    'Dim query As String = String.Format("SELECT Z.MacID, Z.EventName, ISNULL(DIFF,0) AS DIFF FROM (select distinct MacID, EventName from dbo.tblMachine a " _
    '                                        & "join (SELECT DISTINCT EVENTNAME FROM dbo.tblEvtDur where EventName <> 'ON'  ) b on b.EventName <> ''  and MacID in ('" & check1 & "') ) Z " _
    '                                        & "LEFT JOIN (SELECT A.MacID, A.EventName, SUM(DATEDIFF(SECOND, A.STARTdt, A.eNDdt)) as diff,round(SUM(DATEDIFF(SECOND, A.STARTdt, A.eNDdt)) / cast(aVG(Tdiff) " _
    '                                        & "as decimal(30,8)),4) * 100 AS PER FROM dbo.tblEvtDur  A LEFT JOIN ( SELECT MacID, SUM(DATEDIFF(SECOND, STARTdt, eNDdt)) as Tdiff FROM  dbo.tblEvtDur " _
    '                                        & "WHERE DayID between '" & TextBox1.Text & "' and '" & TextBox3.Text & "'  GROUP BY MacID ) B ON  A.MacID = B.MacID WHERE DayID " _
    '                                        & "between '" & TextBox1.Text & "' and '" & TextBox3.Text & "'  AND A.MacID in ('" & check1 & "') group by A.MacID, A.EventName) a " _
    '                                        & "ON A.EVENTNAME = Z.EVENTNAME  and Z.MacID  = a.MacID order by Z.MacID, Z.EventName")
    'Dim dt As DataTable = GetData(query)

    For q As Integer = 0 To dt1.Rows.Count - 1

        check1 = dt1.Rows(q)(0).ToString()

        Dim query As String = String.Format("SELECT Z.MacID, Z.EventName, ISNULL(DIFF,0) AS DIFF FROM (select distinct MacID, EventName from dbo.tblMachine a " _
                                            & "join (SELECT DISTINCT EVENTNAME FROM dbo.tblEvtDur where EventName <> 'ON'  ) b on b.EventName <> ''  and MacID in ('" & check1 & "') ) Z " _
                                            & "LEFT JOIN (SELECT A.MacID, A.EventName, SUM(DATEDIFF(SECOND, A.STARTdt, A.eNDdt)) as diff,round(SUM(DATEDIFF(SECOND, A.STARTdt, A.eNDdt)) / cast(aVG(Tdiff) " _
                                            & "as decimal(30,8)),4) * 100 AS PER FROM dbo.tblEvtDur  A LEFT JOIN ( SELECT MacID, SUM(DATEDIFF(SECOND, STARTdt, eNDdt)) as Tdiff FROM  dbo.tblEvtDur " _
                                            & "WHERE DayID between '" & TextBox1.Text & "' and '" & TextBox3.Text & "'  GROUP BY MacID ) B ON  A.MacID = B.MacID WHERE DayID " _
                                            & "between '" & TextBox1.Text & "' and '" & TextBox3.Text & "'  AND A.MacID in ('" & check1 & "') group by A.MacID, A.EventName) a " _
                                            & "ON A.EVENTNAME = Z.EVENTNAME  and Z.MacID  = a.MacID order by Z.MacID, Z.EventName")

        Dim dt As DataTable = GetData(query) '47 sec to process



        Dim mychart As Chart = New Chart
        Dim ChartArea1 As ChartArea = New ChartArea
        Dim Legend1 As Legend = New Legend

        'Dim dt As DataTable = GetData(query) '30 sec to process
        Dim x As String() = New String(dt.Rows.Count - 1) {}
        Dim y As Integer() = New Integer(dt.Rows.Count - 1) {}
        For i As Integer = 0 To dt.Rows.Count - 1

            x(i) = dt.Rows(i)(1).ToString()
            ' y(i) = dt.Rows(i)(2).ToString()
            y(i) = Convert.ToInt32(dt.Rows(i)(2))
        Next

        'Dim myConnection As New OleDbConnection(myConnectionString)
        'Dim myCommand As New OleDbCommand(sql, myConnection)
        'mychart.Width = Unit.Pixel(Session("sw") - 100)
        'mychart.Height = Unit.Pixel((Session("sh") / 2) - 88)

        mychart.Width = 600
        mychart.Height = 400

        mychart.ChartAreas.Clear()
        mychart.ChartAreas.Add("ChartArea1")

        mychart.Series.Clear()
        mychart.Series.Add(0)
        mychart.Series(0).Points.DataBindXY(x, y)

        mychart.Titles.Clear()
        mychart.Titles.Add("[" & q + 1 & "] " & check1.ToString.ToUpper)
        mychart.Titles(0).Font = New System.Drawing.Font("Tahoma", 12, System.Drawing.FontStyle.Bold)
        mychart.Titles(0).BackColor = Color.PaleTurquoise
        mychart.Titles(0).ForeColor = Color.Black

        mychart.Series(0).ChartType = SeriesChartType.Pie
        '  mychart.Series(0).Points.DataBindXY(x, y)
        mychart.Series(0).LegendText = "#VALX"
        mychart.Series(0)("BarLabelStyle") = "Center"
        mychart.Series(0)("pointWidth") = "1"
        mychart.Series(0).BorderDashStyle = ChartDashStyle.Solid
        mychart.Series(0).BorderWidth = 2
        mychart.Series(0).Label = "#PERCENT"
        mychart.Series(0).ShadowColor = Color.Gray
        mychart.Series(0).ShadowOffset = 10

        mychart.Series(0).LabelBackColor = Drawing.Color.Cornsilk
        mychart.Series(0).Font = New Font("Tahoma", 9, FontStyle.Bold)

        'Chart1.Series(0).LabelToolTip = "#LABEL Percent: #PERCENT"
        mychart.Series(0).LegendToolTip = "#VALX - #PERCENT"
        mychart.Series(0).ToolTip = "#VALX - #PERCENT"

        mychart.ChartAreas("ChartArea1").Area3DStyle.Enable3D = True

        mychart.Series(0).CustomProperties = "DrawingStyle=LightToDark"
        'new
        Chart1.Series(0).CustomProperties = "PieLabelStyle=Outside"

        mychart.ChartAreas("ChartArea1").BorderDashStyle = BorderStyle.Solid
        mychart.Palette = ChartColorPalette.None
        mychart.Series(0).BorderDashStyle = ChartDashStyle.Solid
        mychart.Series(0).BorderWidth = 2
        mychart.Series(0).BorderColor = Color.Black

        mychart.PaletteCustomColors = {Drawing.Color.Black, Drawing.Color.White, Drawing.Color.Blue, Drawing.Color.Yellow, Drawing.Color.Red, Drawing.Color.Orange, Drawing.Color.Green}

        mychart.Legends.Clear()
        mychart.Legends.Add(0)
        'Chart1.Legends(0).Enabled = True
        ''Chart1.Legends(0).BackColor = Drawing.Color.LightGreenplace
        mychart.Legends(0).Font = New Font("Tahoma", 10, FontStyle.Bold)
        mychart.Legends(0).Docking = System.Web.UI.DataVisualization.Charting.Docking.Bottom
        'Chart1.Legends(0).Alignment = Drawing.StringAlignment.Center
        'Chart1.Legends(0).BackColor = System.Drawing.Color.Transparent

        mychart.DataBind()

        'myplace.Visible = True
        If (q + 1) Mod 2 <> 0 Then
            PlaceHolder1.Controls.Add(mychart)
            ' Dim spacer As LiteralControl = New LiteralControl("<p />")
            ' PlaceHolder1.Controls.Add(spacer)
        End If
        'Exit For
        If (q + 1) Mod 2 = 0 Then

            PlaceHolder2.Controls.Add(mychart)
            'Dim spacer As LiteralControl = New LiteralControl("<p />")
            ' PlaceHolder2.Controls.Add(spacer)

        End If

    Next
End Sub

You can use String.Join() to join all DataTable column values by converting the DataTable column into string array first:

Dim dt1 As DataTable = GetData(query1)

Dim arr As String() = dt1.AsEnumerable().[Select](Function(x) x.Field(Of String)("MacId")).ToArray()

Dim result As String = String.Join(",", arr)

Or use this line:

Dim result As String = table.AsEnumerable().[Select](Function(x) x("MacId").ToString()).Aggregate(Function(a, b) String.Concat(a, "," & b))

Afterwards, the output string can be passed into query string with table-valued parameters, or using StringBuilder to add parameters for each element of string array created by ToArray() with WHERE IN query:

Dim sb As New StringBuilder()

' example query string
sb.Append("SELECT * FROM TableName WHERE ColumnName IN (")

Dim cmd As New SqlCommand()

' note: array is zero-based
Dim max As Integer = arr.Length - 1

For i As Integer = 0 To max
    If i = max Then
       sb.Append("@MacId" & i.ToString())
    Else
       sb.Append("@MacId" & i.ToString() & ",")
    End If
    cmd.Parameters.AddWithValue("@MacId" & i.ToString(), arr(i))
Next

sb.Append(")")

Dim query As String = sb.ToString()

cmd.CommandText = query

' execute the query

Update:

Since the query executed inside a function, you need to pass the query parameters to the function:

Dim sb As New StringBuilder()

' example query string
sb.Append("SELECT * FROM TableName WHERE ColumnName IN (")

Dim params As New List(Of SqlParameter)()

For i As Integer = 0 To max
    If i = max Then
       sb.Append("@MacId" & i.ToString())
    Else
       sb.Append("@MacId" & i.ToString() & ",")
    End If
    params.Add(New SqlParameter("@MacId" & i.ToString(), arr(i)))
Next

sb.Append(")")

Dim query As String = sb.ToString()

dt = GetData(query, params)

Function contents

Private Shared Function GetData(ByVal query As String, Optional ByVal parameters As List(Of SqlParameter) = Nothing) As DataTable

    Dim dt As New DataTable()
    Dim cmd As New SqlCommand(query)

    If parameter IsNot Nothing Then
       cmd.Parameters.AddRange(parameters.ToArray())
    End If

    Dim sda As New SqlDataAdapter()
    cmd.CommandType = CommandType.Text
    cmd.Connection = SQLDB_pp
    sda.SelectCommand = cmd
    sda.Fill(dt)
    Return dt

End Function

Convert date column to string in datatable, I am attempting to convert a DataTable Column into a String Array but find no easy way to do this. Would anyone have suggestions? Thanks in You can use LINQ. Assuming the column is of type string and without nulls: theTable.Rows.Select(r => r[0]).ToArray() You can add to the lambda to handle DB Nulls or type conversion.

You could use string.Join to patch together all the Rows values of a specific Column using as separator: ", " (comma + white space).

With the DataTable.Rows (DataRowCollection) as source, using the LINQ Select() method, you can transform the Rows[Column] content in the string format that you require.

A possible result:

Dim separator As String = ", "
Dim ColumnIndex as Integer = 0

Dim AllColumnValues As String = String.Join(separator, 
    dt1.Rows.OfType(Of DataRow).Select(Function(r) "'" + r(ColumnIndex).ToString() + "'"))

If you are referencing the System.Data.DataSetExtension assembly, you can use the DataTable.AsEnumerable extension. The code could change like this:

Dim AllColumnValues As String = String.Join(separator,
    dt1.AsEnumerable.Select(Function(r) "'" + r(ColumnIndex).ToString() + "'"))

If some of the values can be null/empty, you might want to add a Where() clause, filtering the null values:

Dim AllColumnValues As String = String.Join(separator, 
    dt1.AsEnumerable.Where(Function(r) Not r.IsNull(ColumnIndex)).
        Select(Function(r) "'" + r(ColumnIndex).ToString() + "'"))

Convert DataTable Column to String Array - Build, String. An expression to calculate the value of a column, or create an When you are using the CONVERT function, the requested cast is not possible. The following example creates three columns in a DataTable. is calculated using the Expression property, as shown in the Visual Basic code in NET Framework Type. Here Mudassar Ahmed Khan has explained how to Rotate / Flip / Transpose DataTable - Convert DataTable Columns to Rows and Rows to Columns using C# and VB.Net. He has also explained how we can get back the original DataTable from the Rotated / Flipped / Transposed DataTable using C# and VB.Net. TAGs: C#.Net, VB.Net

Finally after hours of trial and error, I finally got the string to work properly.

Note to self: Having a GridView to check the DataTable contents is a huge help.

Now my code basically looks like so:

    Dim query1 As String = String.Format("SELECT DISTINCT A.MacID FROM dbo.tblMachine A LEFT JOIN  dbo.tblDataHdr b on a.MacID = b.MacID where DayID between '" & TextBox1.Text & "' AND '" & TextBox3.Text & "' ORDER BY A.MACID")
    Dim dt1 As DataTable = GetData(query1)


    Dim result As String = dt1.AsEnumerable().[Select](Function(x) x("MacId").ToString()).Aggregate(Function(a, b) String.Concat(a & "'" & "," & "'" & b))


    Dim query As String = String.Format("SELECT Z.MacID, Z.EventName, ISNULL(DIFF,0) AS DIFF FROM (select distinct MacID, EventName from dbo.tblMachine a " _
                                            & "join (SELECT DISTINCT EVENTNAME FROM dbo.tblEvtDur where EventName <> 'ON'  ) b on b.EventName <> ''  and MacID in ('" & result & "') ) Z " _
                                            & "LEFT JOIN (SELECT A.MacID, A.EventName, SUM(DATEDIFF(SECOND, A.STARTdt, A.eNDdt)) as diff,round(SUM(DATEDIFF(SECOND, A.STARTdt, A.eNDdt)) / cast(aVG(Tdiff) " _
                                            & "as decimal(30,8)),4) * 100 AS PER FROM dbo.tblEvtDur  A LEFT JOIN ( SELECT MacID, SUM(DATEDIFF(SECOND, STARTdt, eNDdt)) as Tdiff FROM  dbo.tblEvtDur " _
                                            & "WHERE DayID between '" & TextBox1.Text & "' and '" & TextBox3.Text & "'  GROUP BY MacID ) B ON  A.MacID = B.MacID WHERE DayID " _
                                            & "between '" & TextBox1.Text & "' and '" & TextBox3.Text & "'  AND A.MacID in ('" & result & "') group by A.MacID, A.EventName) a " _
                                            & "ON A.EVENTNAME = Z.EVENTNAME  and Z.MacID  = a.MacID order by Z.MacID, Z.EventName")
    Dim dt As DataTable = GetData(query)

'Use this to help check the DataTable/Strings
GridView2.DataSource = dt 'change what you want to check accordingly
GridView2.DataBind()

By using GridView and binding the data to each query really helps in letting me see how the data looks. And the data shown in dt looks perfect.

Now my next problem is actually using the data to create a pie chart for each machine. But I'll post that in a separate question.

DataColumn.Expression Property (System.Data), VB.NET DataTable Examples. Store data in memory from databases and Columns: These are named with a string argument and a Type argument. GetTable: In a DataTable, each column allows a specific type of data. we could add a method that converts the Object array into an array of just the important elements. I have a DataTable with multiple columns. I want to get a List<String> out of first column of DataTable. How can I do that?

DataTable.ToString Method (System.Data), VB.NET DataTable Select Function. Use the DataTable type and its Select Function Dim table As DataTable = New DataTable("Players") ' Add 2 columns. table. Then: We pass the query string containing a DateTime substring to the Select Old post, but I thought I'd weigh in, with a DataTable extension that can convert a single column at a time, to a given type: public static class DataTableExt { public static void ConvertColumnType(this DataTable dt, string columnName, Type newType) { using (DataColumn dc = new DataColumn(columnName + "_new", newType)) { // Add the new column which has the new type, and move it to the ordinal

VB.NET DataTable Examples, Try this (LINQ method syntax): string[] columnNames = dt.Columns.Cast<DataColumn>() .Select(x => x.ColumnName) .ToArray();. or in LINQ Hi, I have two columns in dataset, first is integer, second column is also number, but in string format with commas, lile 2,345,564. I need the multiplication of these two columns in the third column using Expression.

VB.NET DataTable Select Function, This article shows 3 ways to convert a DataTable to a List in C#. public class Student; {; public int StudentId { get; set; }; public string StudentName Now I will convert the receding DataTable into a List< Student > using all the In this case the DataTable column's name and class property name should Disclaimer: This site is started with intent to serve the ASP.Net Community by providing forums (question-answer) site where people can help each other. The content posted here is free for public and is the content of its poster.

Comments

Probably what you need to convert single-column table to a String is Dim result As String = String.Join(",", DataTableName.AsEnumerable().[Select](Function(x) x.Field(Of String)("MacId")).ToArray()).
Ok. Then if I want to implement it into my SQL, should I enter it like so? ... and MacID in ('" & result & "')...
Just use String.Format to format the query with comma-separated values and use it for MySqlCommand, e.g. Dim command = String.Format("SELECT [something] FROM [tablename] WHERE FIND_IN_SET(MacId, {0})", YourResultString) or use TVP for that.
That's weird... I added that and now some of my machines are reported as invalid column name.
Hold up. Let me place my full sub here. Gives y'all some context.
I tried out the method you gave, since my SQL refers to my 1st query twice I had to use the sb.append twice as well. But I noticed when the code passes through my Private Shared Function I keep getting a double Incorrect syntax near ')'. error. What does that mean?
Incorrect syntax error caused because the last parameter contained additional comma ,, which should be removed when the loop reaching last value inside the array.
if I'm using Dim result only, then for Dim max as Integer will I set to result.Length - 1?
If result is a string array, yes, the max value must be set to result.Length - 1 because array has zero-based index. For the last index, you should remove the comma to build the query statement with IN clause.
Now I got some error on Private Shared Function again. Invalid column name 'MacID154'. to Invalid column name 'MacID0'.