Hi everyone,
I'm having a very frustrating problem executing a stored procedure. I'll put
the code at the bottom.
When I build the SP and add all the parameters everything goes as expected.
However when I run it, the exception tells me that the parameter doesnt
exist for that SP.
Obviously it's founmd the stored procedure, but I am absolutely certain that
it does contain that parameter. It actually does the same with with all four
parmeters that are passed though.
It just swears blind that the parameter isnt in the SP. Its driving me nuts
I hope somone can help
Simon
The code is as follows:
public static bool insertSiteTestR ange(int siteID, int testID, string
minValue, string maxValue){
SqlCommand cmd;
cmd = new SqlCommand("ins ertTestRange");
SqlParameter siteIDParam = new SqlParameter("c entreID",
Convert.ToInt16 (siteID));
cmd.Parameters. Add(siteIDParam );
SqlParameter trialIDParam = new SqlParameter("t estID", testID);
cmd.Parameters. Add(trialIDPara m);
SqlParameter maxValParam = new SqlParameter("u pperBound", minValue);
cmd.Parameters. Add(maxValParam );
SqlParameter minValParam = new SqlParameter("l owerBound", maxValue);
cmd.Parameters. Add(minValParam );
if(!DataAccessP rovider.execute NonQueryTransac tion(cmd)){
return false;
}
// If we get here then we were successful
return true;
}
public static bool executeNonQuery Transaction(Sql Command cmd){
int rowsAffected = 0;
SqlConnection con = new SqlConnection(c onnectionString );
SqlTransaction trans;
// We can't put this in a try block because if con.open fails, trans wont
be assigned to and we'll
// get an unassigned variable. Wont compile
con.Open();
trans = con.BeginTransa ction();
try{
cmd.Connection = con;
cmd.CommandType = CommandType.Sto redProcedure;
cmd.Transaction = trans;
rowsAffected = cmd.ExecuteNonQ uery();
if(rowsAffected == 0){
// updateSiteDetai ls: If no rows are affected, that likely means that
someone has deleted the centre
// whilst the user was viewing the updateSite page
if(cmd.CommandT ext.Equals("upd ateSiteDetails" )){
throw new Exception("Exce ption thrown in
DataAccessProvi der.executeNonQ ueryTransaction (SqlCommand cmd) whilst update
centre details. The centre specified was not found. " +
"If no rows are affected, it could mean that someone has deleted the
centre whilst the user was viewing the updateSite page");
}
else{
return false;
}
}
else{
trans.Commit();
return true;
}
}
catch(Exception e){
trans.Rollback( );
ExceptionManage r.Publish(new Exception("Exce ption detected whilst
executing DataAccessProvi der.executeNonQ ueryTransaction (SqlCommand cmd)",
e));
return false;
}
finally{
con.Close();
}
}
I'm having a very frustrating problem executing a stored procedure. I'll put
the code at the bottom.
When I build the SP and add all the parameters everything goes as expected.
However when I run it, the exception tells me that the parameter doesnt
exist for that SP.
Obviously it's founmd the stored procedure, but I am absolutely certain that
it does contain that parameter. It actually does the same with with all four
parmeters that are passed though.
It just swears blind that the parameter isnt in the SP. Its driving me nuts
I hope somone can help
Simon
The code is as follows:
public static bool insertSiteTestR ange(int siteID, int testID, string
minValue, string maxValue){
SqlCommand cmd;
cmd = new SqlCommand("ins ertTestRange");
SqlParameter siteIDParam = new SqlParameter("c entreID",
Convert.ToInt16 (siteID));
cmd.Parameters. Add(siteIDParam );
SqlParameter trialIDParam = new SqlParameter("t estID", testID);
cmd.Parameters. Add(trialIDPara m);
SqlParameter maxValParam = new SqlParameter("u pperBound", minValue);
cmd.Parameters. Add(maxValParam );
SqlParameter minValParam = new SqlParameter("l owerBound", maxValue);
cmd.Parameters. Add(minValParam );
if(!DataAccessP rovider.execute NonQueryTransac tion(cmd)){
return false;
}
// If we get here then we were successful
return true;
}
public static bool executeNonQuery Transaction(Sql Command cmd){
int rowsAffected = 0;
SqlConnection con = new SqlConnection(c onnectionString );
SqlTransaction trans;
// We can't put this in a try block because if con.open fails, trans wont
be assigned to and we'll
// get an unassigned variable. Wont compile
con.Open();
trans = con.BeginTransa ction();
try{
cmd.Connection = con;
cmd.CommandType = CommandType.Sto redProcedure;
cmd.Transaction = trans;
rowsAffected = cmd.ExecuteNonQ uery();
if(rowsAffected == 0){
// updateSiteDetai ls: If no rows are affected, that likely means that
someone has deleted the centre
// whilst the user was viewing the updateSite page
if(cmd.CommandT ext.Equals("upd ateSiteDetails" )){
throw new Exception("Exce ption thrown in
DataAccessProvi der.executeNonQ ueryTransaction (SqlCommand cmd) whilst update
centre details. The centre specified was not found. " +
"If no rows are affected, it could mean that someone has deleted the
centre whilst the user was viewing the updateSite page");
}
else{
return false;
}
}
else{
trans.Commit();
return true;
}
}
catch(Exception e){
trans.Rollback( );
ExceptionManage r.Publish(new Exception("Exce ption detected whilst
executing DataAccessProvi der.executeNonQ ueryTransaction (SqlCommand cmd)",
e));
return false;
}
finally{
con.Close();
}
}
Comment