My sql server is inserting duplicate rows -- everything is same in two consecutive rows except the primary key. This is in a table named orders. I am using stored procedure to populate the orders table. Friends, what other information would you need to find out what might be going on? Thanks very much.

Hi there,

It looks like your application code is firing twice, causing two records to be inserted.
What kind of application are you using? E.g. ASP, ASP.NET, VB etc?

Can you post some of the relevant code?

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: Ask For Answers by Placebo (Track 4 from the album: Without You I'm Nothing) What's This?

Sure Imar. I am using ASP.NET --coding in C# and SQL Server 2000. When the user clicks the submit button in the checkout page -- my submit button click event is fired. this event is supposed to insert a record in the orders table, among other things. following is the code for my submit button click event.
__________________________________________________ _

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;

namespace perrysplace
{
    /// <summary>
    /// Summary description for PPCheckOut.
    /// </summary>
    public class PPCheckOut : System.Web.UI.Page
    {
        protected System.Web.UI.WebControls.DataGrid MyDataGrid;
        protected System.Web.UI.WebControls.Label TotalLbl;
        protected System.Web.UI.WebControls.Label lblTot;
        protected System.Web.UI.WebControls.Label lblSubmit;
        protected System.Web.UI.WebControls.Label Message;
        protected System.Web.UI.WebControls.Label Header;
        protected System.Web.UI.WebControls.ImageButton SubmitBtn;

        public PPCheckOut()
        {
            Page.Init += new System.EventHandler(Page_Init);
        }



        private void Page_Load(object sender, System.EventArgs e)
        {

            if (Page.IsPostBack == false)
            {

                // Calculate end-user's shopping cart ID
                perrysplace.ShoppingCartDB cart = new ShoppingCartDB();
                String cartId = cart.GetShoppingCartId();

                // Populate datagrid with shopping cart data
                MyDataGrid.DataSource = cart.GetItems(cartId);
                MyDataGrid.DataBind();

                // Update total price label
                TotalLbl.Text = String.Format( "{0:c}", cart.GetTotal(cartId));
            }
        }

        //************************************************** *****
        //
        // The SubmitBtn_Click event handle is used to order the
        // items within the current shopping cart. It then
        // displays the orderid and order status to the screen
        // (hiding the "SubmitBtn" button to ensure that the
        // user can't click it twice).
        //
        //************************************************** *****

        private void SubmitBtn_Click(object sender, System.Web.UI.ImageClickEventArgs e)
        {

            perrysplace.ShoppingCartDB cart = new ShoppingCartDB();

            // Calculate end-user's shopping cart ID
            String cartId = cart.GetShoppingCartId();

            // Calculate end-user's customerID
            String customerId = User.Identity.Name;

            if ((cartId != null) && (customerId != null))
            {

                // Place the order
                perrysplace.OrdersDB ordersDatabase = new OrdersDB();
                int orderId = ordersDatabase.PlaceOrder(customerId, cartId);

                //Update labels to reflect the fact that the order has taken place
                Header.Text="Check Out Complete!";
                Message.Text = "<b>Your Order Number Is: </b>" + orderId;
                SubmitBtn.Visible = false;
            }
        }

        private void Page_Init(object sender, EventArgs e)
        {
            //
            // CODEGEN: This call is required by the ASP.NET Web Form Designer.
            //
            InitializeComponent();
        }
}
}

__________________________________________________ _

Here is my PlaceOrder method called by the click event.
____

public int PlaceOrder(string customerID, string cartID)
        {

            // Create Instance of Connection and Command Object
            SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
            SqlCommand myCommand = new SqlCommand("OrdersAdd", myConnection);

            // Mark the Command as a SPROC
            myCommand.CommandType = CommandType.StoredProcedure;

            // Add Parameters to SPROC
            SqlParameter parameterCustomerID = new SqlParameter("@CustomerID", SqlDbType.Int, 4);
            parameterCustomerID.Value = Int32.Parse(customerID);
            myCommand.Parameters.Add(parameterCustomerID);

            SqlParameter parameterCartID = new SqlParameter("@CartID", SqlDbType.NVarChar, 50);
            parameterCartID.Value = cartID;
            myCommand.Parameters.Add(parameterCartID);

            SqlParameter parameterShipDate = new SqlParameter("@ShipDate", SqlDbType.DateTime, 8);
            parameterShipDate.Value = CalculateShippingDate(customerID, cartID);
            myCommand.Parameters.Add(parameterShipDate);

            SqlParameter parameterOrderDate = new SqlParameter("@OrderDate", SqlDbType.DateTime, 8);
            parameterOrderDate.Value = DateTime.Now;
            myCommand.Parameters.Add(parameterOrderDate);

            SqlParameter parameterOrderID = new SqlParameter("@OrderID", SqlDbType.Int, 4);
            parameterOrderID.Direction = ParameterDirection.Output;
            myCommand.Parameters.Add(parameterOrderID);

            // Open the connection and execute the Command
            myConnection.Open();
            myCommand.ExecuteNonQuery();
            myConnection.Close();

            // Return the OrderID
            return (int)parameterOrderID.Value;
        }
__________________
Here is the OrdersAdd sproc

__________________________


CREATE Procedure OrdersAdd
(
    @CustomerID int,
    @CartID nvarchar(50),
    @OrderDate datetime,
    @ShipDate datetime,
    @OrderID int OUTPUT
)
AS

BEGIN TRAN AddOrder

/* Create the Order header */
INSERT INTO Orders
(
    CustomerID,
    OrderDate,
    ShipDate
)
VALUES
(
    @CustomerID,
    @OrderDate,
    @ShipDate
)

SELECT
    @OrderID = @@Identity

/* Copy items from given shopping cart to OrdersDetail table for given OrderID*/
INSERT INTO OrderDetails
(
    OrderID,
    ProductID,
    Quantity,
    UnitCost
)

SELECT
    @OrderID,
    ShoppingCart.ProductID,
    Quantity,
    Products.UnitCost

FROM
    ShoppingCart
  INNER JOIN Products ON ShoppingCart.ProductID = Products.ProductID

WHERE
    CartID = @CartID

/* Removal of items from user's shopping cart will happen on the business layer*/
EXEC ShoppingCartEmpty @CartId

COMMIT TRAN AddOrder
GO
____________________________________

I hope this mess will make some sense to you Imar. Thanks for your help. I still need to ask you about optional criteria in stored procedures -- in a different post.

Hi shaileshmark,

I don't think this is such a mess; looks like a fine ordering system to me.

Anyway, i looked at your code, and I can't see anything wrong. The sproc uses a single INSERT statement that doesn't use any joins that can result in multiple records returned, so it seems to me that the sproc is called twice (you could use the SQL Server Profiler to make sure).
That means that PlaceOrder is called twice (unless some other method calls your OrdersAdd procedure, which doesn't seem very logical).
In your current code, I cannot see that PlaceOrder is called twice, so going upwards in the chain of events, maybe your SubmitBtn_Click event is called twice.
How do you bind the button to this event? I can't see the EventHandler being wired to the button's Click even in this code...

Did you try to run your page in Debug mode? Set a breakpoint at the first line of Page_Load and step through your app. Together with the SQL Server Profiler that should give you enough information to diagnose the problem.

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: Only You by Portishead (From the album: Portishead) What's This?

Hi Shailesh,

Are you by any chance hitting the submit button TWICE before the page gets refreshed? May be due to the slowness in refreshing the page, one may tend to do that(running out of patience before the page is refreshed, by then SP could have inserted the data once), that results in letting you see duplicate records. May sound silly;), but when there seems to be nothing going wrong with the code, this could be the reason too. I have seen people doing so, even I tried it myself sometimes.

This is common, even in your yahoo inbox Open a mail, (when net connection is slow you can see this for sure), FIRE a delete of that mail, and just before that page gets refreshed, FIRE delete again, that results in ERROR page saying

Invalid Mailbox State
The message you specified cannot be read due to an invalid mailbox state. Please re-list the contents of the folder and select the message again. Reasons this can happen:

* You hit the browser's back button after moving or deleting messages, so that the message you are trying to access is no longer there.
* You are using multiple browser windows and are trying to access a message that has been moved or deleted.

But the reason it says above is not the real cause, and it is ONLY because you are trying to delete a mail that is already deleted, which I can reproduce anytime still. In your case, it is insert. So, just confirm that you are not hitting SUBMIT twice which I could see as the only reason for that happening.

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

If you manually wire events up and don't set autoeventwireup=false in your page directive, you will have two events fire instead of just one because the default is set to true. Add AutoEventWireup=false to your page directive and it might work.

I hope this helps.

Did you ever resolve this? I am having the exact same issue and my stored procedure looks exactly like yours does. It's inserting into two different tables within a transaction. It will create duplicates occasionally and not all of the time. Seems to get worse when the stored procedure is run a greater amount of times. Note: The execution of the stored procedure is in a loop. Anyone have any ideas?