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
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
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
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
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
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.
Submit an article or tip