I Want To Update A Photo Using Sql Update Query And I Am Usgin Fileupload Control

Question

0.00/5 (No votes)

See more:

My on button click code is as below. photo is not updating

C#

 protected void updatecompany_Click(object sender, EventArgs e)
{
            SqlConnection con = new SqlConnection("xyz...");
            con.Open();
string filepath = Server.MapPath("~/CompanyLogo/" + strFileName);
            string path = "~/CompanyLogo/" + strFileName;

            string str = "UPDATE Table1 set CompanyLogo='" + path +"'   where CompanyID=" + Convert.ToInt16(Request.QueryString["CompanyID"].ToString());

 SqlCommand cmd = new SqlCommand(str, con);
            cmd.ExecuteNonQuery();
            con.Close();
        }

Posted 7-Mar-14 5:19am

jayraj86

Add a Solution

Comments

ZurdoDev 7-Mar-14 11:25am

You are leaving yourself open to sql injection but otherwise it looks like the code should work. The easy thing to do is debug it and see what is happening.

The code you have shown of course does not include the uploading of the photo.

1 solution

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)

OriginalGriff · Accepted Answer · 2014-03-07T05:25:00

Solution 1

First off, stop accessing SQL like that! Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.

The next thing to do is check your QueryString return value: if it is wrong, or empty, then the SQL will not find any matching records and will not update anything.

I'd also check the return value from ExecuteNonQuery - if it isn't one, then it didn't do what you wanted and you need to try to report or log the problem with as many details as you can.

Posted 7-Mar-14 5:25am

OriginalGriff

Comments

jayraj86 7-Mar-14 11:35am

I'm not getting you.. how to make changes i this code to update ?

OriginalGriff 7-Mar-14 11:40am

You know how to do parameterized queries, I assume?

jayraj86 7-Mar-14 12:00pm

No. I'm just a beginner

OriginalGriff 7-Mar-14 12:17pm

You mean you are doing a website, and you are concatenating strings throughout?
You have been told about SQL Injection, haven't you?
If not, then google "Bobby Tables" and don't assume it's a joke...anyone could destroy your database from the other side of the world.
Forget your current problem, and fix that fast!

jayraj86 9-Mar-14 5:09am

Thanks, man! I just changed the code to parameterized. now how I can use it to update a photo ?

OriginalGriff 9-Mar-14 5:22am

So, what is your current code?

jayraj86 9-Mar-14 5:30am

This is insert code

SqlConnection con = new SqlConnection("Data Source=jayraj-pc\\sqlexpress;Initial Catalog=Internship;Integrated Security=True;Pooling=False");

string sql = "INSERT INTO Companies (CompanyLogo) VALUES (@Val1)";

try
{

con.Open();

SqlCommand cmd = new SqlCommand(sql, con);

string filepath = Server.MapPath("~/CompanyLogo/" + strFileName);
string path = "~/CompanyLogo/" + strFileName;

cmd.Parameters.AddWithValue("@Val1", path);

cmd.CommandType = CommandType.Text;

cmd.ExecuteNonQuery();

}

catch (System.Data.SqlClient.SqlException ex)
{

string msg = "Insert Error:";

msg += ex.Message;

throw new Exception(msg);

}

finally
{

con.Close();

}

}
}
}

OriginalGriff 9-Mar-14 5:44am

Look like it should work - and the update code?

jayraj86 9-Mar-14 5:46am

here it is

protected void updatecompany_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection("xyz...");
con.Open();
string filepath = Server.MapPath("~/CompanyLogo/" + strFileName);
string path = "~/CompanyLogo/" + strFileName;
string str = "UPDATE Companies set CompanyLogo='" + path +"' where CompanyID=" + Convert.ToInt16(Request.QueryString["CompanyID"].ToString());
SqlCommand cmd = new SqlCommand(str, con);
cmd.ExecuteNonQuery();
con.Close();
}

OriginalGriff 9-Mar-14 5:50am

So...you have converted everything else, but not that?
Parameterize that, and see what you get then.
If there is still a problem, show the parameterized code, and explain what does happen when you execute it (and how you know that is what happens, that can be as important)

jayraj86 9-Mar-14 6:38am

here is update code. everything other than photo gets updated

protected void updatecompany_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection("Data Source=jayraj-pc\\sqlexpress;Initial Catalog=Internship;Integrated Security=True;Pooling=False");

string sql = "UPDATE Companies SET FirstName=@val1, LastName=@val2, UserName=@val3, EmailID=@val4, Password=@val5, ConfirmPassword=@val6, CompanyName=@val7, Designation=@val8, ContactNo=@val9, Country=@val10, State=@val11, City=@val12, CompanyCategory=@val13, NoOfEmployees=@val14, Website=@val15, Aboutcompany=@val16, Address=@val17, CompanyLogo=@val18 where CompanyID=" + Convert.ToInt16(Request.QueryString["CompanyID"].ToString());

try
{

con.Open();

SqlCommand cmd = new SqlCommand(sql, con);

string filepath = Server.MapPath("~/CompanyLogo/" + strFileName);
string path = "~/CompanyLogo/" + strFileName;

cmd.Parameters.AddWithValue("@Val1", firstname.Text);
cmd.Parameters.AddWithValue("@Val2", lastname.Text);
cmd.Parameters.AddWithValue("@Val3", cusername.Text);
cmd.Parameters.AddWithValue("@Val4", email.Text);
cmd.Parameters.AddWithValue("@Val5", password.Text);
cmd.Parameters.AddWithValue("@Val6", cnfpass.Text);
cmd.Parameters.AddWithValue("@Val7", companyname.Text);
cmd.Parameters.AddWithValue("@Val8", designation.Text);
cmd.Parameters.AddWithValue("@Val9", mobileno.Text);
cmd.Parameters.AddWithValue("@Val10", countrylist.SelectedItem.Text);
cmd.Parameters.AddWithValue("@Val11", statelist.SelectedItem.Text);
cmd.Parameters.AddWithValue("@Val12", city.SelectedItem.Text);
cmd.Parameters.AddWithValue("@Val13", companysector.SelectedItem.Text);
cmd.Parameters.AddWithValue("@Val14", DropDownList5.SelectedItem.Text);
cmd.Parameters.AddWithValue("@Val15", website.Text);
cmd.Parameters.AddWithValue("@Val16", aboutcompany.Text);
cmd.Parameters.AddWithValue("@Val17", address.Text);
cmd.Parameters.AddWithValue("@Val18", path);

cmd.CommandType = CommandType.Text;

cmd.ExecuteNonQuery();

}

catch (System.Data.SqlClient.SqlException ex)
{

string msg = "Insert Error:";

msg += ex.Message;

throw new Exception(msg);

}

finally
{

con.Close();

}

}

OriginalGriff 9-Mar-14 7:02am

Firstly, pass the ID as a parameter as well - it's not a problem in this case because you convert it to an int, but it's good practice.
So what does happen when you run that code? Do you get an exception? how many rows are affected? What data gets updated? If it is "everything except the photo" how do you know? What tells you the photo is not updated? What was it before the update? What is it after?