I am having the error below while exporting data to excel .please note i need to add posino for unit1 and unit2 from job_cylinder table, inkcode for unit1 and unit 2 from job_ink table and lastly ink description for unit1 and unit 2 in job_ink table in the join. but I am having this error.
Incorrect syntax near '='. Incorrect syntax near ','. Incorrect syntax near ','. Incorrect syntax near ','. Incorrect syntax near ','.
USE [Test]
GO
/****** Object: Table [dbo].[job] Script Date: 27/10/2022 14:32:34 ******/
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: 27/10/2022 14:32:34 ******/
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: 27/10/2022 14:32:34 ******/
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: 27/10/2022 14:32:34 ******/
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: 27/10/2022 14:32:34 ******/
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, 2, 1, N'AHJKI1')
GO
INSERT [dbo].[job_cylinder] ([id], [pid], [unit], [posino]) VALUES (4, 2, 2, N'AHJK2')
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, 2, 1, N'6300F', N'BC788')
GO
INSERT [dbo].[job_ink] ([id], [pid], [unit], [description], [inkcode]) VALUES (6, 2, 2, N'6300H', N'BCGHO')
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
public partial class Default2 : System.Web.UI.Page
{
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)
{
string query = "SELECT id, pid,[description], variant, country, pack,customer,artworkdate,dierefernceno, templateno,dieno, materialcode, boardname, materialgsm ,ISNULL(posino1, '') CYL1,ISNULL(posino2 , '') CYL2,ISNULL(inkcode1, '') inkcode1,ISNULL(inkcode2, '') inkcode2,ISNULL([descript1],'') INKDESC1,ISNULL([descript2],'') INKDESC2 ";
query += "FROM(Select ROW_NUMBER() OVER(PARTITION BY j.[pid] ORDER BY(j.[id])) RowNo,j.id, j.pid, j.[description], j.variant, j.country, j.pack, j.customer, j.artworkdate, j.dierefernceno, j.templateno,d.dieno, m.materialcode, m.boardname, m.materialgsm, c.posino1=(select posino from job_Cylinder where unit='1'),c.posino2=(select posino from job_Cylinder where unit=2), i.inkcode1=(select inkcode from job_ink where unit='1'),i.inkcode2=(select inkcode from job_ink where unit='2'), i.[description1=(Select description from job_ink where unit='1')][descript1], i.[description2=(Select description from job_ink where unit='2')][descript2] From job j ";
query += "inner join job_die d on j.id= d.id inner join job_material m on d.id = m.pid" +
" LEFT OUTER join job_cylinder c on j.id = c.pid LEFT OUTER join job_ink i on j.id = i.pid)t WHERE t.RowNo = 1";
SqlCommand cmd = new SqlCommand(query);
DataTable dt = GetData(cmd);
using (XLWorkbook wb = new XLWorkbook())
{
var ws = wb.Worksheets.Add(dt, "Table");
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();
}
}
}
}
