CODEWITHSUNDEEP
c#sql-serverdatagridview
cosmin_popescu
cosmin_popescu

Reputation: 196

Trying to update db - no errors but no update either

I have a problem in trying to update a database using SQL update command and DataGridView.

Int16 ID,  An;

// update db using sql command, the  code does not update the database
SqlCommand cmd = new SqlCommand("update filme set ID = @ID, Nume = @Nume, Gen = @Gen, Descriere = @Descriere, Actori = @Actori, An = @An, Rating = @Rating, Pret = @Pret where ID = @ID");

cmd.CommandType = CommandType.Text;
cmd.Connection = connection;

cmd.Parameters.AddWithValue("@ID", SqlDbType.SmallInt).Value = Int16.TryParse("@ID", out ID);
cmd.Parameters.AddWithValue("@Nume", SqlDbType.NVarChar).Value = "@Nume";
cmd.Parameters.AddWithValue("@Gen",SqlDbType.NVarChar).Value = "@Gen";
cmd.Parameters.AddWithValue("@Descriere", SqlDbType.NVarChar).Value = "@Descriere";
cmd.Parameters.AddWithValue("@Actori", SqlDbType.NVarChar).Value = "@Actori";
cmd.Parameters.AddWithValue("@An", SqlDbType.SmallInt).Value = Int16.TryParse("@An", out An) ;
cmd.Parameters.AddWithValue("@Rating", SqlDbType.NVarChar).Value = "@Rating";
cmd.Parameters.AddWithValue("@Pret",SqlDbType.Money).Value = "@Pret";

connection.Open();
cmd.ExecuteNonQuery();

This code does not produce any errors, but does not update the database. Something is wrong but I don't know what.

I use Visual Studio Community and SQL Server 2012. The information from database are displayed in a DataGridView.

Thank you !

Upvotes: 0

Views: 111

Answers (2)

Steve
Steve

Reputation: 216323

You set the @ID parameter with this line

Int16.TryParse("@ID", out ID);

what do you expect to be the result of converting the string @ID to an integer?
And Int16.TryParse returns a boolean, true if the conversion succeed, false otherwise.

Then you use 

cmd.Parameters.AddWithValue("@ID", SqlDbType.SmallInt).Value = .....

The second parameter of AddWithValue is the Value to give to the parameter, not the type.
The remainder follows the same pattern and so this code will never work.

As an example, you should write:

SqlCommand cmd = new SqlCommand(@"update filme set Nume = @Nume, Gen = @Gen, 
                                  Descriere = @Descriere, Actori = @Actori, 
                                  An = @An, Rating = @Rating, Pret = @Pret 
                                  where ID = @ID", connection);

cmd.Parameters.Add(new SqlParameter 
                   { ParameterName = @Nume,
                     SqlDbType = SqlDbType.Int,
                     Value = Convert.ToInt32(someTextBox.Text)   // Or some datagridview cell...
                   };

...and so on for the other parameters...

Notice also that I have removed the part about SET ID = @ID because this makes no sense. If you use the ID field as your search condition then updating it with the value that you are searching for could only lead, in the best situation, at no change for the ID field and in the worst situation to changing a different record from the intended one.

Upvotes: 2

marc_s
marc_s

Reputation: 754598

The way you use the .AddWithValue is all wrong .....

You have

cmd.Parameters.AddWithValue("@ID", SqlDbType.SmallInt).Value = Int16.TryParse("@ID", out ID);

but you're really defining the parameter name and datatype (which is a good thing!) and then you use the .Value = ... to handle the value assignment.

These lines of code should really be:

cmd.Parameters.Add("@ID", SqlDbType.SmallInt).Value = Int16.TryParse("@ID", out ID);

I bet using this approach, your code will work just fine.

Upvotes: 2

Related Questions