ExecuteScalar和ExecuteNonQuery冲突

Question

我遇到了ExecuteScalar的问题，它不允许我使用ExecuteNonQuery，但我必须执行两个查询来获取另一个表中所选项目的日期范围之间的最小日期(dateStart9 )和最大日期(DateEnd)。

sqliteCon.Open();
  if (sqliteCon.State == System.Data.ConnectionState.Open)
  {
    //query date non va a causa dell'execute scalar
    SqlCommand cmdDS = new SqlCommand("SELECT MIN(DateStartSE) AS DATESE FROM tabStoreExec JOIN tabList ON tabStoreExec.idSE=tabList.idL WHERE tabList.selection=1", sqliteCon);
    SqlCommand cmdDE = new SqlCommand("SELECT MAX(DateEndSE) AS DATEED FROM tabStoreExec JOIN tabList ON tabStoreExec.idSE=tabList.idL WHERE tabList.selection=1", sqliteCon);
    cmdDS.Parameters.AddWithValue("@DATESE", " DATESE");
    cmdDE.Parameters.AddWithValue("@DATEED", "DATEED");
    cmdDS.ExecuteNonQuery();
    cmdDE.ExecuteNonQuery();


  SqlCommand cmd1 = new SqlCommand("INSERT INTO tabStoricoDetail(NomeItem,ResItemDet,DateStartDet,DateEndDet) OUTPUT inserted.Id VALUES ('Prova','RProva',@DATESE,@DATEED)", sqliteCon);
  int generatedId = Convert.ToInt32(cmd1.ExecuteScalar());
  cmd1.Parameters.Clear();
  SqlCommand cmd2 = new SqlCommand("UPDATE tabStoreExec SET FK_TSD_id = @tsdId FROM tabList JOIN tabStoreExec ON tabList.idL = tabStoreExec.idSE WHERE tabList.selection=1 ", sqliteCon);
  cmd2.Parameters.AddWithValue("@tsdId", generatedId);
  cmd2.ExecuteNonQuery();

    MessageBox.Show("Dato Aggiunto");

  }
  sqliteCon.Close();

我已经接近解决方案了，但错误是声明标量变量“@DATESE”。

Answer 1

似乎应该将参数添加到cmdDS和cmdDE的cmd1 instad中。不过，您应该调用ExecuteScalar()从这些命令中获取一个值：

SqlCommand cmdDS = new SqlCommand("SELECT MIN(DateStartSE) AS DATESE FROM tabStoreExec JOIN tabList ON tabStoreExec.idSE=tabList.idL WHERE tabList.selection=1", sqliteCon);
SqlCommand cmdDE = new SqlCommand("SELECT MAX(DateEndSE) AS DATEED FROM tabStoreExec JOIN tabList ON tabStoreExec.idSE=tabList.idL WHERE tabList.selection=1", sqliteCon);
var DATESE = cmdDS.ExecuteScalar();
var DATEED = cmdDE.ExecuteScalar();


SqlCommand cmd1 = new SqlCommand("INSERT INTO tabStoricoDetail(NomeItem,ResItemDet,DateStartDet,DateEndDet) OUTPUT inserted.Id VALUES ('Prova','RProva',@DATESE,@DATEED)", sqliteCon);
cmd1.Parameters.AddWithValue("@DATESE", DATESE);
cmd1.Parameters.AddWithValue("@DATEED", DATEED);

Answer 2

在执行之前，需要将参数@DATESE,@DATEED添加到cmd1中。