updating sql server database using c# in asp.net

Question

There isn't any compile error but the database doesn't get updated at all. what is wrong with the code?

protected void Page_Load(object sender, EventArgs e) {
    rno.Text = Request.QueryString["rno"];//rno is a textbox


    string connectionString = @"Data Source = (localdb)\MSSQLLocalDB; Initial Catalog = db1; Integrated Security = True";
    SqlConnection cnn = new SqlConnection(connectionString);
    cnn.Open();

    String sql = "select fname from table1 where rno = @rno";

    SqlCommand command = new SqlCommand(sql, cnn);
    command.Parameters.AddWithValue("@rno", rno.Text.Trim());
    SqlDataReader reader = command.ExecuteReader();
    if (reader.Read()) {
        fname.Text = reader["xcountry"].ToString().Trim(); //fname is a textbox
    }
    reader.Close();
    command.Dispose();
    cnn.Close();

    fName.ReadOnly = true;
}

protected void modify_Click(object sender, EventArgs e) {

    fName.ReadOnly = false;
}

protected void savechanges_Click(object sender, EventArgs e) {
    string connectionString = @"Data Source = (localdb)\MSSQLLocalDB; Initial Catalog = db1; Integrated Security = True";
    SqlConnection cnn = new SqlConnection(connectionString);
    cnn.Open();


    String sql = "update table1 set fname=@fname where rno = @rno";

    SqlCommand command = new SqlCommand(sql, cnn);
    command.Parameters.AddWithValue("@fname", sfname);
    command.Parameters.AddWithValue("@rno", rno.Text.Trim());

    command.ExecuteNonQuery();
    command.Dispose();
    cnn.Close();
    fName.ReadOnly = true;
}

There are some things here only you can test. 1) Does savechanges_Click even get called? 2) Is the SQL correctly generated with the right values? and 3) What happens when you run that generated SQL directly against the database? — stuartd
– stuartd, Commented Feb 19, 2020 at 13:57
Have you debugged? What is the value of rno text?. Does it exist in column rno in table? — apomene
– apomene, Commented Feb 19, 2020 at 13:59
The following returns an integer : int rowsUpdated = command.ExecuteNonQuery(); If the number returned is zero in means the rows are not in the database and you need to perform an Insert instead of an Update. The SQL database will not update a row that doesn't exist, and will also not insert a row that does exist. — jdweng
– jdweng, Commented Feb 19, 2020 at 13:59
@stuartd here I tried these things... 1) savechanges_click gets called. 2) the database gets updated when i run it on the sql query. — Gaurav Thapa
– Gaurav Thapa, Commented Feb 19, 2020 at 14:19

Md Farid Uddin Kiron · Accepted Answer · 2020-02-19 14:39:18Z

I have tried your code which executed fine and updated database table as well.

I have tried like below :

        string connectionString = @"data source=MS-KIRON-01;initial catalog=TestDatabase;integrated security=True;MultipleActiveResultSets=True";
        SqlConnection cnn = new SqlConnection(connectionString);
        cnn.Open();


        String sql = "update TestTable set fname=@fname where rno =rno";

        SqlCommand command = new SqlCommand(sql, cnn);
        command.Parameters.AddWithValue("@fname", "Test");
        command.Parameters.AddWithValue("@rno", "rno");

        command.ExecuteNonQuery();
        command.Dispose();
        cnn.Close();

Another way I have tried.

            using (SqlConnection connection = new SqlConnection(connectionString ))
                    {
                        connection.Open();
                        var queryText = "UPDATE TestTable SET fname = '" + requestPram.fname + "' WHERE rno ='" + requestPram.rno + "'";

                        using (SqlCommand cmd = new SqlCommand(queryText, connection))
                        {
                            responseResults = await cmd.ExecuteNonQueryAsync();
                        }
                        connection.Close();

                    }

Hope it would help

Gaurav Thapa · Accepted Answer · 2020-02-19 18:04:22Z

0

After searching for a while, I found out that this code was executing perfectly. The only problem was that everything was inside the page_Load() method and thus the page was reloading everytime I updated the database and thus removing the small window to edit the textboxes. The appropriate solution was to associate this code with some button event rather than with the page_Load() event.

answered Feb 19, 2020 at 18:04

Gaurav Thapa

891 silver badge10 bronze badges

Collectives™ on Stack Overflow

updating sql server database using c# in asp.net

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related