将布尔参数传递给SQL Server存储过程
本文关键字:Server 存储过程 SQL 布尔 参数传递 | 更新日期: 2023-09-27 18:21:29
我之前问过这个问题,我以为我发现了问题所在,但我没有。将布尔参数传递给存储过程时遇到问题。这是我的c#代码:
public bool upload = false;
protected void showDate(object sender, EventArgs e)
{
if (Radio1.Checked)
{
upload = true;
Radio2.Checked = false;
date_div.Visible = true;
date_div2.Visible = false;
}
}
protected void getMonthList()
{
selectedYear = year.SelectedValue.ToString();
SqlConnection connection = new SqlConnection(connString);
SqlCommand cmd = connection.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
connection.Open();
cmd.CommandText = "getMonth";
cmd.Parameters.Add("@year", SqlDbType.Int, 0).Value = Convert.ToInt32(selectedYear);
cmd.Parameters.AddWithValue("@upload", upload);
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
month.DataSource = dt;
month.DataTextField = "month";
month.DataValueField = "monthValue";
month.DataBind();
month.Items.Insert(0, new ListItem("Select", "0"));
}
这就是存储过程getMonth
:
ALTER PROCEDURE [dbo].[getMonth]
@year int,
@upload Bit
AS
BEGIN
IF @upload = 1
BEGIN
SELECT distinct datename(mm, Upload_date) month
,month (upload_date) monthValue
FROM dbo.RESOLVED
WHERE datepart(yyyy, upload_date) = @year
ORDER by 2
END
ELSE
BEGIN
SELECT distinct datename(mm, substring(CREATE_DT,1,2) + '.' + substring(CREATE_DT,3,2) + '.' + substring(CREATE_DT,5,4)) month
,month (substring(CREATE_DT,1,2) + '.' + substring(CREATE_DT,3,2) + '.' + substring(CREATE_DT,5,4)) monthValue
FROM dbo.RESOLVED
WHERE datepart(yyyy, substring(CREATE_DT,1,2) + '.' + substring(CREATE_DT,3,2) + '.' + substring(CREATE_DT,5,4)) = @year
ORDER by 2
END
存储过程应该填充dropdownlist。它应该执行IF语句,但跳过了IF,而是执行ELSE。
我也倾向于指定布尔参数的类型。也许是类似的;
SqlParameter param = new SqlParameter();
param.ParameterName = "@upload";
param.Value = upload;
param.DbType = System.Data.DbType.Boolean
cmd.Parameters.Add(param);
也许还可以使用断点甚至System.Diagnostics.Debug.Write("@Upload is " + upload)
进行检查,以确保您正在传递您认为正在传递的内容。
最后,我建议将您的SqlConnection
和SqlCommand
状态放在using
块中,以确保在运行后清理资源。