Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 9th, 2004, 03:03 PM
Authorized User
 
Join Date: Jun 2004
Posts: 49
Thanks: 0
Thanked 0 Times in 0 Posts
Default SQL server Inserting duplicate rows.

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.

 
Old July 9th, 2004, 03:10 PM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

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?
 
Old July 9th, 2004, 04:51 PM
Authorized User
 
Join Date: Jun 2004
Posts: 49
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

 
Old July 9th, 2004, 05:15 PM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

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?
 
Old July 11th, 2004, 05:57 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old July 18th, 2004, 10:16 PM
Registered User
 
Join Date: Jul 2004
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

 
Old December 14th, 2006, 11:09 PM
Registered User
 
Join Date: Dec 2006
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?






Similar Threads
Thread Thread Starter Forum Replies Last Post
Inserting Images and retrieving from SQL SERVER adamusufi VB.NET 2002/2003 Basics 0 June 24th, 2005 04:13 AM
Inserting Date in Sql Server lamdog ASP.NET 1.0 and 1.1 Basics 5 March 18th, 2005 01:05 PM
duplicate rows msrnivas Classic ASP Components 1 August 26th, 2004 02:07 AM
SQL Server failing on certain rows of data benibell SQL Server 2000 1 December 10th, 2003 11:29 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.