CODEWITHSUNDEEP
c#sqlsql-serverwinforms
user2944170
user2944170

Reputation:

Update Command with stored procedure

I have an update query(stored procedure) which is working properly in SQL Server when I execute it.

CREATE PROCEDURE updatestudenthws(@stdid nvarchar(50),@hwid int, @grade float)
AS
UPDATE Table_Exercise_Answer 
SET
ExAns_Grade = @grade
WHERE ExAns_Exercise = @hwid AND ExAns_Student = @stdid

but when I run the program it does not have any effect in my table and also I don't have any error. 

     con.Open();
        SqlCommand cmd = new SqlCommand("updatestudenthws", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@hwid", SqlDbType.VarChar);
        cmd.Parameters.Add("@stdid", SqlDbType.VarChar);
        cmd.Parameters.Add("@grade", SqlDbType.VarChar);
        cmd.Parameters["@hwid"].Value = hwid;
        cmd.Parameters["@stdid"].Value = studentid;
        cmd.Parameters["@grade"].Value = grade;

        cmd.ExecuteNonQuery(); 
     con.Close();

What is my mistake? How should I do this work?

Upvotes: 3

Views: 1600

Answers (3)

Tejas
Tejas

Reputation: 467

when you use AddWithValue(), don't you have to provide the type passing like varchar to an int parameter.

 con.Open();
        SqlCommand cmd = new SqlCommand("updatestudenthws", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@hwid", hwid);
        cmd.Parameters.AddWithValue("@stdid", studentid);
        cmd.Parameters.AddWithValue("@grade", grade);
        cmd.ExecuteNonQuery(); 
     con.Close();

Upvotes: 0

marc_s
marc_s

Reputation: 755451

Your ADO.NET code defining the parameters for the stored procedure is wrong in that you don't define the parameters with their proper datatypes.

Your stored procedure defines:

  • @stdid nvarchar(50) --> but you define it as varchar
  • @hwid int --> but you define it as varchar
  • @grade float --> but you define it as varchar

You need to change your code to this:

SqlCommand cmd = new SqlCommand("updatestudenthws", con);
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@hwid", SqlDbType.Int);   // this needs to be SqlDbType.Int 
cmd.Parameters.Add("@stdid", SqlDbType.NVarChar, 50);  // this should be SqlDbType.NVarChar and specify its proper length
cmd.Parameters.Add("@grade", SqlDbType.Float);  // this needs to be SqlDbType.Float

Upvotes: 2

BeemerGuy
BeemerGuy

Reputation: 8279

Use AddWithValue(), so you don't have to provide the type, which allowed you to make the mistake of passing varchar to an int parameter.

 con.Open();
    SqlCommand cmd = new SqlCommand("updatestudenthws", con);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@hwid", hwid);
    cmd.Parameters.AddWithValue("@stdid", studentid);
    cmd.Parameters.AddWithValue("@grade", grade);
    cmd.ExecuteNonQuery(); 
 con.Close();

Upvotes: 2

Related Questions