如何从Gridview获取行并使用C＃将它们插入到asp.net的数据库中？ [英] How to get rows from Gridview and Insert them into database in asp.net using C#?

问题描述

您正在为pharmaLaboratory开发一个Web应用程序，同时从客户处获取订单并单击提交按钮，我会将数据插入到我的数据库的三个表中（customer，orderMaster，orderDetail）。我已插入客户和orderMaster表。但是对于orderDetail，我必须从gridview插入数据。当我在gridview中添加针对客户测试添加测试时。一个gridview可以针对一个客户进行多次测试。请帮我把gird视图中的数据插入到数据库中谢谢...



我从gidview获取数据并将其插入数据库的函数是

  private   void  bindOrderDetails（） 
 {
 database_connectivity dbConnect =  new  database_connectivity（）; 
 SqlConnection con =  new  SqlConnection（）; 
 con = dbConnect.ConnectDB（）; 
 con.Open（）; 
 SqlCommand cmmd =  new  SqlCommand（  SELECT MAX（orderID）as Order_Id FROM testOrder_master WHERE user_id =' + Convert.ToInt32（txtUserID.Text）+  '，con）; 
 cmmd.Connection = con; 
 SqlDataReader dr1 = cmmd.ExecuteReader（）; 
  int  orderID =  0 ; 
  while （dr1.Read（））
 {
 orderID = Convert.ToInt32（dr1 [  Order_Id]）; 
} 
 con.Close（）; 
  //   for（int i = 0; i< GridView1.Rows.Count  -  1; i ++）  
  //   { 
  //   Stquery = @INSERT INTO testOrder_details（orderID，labID，srNo，test_id，price，createdBy，creationTime）VALUES（'+ orderID +'， '+ Convert.ToInt32（txtLabID.Text）+'，'+ Convert.ToString（GridView1.Rows [i] .Cells [1]。 
  //  }  
 
  foreach （GridViewRow row < span class =code-keyword> in  GridView1.Rows）
 {
 con.Open（）; 
 SqlCommand cmd1 = 新 SqlCommand（  INSERT INTO testOrder_details（orderID，labID，srNo，test_id，price，createdBy，creationTime）价值观（@ord erId1，@ lab_id，@ serialNo，@ testID，@ testPrice，@ create_by，@ create_time），con）; 
 cmd1.Parameters.AddWithValue（  @ orderId1，orderID）; 
 cmd1.Parameters.AddWithValue（  @ lab_id，Convert.ToInt32（txtLabID.Text ））; 
 cmd1.Parameters.AddWithValue（  @ serialNo，row.Cells [ 1 ] Text.ToString（））。 
 cmd1.Parameters.AddWithValue（  @ testID，row.Cells [ 2 ]的ToString（））; 
 cmd1.Parameters.AddWithValue（  @ testPrice，row.Cells [ 4 ]的ToString（））; 
 cmd1.Parameters.AddWithValue（  @ created_by，Convert.ToString（txtUserTitle.Text ））; 
 cmd1.Parameters.AddWithValue（  @ creation_time，DateTime.Now）; 
 cmd1.ExecuteNonQuery（）; 
} 
} 

这就是我的gridview的asp代码

 <   asp：GridView     ID   =  GridView1 < span class =code-attribute>   runat   =  server    AutoGenerateColumns   =  错误    
   
      < span class =code-attribute>     BackColor   = 白色    BorderColor   = ＃CCCCCC    
   
           BorderStyle   = 无    BorderWidth   =  1px    CellPadding   =  3  
   
           onrowdeleting   =  GridView1_RowDeleting    
   
           onselectedindexchanged   = < span class =code-keyword> GridView1_SelectedIndexChanged  
   
           onrowcom mand   =  GridView1_RowCommand    
   
           onrowdatabound   =  GridView1_RowDataBound  >  
 <  列 >  
 <   asp：CommandField     ShowDeleteButton   =  True     ButtonType   = 图片    
   
                        < span class =code-attribute>   DeleteImageUrl   = 〜/ Imagess / delete.jpg    /  >  
 <   asp：TemplateField     HeaderText   =  序列号 >  
 <   ItemTemplate  >  < ;％ #Container.DataItemIndex +  1 ％> <   / ItemTemplate  >  
 <   ItemStyle    宽度  =  2％    /  >  <   / asp：TemplateField  >  
 
 <   asp：BoundField     DataField   =  testID    HeaderText   = 测试ID    /  >  
 <   asp：BoundField     DataField   =  testName    HeaderText   = 测试名称   < span class =code-keyword> /  >  
 <   asp：BoundField     DataField   =  testPrice    HeaderText   = 价格    /  >  
 <   /列 >  
  <   FooterStyle    < span class =code-attribute> BackColor   = 白色    ForeColor   = ＃000066    /  >  
 <   HeaderStyle     BackColor   = ＃006699    Font-Bold   =  True    ForeColor   = 白色    /  >  
 <   PagerStyle     BackColor   = 白色    ForeColor   = ＃000066    Horizo​​ntalAlign   = 左    /  >  
 <   RowStyle     ForeColor   = ＃000066    /  > ;  
 <   SelectedRowStyle     BackColor   = ＃669999   字体粗体  = < span class =code-keyword> True    ForeColor   = 白色  /  >  
 <   SortedAscendingCellStyle     BackColor   = ＃F1F1F1    /  >  
 <   SortedAscendingHeaderStyle     BackColor   = ＃007DBB    /  >  
 <   SortedDescendingCellStyle     BackColor   = ＃CAC9C9    /  >  
 <   SortedDescendingHeaderStyle     BackColor   = ＃00547E    /  < span class =code-keyword>>  
 <   / asp：GridView  >  

解决方案

试试这个。它应该工作。

但你需要在代码中设置一个断点并检查GridViewRow单元格中的值。

喜欢这一行：

 cmd1.Parameters.AddWithValue（  @ orderId1， orderID）; 

然后你可以告诉我们你得到了什么价值以及你期望的价值。



我也改为这种语法：

 row.Cells [ 1 ]。文字

那里有各种各样的方法。



这一个

 Convert.ToString（txtUserTitle.Text ）

感觉有点像矫枉过正。

  private   void  bindOrderDetails（）
 {
 database_connectivity dbConnect =  new  database_connectivity（ ）; 
  //  使用意味着在离开范围时将关闭连接 
 使用（SqlConnection con =  new  SqlConnection（））
 {
 con = dbConnect .ConnectDB（）; 
 con.Open（）; 
 SqlCommand cmmd =  new  SqlCommand（ @  
 SELECT MAX（orderID）as Order_Id 
 FROM testOrder_master 
 WHERE user_id =' + Convert.ToInt32（txtUserID.Text）+   '，con）; 
 cmmd.Connection = con; 
 SqlDataReader dr1 = cmmd.ExecuteReader（）; 
  //  这里不需要循环。只有一个值 
 如果（！dr1.Read（））
  throw   new 异常（ 无法读取OrderID ）; 
 
  int  orderID = Convert.ToInt32（dr1 [  ORDER_ID]）; 
 dr1.Close（）;  //  最好关闭阅读器 
 
  foreach （GridViewRow行 in  GridView1.Rows）
 {
 SqlCommand cmd1 =  new  SqlCommand（ @  
 INSERT INTO testOrder_details 
（ orderID，labID，srNo，test_id，price，createdBy，creationTime）
 VALUES 
（@ orderId1，@ lab_id，@ serialNo，@ testID，@ testPrice，@ create_by，@ creation_time） ，con）; 
 cmd1.Parameters.AddWithValue（  @ orderId1，orderID）; 
 cmd1.Parameters.AddWithValue（  @ lab_id，Convert.ToInt32（txtLabID.Text ））; 
 cmd1.Parameters.AddWithValue（  @ serialNo，row.Cells [ 1 ]文本）。 
 cmd1.Parameters.AddWithValue（  @ testID，row.Cells [ 2 ]文本）。 
 cmd1.Parameters.AddWithValue（  @ testPrice，row.Cells [ 4 ]文本）。 
 cmd1.Parameters.AddWithValue（  @ created_by，txtUserTitle.Text）; 
 cmd1.Parameters.AddWithValue（  @ creation_time，DateTime.Now）; 
 cmd1.ExecuteNonQuery（）; 
} 
} 
} 

祝你好运

试试吧......



添加此行cmd1.Parameters.Clear（）;



在cmd1.Parameters.AddWithValue（@ orderId1，orderID）之前;



它将在添加下一个参数之前清除命令（cmd1）中的所有旧参数值行。

hi i am developing a web application for pharmaLaboratory, while taking the order from customer and clicking on submit button, i would have insert data into three tables of my database (customer, orderMaster, orderDetail). i have inserted into customer and orderMaster table. but for orderDetail i would have to insert data from gridview. when i add a test against a customer test is added in gridview. one gridview can have multiple tests against one customer. Please help me to insert data from gird view to database thanks...

my function for getting data from gidview and inserting it into database is

private void bindOrderDetails()
{
    database_connectivity dbConnect = new database_connectivity();
    SqlConnection con = new SqlConnection();
    con = dbConnect.ConnectDB();
    con.Open();
    SqlCommand cmmd = new SqlCommand("SELECT MAX(orderID) as Order_Id FROM testOrder_master WHERE user_id = '" + Convert.ToInt32(txtUserID.Text) + "'", con);
    cmmd.Connection = con;
    SqlDataReader dr1 = cmmd.ExecuteReader();
    int orderID = 0;
    while (dr1.Read())
    {
        orderID = Convert.ToInt32(dr1["Order_Id"]);
    }
    con.Close();
    //for (int i = 0; i < GridView1.Rows.Count - 1; i++)
    //{
    //    Stquery = @"INSERT INTO testOrder_details (orderID, labID, srNo, test_id, price, createdBy, creationTime) VALUES ('"+orderID+"','"+Convert.ToInt32(txtLabID.Text)+"','"+Convert.ToString(GridView1.Rows[i].Cells[1].
    //}

    foreach (GridViewRow row in GridView1.Rows)
    {
        con.Open();
        SqlCommand cmd1 = new SqlCommand("INSERT INTO testOrder_details (orderID, labID, srNo, test_id, price, createdBy, creationTime) VALUES (@orderId1,@lab_id,@serialNo,@testID, @testPrice, @created_by, @creation_time)", con);
        cmd1.Parameters.AddWithValue("@orderId1", orderID);
        cmd1.Parameters.AddWithValue("@lab_id", Convert.ToInt32(txtLabID.Text));
        cmd1.Parameters.AddWithValue("@serialNo", row.Cells[1].Text.ToString());
        cmd1.Parameters.AddWithValue("@testID", row.Cells[2].ToString());
        cmd1.Parameters.AddWithValue("@testPrice", row.Cells[4].ToString());
        cmd1.Parameters.AddWithValue("@created_by", Convert.ToString(txtUserTitle.Text));
        cmd1.Parameters.AddWithValue("@creation_time", DateTime.Now);
        cmd1.ExecuteNonQuery();
    }
}

thats the asp code of my gridview

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 

    BackColor="White" BorderColor="#CCCCCC" 

    BorderStyle="None" BorderWidth="1px" CellPadding="3"

    onrowdeleting="GridView1_RowDeleting" 

    onselectedindexchanged="GridView1_SelectedIndexChanged"

    onrowcommand="GridView1_RowCommand" 

    onrowdatabound="GridView1_RowDataBound">
    <Columns>
        <asp:CommandField ShowDeleteButton="True" ButtonType="Image" 

            DeleteImageUrl="~/Imagess/delete.jpg" />
    <asp:TemplateField HeaderText="Serial No">
    <ItemTemplate><%# Container.DataItemIndex + 1 %></ItemTemplate>
    <ItemStyle Width="2%" /> </asp:TemplateField>

        <asp:BoundField DataField="testID" HeaderText="Test ID" />
        <asp:BoundField DataField="testName" HeaderText="Test Name" />
        <asp:BoundField DataField="testPrice" HeaderText="Price" />
    </Columns>
    <FooterStyle BackColor="White" ForeColor="#000066" />
    <HeaderStyle BackColor="#006699" Font-Bold="True" ForeColor="White" />
    <PagerStyle BackColor="White" ForeColor="#000066" HorizontalAlign="Left" />
    <RowStyle ForeColor="#000066" />
    <SelectedRowStyle BackColor="#669999" Font-Bold="True" ForeColor="White"/>
    <SortedAscendingCellStyle BackColor="#F1F1F1" />
    <SortedAscendingHeaderStyle BackColor="#007DBB" />
    <SortedDescendingCellStyle BackColor="#CAC9C9" />
    <SortedDescendingHeaderStyle BackColor="#00547E" />
</asp:GridView>

解决方案

Try this. It should work.
But you need to set a break point in the code and check the values from the GridViewRow cells.
Like on this row:

cmd1.Parameters.AddWithValue("@orderId1", orderID);

Then you can tell us what value you got and what value you expected.

I changed to this syntax too:

row.Cells[1].Text

You had a great variety of approaches there.

And this one

Convert.ToString(txtUserTitle.Text)

feels a bit like overkill.

private void bindOrderDetails()
{
  database_connectivity dbConnect = new database_connectivity();
  // using means that the connection will be closed upon leaving the scope
  using (SqlConnection con = new SqlConnection())
  {
    con = dbConnect.ConnectDB();
    con.Open();
    SqlCommand cmmd = new SqlCommand(@"
       SELECT MAX(orderID) as Order_Id 
        FROM testOrder_master 
        WHERE user_id = '" + Convert.ToInt32(txtUserID.Text) + "'", con);
    cmmd.Connection = con;
    SqlDataReader dr1 = cmmd.ExecuteReader();
    // No need for a loop here. Only one value
    if (!dr1.Read())
      throw new Exception("Unable to read OrderID");
                
    int orderID = Convert.ToInt32(dr1["Order_Id"]);
    dr1.Close();    // It is good practice to close the reader
                
    foreach (GridViewRow row in GridView1.Rows)
    {
      SqlCommand cmd1 = new SqlCommand(@"
         INSERT INTO testOrder_details 
           (orderID, labID, srNo, test_id, price, createdBy, creationTime) 
         VALUES 
           (@orderId1,@lab_id,@serialNo,@testID, @testPrice, @created_by, @creation_time)", con);
      cmd1.Parameters.AddWithValue("@orderId1", orderID);
      cmd1.Parameters.AddWithValue("@lab_id", Convert.ToInt32(txtLabID.Text));
      cmd1.Parameters.AddWithValue("@serialNo", row.Cells[1].Text);
      cmd1.Parameters.AddWithValue("@testID", row.Cells[2].Text);
      cmd1.Parameters.AddWithValue("@testPrice", row.Cells[4].Text);
      cmd1.Parameters.AddWithValue("@created_by", txtUserTitle.Text);
      cmd1.Parameters.AddWithValue("@creation_time", DateTime.Now);
      cmd1.ExecuteNonQuery();
    }
  }
}

Good luck

Hi, Try this...

Add this line cmd1.Parameters.Clear();

Before cmd1.Parameters.AddWithValue("@orderId1", orderID);

it will clear all old parameter values from command (cmd1), before adding parameters for next row.

这篇关于如何从Gridview获取行并使用C＃将它们插入到asp.net的数据库中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！