insert value from TextBox into sql

Question

I'm getting 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;
        }
    }

just trim the null value to empty before inserting in to database — Meena, Oct 19 '16 at 13:24

score 0 · Answer 1 · edited May 23 '17 at 11:48

I suppose that ID is an IDENTITY column. Its value is generated automatically by the database engine and you want to know what value has been assigned to your record.

Then you should change your query to

string insertCmd = @"INSERT INTO Picture (Album) VALUES (@Album);
                     SELECT SCOPE_IDENTITY()";
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["strConn"].ConnectionString))
{
    conn.Open();
    SqlCommand myCommand = new SqlCommand(insertCmd, conn);
    myCommand.Parameters.AddWithValue("@Album", txtAlbum.Text);
    int newID = Convert.ToInt32(myCommand.ExecuteScalar());
}

The query text now contains a second instruction SELECT SCOPE_IDENTITY() separated from the first command by a semicolon. SCOPE_IDENTITY returns the last IDENTITY value generated for you by the database engine in the current scope.

Now the command is run using the ExecuteScalar to get back the single value returned by the last statement present in the query text without using any output parameter

score 0 · Answer 2 · answered Oct 19 '16 at 13:32

I would think that ID is identity. You don't have to add this value. I would try the following code and check the database if you get automatically an ID.

string insertCmd = "INSERT INTO Picture (Album) VALUES (@Album)";
        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.ExecuteNonQuery();


        }

I case you want to set the id yourself(withoud automatic increment from the db), you should change the schema of the database removing identity from ID as shown below:

I hope this helps

score 0 · Answer 3 · answered Oct 19 '16 at 13:55

0

If you need to stay this column empty you can try to replace to ' '(blank). This will work if you column is not "Key"

Or try to use:

substitute a value when a null value is encountered

NVL( string1, replace_with )

answered Oct 19 '16 at 13:55

Elton C Ramos

9
3

score 0 · Answer 4 · answered Oct 19 '16 at 13:59

You can do this using stored procedure. Below is the script for Create stored procedure.

CREATE PROCEDURE [dbo].[InsertIntoPicture]  
  @Album varchar(500)=null,
  @id int=0 output
AS
BEGIN
  insert INTO Picture(Album)VALUES(@Album)
  SET @id=@@IDENTITY
END

Below is the code for call stored procedure with C# .

 string insertCmd = "InsertIntoPicture";
 using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["strConn"].ConnectionString))
  {
     conn.Open();
     SqlCommand myCommand = new SqlCommand(insertCmd, conn);
     myCommand.CommandType = CommandType.StoredProcedure;
     myCommand.Parameters.AddWithValue("@Album", txtAlbum.Text);
     myCommand.Parameters.Add("@id", SqlDbType.Int).Direction = ParameterDirection.Output;
     myCommand.ExecuteNonQuery();

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

Using above code you can insert a date from TextBox and also get last inserted record ID as an output variable as per your requirement.

Thanks .

insert value from TextBox into sql

4 Answers4