scope_identity查询的数据读取器错误

本文关键字：读取 错误 数据 identity 查询 scope | 更新日期: 2023-09-27 17:51:23

当我尝试使用SqlDataReader读取我的scope_identity pk查询以在下一页上显示行数据时，我得到了著名的"无效尝试读取"错误消息。这是我第一次使用这两种方法，所以任何建议都会有所帮助。我的代码:

insert command ... ; SELECT SCOPE_IDENTITY() AS [lastInsertedProductId]";
    using (SqlConnection sqlConn = new SqlConnection(connectionString))
    {
        sqlConn.Open();
        SqlCommand command = new SqlCommand(thisQuery, sqlConn);
        int lastInsertedProductId = Convert.ToInt32(command.ExecuteScalar());
        using (command)
        {
            command.ExecuteNonQuery();
            using (SqlDataReader dr = command.ExecuteReader())
            {
                dr.Read();
                lastInsertedProductId = Convert.ToInt32(dr["lastInsertedProductId"]);
                Response.Redirect("~/View.aspx?ProductId" + lastInsertedProductId);
            }
        }
    }
}

已经编辑了代码，但现在我在我的视图页面上得到'='附近的"语法错误"，在那里我没有显示实际的语法错误

using (SqlConnection editConn = new SqlConnection(connectionString))
    {
        editConn.Open();
        using (SqlCommand command = new SqlCommand(editQuery, editConn))
        {
            SqlDataReader dr = command.ExecuteReader();
            dr.Read();
            Label6.Text = dr.GetInt32(0).ToString();

scope_identity查询的数据读取器错误

您必须在插入语句中使用作用域标识。像…

INSERT INTO table (ColumnName) VALUES ();SELECT SCOPE_IDENTITY();

在你的代码。

SqlCommand command = new SqlCommand(thisQuery, sqlConn);

thisQuery变量应该在您的insert查询结束时具有此属性。

编辑:我刚刚在网上查了一下，发现了一种类似于你正在做的事情的方法，但它会像…

string idQuery = "Select @@Identity";

你可以用这个来完成你的任务，我不知道使用这个方法是否有任何限制。但我认为这是可行的。

这是URL从那里我得到这个http://www.mikesdotnetting.com/Article/54/Getting-the-identity-of-the-most-recently-added-record

我要感谢每一个张贴有帮助的答案和建议的人;尽管我最终使用了不同的方法，但帮助总是值得感激的。关于这种不同的方式，我决定发布我最终使用的代码，以防有人需要它(尽管没有参数化查询，我希望除了家庭作业之外，没有人需要它，即使这样……)。因此，需要注意的是，下面的代码可以工作，但会犯这里经常谴责的大罪，下面是我的问题的函数解决方案:

 string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
    String thisQuery = "INSERT INTO ProductInstance (CustId, BroId, CustName, SicNaic, CustAdd, CustCity, CustState, CustZip, BroName, BroAdd, BroCity, BroState, BroZip, EntityType, Coverage, CurrentCoverage, PrimEx, Retention, EffectiveDate, Commission, Premium, Comments) VALUES ('" + TbCustId.Text + "', '" + TbBroId.Text + "', '" + TbCustName.Text + "', '" + RblSicNaic.SelectedItem + "', '" + TbCustAddress.Text + "', '" + TbCustCity.Text + "', '" + DdlCustState.SelectedItem + "', '" + TbCustZip.Text + "', '" + TbBroName.Text + "', '" + TbBroAddress.Text + "', '" + TbBroCity.Text + "', '" + DdlBroState.Text + "', '" + TbBroZip.Text + "', '" + DdlEntity.SelectedItem + "', '" + TbCoverage.Text + "','" + TbCurrentCoverage.Text + "','" + TbPrimEx.Text + "','" + TbRetention.Text + "','" + TbEffectiveDate.Text + "','" + TbCommission.Text + "','" + TbPremium.Text + "','" + TbComments.Text + "')";
    string idQuery = "SELECT SCOPE_IDENTITY() AS LastInsertedProductId";
    using (SqlConnection sqlConn = new SqlConnection(connectionString))
    {
        sqlConn.Open();
        SqlCommand command = new SqlCommand(thisQuery, sqlConn);
        SqlCommand idCmd = new SqlCommand(idQuery, sqlConn);
        using (command)
        {
            command.ExecuteNonQuery();               
           command.CommandText=idQuery;
           SqlDataReader dr = command.ExecuteReader();
             dr.Read();
                int lastInsertedProductId = Convert.ToInt32(dr[0]);
                Response.Redirect("~/View.aspx?ProductId=" + lastInsertedProductId);
        }
    }
}
protected void CalEffectDate_SelectionChanged(object sender, EventArgs e)
{
    TbEffectiveDate.Text = CalEffectDate.SelectedDate.ToShortDateString();
}

}你会注意到我在重定向的?ProductId后面缺少了一个"="符号。细节决定成败，不是吗?

和视图页代码:

 protected void Page_Load(object sender, EventArgs e)
{
    string x = Request.QueryString["ProductId"];
    string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
    string editQuery = "SELECT CustName, SicNaic, CustCity, CustAdd, CustState, CustZip, BroName, BroAdd, BroCity, BroState, BroZip, EntityType, Coverage, CurrentCoverage, PrimEx, Retention, EffectiveDate, Commission, Premium, Comments FROM ProductInstance WHERE ProductId =" + x;
    using (SqlConnection editConn = new SqlConnection(connectionString))
    {
        editConn.Open();
        using (SqlCommand command = new SqlCommand(editQuery, editConn))
        {
            SqlDataReader dr = command.ExecuteReader();
            dr.Read();
            LblCustName.Text = dr.GetString(0);
            LblSicNaic.Text = dr.GetString(1);
            LblCustCity.Text = dr.GetString(2);
            LblCustAddress.Text = dr.GetString(3);
            LblCustState.Text = dr.GetString(4);
            LblCustZip.Text = dr.GetInt32(5).ToString();
            LblBroName.Text = dr.GetString(6);
            LblBroAddress.Text = dr.GetString(7);
            LblBroCity.Text = dr.GetString(8);
            LblBroState.Text = dr.GetString(9);
            LblBroZip.Text = dr.GetInt32(10).ToString();
            LblEntity.Text = dr.GetString(11);
            LblCoverage.Text = dr.GetInt32(12).ToString();
            LblCurrentCoverage.Text = dr.GetInt32(13).ToString();
            LblPrimEx.Text = dr.GetInt32(14).ToString();
            LblRetention.Text = dr.GetInt32(15).ToString();
            LblEffectDate.Text = dr.GetDateTime(16).ToString();
            LblCommission.Text = dr.GetInt32(17).ToString();
            LblPremium.Text = dr.GetInt32(18).ToString();
            LblComments.Text = dr.GetString(19);
            HyperLink1.NavigateUrl = "~/ViewEdit.aspx?ProductId=" + x;
        }
    }
}

}

相关文章：