locked
Error when trying to merge two datatables RRS feed

  • Question

  • User1717218719 posted

    Hi all,

    I am trying to merge two different tables in sql. I then want to export them to excel. I wrote out some code but I cant seem to get it to work.

    I recieve the error "Column 'testx' does not belong to table". If I execute the code without mergeing the table the code runs perfectly so it Is defintly Not a naming issue. any help With why I am getting this Error would be great.

    thanks

        Protected Sub ExpExcel_Click(sender As Object, e As EventArgs) Handles ExpExcel.Click
    
            conConn = New SqlConnection(SQLCON)
    
            comComm = New SqlCommand
            comComm.Connection = conConn
            comComm.CommandType = CommandType.Text
            comComm.CommandText = "SELECT Statement"
    
            Dim sda As New SqlDataAdapter(comComm)
            Dim dtsales As New DataTable()
            sda.Fill(dtsales)
    
    
    
            '--Read from Database
            comComm = New SqlCommand
            comComm.Connection = conConn
            comComm.CommandType = CommandType.Text
            comComm.CommandText = "SELECT Statement"
    
            comComm.Parameters.AddWithValue("@date", txtdate.Text)
            comComm.Parameters.AddWithValue("@ID", txtID.Text)
    
            Dim dt As New DataTable()
            sda.Fill(dt)
    
    
            '-- MERGE
            dt.Merge(dtsales)
    
            Dim template As New FileInfo(Server.MapPath("Xls/Template.xlsx"))
    
            Using package = New ExcelPackage(template)
                Dim workbook = package.Workbook
    
                '*** Sheet 1
                Dim worksheet = workbook.Worksheets.First()
                Dim startRows As Integer = 3
                For i As Integer = 0 To dt.Rows.Count - 1
                    worksheet.Cells("A" & (i + startRows)).Value = dt.Rows(i)("testx").ToString()
                    worksheet.Cells("B" & (i + startRows)).Value = dt.Rows(i)("testy").ToString()
                    worksheet.Cells("C" & (i + startRows)).Value = dt.Rows(i)("testz").ToString()
                Next
    
                package.SaveAs(New FileInfo(Server.MapPath("Xls/myExcel.xlsx")))
    
            End Using
    
            sda = Nothing
            conConn.Close()
    
        End Sub

    Friday, March 13, 2020 1:20 PM

All replies

  • User-719153870 posted

    Hi E.RU,

    I recieve the error "Column 'testx' does not belong to table".

    I've tried your code and it works like a charm, please check your two SELECT Statements and make sure the "testx" exists in both of the select output, or you can debug and check your dtsales and dt directly.

    Below is the demo built based on your code:

        Protected Sub Button1_Click(sender As Object, e As EventArgs)
            Dim conConn = New SqlConnection("Data Source=.;Initial Catalog=TestDB;Integrated Security=True")
    
            Dim comComm = New SqlCommand
            comComm.Connection = conConn
            comComm.CommandType = CommandType.Text
            comComm.CommandText = "SELECT UID,UName,UAge from Users where UID <= 4"
    
            Dim sda As New SqlDataAdapter(comComm)
            Dim dtsales As New DataTable()
            sda.Fill(dtsales)
    
    
    
            '--Read from Database
            comComm = New SqlCommand
            comComm.Connection = conConn
            comComm.CommandType = CommandType.Text
            comComm.CommandText = "SELECT UID,UName,UAge from Users where UID > 4"
    
            'comComm.Parameters.AddWithValue("@date", txtdate.Text)
            'comComm.Parameters.AddWithValue("@ID", txtID.Text)
    
            Dim sda1 As New SqlDataAdapter(comComm)
    Dim dt As New DataTable()
    sda1.Fill(dt) '-- MERGE dt.Merge(dtsales) Dim template As New FileInfo(Server.MapPath("files/Template.xlsx")) Using package = New ExcelPackage(template) Dim workbook = package.Workbook '*** Sheet 1 Dim worksheet = workbook.Worksheets.First() Dim startRows As Integer = 3 For i As Integer = 0 To dt.Rows.Count - 1 worksheet.Cells("A" & (i + startRows)).Value = dt.Rows(i)("UID").ToString() worksheet.Cells("B" & (i + startRows)).Value = dt.Rows(i)("UName").ToString() worksheet.Cells("C" & (i + startRows)).Value = dt.Rows(i)("UAge").ToString() Next package.SaveAs(New FileInfo(Server.MapPath("files/myExcel.xlsx"))) End Using sda = Nothing conConn.Close() End Sub

    PS: Not sure why would you do sda.Fill(dt) which fill the same data as dtsales into dt, so i changed to another SqlDataAdapter.

    The Users table in SQL:

    myExcel.xlsx:

    Best Regard,

    Yang Shen

    Monday, March 16, 2020 3:01 AM
  • User-1330468790 posted

    Hi, E.RU,

      

    The reason of the error, as it said, should be the missing column of "testx" in "dt" when you are trying to access it.

    About merge

    The merge will auto add the key if you use the merge method with only one parameter "target datatable".

    dt.Merge(dtsales)

    For example:

    1. dt ("testx", "testy","testz") and  dtsales ("testy","testz")

    after merger: dt ("testx", "testy","testz"), but the row from "dtsales" will be empty.

    2. dt ("testy","testz") and  dtsales ("testx","testy","testz") 

    after merger: dt ("testx", "testy","testz"), but the row from "dt" will be empty.

    3. dt ("testy","testz") and dtsales ("testy","testz")

    after merger: dt("testy","testz"), then "dt" does not contain column named "testx".

     

    Suggestion:

    I suggest you check the columns of the datatable "dt" to see if there is an column named "testx" after the merge by adding break points. Then it should be something wrong in the original data table "dt", while you said it runs perfectly without the merger but you might occasionally change a code or just make a simple spelling error.

    Another way to check would be surrounding the code with code as below:

    If dt.Columns.Contains("testx") Then
        ......
    Else
        ......
    End If

    More details, you could refer to below code in yellow background:

    Protected Sub ExpExcel_Click(ByVal sender As Object, ByVal e As EventArgs)
        conConn = New SqlConnection(SQLCON)
        comComm = New SqlCommand()
        comComm.Connection = conConn
        comComm.CommandType = CommandType.Text
        comComm.CommandText = "SELECT Statement"
        Dim sda As SqlDataAdapter = New SqlDataAdapter(comComm)
        Dim dtsales As DataTable = New DataTable()
        sda.Fill(dtsales)
        comComm = New SqlCommand()
        comComm.Connection = conConn
        comComm.CommandType = CommandType.Text
        comComm.CommandText = "SELECT Statement"
        comComm.Parameters.AddWithValue("@date", txtdate.Text)
        comComm.Parameters.AddWithValue("@ID", txtID.Text)
        Dim dt As DataTable = New DataTable()
        sda.Fill(dt)
        dt.Merge(dtsales)
        Dim template As FileInfo = New FileInfo(Server.MapPath("Xls/Template.xlsx"))
    
        Using package = New ExcelPackage(template)
            Dim workbook = package.Workbook
            Dim worksheet = workbook.Worksheets.First()
            Dim startRows As Integer = 3
    
            For i As Integer = 0 To dt.Rows.Count - 1
    
                If dt.Columns.Contains("testx") Then
                    worksheet.Cells("A" & (i + startRows)).Value = dt.Rows(i)("testx").ToString()
                Else
                    worksheet.Cells("A" & (i + startRows)).Value = "No data"
                End If
    
                worksheet.Cells("B" & (i + startRows)).Value = dt.Rows(i)("testy").ToString()
                worksheet.Cells("C" & (i + startRows)).Value = dt.Rows(i)("testz").ToString()
            Next
    
            package.SaveAs(New FileInfo(Server.MapPath("Xls/myExcel.xlsx")))
        End Using
    
        sda = Nothing
        conConn.Close()
    End Sub

      

    If you could provide more details, for example, the column names in the select statement for "dt" and "dtsales", it would be much helpful to target the problem more precisely.

    With the version of the framework, it would be better.

     

    Best regards,

    Sean

    Monday, March 16, 2020 3:31 AM
  • User1717218719 posted

    thakyou for your reply.

    I think there couls be isuue with my select statement as I have a statement like this:

    "SELECT UID,UName,UAge from Users where UID = 4"

    "SELECT TID,TName,TAge from Test where ID = 4"

    I simply want to merege two tables and cant use an inner join in this case.

    Monday, March 16, 2020 8:02 AM
  • User-1330468790 posted

    Hi, E.RU,

     

    You can still use the merge by changing the select statement with alias as below:

    SELECT UID AS ID,UName AS Name,UAge AS Age from Users where UID = 4;  -- for dt
    SELECT TID AS ID,TName AS Name,TAge AS Age from Test where TID = 4; -- for dtsales

    Code:

    dt.Merge(dtsales)

    Then you should use "ID", "Name", "Age" as column names to access the data from merged data table. 

    Note that the data type for the merged columns should be the same/compatible.

     

    The approach is also applicable for the inner join.

     

    Best regards,

    Sean

    Monday, March 16, 2020 8:44 AM