在ASP点网中使用存储过程进行多记录添加 [英] Multiple Record add using Store procedure in asp dot net

问题描述

Hello Frds
我有使用存储过程在数据库中添加多个记录的问题.尝试许多但只有最后一条记录保存在数据库中.我想保存所有在gridview中的记录.
这是savebutton的代码:-

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

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

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

推荐答案

调试db.ExecuteNonQuery方法调用返回的值.你应该得到三倍1.
这不是INSERT或UPDATE替换的最佳实现.在此处阅读帖子: http://stackoverflow.com/questions/639854/check -if-a-row-exists-otherwise-insert [ ^ ].您应该使用 MERGE语句 [

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.

似乎问题出在sp.

这是正确的sp，

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

这篇关于在ASP点网中使用存储过程进行多记录添加的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！