插入数据库时出现错误。

Question

我有一个网页表单，基本上是这样的：

我的页面有标签、按钮、网格视图和文本框。

显示的数据来自另一个数据库。

每当我尝试再次插入到另一个数据库时，会出现错误，提示：

“只有在使用列列表并且 IDENTITY_INSERT 为 ON 时，才能为表 'PO2' 中的标识列指定显式值。”

我正在使用 Sql server。

ASP.NET 代码：

 <table class="col-lg-12">

    <tr>
        <td class="style5">  
        Shipping Method</td>
        <td class="style6">Shipping Term</td>
        <td class="style6">Payment Term</td>
        <td class="style4">Delivery Date</td>
         <td class="style4">Final Delivery Date</td>
    </tr>

    <tr>
        <td> 
            <asp:Label ID="lbShippingMethod" runat="server" BorderColor="Black" Font-Size="Larger" /></td>
        <td> 
            <asp:Label ID="lbShippingTerm" runat="server" BorderColor="Black" Font-Size="Larger" /></td>

        <td> 
            <asp:Label ID="lbPaymentTerm" runat="server" BorderColor="Black" Font-Size="Larger" /></td>
        <td> 
            <asp:Label ID="lbDeliveryDate" runat="server" BorderColor="Black" Font-Size="Larger" /></td>
        <td> 
           <asp:Textbox ID="txtDate" runat="server" BorderColor="Black" Font-Size="Larger" type="date" /></td>
    </tr>

        <tr>
            <td>
                <br />
                <br />
            </td>
        </tr>

        </div>
    </table>


<div class="container">
<div class="col-lg-10 pull-right">
 <table class="col-lg-12">

<tr>  
     <td>
         PR#</td> 


    <td class="style2">  
        Product Name  
    </td> 
    <td>Product#</td>

    <td>Price</td>

    <td>  
        Quantity  
    </td> 


</tr>  
<tr>  

            <td>
                <asp:Label ID="PRID" runat="server" BorderColor="Black" Font-Size="Larger" />
           </td>

           <td>
                <asp:DropDownList ID="ddlName" runat="server" class="form-control" 
                    Width="150px" onselectedindexchanged="ddlName_SelectedIndexChanged" AutoPostBack="true"></asp:DropDownList>
           </td>

 <td class="style2">   
    <asp:Label ID="lbProductID" runat="server" Width="90px"></asp:Label>
 </td> 


  <td class="style2">   
    <asp:Label ID="lbPrice" runat="server"></asp:Label>
     </td>  

      <td>  
      <asp:TextBox ID="Quantity" runat="server" Width="90px" Type="Number"  required></asp:TextBox>  
      </td>

      <td>  
      <asp:Label ID="Amount" runat="server" Width="90px" Type="Number" required> </asp:Label>  
  </td>


    <div class="pull-right">  
        <td><asp:Button ID="btnAdd" runat="server" class="btn btn-default" 
            style="background-color:Silver" text="Add Product"   ForeColor="Black" 
            onclick="btnAdd_Click" />
    </td>
    </div>
    </tr>
    </table>
    </div>  
    </div>
    <br />



<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"   Width="1000px" HorizontalAlign="Center"
            DataSourceID="SqlDataSource1">
    <Columns>
        <asp:BoundField DataField="PRID" HeaderText="PRID" SortExpression="PRID"  />
        <asp:BoundField DataField="ProductID" HeaderText="ProductID" 
            SortExpression="ProductID" />
        <asp:BoundField DataField="ProductName" HeaderText="ProductName" 
            SortExpression="ProductName" />
        <asp:BoundField DataField="Price" HeaderText="Price"   SortExpression="Price" />
        <asp:BoundField DataField="Quantity" HeaderText="Quantity" 
        SortExpression="Quantity" />
        <asp:BoundField DataField="Amount" HeaderText="Amount" 
            SortExpression="Amount" />
    </Columns>
</asp:GridView>

        <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
        ConnectionString="<%$  ConnectionStrings:MyOwnMeatshopConnectionString %>" 
        SelectCommand="SELECT [PRID], [ProductID], [ProductName], [Price],  [Quantity], [Amount] FROM [PRDetails] WHERE ([PRID] = @PRID)">
        <SelectParameters>
            <asp:QueryStringParameter Name="PRID" QueryStringField="ID"  Type="Int32" />
        </SelectParameters>
    </asp:SqlDataSource>


   <div class="container">
    <div class="pull-right col-lg-5">
        <h4 />SUB TOTAL Php   <asp:Label ID="lbSubTotal" runat="server"></asp:Label>
        <h4 />VAT (12%) 
        Php   <asp:Literal ID="ltVAT" runat="server"></asp:Literal>
        <h4 />TOTAL Php    <asp:Literal ID="ltTotal" runat="server">    </asp:Literal>
     </div>
   </div>  


   <div class="pull-right">  
    <td>
     <asp:Button ID="btnSum" runat="server" class="btn btn-default"  style="background-color:Silver" text="TOTAL" ForeColor="Black" />
    </td>
   </div>

   <br />
   <br />
   <br />
   <br />

     <div class="form-group">
            <label class="control-label col-lg-4">Remarks</label>
            <div class="col-lg-8">
                <asp:TextBox ID="txtRemarks" runat="server" class="form- control" MaxLength="100" TextMode="MultiLine" />
            </div>
        </div>

    <div class="pull-right"> 
     <div class="form-group">
     <asp:Button ID="btnApprove" runat="server" class="btn btn-success"  text="Approve" 
             onclick="btnApprove_Click" />
            <asp:Button ID="btnCancel" runat="server" class="btn"    style="color:White" text="Disapprove" BackColor="Black"  PostBackUrl="~/Default.aspx" />


            </div>
        </div>
</div>

    </form>

这里是按钮代码（批准）

void addtoPO()
    {
        con.Open();
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "INSERT INTO PO2 VALUES (@UserID, @SupplierID, @CompanyName, @ShippingMethod, " +
            "@ShippingTerm, @Term, @DeliveryDate, @ModifiedBy, @DateModified, @Status, @Remarks, @PODate, @DeliveryDate)";
        cmd.Parameters.AddWithValue("@UserID", Session["userid"].ToString());
        cmd.Parameters.AddWithValue("@SupplierID", lbSupplierID.Text);
        cmd.Parameters.AddWithValue("@CompanyName", lbCompName.Text);

        cmd.Parameters.AddWithValue("@ShippingMethod", lbShippingMethod.Text);
        cmd.Parameters.AddWithValue("@ShippingTerm", lbShippingTerm.Text);
        cmd.Parameters.AddWithValue("@Term", lbPaymentTerm.Text);

        cmd.Parameters.AddWithValue("@ModifiedBy", lbFNA.Text + lbLNA.Text);
        cmd.Parameters.AddWithValue("@DateModified", DateTime.Now);
        cmd.Parameters.AddWithValue("@Status", "Approved");
        cmd.Parameters.AddWithValue("@Remarks", txtRemarks.Text);

        cmd.Parameters.AddWithValue("@PODate", DateTime.Now);
        cmd.Parameters.AddWithValue("@DeliveryDate", txtDate.Text);
        cmd.ExecuteNonQuery();
        con.Close();
    }
    protected void btnApprove_Click(object sender, EventArgs e)
    {
        addtoPO();

        foreach (GridViewRow row in GridView1.Rows)
        {

            if (row.RowType == DataControlRowType.DataRow)
            {


                int ProductID = int.Parse(GridView1.Rows[row.RowIndex].Cells[1].Text);
                string ProductName = GridView1.Rows[row.RowIndex].Cells[2].Text;
                decimal Price = decimal.Parse(GridView1.Rows[row.RowIndex].Cells[3].Text);
                int Quantity = int.Parse(GridView1.Rows[row.RowIndex].Cells[4].Text);
                int Amount = int.Parse(GridView1.Rows[row.RowIndex].Cells[5].Text);

                con.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandText = "INSERT INTO PODetails (ProductID, Name, Price, Quantity, Amount) " +
                     "VALUES (@ProductID, @Name, @Price, @Quantity, @Amount)";

                //cmd.Parameters.AddWithValue("@UserID", Session["userid"].ToString());
                cmd.Parameters.Add("@ProductID", SqlDbType.VarChar).Value = ProductID;
                cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = ProductName.ToString();
                cmd.Parameters.Add("@Price", SqlDbType.Decimal).Value = Price;
                cmd.Parameters.Add("@Quantity", SqlDbType.Int).Value = Quantity;
                cmd.Parameters.Add("@Amount", SqlDbType.Int).Value = Amount;

                cmd.ExecuteNonQuery();



                GridView1.EditIndex = -1;
                GridView1.DataBind();

                //con.Open();

                //cmd.CommandText = "DELETE FROM Orders";
                //cmd.Parameters.Add("@RefNo", RefNo);


                con.Close();

            }
        }

我在插入两个不同的表格，所以插入语句是分开的，其他值都被获取到GRIDVIEW中。

我已经尝试了打开和关闭is identity但没有起作用。

我有其他的插入语句都很好，只有这一个出错了。

- Pauline Gail Mallari Chan

---

顺便提一下，这是 SQL Server 而不是 MySql。 - Steve

这看起来一点也不像MySQL... - David

如果你正在明确地向字段传递值，那么请删除 identity，或者按照错误提示打开和关闭表的 identity_insert。请参考 https://dev59.com/iHA75IYBdhLWcg3wS3BA#3373070。 - Vinay Pandey

对不起，是SQL Server。 - Pauline Gail Mallari Chan

Answer 1

insert into TABLE ([column list])
values ([values list])

您没有列出插入的列列表，因此服务器尝试从左到右填充所有列。下一个错误将是有关未提供足够的值的表列的错误。目前它尝试将@UserID发布到PO2.ID，@SupplierID发布到PO2.UserID等等。

- Ivan Starostin

---

非常感谢@Ivan Starostin，它完美地解决了我的问题！感谢您回答我这个低调的问题:D - Pauline Gail Mallari Chan

Answer 2

感谢大家的回答，非常感激！ 因为我对C#和ASP.NET这些硬编码非常新手，所以请原谅我的问题。

我只是按照@Ivan Starostin先生的答案进行了操作。

 cmd.CommandText = "INSERT INTO PO2 (UserID, SupplierID, CompanyName, ShippingMethod, " +
            "ShippingTerm, Term, ModifiedBy, DateModified, Status, Remarks, PODate, DeliveryDate, FinalDeliveryDate, PRID) VALUES (@UserID, @SupplierID, @CompanyName, @ShippingMethod, " +
            "@ShippingTerm, @Term, @ModifiedBy, @DateModified, @Status, @Remarks, @PODate, @DeliveryDate, @FinalDeliveryDate, @PRID)";

我像这样做了，修复了一些列插入语句，然后就行了！

- Pauline Gail Mallari Chan