我试图在C#DataGridView中显示动态枢轴的结果。到目前为止,我已经有以下代码,但是我很想将@date变量合并到@Query字符串中。我在这里想念什么?该代码在硬编码日期中正常工作,并且随着返回其他信息:" @date"附近的语法不正确。请帮助,
谢谢
da2.SelectCommand = new SqlCommand(@"DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(currency)
FROM Alpha.dbo.Beta
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
SET @query = 'SELECT Customer, ' + @cols + ' FROM
(
SELECT
Customer, Amount, Currency
FROM Alpha.dbo.Beta
WHERE Date Between ''2010-01-01'' and '@Date' ----PROBLEM AREA----
) x
PIVOT
(
SUM(Amount)
for Currency in (' + @cols + ')
) AS pvt
ORDER BY Customer; '
execute(@query)", MyConnection);
da2.SelectCommand.Parameters.Add("Date", SqlDbType.DateTime).Value = dateTimePicker4.Text;
ds2.Clear();
da2.Fill(ds2);
更正您的代码为:
da2.SelectCommand.Parameters.Add("@Date", SqlDbType.DateTime);
da2.SelectCommand.Parameters["@Date"].Value = dateTimePicker4.Text;
另外,使用 stored procedure
代替查询。