Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old July 9th, 2004, 03:03 PM
Authorized User
 
Join Date: Jun 2004
Location: , , USA.
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.

Reply With Quote
  #2 (permalink)  
Old July 9th, 2004, 03:10 PM
Imar's Avatar
Wrox Author
Points: 72,055, Level: 100
Points: 72,055, Level: 100 Points: 72,055, Level: 100 Points: 72,055, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,086
Thanks: 80
Thanked 1,587 Times in 1,563 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?
Reply With Quote
  #3 (permalink)  
Old July 9th, 2004, 04:51 PM
Authorized User
 
Join Date: Jun 2004
Location: , , USA.
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.

Reply With Quote
  #4 (permalink)  
Old July 9th, 2004, 05:15 PM
Imar's Avatar
Wrox Author
Points: 72,055, Level: 100
Points: 72,055, Level: 100 Points: 72,055, Level: 100 Points: 72,055, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,086
Thanks: 80
Thanked 1,587 Times in 1,563 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?
Reply With Quote
  #5 (permalink)  
Old July 11th, 2004, 05:57 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
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
Reply With Quote
  #6 (permalink)  
Old July 18th, 2004, 10:16 PM
Registered User
 
Join Date: Jul 2004
Location: , , .
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.

Reply With Quote
  #7 (permalink)  
Old December 14th, 2006, 11:09 PM
Registered User
 
Join Date: Dec 2006
Location: , , .
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?

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 01:15 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.