将值从文本框插入SQL - 错误 [英] Insert value from textbox to SQL - error

问题描述

得到此错误消息：无法将值NULL插入列'id'，表'';列不允许空值。 INSERT失败。提前谢谢

 受保护  void  AddItem （ object  sender，EventArgs e）{
  string  insertCmd =   INSERT INTO Picture（Album，id）VALUES（@ Album，@ id）; 
 使用（SqlConnection conn =  new  SqlConnection（ConfigurationManager.ConnectionStrings [  strConn]。ConnectionString））
 {
 conn.Open（）; 
 SqlCommand myCommand =  new  SqlCommand（insertCmd，conn）; 
  //  为SqlCommand对象创建参数 
  //  使用输入表单字段值初始化 
 myCommand.Parameters.AddWithValue（  @ Album，txtAlbum.Text）; 
 myCommand.Parameters.Add（  @ id，SqlDbType.Int）.Direction = ParameterDirection.Output; 
 myCommand.ExecuteNonQuery（）; 
 
  int  id =（ int ）myCommand.Parameters [  @ id]。 
} 
} 

我的尝试：

 myCommand.Parameters.Add（  @id，SqlDbType.Int）.Direction = ParameterDirection.Output; 

解决方案

你不能设置@ID作为INSERT命令中的输出参数 - 它是您需要设置的值，而不是任何可以返回的值，INSERT只会返回受影响的行数。

设置@ID值与设置@ALBUM参数值的方式相同。

如果您尝试从代码中设置Id列的值，那么如解决方案1中所述，更改方向参数并为其定义一个值。



但是如果你真的试图为行使用自动生成的值，那么你应该确保表有 IDENTITY（Property） [ ^ ]为Id列定义。这样，当插入行时，列将自动获得唯一值。在这种情况下，如果需要从INSERT语句获取新插入的值作为输出参数，则更改语句以使其包含OUTPUT条款 [ ^ 。例如

  string  insertCmd =   INSERT INTO Picture（Album，id）OUTPUT inserted.Id VALUES（@Album）; 
 ... 
  int   value  = myCommand.ExecuteScalar（）; 

get this error message: Cannot insert the value NULL into column 'id', table ''; column does not allow nulls. INSERT fails. thanks in advance

protected void AddItem(object sender, EventArgs e) {
        string insertCmd = "INSERT INTO Picture (Album, id) VALUES (@Album, @id)";
        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["strConn"].ConnectionString))
        {
            conn.Open();
            SqlCommand myCommand = new SqlCommand(insertCmd, conn);
            // Create parameters for the SqlCommand object
            // initialize with input-form field values
            myCommand.Parameters.AddWithValue("@Album", txtAlbum.Text);
            myCommand.Parameters.Add("@id", SqlDbType.Int).Direction = ParameterDirection.Output;
            myCommand.ExecuteNonQuery();

            int id = (int)myCommand.Parameters["@id"].Value;
        }
    }

What I have tried:

myCommand.Parameters.Add("@id", SqlDbType.Int).Direction = ParameterDirection.Output;

解决方案

You can't set @ID as an output parameter in an INSERT command - it's a value you need to set, not anything that can be returned, and INSERT only ever returns the number of rows affected anyway.
Set the @ID value in the same way you set the @ALBUM parameters value.

If you're trying to set the value for Id column from the code, then as said in solution 1, change the direction of the parameter and define a value for it.

But if you're actually trying to use an auto-generated value for the row, then you should ensure that the table has IDENTITY (Property)[^] defined for the Id column. This way the column would automatically get an unique value when a row is inserted. In this case if you need to get the newly inserted value as an output parameter from the INSERT statement, you change the statement so that it includes an OUTPUT Clause[^]. For example

string insertCmd = "INSERT INTO Picture (Album, id) OUTPUT inserted.Id VALUES (@Album)";
...
int value = myCommand.ExecuteScalar();

这篇关于将值从文本框插入SQL - 错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！