1

I want to select a photo based on user that has opened the window form.

If I put a number in the where clause, "where id=36", it shows only the photo of ID 34 (its static). How can I make it dynamic? Every user has its own photo, so it should be loaded. Code below.

 cmd = new SqlCommand("select profilepic from users where id=@Id", con);
        cmd.Parameters.Add("@ID", SqlDbType.Int);
        cmd.Parameters["@ID"].Value = profilePic;
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);
        if (ds.Tables[0].Rows.Count > 0)
        {
            MemoryStream ms = new MemoryStream((byte[])ds.Tables[0].Rows[0]["profilepic"]);
            pictureBox1.Image = new Bitmap(ms);
        }
Ardi Hasani
  • 11
  • 2

1 Answers1

1

You should use a Parameter object to add that instead of dynamically creating a SQL statement. It is usually safer and does not really penalize in terms of performance

var con = new SqlConnection();
var cmd = new SqlCommand("select profilepic from users where id=@id", con);
cmd.Parameters.AddWithValue("@id", 36);
var pic = cmd.ExecuteScalar();
Vikhram
  • 4,294
  • 1
  • 20
  • 32
  • Error: System.InvalidCastException: 'Failed to convert parameter value from a profilePic to a Int32.' – Ardi Hasani Mar 31 '17 at 18:00
  • Well, what type is profilePic?? – Ross Presser Mar 31 '17 at 18:15
  • @ArdiHasani Unfortunately I don't have a SQL Server handy to try the code, but your error seems to suggest that for some strange reason `profilePic` is a parameter and not `@id`. I have a feeling there is something wrong in your query (wrong datatype, keyword used as a table/field in query, etc) – Vikhram Mar 31 '17 at 18:25
  • I think its pretty obvious that whatever `profilePic` is, it is not an `int`. – Crowcoder Mar 31 '17 at 18:31
  • @Crowcoder and what in the code suggests that `profilePic` is an `int`? – Vikhram Mar 31 '17 at 18:48
  • The SqlParameter type is `SqlDbType.Int` – Crowcoder Mar 31 '17 at 18:49
  • @Crowcoder Hopefully you see that `profilePic` is not a SQL Parameter and just the field name in the query, hence no connection between what you are suggesting and the error. – Vikhram Mar 31 '17 at 18:54
  • Are you seeing the edit to the question? `profilePic` is being assigned to a SqlParameter of type `int`. It also happens to be the name of a column in the table. The fact that the query works with a hard coded integer, and the error message in the first comment above all mean that the variable is not an integer. – Crowcoder Mar 31 '17 at 18:59
  • @crowcoder thanks for bringing this to my attention but seems like op never tried the code I suggested and seemed to have replied to my answer with problems in his modified code as if the issue happened in my suggested code – Vikhram Apr 01 '17 at 21:05