How Can I Update Values Inside Database Using Storedprocedure

Question

0.00/5 (No votes)

See more:

, +

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.

Posted 6-Aug-14 4:57am

akhilesh1890

Updated 6-Aug-14 5:06am

v2

Add a Solution

3 solutions

Solution 2

Convert your @EmpId to int before passing to stored procedure

C#

Convert.ToInt32(txtEmpId.Text);

Posted 6-Aug-14 5:16am

Dilan Shaminda

v2

Solution 3

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.

C#

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;
}

Posted 6-Aug-14 5:22am

danigeraleddin

Updated 6-Aug-14 5:33am

v4

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

AnvilRanger · Accepted Answer · 2014-08-06T05:15:00

Solution 1

The error message is pretty clear:

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

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.

C#

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

Posted 6-Aug-14 5:15am

AnvilRanger

Updated 6-Aug-14 5:18am

v2

Comments

akhilesh1890 6-Aug-14 14:11pm

Thanks for the response,
I have made the following change but i am getting the same problem

protected void btnUpdate_Click(object sender, EventArgs e)
{
int empId = Convert.ToInt32(txtEmpId.Text);
string empName = txtEmployeeName.Text;
string empAdd = txtEmpAddress.Text;
string phone = txtEmpId.Text;
SqlConnection con = new SqlConnection(connstr);
con.Open();
SqlCommand cmd = new SqlCommand("sp_Update", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@EmpId", SqlDbType.Int);
cmd.Parameters["@EmpId"].Value = Convert.ToInt32(txtEmpId.Text);
cmd.Parameters.Add("@EmpName", SqlDbType.VarChar);
cmd.Parameters["@EmpName"].Value = txtEmployeeName.Text;
cmd.Parameters.Add("@EmpAdd", SqlDbType.VarChar);
cmd.Parameters["@EmpAdd"].Value = txtEmpAddress.Text;
cmd.Parameters.Add("@Phone", SqlDbType.VarChar);
cmd.Parameters["@Phone"].Value = txtMobile.Text;
int result = cmd.ExecuteNonQuery();
if (result == 1)
{
lblResult.Text = "Value Updated";
}
else
{
lblResult.Text = "Failed";
}
}