I have made a code for image insertion to sql database.. but when i run that code it gives an error

Question

0.00/5 (No votes)

See more:

C#

protected void Button2_Click(object sender, EventArgs e)
    {

        SqlConnection conn = new SqlConnection();
        conn.ConnectionString = "Data Source=PROMOD-PC;Initial Catalog=travel_Directions;Integrated Security=True";

        String Strt_Address = TextBox1.Text;
        String End_Address = TextBox2.Text;

        String filePath = FileUpload1.PostedFile.FileName;
        String filename = Path.GetFileName(filePath);
        String ExtStr = Path.GetExtension(filename);
        String contenttype = String.Empty;

        switch (ExtStr)
        { 
            case ".png":
                contenttype = "image/png";
                break;

            case ".jpg":
                contenttype = "image/jpg";
                break;

            case ".gif":
                contenttype = "image/gif";
                break;
        }

        if (contenttype != string.Empty)
        {
        Stream Strmf = FileUpload1.PostedFile.InputStream;
        BinaryReader br = new BinaryReader(Strmf);
            Byte[] imgbytes = br.ReadBytes((Int32)Strmf.Length);
            
            conn.Open();

            String QueryStr = "UPDATE MapDataImage SET Image = @Image WHERE Source='" + TextBox1.Text + "';";
            SqlCommand scmd = new SqlCommand(QueryStr, conn);
            scmd.Parameters.Add("@Image", SqlDbType.VarBinary).Value = imgbytes;
            scmd.ExecuteNonQuery();


        }

guys it points to

scmd.ExecuteNonQuery(); and says

Incorrect syntax near 'Image'.

....................................................

i have values on database...
and my sql database consist..

Source Varchar(max)
Age int
Image Varbinary(max)

so there, i store values manually

eg = name john , age 20

i want to check weather user input john or not to the textbox...

if textbox value equals to john ... then only i can add images to Image Field..
// i need to compare the values between textbox and database.
.....

Think example as Facebook... i can only add my pics if i'm login with my account only know.

so kind of that...

C#

String QueryStr = "UPDATE MapDataImage SET Image = @Image WHERE Source='" + TextBox1.Text + "';";

how about this?

Posted 3-Jul-13 21:41pm

promod madushan

Updated 3-Jul-13 22:41pm

v8

Add a Solution

2 solutions

Solution 2

Look at your query:

C#

String QueryStr = "INSERT INTO MapDataImage VALUES Image WHERE @Source='" + TextBox1.Text + "';";
SqlCommand scmd = new SqlCommand(QueryStr);
scmd.Parameters.Add("@Image", SqlDbType.VarBinary).Value = imgbytes;
scmd.ExecuteNonQuery();

You are specifying the variable @Source but only providing a value for the variable @ImageProbably, what you wanted to say was:

C#

String QueryStr = "INSERT INTO MapDataImage VALUES (@Source, @Image)";
SqlCommand scmd = new SqlCommand(QueryStr);
scmd.Parameters.AddWithValue("@Image", imgbytes);
scmd.Parameters.AddWithValue("@Source", TextBox1.Text);
scmd.ExecuteNonQuery();

But it is a good idea to also provide the field names in your query to ensure they go in the right place...

SQL

INSERT INTO myTable (MyColumn1, MyColumn2) VALUES (@MC1, @MC2)

Posted 3-Jul-13 21:58pm

OriginalGriff

Comments

Thanks7872 4-Jul-13 4:03am

↑voted.I just forgot to point out @source and @image issue in my above answer.updating shortly.Suggested to have a look at this one.Great.

OriginalGriff 4-Jul-13 4:15am

I saw your answer, which was why I didn't mention the connection - you had that covered!

promod madushan 4-Jul-13 4:07am

thanks for the comment mate....

but i want to check if textbox value match with particular field and then only i can add image to the image field.

eg - if my name is promod and if sql database hold myname only i can upload my photo.

that's my logic...

if i'm wrong correct me....

thanks mate!!!!

promod madushan 4-Jul-13 4:11am

String QueryStr = "INSERT INTO MapDataImage VALUES @Image WHERE @Source='" + TextBox1.Text + "';";

now is it okay?

OriginalGriff 4-Jul-13 4:15am

No - INSERT queries do not have a WHERE clause, because INSERT *always* creates a new row.
Are you trying to change an existing image on a specific row? If not, what are you trying to do?

promod madushan 4-Jul-13 4:22am

i have values on database...
my sql database consist..

Source Varchar(max)
Age int
Image Varbinary(max)

so there, i store values manually

eg = name john , age 20

i want to check weather user input john or not to the textbox...

if textbox value equals to john ... then only i can add images to Image Field..
// i need to compare the values between textbox and database.
.....

Think example as Facebook... i can only add my pics if i'm login with my account only know.

so kind of that...

OriginalGriff 4-Jul-13 4:25am

You don't want an INSERT query then: you want an UPDATE query:
UPDATE MyTable SET Image=@Image WHERE Source=@Source
This changes only those rows where the Source matches the supplied parameter.

promod madushan 4-Jul-13 4:25am

I think then i need a update query... yeah?

promod madushan 4-Jul-13 4:28am

Thanks mate!!!!

promod madushan 4-Jul-13 4:40am

String QueryStr = "UPDATE MapDataImage SET Image = @Image WHERE Source='" + TextBox1.Text + "';";

now is it okay?

OriginalGriff 4-Jul-13 4:43am

NO! Please, never, ever concatenate strings to form an SQL command - it leaves you wide open to SQL Injection attacks which can damage or destroy your database, just by typing in a text box. Use a parameterized query, like you do for the image data!

promod madushan 4-Jul-13 4:43am

Thanks mate , Its Working

OriginalGriff 4-Jul-13 4:53am

You're welcome!

promod madushan 4-Jul-13 5:28am

String QueryStr = "UPDATE MapDataImage SET Image = @Image WHERE Source='" + TextBox1.Text + "';";
i'm using the same code.. and it's working.... so does it matter?

OriginalGriff 4-Jul-13 5:41am

Yes! Oh, yes, it does...
Try this:
1) Backup your database. Seriously - back it up, and make sure your backup works, and that you know how to restore it. Because the next step is going to destroy your database. Do not move to the next step until your DB is safe and secure and you can get it back.
2) Run your application with your query as it is, with the string concatenation.
3) Type into your textbox:
1';DROP TABLES MapDataImage;--
4) Take a deep breath and press the OK, or ENTER, or SAVE key.
5) Your database is now missing a table. And you can't undelete it.

That was an SQL Injection attack. Simple isn't it? If you do the same kind of thing throughout your application, then I suspect anyone can do it from the login screen as well...

Now, restore your database from the backup, and always use parametrized queries. Because your best mate will try that, just to see the look on your face...

promod madushan 4-Jul-13 5:50am

Thanks Mate!!!!

OriginalGriff 4-Jul-13 6:03am

You're welcome!

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Thanks7872 · Accepted Answer · 2013-07-03T21:52:00

Solution 1

Edit this line

C#

.....................
 Stream Strmf = FileUpload1.PostedFile.InputStream;
 BinaryReader br = new BinaryReader(Strmf);
 Byte[] imgbytes = br.ReadBytes((Int32)Strmf.Length);            
 conn.Open(); 
 String QueryStr = "INSERT INTO MapDataImage VALUES Image WHERE @Source='" + TextBox1.Text + "';";
 SqlCommand scmd = new SqlCommand(QueryStr,your connection);//Connection required

Regards..:laugh:

Posted 3-Jul-13 21:52pm

Thanks7872

Updated 3-Jul-13 21:54pm

v2

Comments

promod madushan 4-Jul-13 3:59am

thanks mate!!!

promod madushan 4-Jul-13 4:01am

guys it points to

scmd.ExecuteNonQuery(); and says

Incorrect syntax near 'Image'.

Thanks7872 4-Jul-13 4:02am

Refer to below answer by OriginalGriff.I forgot to mention it.