How to update data in a sql server database with values from textboxes when only some of the textboxes are empty and some are filled?

Question

We have a form on our aspx page with a number of textboxes. We would like to UPDATE several tables in our sql server database with the values inputted into these text boxes however if any of the text boxes are left blank we would like to leave the data for that column as it is, currently it won't accept empty values.

If all textboxes are filled, it functions correctly, if not, we get the error: Input string was not in a correct format.

Code fired when submit button is clicked:

    protected void buttonClicked(object sender, EventArgs e)
    {
        string CS = ConfigurationManager.ConnectionStrings["G4StowawaysConnectionString"].ConnectionString;

        SqlConnection conn = new SqlConnection(CS);

        conn.Open();

        String sql5 = "" ;

        if (!@BalanceOutstanding.Text.Equals(null) | !@Comments.Text.Equals(null))
        {

        sql5 += "UPDATE BookingView SET ";

        if (!@BalanceOutstanding.Text.Equals(null))
        {
            sql5 += " [Balance_Outstanding]=@BalanceOutstanding,";
        }

        if (!@Comments.Text.Equals(null))
        {
            sql5 += " [Comments]=@Comments ";
        }

        sql5 += "WHERE [Booking_Ref] = @BookingRef;";

        }


        if (!@BoatID.Text.Equals(null) | !@SpecialRequests.Text.Equals(null) | !@LeadPassenger.Text.Equals(null) | !@Duration.Text.Equals(null) | !@Pets.Text.Equals(null) | !@Children.Text.Equals(null) | !@Passengers.Text.Equals(null))
        {

        sql5 += "UPDATE vBoat_Booking SET";

        if (!@BoatID.Text.Equals(null))
        {
            sql5 += " [BoatID]=@BoatID,";
        }

        if (!@SpecialRequests.Text.Equals(null))
        {
            sql5 += " [Special_Request]=@SpecialRequests,";
        }

        if (!@LeadPassenger.Text.Equals(null))
        {
            sql5 += " [Lead_PassengerID]=@LeadPassenger,";
        }

        if (!@Duration.Text.Equals(null))
        {
            sql5 += " [Duration_In_Hours]=@Duration,";
        }

        if (!@Pets.Text.Equals(null))
        {
            sql5 += " [Number_of_pets]=@Pets,";
        }

        if (!@Children.Text.Equals(null))
        {
            sql5 += " [Number_of_children]=@Children,";
        }

        if (!@Passengers.Text.Equals(null))
        {
            sql5 += " [Number_of_passengers]=@Passengers ";
        }

        sql5 += "WHERE [Booking_Ref] = @BookingRef;";

        }


        SqlCommand cmd2 = new SqlCommand(sql5, conn);

        //add our parameters to our command object  


     cmd2.Parameters.AddWithValue("@BookingRef", Convert.ToInt32(BookingRef.Text));
            cmd2.Parameters.AddWithValue("@BoatID", Convert.ToInt32(BoatID.Text));
            cmd2.Parameters.AddWithValue("@LeadPassenger", Convert.ToInt32(LeadPassenger.Text));
            cmd2.Parameters.AddWithValue("@Duration", Convert.ToInt32(Duration.Text));
            cmd2.Parameters.AddWithValue("@Pets", Convert.ToInt32(Pets.Text));
            cmd2.Parameters.AddWithValue("@Children", Convert.ToInt32(Children.Text));
            cmd2.Parameters.AddWithValue("@Passengers", Convert.ToInt32(Passengers.Text));
            cmd2.Parameters.AddWithValue("@SpecialRequests", SpecialRequests.Text);
            cmd2.Parameters.AddWithValue("@BalanceOutstanding", Convert.ToInt32(BalanceOutstanding.Text));
            cmd2.Parameters.AddWithValue("@Comments", Comments.Text);

            cmd2.ExecuteNonQuery();

            conn.Close();


    }

Have a look at this: http://stackoverflow.com/questions/1547026/t-sql-conditional-update-v2 — JaggenSWE, May 11 '16 at 12:02
if(!@SpecialRequests.Text.Equals(null) && @SpecialRequests.Text!="") check for both condtitions. because the value does not come as null it comes as empty string. i.e "" — Sujit.Warrier, May 11 '16 at 12:02
You have trailing commas; e.g. `sql5 += " [Balance_Outstanding]=@BalanceOutstanding,";` leaves the SQL ending with a comma (if that's the only field being updated). A better approach may be to use stored procedures to hold the logic as follows: http://stackoverflow.com/questions/15569860/passing-parameter-to-stored-procedure-in-c-sharp — JohnLBevan, May 11 '16 at 12:12

Clint B · Answer 1 · 2016-05-11T12:23:56.437

You are using trailing commas in your if blocks and should be using leading commas. Except for the first field that is added to your dynamic SQL. You can use a bool to determine if the code segment needs a leading comma.

Another option is to create the dynamic SQL in a stored procedure. If you do this, you will want to use parameters much like you're using them in your C# code. Use sp_executesql instead of exec. This allows you to define parameters and give those parameters a value. Go here to see an example of using sp_executesql with parameters.

How to update data in a sql server database with values from textboxes when only some of the textboxes are empty and some are filled?

1 Answers1