传递一个布尔参数到SQL Server存储过程 [英] Passing a boolean parameter to a SQL Server stored procedure

问题描述

我问这个问题前面，我想我发现了什么问题，但我没有。我在传递一个布尔值参数存储过程的问题​​。这里是我的C＃code：

I asked this question earlier and I thought I found what the problem was, but I didn't. I'm having a problem passing a boolean parameter to a stored procedure. Here's my c# code:

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语句，但是如果跳过ELSE，转而执行。

The stored procedure is supposed to populate dropdownlist. It supposed to execute the IF statement, but IF is skipped and ELSE is executed instead.

推荐答案

我倾向于指定布尔参数同样的类型。也许是这样;

I'd be inclined to specify the type for the boolean parameter also. Maybe something like;

            SqlParameter param = new SqlParameter();
            param.ParameterName = "@upload";
            param.Value = upload;
            param.DbType = System.Data.DbType.Boolean
            cmd.Parameters.Add(param);

也许还要检查使用断点，甚至 System.Diagnostics.Debug.Write（@上传为+上传），以确保您传递你认为你在传递

Maybe also check using a breakpoint or even System.Diagnostics.Debug.Write("@Upload is " + upload) to ensure you are passing in what you think you are passing in.

最后，我建议把你的的SqlConnection 和的SqlCommand statments一个使用块，以确保资源的及时清理这个运行之后。

Lastly, I'd suggest putting your SqlConnection and SqlCommand statments in a using block to ensure the resources are cleaned up after this has run.

这篇关于传递一个布尔参数到SQL Server存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！