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
– Tim Biegeleisen, Commented Oct 4, 2016 at 8:08
Which tutorial suggests using string concatenation to build sql queries? Use parameterized queries. — Tim Schmelter
– Tim Schmelter, Commented Oct 4, 2016 at 8:09
I hope none of your students enters 0'; DROP TABLE table3; -- — nvoigt
– nvoigt, Commented Oct 4, 2016 at 8:10
@andrewfaz That is a terrible tutorial then. Read up on What is SQL injection — RB.
– RB., Commented Oct 4, 2016 at 8:11
@andrewfaz: then forget that "tutorial" and start with MSDN, especially the section Commands and Parameters. — Tim Schmelter
– Tim Schmelter, Commented Oct 4, 2016 at 8:12

Gaurav P · Accepted Answer · 2016-10-04 09:55:04Z

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"

Community · Accepted Answer · 2017-05-23 11:48:30Z

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, 2017 at 11:48

CommunityBot

11 silver badge

answered Oct 4, 2016 at 8:34

John Wu

52.5k8 gold badges50 silver badges92 bronze badges

3 Comments

andrew faz Over a year ago

yeah maybe invalid image but i save in varbinary but now i replace it with image data type but it still the same error

John Wu Over a year ago

I wasn't referring to the data type, I was referring to the data content.

John Wu Over a year ago

Perhaps as a troubleshooting measure you can alter your program to save the image to a file, then try to open it using MS Paint. If you can't open it, you have an issue with the data.

Collectives™ on Stack Overflow

C# parameter is not valid SQL

2 Answers 2

2 Comments

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Linked

Related