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

okxuctiv  于 2023-10-15  发布在  Scala
关注(0)|答案(2)|浏览(126)

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?

js5cn81o

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);
pu3pd22g

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");

相关问题