Multiple Record add using Store procedure in asp dot net

Question

0.00/5 (No votes)

See more:

Hello Frds
i have problem that to add multiple record in database using store procedure. try many but only last record save in database. i want to save all the record which is in gridview.
here is code for savebutton :-

C#

if (ViewState["CurrentTable"] != null)
            {
                DataTable dt = (DataTable)ViewState["CurrentTable"];

                foreach (GridViewRow gr in GrdProduct.Rows)
                {
                    TextBox box1 = (TextBox)gr.FindControl("txtitemname");
                    TextBox box2 = (TextBox)gr.FindControl("txtqty");
                    TextBox box3 = (TextBox)gr.FindControl("txtprice");

                    string ProductName = box1.Text.ToString();
                    int Quanity = Convert.ToInt32(box2.Text);
                    double Price = Convert.ToDouble(box3.Text);

                    db.InsertUpdateOrderProduct(OrderNo, ProductName, Quanity, Price);
                }

here is storeprocedure :-

SQL

public void InsertUpdateOrderProduct(int OrderID, string ProductName, int Quantity, double Price)
    {
        Database db = new SqlDatabase(this.ConnectionString);
        using (DbCommand objcmd = db.GetStoredProcCommand("dbo.InsertUpdateOrderProduct"))
        {
            db.AddInParameter(objcmd, "@OrderID", DbType.Int32, OrderID);
            db.AddInParameter(objcmd, "@ProductName", DbType.String, ProductName);
            db.AddInParameter(objcmd, "@Quantity", DbType.Int32, Quantity);
            db.AddInParameter(objcmd, "@Price", DbType.Double, Price);
            db.ExecuteNonQuery(objcmd);
        }
    }

both are execute well but there is only last record save in database..
Where i am wrong in this?????
Please help me...!!!

Thank You All..

Posted 20-Aug-12 3:17am

Yatin chauhan

Updated 20-Aug-12 4:04am

v2

Add a Solution

Comments

I.explore.code 20-Aug-12 10:05am

is there any error?

Yatin chauhan 20-Aug-12 10:14am

There is no erroer... i have insert 3 record in gridview all are found while debuge but only last insert in database. @Gladiatron

Zoltán Zörgő 20-Aug-12 10:11am

And how the stored procedure looks like?

Yatin chauhan 20-Aug-12 10:15am

here is my Store Procedure For insert:-
USE [DomesticRates]
GO
/****** Object: StoredProcedure [dbo].[InsertUpdateOrderProduct] Script Date: 08/20/2012 18:36:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[InsertUpdateOrderProduct]
@OrderID INT,
@ProductName VARCHAR(100),
@Quantity INT,
@Price FLOAT

AS
BEGIN

IF EXISTS (SELECT 1 From dbo.OrderProduct OP where @OrderID=OrderID)
BEGIN
UPDATE
dbo.OrderProduct
SET
OrderID=@OrderID,
ProductName=@ProductName,
Quantity=@Quantity,
Price=@Price
Where
OrderID=@OrderID
END
ELSE
BEGIN
INSERT INTO dbo.OrderProduct
(OrderID,ProductName,Quantity,Price)
SELECT
@OrderID,@ProductName,@Quantity,@Price
END
END

ZurdoDev 20-Aug-12 10:46am

So, is @OrderID different every time?

Yatin chauhan 21-Aug-12 8:50am

Hello Ryanb31
No When i create an order for the OrderMaster Table at that time i insert OrderProduct Table. So OrderID is same for one Record.

ZurdoDev 21-Aug-12 8:52am

If @OrderID is the same then you will always do the Update because you have the IF EXISTS statement. One will insert and then the rest will update. Or, am I missing something?

Yatin chauhan 22-Aug-12 1:51am

Hello Ryanb31
You right if i have to insert in then if found so me update...Thank you for posting.

I.explore.code 20-Aug-12 10:33am

sholdn't this SELECT 1 From dbo.OrderProduct OP where @OrderID=OrderID be SELECT 1 From dbo.OrderProduct OP where OrderID=@OrderID ?? and also in your else block you are doing INSERT INTO...SELECT, shouldn't that be INSERT INTO...VALUES(bla, bla)??? just something off the top of my head, don't really use sprocs much neither a great fan of them.

[no name] 20-Aug-12 17:41pm

db.InsertUpdateOrderProduct(OrderNo, ProductName, Quanity, Price); ... you are adding the values for everything except for the OrderNo.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

**Zoltán Zörgő** · Answer 1 · 2012-08-20T04:55:00

Debug the values returned by db.ExecuteNonQuery method calls. You should get three times 1.
This is not the best implementation for an INSERT OR UPDATE replacement. Read the posts here: http://stackoverflow.com/questions/639854/check-if-a-row-exists-otherwise-insert[^]. You should use the MERGE statement[^] or apply proper locks.
And don't use SELECT 1, use SELECT * instead for EXISTS statement.

Sandip.Nascar · Answer 2 · 2012-08-20T10:11:00

It seems, the problem is in sp.

Here is the right sp,

ALTER PROCEDURE [dbo].[InsertUpdateOrderProduct]
	@OrderID INT,
	@ProductName VARCHAR(100),
	@Quantity INT,
	@Price FLOAT
	
AS
	BEGIN
	
	IF EXISTS (SELECT orderid From dbo.OrderProduct where OrderID=@OrderID)
		BEGIN
			UPDATE 
				dbo.OrderProduct
			SET
				OrderID=@OrderID,
				ProductName=@ProductName,
				Quantity=@Quantity,
				Price=@Price
			Where
				OrderID=@OrderID						
		END
	ELSE
		BEGIN
			INSERT INTO dbo.OrderProduct
				(OrderID,ProductName,Quantity,Price)
			SELECT
				@OrderID,@ProductName,@Quantity,@Price
		END		
	END

I assume the code behind codes are executed well without error.

Best of luck.
cheers

Multiple Record add using Store procedure in asp dot net

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0