'1'附近的语法不正确 [英] Incorrect syntax near '1'

问题描述

我的控制台应用程序中有以下代码c＃：

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(); //<-- error here

    //Sto-Pro Details
    SqlDataAdapter da = new SqlDataAdapter(command);
    DataSet ds = new DataSet();
    da.Fill(ds);
    dt = ds.Tables[0];

    conn.Close();
}
}

每当我运行它时，它会提示我错误，如下面的标题，下面是我的存储过程。存储过程运行正常，只有c＃代码中的错误：

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]
-- Add the parameters for the stored procedure here
@STARTDATE VARCHAR(MAX),
@ENDDATE VARCHAR(MAX)
	
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
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

推荐答案

1st C hange -


SP中的
，更改参数为datetime，而不是nvarchar或varchar。



2nd



使用以下语法

Hi,

1st Change -

in SP, Change parameters as datetime, not in nvarchar or varchar.

2nd

Use below syntax in where

SELECT @SQL = @SQL + ' WHERE CONVERT(DATE, CSD_Request_Date) BETWEEN CONVERT(DATE, '''+@STARTDATE+''') AND CONVERT(DATE, '''+@ENDDATE+''')'

使用DateTime.TryParse或C＃代码将日期值转换为DateTime DateTime.TryParseExact，并通过参数将它们作为有效的DateTime值传递。

修改你的SP直接接受Date值而不是NVARCHAR并摆脱SP内部的转换。



那么它会正常工作......

Convert your date values to DateTime in your C# code, using DateTime.TryParse or DateTime.TryParseExact, and pass them through as valid DateTime values via the parameters.
Modify your SP to accept Date values directly instead of NVARCHAR and get rid of the conversions inside the SP.

Then it'll work fine...

你好，

首先检查查询中的参数数据类型。因为你在SP中声明了@STARTDATE VARCHAR（MAX），@ ENDDATE VARCHAR（MAX），因此检查这两个对象是否是字符串。



另一种方式

如果你想填充DataSet，那么只需将connectionstring和sqlcommand（作为字符串）放在DataAdapter对象中。它有直接接受那些参数的构造函数。

Hello ,
first of all check the parameters datatype in your query . as you have declared the @STARTDATE VARCHAR(MAX),@ENDDATE VARCHAR(MAX) in SP , hence check whether these two objects are string or not .

another way
if you want to fill the DataSet then just put the connectionstring and sqlcommand(as string) in DataAdapter object . It has constructor which directly takes those paramter .

 //one way
  SqlDataAdapter da = new SqlDataAdapter(string command , string connection);
  DataSet ds = new DataSet();
  da.Fill(ds);


//another way
  string cmdString = "Select * FROM table";
  var cmd = new SqlCommand(cmdString, connection);
  SqlDataAdapter da= new SqlDataAdapter(cmd);
  DataSet ds = new DataSet();
  da.Fill(ds);

希望它可以帮到你

hope it helps you

这篇关于'1'附近的语法不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！