C# parameter is not valid SQL

Question

I have code that I copied from the tutorial that I watch and our code is so similar in the tutorial.

When the presenter runs the code, it runs ok, but when I try to run my code which is the same as in the tutorial, I get an error "the parameter is not valid".

Please help

    private void Viewbutton_Click(object sender, EventArgs e)
    {
        conection.Open();

        string sqlQuery = "select studnum, course, f_name, l_name, color_image from table3 where studnum='" + textBox1.Text + "'";

        cmd = new SqlCommand(sqlQuery, conection);

        SqlDataReader dataread = cmd.ExecuteReader();
        dataread.Read();

        if (dataread.HasRows)
        {
            lblstudnum.Text = dataread[0].ToString();
            lblcourse.Text = dataread[1].ToString();
            lblfname.Text = dataread[2].ToString();
            lbllname.Text = dataread[3].ToString();
            byte[] images = (byte[])dataread[4];

            if(images==null)
            {
                pictureBox1.Image = null;
            }
            else
            {
                MemoryStream mstreem = new MemoryStream(images);
                pictureBox1.Image = Image.FromStream(mstreem);
            }
        }
        else
        {
            MessageBox.Show("this data not available");
        }
    }

The error line is the

pictureBox1.Image = Image.FromStream(mstreem);

You should use parameterized queries instead of concatenation in the `WHERE` clause. — Tim Biegeleisen, Oct 04 '16 at 08:08
Which tutorial suggests using string concatenation to build sql queries? Use parameterized queries. — Tim Schmelter, Oct 04 '16 at 08:09
i found that code in this tutorial https://www.youtube.com/watch?v=d7klnhcFBEg — andrew faz, Oct 04 '16 at 08:09
I hope none of your students enters `0'; DROP TABLE table3; --` — nvoigt, Oct 04 '16 at 08:10
@andrewfaz That is a terrible tutorial then. Read up on [What is SQL injection](http://stackoverflow.com/questions/601300/what-is-sql-injection) — RB., Oct 04 '16 at 08:11
@andrewfaz: then forget that "tutorial" and start with [MSDN](https://msdn.microsoft.com/en-us/library/ms254937(v=vs.110).aspx), especially the section [Commands and Parameters](https://msdn.microsoft.com/en-us/library/ms254953(v=vs.110).aspx). — Tim Schmelter, Oct 04 '16 at 08:12
wait but the error lies in pictureBox1.Image = Image.FromStream(mstreem); i will not edit this line? — andrew faz, Oct 04 '16 at 08:14
plus all comments above; you should dispose your disposeable objects for a qualified, individual and good 'Tutorial'. — Cihan Uygun, Oct 04 '16 at 08:16
@andrewfaz To put the other comments in perspective - imagine you're trying to paint the wall using a hammer and you're worried you're not holding the hammer correctly. Everyone else asks you kindly to read up on using a brush. Then and only then - if you're still having problems with your way of holding it, those can be addressed. — decPL, Oct 04 '16 at 08:20
I do get that everyone here is trying to get the OP to change his absolutely dream-for-hackers code, but could he at least post the error that he is getting as opposed to redirecting his question to his SQL code, maybe the tutorial introduces the concepts and later explains what is dangerous about it? — mahlatse, Oct 04 '16 at 08:26

Gaurav P · Answer 1 · 2016-10-04T09:55:04.760

Better to use parametric query and column name instead of using [0],[1] etc.. The Memory Stream is used by Data reader.So you shall use as below, provided a valid Image is saved in database

    var con = new SqlConnection("the connection string to database");
    con.Open();

    SqlCommand cmd = new SqlCommand(@"sql query",con);
    byte[] images = null;
    using (SqlDataReader dataread = cmd.ExecuteReader())
    {
        if (dataread.Read())
        {
            //lblstudnum.Text = dataread[0].ToString();
            //lblcourse.Text = dataread[1].ToString();
            //lblfname.Text = dataread[2].ToString();
            //lbllname.Text = dataread[3].ToString();
            images = (byte[])dataread["color_image"];// column name is recommended
        }
    }
    con.Close();
    if (images == null)
    {
        pictureBox1.Image = null;
    }
    else
    {
        MemoryStream mstreem = new MemoryStream(images);
        pictureBox1.Image = Image.FromStream(mstreem);
    }

i try these and it says "Connection property has not been initialized" — andrew faz, Oct 04 '16 at 09:41

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

0

Probably not a valid image. Add some debugging code to your program (or set up a watch) that will output the length of the memory stream and its first few bytes. Make sure the length is what you were expecting. Make sure the file prefix is there, if any, e.g. bitmap files have a two-letter alphanumeric prefix. Make sure it didn't get truncated. Make sure it is an allowed file format. The problem may be that your instructor's database has data in it while yours doesn't.

edited May 23 '17 at 11:48

Community

1
1

answered Oct 04 '16 at 08:34

John Wu

50,556
8
44
80

yeah maybe invalid image but i save in varbinary but now i replace it with image data type but it still the same error – andrew faz Oct 04 '16 at 09:49
I wasn't referring to the data type, I was referring to the data content. – John Wu Oct 04 '16 at 15:30
Perhaps as a troubleshooting measure you can alter your program to [save the image to a file](http://stackoverflow.com/questions/18766055/copy-memorystream-to-filestream-and-save-the-file), then try to open it using MS Paint. If you can't open it, you have an issue with the data. – John Wu Oct 04 '16 at 18:47

C# parameter is not valid SQL

2 Answers2