How to update records in SQL Server database through asp.net?

Question

I'm writing this code in asp.net but still it's not updating record in a SQL Server database:

SqlCommand cmd4 = new SqlCommand("Select * from roomdetail", conn);
SqlDataReader dr = cmd4.ExecuteReader();

while (dr.Read())
    SqlCommand cmd3 = new SqlCommand("update [roomdetail] set [rid]=' " +count+1 + " '  where rid = 0 AND roomtype='"+typeRadioButtonList1.SelectedItem.ToString()+        "' ", conn);

I don't think this even compiles because a declaration cannot be the body of a control flow block without braces. Additionally what is it that you're trying to achieve? Why do you do a SELECT query and then ignore the results? — Matti Virkkunen
– Matti Virkkunen, Commented Jul 4, 2016 at 21:50
it compiles successfully but does not update, i am using the select query in order to read the rows and to update the specified column according to the condition. — Shivani Varshney
– Shivani Varshney, Commented Jul 4, 2016 at 22:02
The code in your question will not compile successfully. Be precise when pasting. — Matti Virkkunen
– Matti Virkkunen, Commented Jul 4, 2016 at 22:03
You need to use parameters, you need to use using blocks, and you need some help. here is a great tutorial for you dotnetperls.com/sqlclient — prospector
– prospector, Commented Jul 4, 2016 at 22:03
My fault at posting. Thanks for your suggestion @MattiVirkkunen — Shivani Varshney
– Shivani Varshney, Commented Jul 4, 2016 at 22:15

pilak · Accepted Answer · 2016-07-04 22:08:51Z

2

Proper way to use ado.net:

var newId = count + 1;
var roomType = typeRadioButtonList1.SelectedItem.ToString();

using (var connection = new SqlConnection("your db connection string here"))
{
        var query = "UPDATE [roomdetail] SET [rid] = @rid WHERE [rid] = 0 AND roomtype = @roomType";

        SqlCommand command = new SqlCommand(query, connection);
        command.Parameters.AddWithValue("@rid", newId);
        command.Parameters.AddWithValue("@roomType", roomType);

        try
        {
            command.Connection.Open();
            command.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            //handle exception
        }
}

answered Jul 4, 2016 at 22:08

pilak

393 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

marc_s Over a year ago

Actually, the really proper way would be to put the SqlCommand into a using(...) { .... } block as well !

thehennyy · Accepted Answer · 2017-12-05 18:35:29Z

1

You are Missing ExecuteNonQuery Method Write Below code

SqlCommand cmd4 = new SqlCommand("Select * from roomdetail", conn);
SqlDataReader dr = cmd4.ExecuteReader();

while (dr.Read())
SqlCommand cmd3 = new SqlCommand("update [roomdetail] set [rid]=' " +count+1 + " '  where rid = 0 AND roomtype='"+typeRadioButtonList1.SelectedItem.ToString()+        "' ", conn);
cmd3.executeNonQuery();

edited Dec 5, 2017 at 18:35

thehennyy

4,2241 gold badge27 silver badges34 bronze badges

answered Dec 3, 2017 at 7:12

RiyajKhan

535 bronze badges

Comments

Avi K. · Accepted Answer · 2016-07-04 22:04:54Z

0

Assuming the connection is already opened, in your while loop you are missing the following statement:

cmd3.ExecuteNonQuery();

You have to execute the command in order for the database to be updated.

edited Jul 4, 2016 at 22:04

answered Jul 4, 2016 at 21:58

Avi K.

1,8022 gold badges19 silver badges29 bronze badges

Collectives™ on Stack Overflow

How to update records in SQL Server database through asp.net?

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related