Updating SQL Server database with values from textboxes using stored procedure

Question

We have an aspx page which is a has a number of text boxes. We want to take the data entered into these textboxes and update our SQL Server database with them. However if any of the textboxes are left blank then we would like the data to be left as it is.

We have written the following stored procedure to carry out the update:

ALTER PROCEDURE pr_updateBooking
(
    @BookingRef INT, 
    @BoatID INT, 
    @LeadPassenger INT, 
    @StartDate Date, 
    @Duration INT, 
    @Pets INT, 
    @Children INT, 
    @Passengers INT, 
    @SpecialRequests VARCHAR(255), 
    @BalanceOutstanding NUMERIC(12, 2), 
    @Comments VARCHAR(50)
)
AS
    DECLARE @error INT

    UPDATE BookingView
    SET Balance_Outstanding = @BalanceOutstanding, 
        Comments = @Comments 
    WHERE Booking_Ref = @BookingRef

    UPDATE vBoat_Booking 
    SET BoatID = @BoatID, Lead_PassengerID = @LeadPassenger, 
        Start_Date = @StartDate, Duration_In_hours = @Duration,  
        Number_of_pets = @Pets, Number_of_children = @Children, 
        Number_of_passengers = @Passengers
    WHERE Booking_Ref = @BookingRef

    SET @error = @@error

    IF @error <> 0 
       RETURN 99
    ELSE 
       RETURN 0

Here is the C# code which will be run when the submit button is clicked on our aspx page

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

    SqlConnection conn = new SqlConnection(CS);
    conn.Open();

    SqlCommand cmd2 = new SqlCommand("pr_updateBooking", conn);
    cmd2.CommandType = CommandType.StoredProcedure;

    // add our parameters to our command object  
    cmd2.Parameters.Add("@BookingRef", SqlDbType.Int).Value = BookingRef.Text;
    cmd2.Parameters.Add("@BoatID", SqlDbType.Int).Value = BoatID.Text;
    cmd2.Parameters.Add("@LeadPassenger", SqlDbType.Int).Value = LeadPassenger.Text;
    cmd2.Parameters.Add("@StartDate", SqlDbType.Date).Value = StartDate.Text;
    cmd2.Parameters.Add("@Duration", SqlDbType.Money).Value = Duration.Text;
    cmd2.Parameters.Add("@Pets", SqlDbType.Int).Value = Pets.Text;
    cmd2.Parameters.Add("@Children", SqlDbType.Int).Value = Children.Text;
    cmd2.Parameters.Add("@Passengers", SqlDbType.Int).Value = Passengers.Text;
    cmd2.Parameters.Add("@SpecialRequests", SqlDbType.VarChar, 255).Value = SpecialRequests.Text;
    cmd2.Parameters.Add("@BalanceOutstanding", SqlDbType.Int).Value = BalanceOutstanding.Text;
    cmd2.Parameters.Add("@Comments", SqlDbType.VarChar, 50).Value = Comments.Text;

    try
    {
        if (cmd2.Connection.State == ConnectionState.Closed)
        {
            cmd2.Connection.Open();
        }
        cmd2.ExecuteNonQuery();
    }
    catch (Exception)
    {
    }
    finally
    {                
        cmd2.Connection.Close();
    }
}

When we run the page there is no error message however the data is not appearing in the database!?

Use profiler to see what command, if any, is being sent from the page to the SQL Server. — Tab Alleman
– Tab Alleman, Commented May 11, 2016 at 14:23
What count is returned by cmd2.ExecuteNonQuery();? Also you are returning 0 or 99, how are you reading that in your C# code? Going forward please format your content/code properly so that your post is taken more seriously. — Nikhil Vartak
– Nikhil Vartak, Commented May 11, 2016 at 14:28
I'm really sorry I'm quite new to this. I don't know how to use a profiler, and I don't know how to find out what count is returned by that method? — Bryn Dukes
– Bryn Dukes, Commented May 11, 2016 at 14:30
Please have a look., See the last comment given by @Guffa stackoverflow.com/questions/767574/… — Shanthini
– Shanthini, Commented May 11, 2016 at 14:36
What does your connection string look like? Can you show us, please? — marc_s
– marc_s, Commented May 11, 2016 at 15:08

Alex Kudryashev · Accepted Answer · 2016-05-11 15:05:00Z

There are several issues in your application.
1. As noted in comments, use return value from int result = cmd.ExecuteNonQuery() (0 or 99). In fact it is not enough.
2. Check your table schemas to see whether or not fields of interest allow null.
3. In your stored procedure use transaction.

...
AS
--DECLARE @error INT --no need
begin transaction
begin try
UPDATE BookingView
SET Balance_Outstanding = @BalanceOutstanding, Comments = @Comments 
WHERE Booking_Ref = @BookingRef

UPDATE vBoat_Booking 
SET BoatID = @BoatID, Lead_PassengerID = @LeadPassenger, Start_Date =     @StartDate, Duration_In_hours = @Duration, Number_of_pets = @Pets,
    Number_of_children = @Children, Number_of_passengers = @Passengers
WHERE Booking_Ref = @BookingRef

commit
end try
begin catch
    DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;

SELECT 
    @ErrorMessage = ERROR_MESSAGE(),
    @ErrorSeverity = ERROR_SEVERITY(),
    @ErrorState = ERROR_STATE();

rollback

RAISERROR (@ErrorMessage, -- Message text.
           @ErrorSeverity, -- Severity.
           @ErrorState -- State.
           )
end catch


--SET @error = @@error

--IF @error <> 0 RETURN 99
--ELSE RETURN 0

In C# use catch part to see what happened.

Mark Bonafe · Accepted Answer · 2016-05-11 15:00:48Z

The stored procedure will not accept nulls in the parameter values, so you have somewhat of a check in place - maybe. You really need to put some code in the Catch {} block to see if the procedure is returning an error.

I don't know WHERE you want to prevent the update. The problem, as presented, should be solved in the UI. If any of the entries are empty, then don't allow a submit. Simple.

If you want the procedure to avoid performing an update, then you should set all the parameters to allow nulls. Then check for any null values before allowing the update. You could throw a user defined error or 99 (as designed). This approach would also require that you only set parameter values when the textboxes are not empty.

Collectives™ on Stack Overflow

Updating SQL Server database with values from textboxes using stored procedure

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related