SQL insert and Update

hi everyone, when i want to insert sql some value i have a getting “executenonquery connection property has not been initialized” error. but i dont know why ?

everything is clearly code is below

anyone have a any idea for this trouble ?

for (int i = 0; i < this._blocks.Count; i++)
{
if (this._blocks[i].Visible && !string.IsNullOrEmpty(this._blocks[i].Year))
{
string constr = ConfigurationManager.AppSettings["ConnInfo"];
using (SqlConnection con = new SqlConnection(constr))
{
string sql = string.Format(@"IF EXISTS (SELECT * FROM INDUS_OVER_STAT WHERE YEARS = {0}) UPDATE INDUS_OVER_STAT
SET
VAL_1 = {1},
VAL_2 = {2},
VAL_3 = {3},
TARGET_SID = '{4}'
WHERE YEARS = {0}
ELSE
INSERT INDUS_OVER_STAT(TARGET_SID, YEARS,VAL_1,VAL_2,VAL_3)
VALUES ('{4}', {0},{1},{2},{3})", this._blocks[i].Year, this._blocks[i].MS,this._blocks[i].BNU,this._blocks[i].Emp,this._groupsSId);
using (SqlCommand cmd = new SqlCommand(sql))
{
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
}

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Praveen Sreeram10y ago
Accepted answer
Hi ,
You haven't assigned the SQLConnection with the SQLCommand and so it is throwing an error.
Please pass your connection object to the SQL Command constructor.
Replace the following line of code
using(SqlCommandcmd=newSqlCommand(sql))
with
using(SqlCommandcmd=newSqlCommand(sql,con))
It will resolve your problem... BUT.. it is not suggested to make database trips in a Forloop. It will be too costly and your application will run into performance issues.
It's better to use Table Valued Paramters in such cases
Hope it helps
Thanks,
Prawin
+1
Vasanth Natarajan10y ago
change your SQLCommand statement. it have two parameter first Command and then Connection string.
SqlCommand cmd = new SqlCommand(sql,con)
0
Praveen Sreeram10y ago
You are welcome :-) and glad that your problem is resolved!
+1
Ekrem Tapan10y ago
Thanks for your advice ;)
0