Identity_Insert ON in EF along with insert command

Question

This is my C# code, and I get this error

An explicit value for the identity column in table 'tblImages' can only be specified when a column list is used and IDENTITY_INSERT is ON.

public partial class _Default : System.Web.UI.Page
{
    // string strconn= ConfigurationManager.ConnectionStrings["con"].ConnectionString;
    SqlConnection conn=new SqlConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=E:\Final_Year_Project\ProjectFiles\ComSysForDeafAndDumb\App_Data\Database.mdf;Integrated Security=True");

    static string query="";
    static int myID = 989;
    static string imgName;
    static string imgSize;

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            lblMessage.Visible = false;
            hyperlink.Visible = false;
        }
    }

    protected void btnUpload_Click(object sender, EventArgs e)
    {
       string b = "";
       FileUpload1.SaveAs(Request.PhysicalApplicationPath +"./images/"+ FileUpload1.FileName.ToString());
       b="~/images/"+ FileUpload1.FileName.ToString();        
       query = "insert into tblImages values ("+myID+",'"+imgName+"','"+imgSize+"','"+b+"','"+txtdes.Value+"')";

       SqlCommand cmd = new SqlCommand(query, conn);
       conn.Open();

       cmd.ExecuteNonQuery();
       conn.Close();

       Response.Write("successful");
       imgUpload.ImageUrl = b;
    }
}

[SQL Injection alert](http://msdn.microsoft.com/en-us/library/ms161953%28v=sql.105%29.aspx) - you should **not** concatenate together your SQL statements - use **parametrized queries** instead to avoid SQL injection - check out [Little Bobby Tables](https://xkcd.com/327/)+ — marc_s, Apr 27 '18 at 16:50
@marc_s Have you seen [bobby-tables.com](http://bobby-tables.com/)? It has a bit more explanation there, you may wish to link to that instead of directly to the XKCD in the future. It even has language specific examples - I contributed the C# example! — mason, Apr 27 '18 at 16:54
@mason: great resource - thanks for the pointer - updated my response template :-) — marc_s, Apr 27 '18 at 16:55

score 0 · Answer 1 · answered Apr 27 '18 at 16:51

When you do this:

insert into tblImages values (123, 'some value', 'another value')

The database will map the supplied values directly to the column list in the table. But one of your columns does not allow explicitly inserted values. Specifically the identity column, which is usually the first column in the table and usually called something like ID.

Instead of trying to insert into all columns, explicitly insert into the columns you want. We don't know the structure of your table, but as an example the INSERT command would look something like this:

insert into tblImages (ImageName, ImageSize) values ('some value', 'another value')

Where ImageName and ImageSize would be names of your columns. Expand this to include all of the columns into which you want to insert values, and to include all of the values you're trying to insert for that record. Basically, explicitly specify which columns you want to write to and don't try to write to the identity column.

Also, and this is important, your code is wide open to SQL injection. This question has some good explanations and examples of how to address that. Basically you should always treat user input as values and not as executable code in your SQL commands.

Identity_Insert ON in EF along with insert command

1 Answers1