CODEWITHSUNDEEP
c#asp.net
Raghu
Raghu

Reputation: 41

Getting error on inserting image into database

I'm getting an error on inserting image into database. Given below is the code which I'm trying to inserting an image but couldn't be able to do it correctly.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;

public partial class welcome : System.Web.UI.Page
{
    string fname;
    SqlConnection con = new SqlConnection();
    string emailname;

    protected void Page_Load(object sender, EventArgs e)
    {
        if ((Session["Username"] == null) && (Session["useraddress"] == null))
        {
            Response.Redirect("Registration.aspx");
        }
        else
        {
            emailname = Session["useremail"].ToString();
            Label2.Text = Session["Username"].ToString();
            Label3.Text = Session["useraddress"].ToString();
            welcomelbl.Text = Session["Username"].ToString();
            addlbl.Text = Session["useraddress"].ToString();
        }
    }

    protected void Button1_Click1(object sender, EventArgs e)
    {
        Session.Clear();
        Response.Redirect("login.aspx");
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        imageupload();
    }

The function which I'm using to upload image is given below.

    private void imageupload()
    {
            int imglength = FileUpload2.PostedFile.ContentLength;
            byte[] bytearray = new byte[imglength];
            fname = FileUpload2.PostedFile.FileName;
            TextBox1.Text = fname;
            HttpPostedFile image = FileUpload2.PostedFile;
            image.InputStream.Read(bytearray, 0, imglength);
            SqlConnection con = Connection.conn();
            con.Open();
            SqlCommand cmd = new SqlCommand("insert into imgtbl (imgname,img,useraddress) values(@name ,@image '" + emailname + "')", con);
            cmd.Parameters.AddWithValue("@name", SqlDbType.VarChar).Value = TextBox1.Text;
            cmd.Parameters.AddWithValue("@image", SqlDbType.Image).Value = bytearray;
            cmd.ExecuteNonQuery();
            con.Close();

    }
}

Upvotes: 0

Views: 1166

Answers (3)

Lucian
Lucian

Reputation: 4001

When passing data to your SQL server, you should always try to use parameters instead of manually building SQL commands with concatenation.

Instead of:

SqlCommand cmd = new SqlCommand("insert into imgtbl (imgname,img,useraddress) values(@name ,@image '" + emailname + "')", con);

you should parametrize the email name also, like you did with @name and @image:

string sqlCommand = "INSERT INTO imgtbl (imgname, img, useraddress) VALUES (@name, @image, @emailName)";
SqlCommand cmd = new SqlCommand(sqlCommand, con);
cmd.Parameters.AddWithValue("@name", TextBox1.Text);
cmd.Parameters.AddWithValue("@image", bytearray);
cmd.Parameters.AddWithValue("@emailname", emailname);

This will prevent any malformed sql commands to be sent to the database, but most importantly, it will make your calls much more safer. Otherwise you will be exposed to SQL injection attacks!

UPDATE: check out @ThorstenDittmar response, the missing comma is probably causing the syntax error

Upvotes: 0

Ajay
Ajay

Reputation: 6590

Please correct you query 

SqlCommand cmd = new SqlCommand("insert into imgtbl (imgname,img,useraddress) values(@name ,@image '" + emailname + "')", con);

it should be like this

SqlCommand cmd = new SqlCommand("INSERT INTO imgtbl (imgname, img, useraddress) VALUES (@name, @image, '" + emailname + "')", con);

You missed comma in your query.

Upvotes: 0

Thorsten Dittmar
Thorsten Dittmar

Reputation: 56707

The following is wrong (you're missing a comma between @image and the string that contains emailname):

SqlCommand cmd = new SqlCommand("insert into imgtbl (imgname,img,useraddress) values(@name ,@image '" + emailname + "')", con);

It should be:

SqlCommand cmd = new SqlCommand("insert into imgtbl (imgname,img,useraddress) values(@name ,@image, '" + emailname + "')", con); // Note the missing comma!

Also you started correctly by parameterizing your query, why not the email address? This is user input and thus parameterizing is really a must do to avoid SQL injection.

SqlCommand cmd = new SqlCommand("insert into imgtbl (imgname,img,useraddress) values(@name ,@image, @emailname)", con);
cmd.Parameters.AddWithValue("@name", SqlDbType.VarChar).Value = TextBox1.Text;
cmd.Parameters.AddWithValue("@image", SqlDbType.Image).Value = bytearray;
cmd.Parameters.AddWithValue("@emailname", SqlDbType.VarChar).Value = emailname;

Another thing: Do you want international users to use your site? Then you should really switch from VARCHAR to NVARCHAR to allow for unicode characters in names.

Upvotes: 1

Related Questions