如何使用Storedprocedure更新数据库内的值 [英] How Can I Update Values Inside Database Using Storedprocedure

问题描述

Crud.aspx

< asp：Button ID =btnUpdateText =Updaterunat =server onclick =btnUpdate_Click/>

Crud.aspx.cs

protected void btnUpdate_Click（object sender，EventArgs e）

{

SqlConnection con = new SqlConnection（connstr）;

con.Open（）;

SqlCommand cmd = new SqlCommand（sp_Update，con）;

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue（@ EmpId ，txtEmpId.Text）;

cmd.Parameters.AddWithValue（@ EmpName，txtEmployeeName.Text）;

cmd.Parameters.AddWithValue（@ EmpAdd，txtEmpAddress .Text）;

cmd.Parameters.AddWithValue（@ Phone，txtMobile.Text）;

int result = cmd.ExecuteNonQuery（）;

if（result == 1）

{

lblResult.Text =Value Updated;

}

其他

{

lblResult.Text =失败;

}

}

** sp_Update是存储过程

我无法使用此代码更新值，但如果我编写代码直接sql代码，即更新TableName设置EmpName = @EmpName，EmpAdd = @ EmpAdd，Phone = @ Phone，其中EmpId = @EmpId，任何人都可以请教我怎么样更新数据库

显示错误：将varchar值'@EmpId'转换为数据类型int时转换失败。

Crud.aspx

<asp:Button ID="btnUpdate" Text="Update" runat="server" onclick="btnUpdate_Click"/>

Crud.aspx.cs
protected void btnUpdate_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(connstr);
con.Open();
SqlCommand cmd = new SqlCommand("sp_Update", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@EmpId", txtEmpId.Text);
cmd.Parameters.AddWithValue("@EmpName", txtEmployeeName.Text);
cmd.Parameters.AddWithValue("@EmpAdd", txtEmpAddress.Text);
cmd.Parameters.AddWithValue("@Phone", txtMobile.Text);
int result = cmd.ExecuteNonQuery();
if (result == 1)
{
lblResult.Text = "Value Updated";
}
else
{
lblResult.Text = "Failed";
}
}
**sp_Update is the stored procedure
I am unable to update the values using this code,But if i write the code direct sql code i.e update TableName set EmpName=@EmpName,EmpAdd=@EmpAdd,Phone=@Phone where EmpId=@EmpId, can anyone please suggest me how to update in database
It is showing the error:Conversion failed when converting the varchar value '@EmpId' to data type int.

推荐答案

错误信息非常清楚：

The error message is pretty clear:

Quote：

转换时转换失败varchar值'@EmpId'到数据类型int。

Conversion failed when converting the varchar value '@EmpId' to data type int.

您传入的EmpId值不是int。您应该做的是验证客户端和服务器端的输入。



AddWithValue会将参数发送为nvarchar并尝试执行隐式转换与并不总是一件好事。你应该使用下面的东西。

You are passing in a value for EmpId that is not an int. What you should be doing is validating the input in the client side as well as the server side.

AddWithValue will send you parameter as nvarchar and try to preform an implicit conversion with is not always a great thing. You should use something like below.

cmd.Parameters.Add("@EmpId", SqlDbType.Int);
cmd.Parameters["@EmpId"].Value = txtEmpId.Text;

在传递给存储过程之前将 @EmpId 转换为int

Convert your @EmpId to int before passing to stored procedure

Convert.ToInt32(txtEmpId.Text);

对于参数很少的程序，我使用它，从而确保所有类型的数据都能正常工作，如果你有布尔数据，整数或日期工作。

For procedures with few parameters I use this, thus ensures that all types of data work, if you have Boolean data, integer or datetime work.

protected void btnUpdate_Click(object sender, EventArgs e)
{
bool t = sp_update(txtEmpId.Text,txtEmployeeName.Text,txtEmpAddress.Text,txtMobile.Text);
if (t)
{
lblResult.Text = "Value Updated";
}
else
{
lblResult.Text = "Failed";
} 
}

public bool sp_update( string EmpId ,string EmpName,string EmpAdd,string Phone)
{
	bool _flag = false;
    SqlConnection _conn = new SqlConnection(connstr);
	try
	{
		SqlCommand _cmd = new SqlCommand();
		_cmd.Connection = _conn;
		_cmd.CommandType = CommandType.Text;
		_cmd.CommandText = String.Format(" exec dbo.sp_Update @EmpId ='{0}',@EmpName='{1}',@EmpAdd='{2}',@Phone='{3}'", EmpId ,EmpName,EmpAdd,Phone);
		_conn.Open();
		_cmd.ExecuteNonQuery();
		_conn.Close();
		_flag = true;
	}
	catch (ServiciosSqlNet.SqlNetException ex)
	{
		throw new Exception(ex.Message, (Exception)ex);
	}
	catch (Exception ex)
	{
		throw ex;
	}
	return _flag;
}

这篇关于如何使用Storedprocedure更新数据库内的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！