Hi, i have the following codes inside my console apps c#:
using (conn = new SqlConnection(GeminiConnString))
{
using (command = new SqlCommand("dbo.CZ_InsertIntoProjectManagementOutstandingExcel", conn))
{
conn.Open();
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@startDate", StartDate);
command.Parameters.AddWithValue("@endDate", EndDate);
command.ExecuteNonQuery();
SqlDataAdapter da = new SqlDataAdapter(command);
DataSet ds = new DataSet();
da.Fill(ds);
dt = ds.Tables[0];
conn.Close();
}
}
whenever i run it, it would prompt me an error as the title above, below are my stored procedure. Stored procedure runs fine, only has error in the c# code:
ALTER PROCEDURE [dbo].[CZ_InsertIntoProjectManagementOutstandingExcel]
@STARTDATE VARCHAR(MAX),
@ENDDATE VARCHAR(MAX)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(MAX)
SET @SQL =
'
DROP TABLE CustomizationYesNo
DROP TABLE SystemDetermination
DROP TABLE ProjectName
DROP TABLE CustName
DROP TABLE RequesterName
DROP TABLE VersionNumber
DROP TABLE GetTargetDate
DROP TABLE GetActualDate
DROP TABLE ProjectManagement
CREATE TABLE CustomizationYesNo(IssueID VARCHAR(100), YesNo VARCHAR(100))
INSERT [CustomizationYesNo]
SELECT issueid, CASE WHEN projectcode IN (''CESS'', ''CHRM'') THEN ''Yes'' ELSE ''No'' END as Customization
FROM [dbo].[gemini_issuesview]
CREATE TABLE SystemDetermination(IssueID VARCHAR(100), Systems VARCHAR(100))
INSERT [SystemDetermination]
SELECT
i.issueid,
CASE SUBSTRING(projectname, 1, 1)
WHEN ''E'' THEN ''ESS''
WHEN ''F'' THEN ''HRMS''
ELSE ''PayFlex''
END AS System
FROM [dbo].[gemini_projects] p, [dbo].[gemini_issuesview] i
WHERE p.projectid = i.projectid
CREATE TABLE ProjectName(IssueID VARCHAR(100), ProjectName VARCHAR(100))
INSERT [ProjectName]
SELECT i.issueid, projectname
FROM [dbo].[gemini_projects] p,[dbo].[gemini_issuesview] i
WHERE p.projectid = i.projectid
CREATE TABLE CustName(IssueID VARCHAR(100), CustomerName VARCHAR(100))
INSERT [CustName]
SELECT i.issueid, fielddata
FROM [dbo].[gemini_customfielddata] c, [dbo].[gemini_issuesview] i
WHERE customfieldid = 200 AND c.issueid = i.issueid
CREATE TABLE RequesterName(IssueID VARCHAR(100), ReqName VARCHAR(100))
INSERT [RequesterName]
SELECT i.issueid, firstname
FROM [dbo].[gemini_users] u , [dbo].[gemini_issuesview] i
WHERE u.userid = i.reportedby
CREATE TABLE VersionNumber(IssueID VARCHAR(100), VersionsNum VARCHAR(100))
INSERT [VersionNumber]
SELECT i.issueid, MAX(v.versionnumber) AS Versions
FROM [dbo].[gemini_versions] v, [dbo].[gemini_issuesview] i
WHERE i.projectid = V.projectid
GROUP BY i.issueid
ORDER BY i.issueid
CREATE TABLE GetTargetDate(IssueID VARCHAR(100), TargetDate VARCHAR(100))
INSERT [GetTargetDate]
SELECT issueid, fielddata
FROM [dbo].[gemini_customfielddata]
WHERE customfieldid = 215
CREATE TABLE GetActualDate(IssueID VARCHAR(100), ActualDate VARCHAR(100))
INSERT [GetActualDate]
SELECT issueid, fielddata
FROM [dbo].[gemini_customfielddata]
WHERE customfieldid = 217
CREATE TABLE ProjectManagement(Category VARCHAR(100), Systems VARCHAR(100), Module VARCHAR(100), Customization VARCHAR(100), Descriptions VARCHAR(MAX),
Customer VARCHAR(100), Statuss VARCHAR(100), CSD_Requester VARCHAR(100), CSD_Request_Date VARCHAR(100), GeminiID VARCHAR(100), Versions VARCHAR(100),
TargetDate VARCHAR(100), ActualDate VARCHAR(100))
INSERT [ProjectManagement]
SELECT i.typedesc, s.Systems, p.ProjectName, c.YesNo, i.longdesc, n.CustomerName, i.statusdesc, r.ReqName, REPLACE(CONVERT(VARCHAR(100), i.created, 106), '' '', ''-''), i.issuekey, v.VersionsNum, REPLACE(t.TargetDate, ''/'', ''-''), REPLACE(a.ActualDate, ''/'', ''-'')
FROM [dbo].[gemini_issuesview] i, SystemDetermination s, ProjectName p, CustomizationYesNo c, CustName n, RequesterName r, VersionNumber v, GetTargetDate t, GetActualDate a
WHERE i.issueid = s.IssueID AND i.issueid = p.IssueID AND s.IssueID = p.IssueID
AND i.issueid = c.IssueID AND p.IssueID = c.IssueID AND s.IssueID = c.IssueID
AND i.issueid = n.IssueID AND p.IssueID = n.IssueID AND c.IssueID = n.IssueID AND s.IssueID = n.IssueID
AND i.issueid = r.IssueID AND p.IssueID = r.IssueID AND c.IssueID = r.IssueID AND n.IssueID = r.IssueID AND s.IssueID = r.IssueID
AND i.issueid = v.IssueID AND p.IssueID = v.IssueID AND c.IssueID = v.IssueID AND n.IssueID = v.IssueID AND s.IssueID = v.IssueID AND r.IssueID = v.IssueID
AND i.issueid = t.IssueID AND p.IssueID = t.IssueID AND c.IssueID = t.IssueID AND n.IssueID = t.IssueID AND s.IssueID = t.IssueID AND r.IssueID = t.IssueID AND v.IssueID = t.IssueID
AND i.issueid = a.IssueID AND p.IssueID = a.IssueID AND c.IssueID = a.IssueID AND n.IssueID = a.IssueID AND s.IssueID = a.IssueID AND r.IssueID = a.IssueID AND v.IssueID = a.IssueID AND t.IssueID = a.IssueID
SELECT * FROM ProjectManagement
'
IF(@STARTDATE != '' AND @ENDDATE != '')
SELECT @SQL = @SQL + ' WHERE CONVERT(DATE, CSD_Request_Date) BETWEEN CONVERT(DATE, '+@STARTDATE+') AND CONVERT(DATE, '+@ENDDATE+')'
ELSE
SELECT @SQL = @SQL
SELECT @SQL = @SQL
EXEC sp_executesql @SQL
END