Something goes wrong dealing with images

Question

I am really confused .I have table with 3 columns called id,title,images .What I want to do is to store images in the db.But I couldnt .I am getting exception at this line

int id = Convert.ToInt32(cmd.ExecuteScalar());

Connnection state seems open.What else do i need to check?

 SqlConnection connection = null;
        try
        {
            Byte[] imgByte = null;
            if (FileUpload1.HasFile && FileUpload1.PostedFile != null)
            {
                HttpPostedFile File = FileUpload1.PostedFile;
                imgByte = new Byte[File.ContentLength];
                File.InputStream.Read(imgByte, 0, File.ContentLength);
            }
            connection = new SqlConnection("server=.\\sqlexpress;database=Northwind;UID=sa;Password=1234");

            connection.Open();
            string sql = "INSERT INTO imgtable (title,images) VALUES(@theTitle, @theImage) SELECT @@IDENTITY";
            SqlCommand cmd = new SqlCommand(sql, connection);
            cmd.Parameters.AddWithValue("@theTitle", txtTitle.Text);
            cmd.Parameters.AddWithValue("@theImage", imgByte);
            int id = Convert.ToInt32(cmd.ExecuteScalar());

When you say "something goes wrong" what do you mean? What exception message do you see? — glenatron, Jul 04 '14 at 11:27
By the way: `INSERT` does *not* return an ID! And you `INSERT ... SELECT` is not valid SQL syntax. — Thorsten Dittmar, Jul 04 '14 at 11:28
Maybe [this](http://stackoverflow.com/questions/5228780/how-to-get-last-inserted-id) would help. — Carlos Oliveira, Jul 04 '14 at 11:31

score 0 · Answer 1 · answered Jul 04 '14 at 11:39

My crystal ball is telling me that you get an error about invalid SQL syntax. That is because you can not execute two statements by just writing them on one line.

Put a semicolon between the INSERT and the SELECT:

string sql = "INSERT INTO imgtable (title,images) VALUES(@theTitle, @theImage); SELECT @@IDENTITY";

score 0 · Answer 2 · answered Jul 04 '14 at 11:39

If you look at the MSDN documentation SQLCommand ExecuteScalar, they give a complete example:

static public int AddProductCategory(string newName, string connString)
{
    Int32 newProdID = 0;
    string sql =
        "INSERT INTO Production.ProductCategory (Name) VALUES (@Name); "
        + "SELECT CAST(scope_identity() AS int)";
    using (SqlConnection conn = new SqlConnection(connString))
    {
        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.Parameters.Add("@Name", SqlDbType.VarChar);
        cmd.Parameters["@name"].Value = newName;
        try
        {
            conn.Open();
            newProdID = (Int32)cmd.ExecuteScalar();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
    }
    return (int)newProdID;
}

Juste replace int id = Convert.ToInt32(cmd.ExecuteScalar()); by int id = (Int32)cmd.ExecuteScalar();.

Also edit your SQL statement like this: "INSERT INTO imgtable (title,images) VALUES(@theTitle, @theImage); SELECT CAST(scope_identity() AS int)";

That's not the source of the problem. `Convert.ToInt32` and `(Int32)` are equivalent if the result of `ExecuteScalar` is actually an `int`. The problem is that `cmd.ExecuteScalar()` throws an error due to invalid SQL. — Thorsten Dittmar, Jul 04 '14 at 11:43

Something goes wrong dealing with images

2 Answers2