如何在ASP.NET中将多个记录从grdviw插入SQL服务器 [英] How to insert more then one record from grdviw to SQL server in ASP.NET

问题描述

大家好，我有两个表，saleorder和itemorder，我可以在点击时在两个表中插入，但是当我想从gridview插入多个记录时，它会给我这个错误，（过程或函数insertale指定了太多的参数。）任何人都可以帮助我，下面是我的代码



我尝试了什么：



SqlCommand cmd = new SqlCommand（insertsale）;



cmd.Parameters.AddWithValue（@ CustomerId， DropDownList3.SelectedValue）;

//cmd.Parameters.AddWi

cmd.Parameters.AddWithValue（@ SoDate，Convert.ToDateTime（TextBox13.Text））;

cmd.Parameters.AddWithValue（@ MadeBY，int.Parse（DropDownList4.SelectedValue））;

cmd.Parameters.AddWithValue（@ SubTotal，TextBox1.Text ）;

cmd.Connection = mycon;



cmd.CommandType = CommandType.Stor edProcedure;



DataTable dt1 =（DataTable）Session [DataTable];

for（int i = 0;我< dt1.Rows.Count; i ++）

{



cmd.Parameters.AddWithValue（@ ItemID，dt1.Rows [i] [ItemID]。 ToString（））;

cmd.Parameters.AddWithValue（@ Quantity，dt1.Rows [i] [Quantity]。ToString（））;

cmd .Parameters.AddWithValue（@ Rate，dt1.Rows [i] [Rate]。ToString（））;

cmd.Parameters.AddWithValue（@ Total，dt1.Rows [ i] [total]。ToString（））;



}





mycon.Open（）;

cmd.ExecuteNonQuery（）;

mycon.Close（）;



和我的storprocdure是

StoredProcedure [dbo]。[insertsale]脚本日期：3/31/2016 9:00:27 AM ****** /

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER程序[dbo]。[ insertale]

（

@CustomerID int，

@SoDate datetime，

@SubTotal varchar（50），

@MadeBY varchar（50），

@ItemID int，

@Quantity varchar（50 ），

@Rate varchar（50），

@Total varchar（50）

）

as

开始



插入SalesOrder（CustomerId，SoDate，SubTotal，MadeBY）

值（@ CustomerID，@ SoDate，@ SubTotal，@ MadeBY）



插入OrderItems（SalesID，ItemID，数量，费率，总计）

值（SCOPE_IDENTITY（ ），@ ItemID，@ Quantity，@ Rate，@ Total）



end

Hi everybody, i have two table, saleorder,and itemorder, i can insert in both table from on click, but when i want to insert more then one record from gridview, it give me this error,(Procedure or function insertsale has too many arguments specified.) can any body help me please, below is my code

What I have tried:

SqlCommand cmd = new SqlCommand("insertsale");

cmd.Parameters.AddWithValue("@CustomerId", DropDownList3.SelectedValue);
//cmd.Parameters.AddWi
cmd.Parameters.AddWithValue("@SoDate", Convert.ToDateTime(TextBox13.Text));
cmd.Parameters.AddWithValue("@MadeBY", int.Parse(DropDownList4.SelectedValue));
cmd.Parameters.AddWithValue("@SubTotal", TextBox1.Text);
cmd.Connection = mycon;

cmd.CommandType = CommandType.StoredProcedure;

DataTable dt1 = (DataTable)Session["DataTable"];
for (int i = 0; i < dt1.Rows.Count; i++)
{

cmd.Parameters.AddWithValue("@ItemID", dt1.Rows[i]["ItemID"].ToString());
cmd.Parameters.AddWithValue("@Quantity", dt1.Rows[i]["Quantity"].ToString());
cmd.Parameters.AddWithValue("@Rate", dt1.Rows[i]["Rate"].ToString());
cmd.Parameters.AddWithValue("@Total", dt1.Rows[i]["total"].ToString());

}


mycon.Open();
cmd.ExecuteNonQuery();
mycon.Close();

and my storprocdure is
StoredProcedure [dbo].[insertsale] Script Date: 3/31/2016 9:00:27 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[insertsale]
(
@CustomerID int,
@SoDate datetime,
@SubTotal varchar(50),
@MadeBY varchar (50),
@ItemID int,
@Quantity varchar(50),
@Rate varchar(50),
@Total varchar(50)
)
as
begin

insert into SalesOrder(CustomerId,SoDate,SubTotal,MadeBY)
values (@CustomerID,@SoDate,@SubTotal,@MadeBY)

insert into OrderItems(SalesID,ItemID,Quantity,Rate,Total)
values (SCOPE_IDENTITY(),@ItemID,@Quantity,@Rate,@Total)

end

推荐答案

几乎没有更正对你的代码做了。

Few corrections made to your code.

DataTable dt1 = (DataTable)Session["DataTable"];
           for (int i = 0; i < dt1.Rows.Count; i++)
           {

               SqlCommand cmd = new SqlCommand("insertsale");
               cmd.Connection = mycon;
               cmd.CommandType = CommandType.StoredProcedure;

               cmd.Parameters.AddWithValue("@CustomerId", DropDownList3.SelectedValue);
               cmd.Parameters.AddWithValue("@SoDate", Convert.ToDateTime(TextBox13.Text));
               cmd.Parameters.AddWithValue("@MadeBY", int.Parse(DropDownList4.SelectedValue));
               cmd.Parameters.AddWithValue("@SubTotal", TextBox1.Text);
               cmd.Parameters.AddWithValue("@ItemID", dt1.Rows[i]["ItemID"].ToString());
               cmd.Parameters.AddWithValue("@Quantity", dt1.Rows[i]["Quantity"].ToString());
               cmd.Parameters.AddWithValue("@Rate", dt1.Rows[i]["Rate"].ToString());
               cmd.Parameters.AddWithValue("@Total", dt1.Rows[i]["total"].ToString());
               mycon.Open();
               cmd.ExecuteNonQuery();
               mycon.Close();

           }

根据您使用的SQL版本，您可以插入多个记录，但您需要更改自己的方式将值传递给存储过程。

插入多个值（需要SQL 2008或更高版本）;

Depending on the SQL Version you are using you can insert multiple records but you would need to change the way you are passing your values to the Stored Procedure.
To insert multiple values (requires SQL 2008 or later);

INSERT INTO MyTable
(FieldNameA, FieldNameB)
VALUES
('Row1Value1', 'Row1Value2'),('Row2Value1', 'Row2Value2')

为了达到这个目的，你需要将字符串传递给你的存储过程，然后将值拆分 - 合理有效，因为它需要一次往返e SQL Server。

或者你需要使用2个存储过程。第一个返回您的新订单ID，第二个将每个订单行作为单个记录插入，并在您的循环中多次调用



亲切的问候

To achieve this though you would need to pass strings to your Stored procedure and then split the values out - reasonably efficient as it requires a single round trip to the SQL Server.
Alternatively you would need to use 2 stored procedures. The first returns your new Order Id and the second inserts each Order Line as a single record and is called multiple times within your loop

Kind Regards

这篇关于如何在ASP.NET中将多个记录从grdviw插入SQL服务器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！