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 ([email protected](null) | [email protected](null))
        {

        sql5 += "UPDATE BookingView SET ";

        if ([email protected](null))
        {
            sql5 += " [Balance_Outstanding]=@BalanceOutstanding,";
        }

        if ([email protected](null))
        {
            sql5 += " [Comments]=@Comments ";
        }

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

        }


        if ([email protected](null) | [email protected](null) | [email protected](null) | [email protected](null) | [email protected](null) | [email protected](null) | [email protected](null))
        {

        sql5 += "UPDATE vBoat_Booking SET";

        if ([email protected](null))
        {
            sql5 += " [BoatID]=@BoatID,";
        }

        if ([email protected](null))
        {
            sql5 += " [Special_Request]=@SpecialRequests,";
        }

        if ([email protected](null))
        {
            sql5 += " [Lead_PassengerID]=@LeadPassenger,";
        }

        if ([email protected](null))
        {
            sql5 += " [Duration_In_Hours]=@Duration,";
        }

        if ([email protected](null))
        {
            sql5 += " [Number_of_pets]=@Pets,";
        }

        if ([email protected](null))
        {
            sql5 += " [Number_of_children]=@Children,";
        }

        if ([email protected](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: stackoverflow.com/questions/1547026/t-sql-conditional-update-v2 — JaggenSWE
– JaggenSWE, Commented May 11, 2016 at 12:02
if([email protected](null) && @SpecialRequests.Text!="") check for both condtitions. because the value does not come as null it comes as empty string. i.e "" — Sujit.Warrier
– Sujit.Warrier, Commented May 11, 2016 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: stackoverflow.com/questions/15569860/… — JohnLBevan
– JohnLBevan, Commented May 11, 2016 at 12:12

Clint B · Accepted Answer · 2016-05-11 12:23:56Z

0

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.

edited May 11, 2016 at 12:23

answered May 11, 2016 at 12:07

Clint B

4,7102 gold badges20 silver badges22 bronze badges

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

Collectives™ on Stack Overflow

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 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related