Execute Stored Procedure

Discussion in 'ASP .Net' started by rn5a@rediffmail.com, Oct 22, 2006.

  1. Guest

    In a shopping cart app, when a user finalizes his order, records from a
    temporary table named 'TempCart' get inserted into another table name
    'Orders' after which the records from 'TempCart' are deleted.
    'TempCart' has the following columns:

    UserID int (no NULLs)
    Carpet int (NULLs allowed)
    Color int (NULLs allowed)
    Engine int (NULLs allowed)
    Total decimal(10, 2) (NULLs allowed)

    Just prior to finalizing his order, a user has to enter his address
    where he wants the items to be shipped to. The address details are
    inserted in another table named 'Address'. The 'Address' table has the
    following columns:

    AddrID int IDENTITY
    UserID int (no NULLs)
    OrderID int (no NULLs)
    Addr varchar(100) (NULLs allowed)
    City varchar(50) (NULLs allowed)
    State varchar(50) (NULLs allowed)
    Country varchar(50) (NULLs allowed)
    Zip varchar(50) (NULLs allowed)

    The entire app is ASP.NET based.

    Since a user can place multiple orders, they have been given the
    provision to view the various details of the orders they have placed.
    One of those details is the address they had entered just before
    finalizing an order. Before placing an order, a user has to register
    himself during which his address details are mandatory (registration
    details are inserted in a table named 'AddUsers'). Hence just before
    finalizing an order, a user can choose whether the address he wants his
    items to be shipped to remains the same or he wants to get the items
    shipped to another address. If he opts for the former option, the
    'Addr', 'City', 'State', 'Country' & 'Zip' values in the 'Address'
    table will be NULL else these 5 columns will be inserted with the new
    address. To accomplish this, I framed the following stored procedure:

    ALTER PROCEDURE dbo.PlaceOrder
    @UserID int,
    @Total decimal,
    @Addr varchar(100) = NULL,
    @City varchar(50) = NULL,
    @State varchar(50) = NULL,
    @Country varchar(50) = NULL,
    @Zip varchar(50) = NULL
    AS
    INSERT INTO Orders (UserID, Carpet, Color, Engine, Total, OrderDate)
    SELECT tc.UserID, tc.Carpet, tc.Color, tc.Engine, @Total, GETDATE()
    FROM
    TempCart tc
    WHERE
    tc.UserID = @UserID

    INSERT INTO Address (OrderID, UserID)
    SELECT OrderID, UserID
    FROM
    Orders
    WHERE
    UserID = @UserID

    INSERT INTO Address (Addr, City, State, Country, Zip) VALUES (@Addr,
    @City, @State, @Country, @Zip)
    WHERE
    UserID = @UserID

    DELETE FROM TempCart WHERE UserID = @UserID

    Now what I find is when a user finalizes an order & the above stored
    procedure gets executed, though the 'OrderID' & 'UserID' columns in the
    'Address' table gets populated successfully with the 'OrderID' &
    'UserID' values from the 'Orders' table (i.e. if the OrderID of an
    order in the 'Orders' table is, say, 15 for UserID=7, then the
    'OrderID' & 'UserID' columns in the 'Address' table also get correctly
    populated with 15 & 7 respectively) but still the ASP.NET page
    generates this error:

    Cannot insert the value NULL into column 'OrderID', table
    'dbo.Address'; column does not allow nulls. INSERT fails.
    The statement has been terminated.

    This is how I am invoking the above stored procedure from the ASPX
    page:

    Sub CheckOut(ByVal obj As Object, ByVal ea As EventArgs)
    boShopCart = New ShopCart
    iUserID = Request.Cookies("UserID").Value
    If (rdlAddress.SelectedItem.Value = "no") Then
    boShopCart.PlaceOrder(iUserID, txtAddress.Text, txtCity.Text,
    txtState.Text, txtCountry.Text, txtZip.Text)
    Else
    boShopCart.PlaceOrder(iUserID, "", "", "", "", "")
    End If
    Response.Redirect("ThankYou.aspx")
    End Sub

    & this is the 'PlaceOrder' function in a VB class file:

    Public Sub PlaceOrder(ByVal UserID As Integer, ByVal Addr As String,
    ByVal City As String, ByVal State As String, ByVal Country As String,
    ByVal Zip As String)
    Dim sqlCmd As SqlCommand
    Dim dblTotal As Double = 0

    dblTotal = GetTotal(UserID)
    sqlCmd = New SqlCommand("PlaceOrder", sqlConn)
    sqlCmd.CommandType = CommandType.StoredProcedure

    Try
    With sqlCmd
    .Parameters.Add("@UserID", SqlDbType.Int).Value = UserID
    .Parameters.Add("@Total", SqlDbType.Decimal).Value =
    dblTotal

    If (Addr <> "") Then
    .Parameters.Add("Addr", SqlDbType.VarChar, 100).Value =
    Addr
    Else
    .Parameters.Add("Addr", SqlDbType.VarChar, 100).Value =
    DBNull.Value
    End If

    If (City <> "") Then
    .Parameters.Add("City", SqlDbType.VarChar, 50).Value =
    City
    Else
    .Parameters.Add("City", SqlDbType.VarChar, 50).Value =
    DBNull.Value
    End If

    If (State <> "") Then
    .Parameters.Add("State", SqlDbType.VarChar, 50).Value =
    State
    Else
    .Parameters.Add("State", SqlDbType.VarChar, 50).Value =
    DBNull.Value
    End If

    If (Country <> "") Then
    .Parameters.Add("Country", SqlDbType.VarChar, 50).Value
    = Country
    Else
    .Parameters.Add("Country", SqlDbType.VarChar, 50).Value
    = DBNull.Value
    End If

    If (Zip <> "") Then
    .Parameters.Add("Zip", SqlDbType.VarChar, 50).Value =
    Zip
    Else
    .Parameters.Add("Zip", SqlDbType.VarChar, 50).Value =
    DBNull.Value
    End If

    sqlConn.Open()
    sqlCmd.ExecuteNonQuery()
    sqlConn.Close()
    Catch ex As Exception
    Throw ex
    End Try
    End Sub

    The error points to the Throw ex line within the Catch statement.

    What am I doing wrong here?
    , Oct 22, 2006
    #1
    1. Advertising

  2. Mark Rae Guest

    <> wrote in message
    news:...

    > Now what I find is when a user finalizes an order & the above stored
    > procedure gets executed, though the 'OrderID' & 'UserID' columns in the
    > 'Address' table gets populated successfully with the 'OrderID' &
    > 'UserID' values from the 'Orders' table (i.e. if the OrderID of an
    > order in the 'Orders' table is, say, 15 for UserID=7, then the
    > 'OrderID' & 'UserID' columns in the 'Address' table also get correctly
    > populated with 15 & 7 respectively) but still the ASP.NET page
    > generates this error:
    >
    > Cannot insert the value NULL into column 'OrderID', table
    > 'dbo.Address'; column does not allow nulls. INSERT fails.
    > The statement has been terminated.


    > The error points to the Throw ex line within the Catch statement.
    >
    > What am I doing wrong here?


    Is it possible that your code is trying to insert the record into the
    Address table more times than it should...?

    Pretty easy to find out...

    1) Step through the code (!). You say that the record *DOES* get created
    correctly, so you know at least that that part of the code works, but maybe
    your loop has a bug in it...?

    2) Set up an SQL Trace and inspect the raw SQL that ADO.NET is generating
    and sending to SQL Server...
    Mark Rae, Oct 22, 2006
    #2
    1. Advertising

  3. Guest

    Thanks for the prompt response, Mark, but which loop are you referring
    to?

    Do you find anything wrong in either the stored procedure or the ASPX
    code?


    Mark Rae wrote:
    > <> wrote in message
    > news:...
    >
    > > Now what I find is when a user finalizes an order & the above stored
    > > procedure gets executed, though the 'OrderID' & 'UserID' columns in the
    > > 'Address' table gets populated successfully with the 'OrderID' &
    > > 'UserID' values from the 'Orders' table (i.e. if the OrderID of an
    > > order in the 'Orders' table is, say, 15 for UserID=7, then the
    > > 'OrderID' & 'UserID' columns in the 'Address' table also get correctly
    > > populated with 15 & 7 respectively) but still the ASP.NET page
    > > generates this error:
    > >
    > > Cannot insert the value NULL into column 'OrderID', table
    > > 'dbo.Address'; column does not allow nulls. INSERT fails.
    > > The statement has been terminated.

    >
    > > The error points to the Throw ex line within the Catch statement.
    > >
    > > What am I doing wrong here?

    >
    > Is it possible that your code is trying to insert the record into the
    > Address table more times than it should...?
    >
    > Pretty easy to find out...
    >
    > 1) Step through the code (!). You say that the record *DOES* get created
    > correctly, so you know at least that that part of the code works, but maybe
    > your loop has a bug in it...?
    >
    > 2) Set up an SQL Trace and inspect the raw SQL that ADO.NET is generating
    > and sending to SQL Server...
    , Oct 22, 2006
    #3
  4. Mark Rae Guest

    <> wrote in message
    news:...

    > Thanks for the prompt response, Mark, but which loop are you referring
    > to?


    You mention that the user can make multiple orders on the same page - aren't
    you going through some sort of logic loop to enter these one by one...?

    > Do you find anything wrong in either the stored procedure or the ASPX
    > code?


    Not at first glance...
    Mark Rae, Oct 22, 2006
    #4
  5. Guest

    You hit the nail on the head, Mark.....the looping logic was going
    wrong....a few changes in the stored procedure took care of the problem

    ALTER PROCEDURE dbo.PlaceOrder
    @UserID int,
    @Total decimal,
    @Addr varchar(100) = NULL,
    @City varchar(50) = NULL,
    @State varchar(50) = NULL,
    @Country varchar(50) = NULL,
    @Zip varchar(50) = NULL
    AS
    INSERT INTO Orders (UserID, Carpet, Color, Engine, Total, OrderDate)
    SELECT tc.UserID, tc.Carpet, tc.Color, tc.Engine, @Total, GETDATE()
    FROM
    TempCart tc
    WHERE
    tc.UserID = @UserID

    INSERT INTO Address (OrderID, UserID)
    SELECT OrderID, UserID
    FROM
    Orders
    WHERE
    UserID = @UserID AND
    OrderID = (SELECT MAX(OrderID) FROM Orders WHERE UserID = @UserID)

    UPDATE Address
    SET
    Addr = @Addr,
    City = @City,
    State = @State,
    Country = @Country,
    Zip = @Zip,
    WHERE
    UserID = @UserID AND
    OrderID = (SELECT MAX(OrderID) FROM Orders WHERE UserID = @UserID)

    DELETE FROM TempCart WHERE UserID = @UserID

    Thanks for pointing out the flaw, Mark :)


    Mark Rae wrote:
    > <> wrote in message
    > news:...
    >
    > > Thanks for the prompt response, Mark, but which loop are you referring
    > > to?

    >
    > You mention that the user can make multiple orders on the same page - aren't
    > you going through some sort of logic loop to enter these one by one...?
    >
    > > Do you find anything wrong in either the stored procedure or the ASPX
    > > code?

    >
    > Not at first glance...
    , Oct 22, 2006
    #5
  6. Mark Rae Guest

    <> wrote in message
    news:...

    > You hit the nail on the head, Mark.....the looping logic was going
    > wrong....
    > Thanks for pointing out the flaw, Mark :)


    Welcome.

    BTW, you could make the SP much more robust by wrapping the four separate
    statements in a transaction...
    Mark Rae, Oct 22, 2006
    #6
  7. Guest

    Wrapping the 4 separate statements in a transaction.......that's indeed
    a great suggestion.


    Mark Rae wrote:
    > <> wrote in message
    > news:...
    >
    > > You hit the nail on the head, Mark.....the looping logic was going
    > > wrong....
    > > Thanks for pointing out the flaw, Mark :)

    >
    > Welcome.
    >
    > BTW, you could make the SP much more robust by wrapping the four separate
    > statements in a transaction...
    , Oct 22, 2006
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. FireListen
    Replies:
    1
    Views:
    495
    James J. Foster
    Jun 27, 2003
  2. Jeff Thur
    Replies:
    2
    Views:
    1,030
    Guest
    Feb 7, 2005
  3. =?Utf-8?B?SklNLkgu?=

    Stored Procedure vs direct execute SQL

    =?Utf-8?B?SklNLkgu?=, May 27, 2005, in forum: ASP .Net
    Replies:
    7
    Views:
    5,052
    Robbe Morris [C# MVP]
    May 27, 2005
  4. nicholas
    Replies:
    7
    Views:
    600
    John.Net
    Aug 3, 2005
  5. Mike P
    Replies:
    0
    Views:
    3,298
    Mike P
    Jun 19, 2006
Loading...

Share This Page