我收到“1'附近的语法不正确。”错误

My C# code is pasted below:

我的C#代码粘贴在下面:

SqlConnection con = new SqlConnection("Data Source=pawan-pc;Initial Catalog=App91;Integrated Security=True");
try
{
    SqlCommand cmd = new SqlCommand("sp_gettinglogsfromdevice", con);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@tablename","DeviceLogs_"+comboBox_Attendance_SelMonth.Text+"_"+combobox_Attendance_SelectYear.Text);
    cmd.Parameters.AddWithValue("@fromduration",datetimepicker_att_From.Value.Date);
    cmd.Parameters.AddWithValue("@Toduration",datetimepicker_att_To.Value.Date);

    DataRowView drow = (DataRowView)combobox_att_events.SelectedValue;
    string l = drow.Row.ItemArray[0].ToString();


    SqlCommand cmd1 = new SqlCommand();
    cmd1.Connection = con;
    con.Open();


    cmd1.CommandText = "select WorkCode from WorkCodes where WorkCodeName='"+l+"'";
    SqlDataReader reader = cmd1.ExecuteReader();

    dk = new DataTable();
    dk.Load(reader);



    cmd.Parameters.AddWithValue("@WorkCode", dk.Rows[0][0]);

    SqlDataReader reader1 = cmd.ExecuteReader();
    DataTable dp =new DataTable();
    dp.Load(reader1);

    datagridview_att_attendance.DataSource=dp;

The referenced stored procedure is pasted below:

引用的存储过程粘贴在下面:

create procedure sp_gettinglogsfromdevice
(
    @tablename varchar(75),
    @fromduration datetime,
    @Toduration datetime,
    @WorkCode nvarchar(100)
)
As
Begin
Exec('select UserId,LogDate,WorkCode from '
    +@tablename+' where LogDate between '
    +@fromduration+' and '+@Toduration+' and WorkCode = '+@WorkCode);
End
Go

Iam actually taking the name of the table dynamically as in my database a table is created automatically with the name DeviceLogs_(month)_(year) for every month of logs that are created.Please help me with this error.I have seen numerous post but i cannot seem to wrap my head along this idea

Iam实际上动态地获取表的名称,因为在我的数据库中,每个月创建的日志都会自动创建一个名为DeviceLogs_(月)_(年)的表。请帮我解决这个错误。我看过很多帖子但我似乎无法将这个想法包裹起来

1 个解决方案

#1

Exec('select UserId,LogDate,WorkCode from '
+@tablename+' where LogDate between '
+@fromduration+' and '+@Toduration+' and WorkCode = '+@WorkCode);

should be changed to:

应改为:

declare @statement nvarchar(4000)

set @statement = N'select UserId,LogDate,WorkCode from '
    +@tablename+' where LogDate between @fromduration and @Toduration and WorkCode = @WorkCode'

EXEC sp_executesql  @statement,N'@fromduration datetime, @Toduration datetime, @WorkCode nvarchar(100)', @fromduration, @Toduration, @WorkCode;

To ensure that the dates and work code are passed correctly.

确保正确传递日期和工作代码。

#1

Exec('select UserId,LogDate,WorkCode from '
+@tablename+' where LogDate between '
+@fromduration+' and '+@Toduration+' and WorkCode = '+@WorkCode);