Click here to Skip to main content
15,562,222 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
I have the description column in 2 different table and i also want only the first record in job_material for each pid in job_material table to be displayed in the excel.

The column 'description' was specified multiple times for 't'. Invalid column name 'id'.

I have five table namely

job 2 job_cylinder
job_die
job_ink
job_material
I am trying to export data from this five (5)table into excel, screen shot below

https://ctrl.vi/i/Ggl5CWCg6

What I want

https://ctrl.vi/i/oCmjz_lhi

please note that

the id in the job table(j) is the pid in the cylinder table (c) (inner join job_cylinder c on j.id =c.pid)
the dierefernceno in the job table (j) the id in job_die table (d) ( inner join job_die d on j.dierefernceno=d.id)
the id in the job table(j) is the pid in the job_ink table (i) (inner join job_ink i on j.id =i.pid)
the id in the job table(j) is the pid in the job_material table (m) (inner join job_material m on j.id =m.pid

What I have tried:

This is the mssqlcode with the raw data in it,so that you can just run the code in your mssql machine so as to see the error first hand.

USE [Test]
GO
/****** Object:  Table [dbo].[job]    Script Date: 23/10/2022 23:23:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[job](
    [id] [int] NULL,
    [pid] [nvarchar](50) NULL,
    [description] [nvarchar](50) NULL,
    [variant] [nvarchar](50) NULL,
    [country] [nvarchar](50) NULL,
    [pack] [nvarchar](50) NULL,
    [customer] [nvarchar](50) NULL,
    [artworkdate] [nvarchar](50) NULL,
    [dierefernceno] [nvarchar](50) NULL,
    [templateno] [nvarchar](50) NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[job_cylinder]    Script Date: 23/10/2022 23:23:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[job_cylinder](
    [id] [int] NULL,
    [pid] [int] NULL,
    [unit] [int] NULL,
    [posino] [nvarchar](50) NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[job_die]    Script Date: 23/10/2022 23:23:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[job_die](
    [id] [int] NULL,
    [dieno] [nvarchar](50) NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[job_ink]    Script Date: 23/10/2022 23:23:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[job_ink](
    [id] [int] NULL,
    [pid] [int] NULL,
    [unit] [int] NULL,
    [description] [nvarchar](50) NULL,
    [inkcode] [nvarchar](50) NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[job_material]    Script Date: 23/10/2022 23:23:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[job_material](
    [id] [int] NULL,
    [pid] [int] NULL,
    [materialcode] [nvarchar](50) NULL,
    [boardname] [nvarchar](50) NULL,
    [materialgsm] [nvarchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[job] ([id], [pid], [description], [variant], [country], [pack], [customer], [artworkdate], [dierefernceno], [templateno]) VALUES (1, N'SP001', N'AB-CD-EF', N'TALL', N'SPAIN', N'24A', N'SONI', N'20/10/2022', N'1', N'2001A')
GO
INSERT [dbo].[job] ([id], [pid], [description], [variant], [country], [pack], [customer], [artworkdate], [dierefernceno], [templateno]) VALUES (2, N'SP002', N'GH-IJ-KL', N'SHORT', N'UK', N'24B', N'PANON', N'26/11/2013', N'2', N'2002B')
GO
INSERT [dbo].[job] ([id], [pid], [description], [variant], [country], [pack], [customer], [artworkdate], [dierefernceno], [templateno]) VALUES (3, N'SP003', N'MN-OP-QR', N'MEDIUM', N'USA', N'24C', N'LGE', N'20/9/2017', N'3', N'2003C')
GO
INSERT [dbo].[job] ([id], [pid], [description], [variant], [country], [pack], [customer], [artworkdate], [dierefernceno], [templateno]) VALUES (4, N'SP004', N'ST-UV-WX', N'TALL-SHORT', N'DENMARK', N'24D', N'HPA', N'18/6/2016', N'4', N'2004D')
GO
INSERT [dbo].[job_cylinder] ([id], [pid], [unit], [posino]) VALUES (1, 1, 1, N'A2300')
GO
INSERT [dbo].[job_cylinder] ([id], [pid], [unit], [posino]) VALUES (2, 1, 2, N'A2301')
GO
INSERT [dbo].[job_cylinder] ([id], [pid], [unit], [posino]) VALUES (3, 1, 3, N'A2302')
GO
INSERT [dbo].[job_cylinder] ([id], [pid], [unit], [posino]) VALUES (4, 1, 4, N'A2303')
GO
INSERT [dbo].[job_die] ([id], [dieno]) VALUES (1, N'D3900')
GO
INSERT [dbo].[job_die] ([id], [dieno]) VALUES (2, N'D4900')
GO
INSERT [dbo].[job_die] ([id], [dieno]) VALUES (3, N'D5900')
GO
INSERT [dbo].[job_die] ([id], [dieno]) VALUES (4, N'D6900')
GO
INSERT [dbo].[job_ink] ([id], [pid], [unit], [description], [inkcode]) VALUES (1, 1, 1, N'6700A', N'BC678')
GO
INSERT [dbo].[job_ink] ([id], [pid], [unit], [description], [inkcode]) VALUES (2, 1, 2, N'6700B', N'BD679')
GO
INSERT [dbo].[job_ink] ([id], [pid], [unit], [description], [inkcode]) VALUES (3, 1, 3, N'6700C', N'BD701')
GO
INSERT [dbo].[job_ink] ([id], [pid], [unit], [description], [inkcode]) VALUES (4, 1, 4, N'6700D', N'BD703')
GO
INSERT [dbo].[job_material] ([id], [pid], [materialcode], [boardname], [materialgsm]) VALUES (1, 1, N'SNG111', N'SILBLUE', N'G2000')
GO
INSERT [dbo].[job_material] ([id], [pid], [materialcode], [boardname], [materialgsm]) VALUES (2, 1, NULL, NULL, NULL)
GO
INSERT [dbo].[job_material] ([id], [pid], [materialcode], [boardname], [materialgsm]) VALUES (3, 2, N'SNG222', N'GLDRED', N'G3000')
GO
INSERT [dbo].[job_material] ([id], [pid], [materialcode], [boardname], [materialgsm]) VALUES (4, 2, NULL, NULL, NULL)
GO
INSERT [dbo].[job_material] ([id], [pid], [materialcode], [boardname], [materialgsm]) VALUES (5, 3, N'SNG333', N'BLKBLUE', N'G4000')
GO
INSERT [dbo].[job_material] ([id], [pid], [materialcode], [boardname], [materialgsm]) VALUES (6, 3, NULL, NULL, NULL)
GO
INSERT [dbo].[job_material] ([id], [pid], [materialcode], [boardname], [materialgsm]) VALUES (7, 4, N'SNG444', N'YLWGRY', N'G5000')
GO
INSERT [dbo].[job_material] ([id], [pid], [materialcode], [boardname], [materialgsm]) VALUES (8, 4, NULL, NULL, NULL)
GO
This is the Default3.aspx Code which is the code in the front end.It contains only the export button.The idea is that on button click it export data from all the 5 tables using inner join unto an excel file.

<%@ Page Language="C#" AutoEventWireup="true" Debug="true"         CodeFile="Default3.aspx.cs" Inherits="Default3" %>





   <title>


   
       <asp:button id="Button1" text="Export" runat="server" onclick="OnExport">
   


This is the Default3.aspx.cs Code which is the code behind.Please note that you have to install the following below in your bin

ClosedXML.dll
ClosedXML.dll.refresh
DocumentFormat.OpenXml.dll




    using System;
    using System.Collections.Generic;
    using System.Configuration;
    using System.Data;
    using System.Data.SqlClient;
    using System.IO;
    using ClosedXML.Excel;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    
    public partial class Default3 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
        }
    
        private DataTable GetData(SqlCommand cmd)
        {
            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString))
            {
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                cmd.CommandType = CommandType.Text;
                cmd.Connection = con;
                DataTable dt = new DataTable();
                sda.Fill(dt);
    
                return dt;
            }
        }
    
        protected void OnExport(object sender, EventArgs e)
        {
    
    
    
            SqlCommand cmd = new SqlCommand("SELECT id, pid,description,variant,country,pack,customer,artworkdate,templateno,dieno,materialcode,boardname,materialgsm,posi1=(select posino from job_Cylinder where unit='1'),inkcode1=(select inkcode from job_ink where unit='1'),description1=(select description from job_ink where unit='1')FROM(SELECT    ROW_NUMBER() OVER(PARTITION BY j.id ORDER BY (j.id)) RowNo,j.pid,j.description,j.variant,j.country,j.pack,j.customer,j.artworkdate,j.templateno,d.dieno,m.materialcode,m.boardname,m.materialgsm,c.posino,i.inkcode,i.description from job j inner join job_die d on j.dierefernceno=d.id inner join job_material m on j.id = m.pid inner join job_cylinder c on j.id =c.pid inner join job_ink i  on j.id=i.pid  ) t WHERE t.RowNo = 1");
    
    
    
            DataTable dt1 = GetData(cmd);
    
    
    
            DataTable dt = new DataTable("Data");
            dt.Columns.AddRange(new DataColumn[]
            {
    
    
    
               new DataColumn("Pid"),
    new DataColumn(" Description"),
    new DataColumn("Variant"),
    new DataColumn("Country"),
    new DataColumn("Pack"),
    new DataColumn("Customer"),
    new DataColumn("Artworkdate"),
    new DataColumn("Templateno"),
    new DataColumn(" Die No"),
    new DataColumn("Materialcode"),
    new DataColumn("Boardname"),
    new DataColumn("Materialgsm"),
    new DataColumn("CYL POSI NO.1"),
    new DataColumn("INKCODE.1"),
    new DataColumn("INKDESCRTION.1")
    
     });
            using (XLWorkbook wb = new XLWorkbook())
            {
                foreach (DataRow row in dt1.Rows)
                {
                    dt.Rows.Add();
                    for (int j = 0; j < row.ItemArray.Length; j++)
                    {
                        dt.Rows[dt.Rows.Count - 1][j] = row.ItemArray[j].ToString().Trim();
                    }
                }
    
    
    
                var ws = wb.Worksheets.Add(dt);
                ws.Table(0).ShowAutoFilter = false;
                ws.Table(0).Theme = XLTableTheme.None;
                ws.Columns().AdjustToContents();
                ws.Row(1).Style.Font.Bold = true;
                Response.Clear();
                Response.Buffer = true;
                Response.Charset = "";
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                Response.AddHeader("content-disposition", "attachment;filename=Data.xlsx");
    
                using (MemoryStream memoryStream = new MemoryStream())
                {
                    wb.SaveAs(memoryStream);
                    memoryStream.WriteTo(Response.OutputStream);
                    Response.Flush();
                    Response.End();
                }
            }
        }
    }
Please note that I only need the first record in the material table.
Posted
Updated 25-Oct-22 12:16pm

1 solution

I'm not about to load up a database and run your script. What I will do is offer tips to debug your issue.

First thing is to simplify the db access - use a View. Here is a great article that covers this topic: SQL View – A complete introduction and walk-through - SqlShack[^]

Once the view returns normalised data (query result) in your view, you can then do a simple Select * from [view_name].

Now you can debug the code that works with Excel.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900