From aspx page
sqlcmd = new SqlCommand("select * from [RptAuditCrystal] where EditedBy='" + Session["userid"].ToString() + "' order by " + Request.QueryString["order"].ToString(), sqlcon);
da = new SqlDataAdapter(sqlcmd);
dt3.Clear();
da.Fill(dt3);
sqlcmd.Dispose();
sqlcmd = new SqlCommand("rpt_closeaudituploaddata";, sqlcon);
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Parameters.AddWithValue("@AuditPlanId", Request.QueryString["Id"].ToString());
sqlcmd.Parameters.AddWithValue("@EditedBy", Session["userid"].ToString());
da = new SqlDataAdapter(sqlcmd);
dt4.Clear();
da.Fill(dt4);
sqlcmd.Parameters.Clear();
sqlcmd.Dispose();
From SQL Stored Procedure -
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[rpt_closeaudituploaddata]
@AuditPlanId varchar(30)
,@EditedBy varchar(20)
AS
BEGIN TRY
SET NOCOUNT ON;
delete from RptTemplateData where EditedBy=@EditedBy
Declare @QuestionID varchar(20)
Declare @TemplateId varchar(20)
Declare @sql varchar(max)
Declare @coldata varchar(2000)
Declare @colname varchar(2000)
DECLARE @PKgetLevelID CURSOR
SET @PKgetLevelID = CURSOR FOR select distinct TA.QuestionID,MT.TemplateId from TrnsAuditorUploadedData TA left join MstrUploadTemplate MT on MT.UploadId=TA.UploadId where TA.IsActive='Y' and AuditPlanId=@AuditPlanId order by TA.QuestionID
OPEN @PKgetLevelID
FETCH NEXT
FROM @PKgetLevelID INTO @QuestionID,@TemplateId
WHILE @@FETCH_STATUS = 0
BEGIN
set @coldata = (select dbo.GetReportTemplate(@TemplateId))
set @colname = (select dbo.GetReportTemplateColumns(@TemplateId))
set @sql = 'INSERT INTO [dbo].[RptTemplateData] ([QuestionID],[QuestionDesc]
,[RCol1],[RCol2],[RCol3],[RCol4],[RCol5],[RCol6],[RCol7]
,[RColName1],[RColName2],[RColName3],[RColName4],[RColName5],[RColName6],[RColName7],[EditedBy]
,[AuditorRM],[AuditorRMOn],[AuditorRMBy],[AuditeeRMSR],[AuditeeRMSROn],[AuditeeRMSRBy],[AuditeeRMCP],[AuditeeRMCPOn],[AuditeeRMCPBy])
select MQ.QuestionID,MQ.QuestionDesc,' + @coldata + ',' + @colname + ', ''' + @EditedBy + '''
,[AuditorRM],AuditorRMOn=CONVERT(varchar,CAST([AuditorRMOn] as datetime2(0)),109),[AuditorRMBy]
,[AuditeeRMSR],AuditeeRMSROn=CONVERT(varchar,CAST([AuditeeRMSROn] as datetime2(0)),109),[AuditeeRMSRBy]
,[AuditeeRMCP],AuditeeRMCPOn=CONVERT(varchar,CAST([AuditeeRMCPOn] as datetime2(0)),109),[AuditeeRMCPBy]
from TrnsAuditorUploadedData TA left join MstrQuestions MQ on MQ.QuestionId=TA.QuestionId
where AuditPlanId=''' + @AuditPlanId + ''' and TA.QuestionID=''' + @QuestionID + ''' '
exec (@sql)
FETCH NEXT
FROM @PKgetLevelID INTO @QuestionID,@TemplateId
END
CLOSE @PKgetLevelID
DEALLOCATE @PKgetLevelID
select * from RptTemplateData where EditedBy=@EditedBy
END TRY
BEGIN CATCH
DECLARE @errmsg NVARCHAR(4000)
DECLARE @errseverity INT
DECLARE @errstate INT
SELECT @errmsg = ERROR_MESSAGE(), @errseverity = ERROR_SEVERITY(), @errstate = ERROR_STATE()
RAISERROR(@errmsg, @errseverity, @errstate)
END CATCH
How I can join "dt3" result and "dt4" result in one single DataTable?