 |
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
|
|
|

July 9th, 2004, 03:03 PM
|
Authorized User
|
|
Join Date: Jun 2004
Posts: 49
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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.
|

July 9th, 2004, 03:10 PM
|
 |
Wrox Author
|
|
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
|
|
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?
|

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

July 9th, 2004, 05:15 PM
|
 |
Wrox Author
|
|
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
|
|
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?
|

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

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

December 14th, 2006, 11:09 PM
|
Registered User
|
|
Join Date: Dec 2006
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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?
|
|
 |