Problem with SQL Parameters

[Simon Harvey]

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 insertSiteTestRange(int siteID, int testID, string
minValue, string maxValue){
SqlCommand cmd;

cmd = new SqlCommand("insertTestRange");

SqlParameter siteIDParam = new SqlParameter("centreID",
Convert.ToInt16(siteID));
cmd.Parameters.Add(siteIDParam);

SqlParameter trialIDParam = new SqlParameter("testID", testID);
cmd.Parameters.Add(trialIDParam);

SqlParameter maxValParam = new SqlParameter("upperBound", minValue);
cmd.Parameters.Add(maxValParam);

SqlParameter minValParam = new SqlParameter("lowerBound", maxValue);
cmd.Parameters.Add(minValParam);

if(!DataAccessProvider.executeNonQueryTransaction( cmd)){
return false;
}

// If we get here then we were successful
return true;
}

public static bool executeNonQueryTransaction(SqlCommand cmd){
int rowsAffected = 0;
SqlConnection con = new SqlConnection(connectionString);
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.BeginTransaction();

try{
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Transaction = trans;

rowsAffected = cmd.ExecuteNonQuery();

if(rowsAffected == 0){
// updateSiteDetails: If no rows are affected, that likely means that
someone has deleted the centre
// whilst the user was viewing the updateSite page
if(cmd.CommandText.Equals("updateSiteDetails")){
throw new Exception("Exception thrown in
DataAccessProvider.executeNonQueryTransaction(SqlC ommand 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();
ExceptionManager.Publish(new Exception("Exception detected whilst
executing DataAccessProvider.executeNonQueryTransaction(SqlC ommand cmd)",
e));
return false;
}

finally{
con.Close();
}

}

[Peter Rilling]

Stored procedure parameters all have the "@" in the front. This symbol most
also be included when referencing the parameter by name in your code.

"Simon Harvey" <si**********@the-web-works.co.uk> wrote in message
news:%2***************@tk2msftngp13.phx.gbl...

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 insertSiteTestRange(int siteID, int testID, string
minValue, string maxValue){
SqlCommand cmd;

cmd = new SqlCommand("insertTestRange");

SqlParameter siteIDParam = new SqlParameter("centreID",
Convert.ToInt16(siteID));
cmd.Parameters.Add(siteIDParam);

SqlParameter trialIDParam = new SqlParameter("testID", testID);
cmd.Parameters.Add(trialIDParam);

SqlParameter maxValParam = new SqlParameter("upperBound", minValue);
cmd.Parameters.Add(maxValParam);

SqlParameter minValParam = new SqlParameter("lowerBound", maxValue);
cmd.Parameters.Add(minValParam);

if(!DataAccessProvider.executeNonQueryTransaction( cmd)){
return false;
}

// If we get here then we were successful
return true;
}

public static bool executeNonQueryTransaction(SqlCommand cmd){
int rowsAffected = 0;
SqlConnection con = new SqlConnection(connectionString);
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.BeginTransaction();

try{
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Transaction = trans;

rowsAffected = cmd.ExecuteNonQuery();

if(rowsAffected == 0){
// updateSiteDetails: If no rows are affected, that likely means that
someone has deleted the centre
// whilst the user was viewing the updateSite page
if(cmd.CommandText.Equals("updateSiteDetails")){
throw new Exception("Exception thrown in
DataAccessProvider.executeNonQueryTransaction(SqlC ommand 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();
ExceptionManager.Publish(new Exception("Exception detected whilst
executing DataAccessProvider.executeNonQueryTransaction(SqlC ommand cmd)",
e));
return false;
}

finally{
con.Close();
}

}

[Simon Harvey]

I am such a dick

Thank you. Yoiu are a Gem amongst sh*t as we say where I come from

Take care

Simon

[Miha Markic [MVP C#]]

Hi Simon,

I see that you're already solved the problem thanks to Peter.
As an another approach to solve the problem you might:
use server explorer, drag & drop the sp on the form and check the code
windows forms designer has generated.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

"Simon Harvey" <si**********@the-web-works.co.uk> wrote in message
news:%2***************@tk2msftngp13.phx.gbl...

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 insertSiteTestRange(int siteID, int testID, string
minValue, string maxValue){
SqlCommand cmd;

cmd = new SqlCommand("insertTestRange");

SqlParameter siteIDParam = new SqlParameter("centreID",
Convert.ToInt16(siteID));
cmd.Parameters.Add(siteIDParam);

SqlParameter trialIDParam = new SqlParameter("testID", testID);
cmd.Parameters.Add(trialIDParam);

SqlParameter maxValParam = new SqlParameter("upperBound", minValue);
cmd.Parameters.Add(maxValParam);

SqlParameter minValParam = new SqlParameter("lowerBound", maxValue);
cmd.Parameters.Add(minValParam);

if(!DataAccessProvider.executeNonQueryTransaction( cmd)){
return false;
}

// If we get here then we were successful
return true;
}

public static bool executeNonQueryTransaction(SqlCommand cmd){
int rowsAffected = 0;
SqlConnection con = new SqlConnection(connectionString);
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.BeginTransaction();

try{
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Transaction = trans;

rowsAffected = cmd.ExecuteNonQuery();

if(rowsAffected == 0){
// updateSiteDetails: If no rows are affected, that likely means that
someone has deleted the centre
// whilst the user was viewing the updateSite page
if(cmd.CommandText.Equals("updateSiteDetails")){
throw new Exception("Exception thrown in
DataAccessProvider.executeNonQueryTransaction(SqlC ommand 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();
ExceptionManager.Publish(new Exception("Exception detected whilst
executing DataAccessProvider.executeNonQueryTransaction(SqlC ommand cmd)",
e));
return false;
}

finally{
con.Close();
}

}