SQL Server Passing a parameter to SqlDataAdapter results in "Must declare the scalar variable"

okxuctiv 于 2023-10-15 发布在 Scala

关注(0)|答案(2)|浏览(127)

The method below should return ds which will be bounded to a datagridview but I am getting error message that says
Must declare the scalar variable "@ID"

public DataSet GetPatientSessions() {

    string connStr = ConfigurationManager.ConnectionStrings["connstr"].ToString();
    string cmdStr = @"SELECT ROW_NUMBER()OVER(ORDER BY ID) AS SEQ,
                      ID,
                      DAT,
                      S_HOUR,
                      E_HOUR,
                      KIND,
                      ENOPER,
                      ENDATETIME
                      FROM SEANCE
                      WHERE SICK_ID=@SICK_ID
                      ORDER BY ENDATETIME ASC;";

    using (SqlConnection conn = new SqlConnection(connStr))

    using (SqlCommand cmd = new SqlCommand(cmdStr, conn)) {

        try {

            conn.Open();
            cmd.CommandText = cmdStr;
            cmd.CommandType = CommandType.Text;

            DataSet ds = new DataSet();

            cmd.Parameters.Add(new SqlParameter("@SICK_ID", SqlDbType.Int)).Value = Convert.ToInt32(TB_PatientID.Text);

            SqlDataAdapter da = new SqlDataAdapter(cmd);

            //da.SelectCommand = cmd;
            da.Fill(ds, "PatientSessions");
            return ds;
        }

        catch (Exception ex) {
            string ErrorMsg = ex.Message.Substring(0, Math.Min(ex.Message.Length, 1024));

            return null;
        }   
    }
}

How can I resolve this error?

sql-server

来源：https://stackoverflow.com/questions/52449262/passing-a-parameter-to-sqldataadapter-results-in-must-declare-the-scalar-variab

2条答案

按热度按时间

js5cn81o1#

You add the parameter to your SqlCommand but then you don't associate this SqlCommand to the SqlDataAdapter. Thus, when you execute the SqlDataAdapter.Fill method your adapter has no knowledge of that parameter

You just need to use the DataAdapter's SelectCommand property or pass your command to the SqlDataAdapter constructor

// You can build a parameter directly with the Add method 
// using the proper overload
cmd.Parameters.Add("@ID", SqlDbType.Int).Value = Convert.ToInt32(TB_PatientID.Text); 
SqlDataAdapter da = new SqlDataAdapter(cmd);

赞(0）回复(0）举报 2023-10-15

pu3pd22g2#

Either pass cmd to SqlDataAdapter 's constructor as Steve suggested

Or assign cmd to the SelectCommand of the SqlDataAdapter :

conn.Open();
cmd.CommandText = cmdStr;
cmd.CommandType = CommandType.Text;

DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(/*remove cmdStr and conn from here*/);

//----
da.SelectCommand = cmd;
//----

cmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int)).Value = Convert.ToInt32(TB_PatientID.Text);

da.Fill(ds, "dsTable1");

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server Passing a parameter to SqlDataAdapter results in "Must declare the scalar variable"

2条答案

相关问题

热门标签

最新问答