CODEWITHSUNDEEP
c#sql-serverstored-proceduresexecutenonquery
Johnathan
Johnathan

Reputation: 909

C# - ExecuteNonQuery isn't executing stored procedure

I am trying to run a stored procedure with parameters as shown below:

Stored procedure:

CREATE PROCEDURE [dbo].[spPurchase]
     @commodityName VARCHAR(10),
     @startdate DATE,  
     @enddate DATE,
     @tonnes FLOAT,
     @lots INT,
     @value FLOAT,
     @ccy VARCHAR(3),
     @clientref VARCHAR(50),
     @clientid INT,
     @userid INT
AS
BEGIN
    ........
    ........
    ........
END

C# code to execute this stored procedure:

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["InventoryConnectionString"].ConnectionString);

SqlCommand cmd = new SqlCommand("spPurchase", conn);

cmd.Parameters.Add("@commodityName", SqlDbType.VarChar, 10).Value = ddlMetals.SelectedValue;
cmd.Parameters.Add("@startdate", SqlDbType.Date).Value = startDate.Value;
cmd.Parameters.Add("@enddate", SqlDbType.Date).Value = endDate.Value;
cmd.Parameters.Add("@tonnes", SqlDbType.Float).Value = Convert.ToDouble(tbQuantity.Value);
cmd.Parameters.Add("@lots", SqlDbType.Int).Value = DBNull.Value;
cmd.Parameters.Add("@value", SqlDbType.Float).Value = DBNull.Value;
cmd.Parameters.Add("@ccy", SqlDbType.VarChar, 3).Value = DBNull.Value;
cmd.Parameters.Add("@clientref", SqlDbType.VarChar, 50).Value = "EigerTest";
cmd.Parameters.Add("@clientid", SqlDbType.Int).Value = Convert.ToInt32(Utils.LoggedInUserId);
cmd.Parameters.Add("@userid", SqlDbType.Int).Value = Convert.ToInt32(Utils.LoggedInUsersAccountId);

cmd.CommandType = CommandType.StoredProcedure;

try
{
    conn.Open();
    cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
    throw new Exception(ex.Message, ex);
}
finally
{
    cmd.Connection.Close();
}

The stored procedure runs fine from SQL Server and the C# code is successfully connecting to the database. However, it is not executing the stored procedure and is not producing any exceptions or errors.

Looking at recent queries in my database, it looks as though my C# code may be just trying to CREATE the stored procedure rather than executing it.

Can anyone see what I am doing wrong?

Upvotes: 1

Views: 1971

Answers (1)

susodapop
susodapop

Reputation: 442

When debugging a similar issue, I found the problem was my stored procedure. It didn't raise any SQL errors by default. So I added a RAISEERROR statement, but even that didn't cause an issue. I had to change my RAISEERROR to use a severity of >10 before it would surface into C#.

After that it was straightforward to debug. There was a type mismatch in the parameters I passed in C# which caused an IF statement to fail in the procedure. Without the RAISEERROR it was really perplexing to debug.

Upvotes: 0

Related Questions