user1056466 user1056466 - 2 months ago 14
SQL Question

Visual Studio 2012 Update query

I am trying to use this

Update
command but it isn't working properly. It is working when I run it in SQL Server 2008 but not in Visual Studio 2012

SqlCommand cmd= new SqlCommand("UPDATE dwh_staging_table SET pestpopulation1 = @avg_pest1 WHERE DistrictName = @DistrictName and TownName = @TownName And VarietyOfCrop = @V_Crop And pestpopulation1 = @pest", con1);
cmd.Parameters.AddWithValue("@avg_pest1",16);
cmd.Parameters.AddWithValue("@DistrictName", "Multan");
cmd.Parameters.AddWithValue("@TownName","Ambala");
cmd.Parameters.AddWithValue("@V_Crop","NIAB 999");
cmd.Parameters.AddWithValue("@pest",0);

adapter.UpdateCommand = cmd;

Answer

You are assigning Sql Update Command to SelectCommand of SqlDataAdapter.

Try this:

adapter.UpdateCommand = new SqlCommand("your update command");

Note: your update command is open to SQL injection attacks.
Use PARAMETERISED QUERIES to avoid them.

Example of PARAMETERISED QUERIES :

SqlCommand cmd=new SqlCommand("update products set price=@price where id=@id");

cmd.Parameters.AddWithValue("@price",1234);
cmd.Parameters.AddWithValue("@id",12);

adapter.UpdateCommand=cmd;

Complete Solution: (using PARAMETERISED QUERIES)

SqlCommand cmd = new SqlCommand("UPDATE dwh_staging_table SET pestpopulation1=@pestpopulation1 WHERE DistrictName=@DistrictName and TownName=@TownName And VarietyOfCrop=@VarietyOfCrop And pestpopulation1=@pestpopulation11", con1);

cmd.Parameters.AddWithValue("@pestpopulation1", row[3]);
cmd.Parameters.AddWithValue("@DistrictName", row[0].ToString());
cmd.Parameters.AddWithValue("@TownName", row[1].ToString());
cmd.Parameters.AddWithValue("@VarietyOfCrop", row[2].ToString());
cmd.Parameters.AddWithValue("@pestpopulation11", 0);

adapter.UpdateCommand = cmd;
Comments