1

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Johnathan
  • 879
  • 3
  • 12
  • 22
  • 1
    What is the stored proc doing? – DoctorMick May 09 '16 at 15:57
  • Just updating various tables. – Johnathan May 09 '16 at 15:59
  • `lots` is `float` in your code but `int` in db; `value` is `int` in your code but `float` in db; does it cause an issue? – techspider May 09 '16 at 15:59
  • 3
    Just open Sql profiler and see what happens. – Giorgi Nakeuri May 09 '16 at 16:00
  • When you say the SP is not executing and no errors, .Net is sending the parameters to SP but inside queries didn't do something; Did you try using `SQL Server Profiler`. You can see the SP with the actual parameters passed to SQL Server. Take that out of profiler and try to debug from SSMS – techspider May 09 '16 at 16:01
  • Set a breakpoint inside your Catch block and see what happens. You might be missing the exception altogether. – Xavier J May 09 '16 at 16:12
  • Double-check your connection string and make sure you're pointing at the database you think you are? I would think an error would surface if it was something like a parameter mismatch. – Chris Steele May 09 '16 at 16:13
  • `ExecuteNonQuery()` isn't going to return any results, only the affected rows. Have you examined the number of rows affected? Also, can you post the sproc? – Cam Bruce May 09 '16 at 16:14
  • Have you tried enumerating the results of the SP, if there are any? – Martin Costello May 09 '16 at 16:15
  • I don't have permissions to run a Profiler unfortunately. I know it is connecting to my database as I can see the code is trying to create the Stored Procedure. It's just not executing it. `cmd.ExecuteNonQuery` returns 0 as well. – Johnathan May 09 '16 at 16:18
  • @Johnathan, how do you know that it is trying to create? – techspider May 09 '16 at 16:19
  • @techspider Because I have a query which I run to view recently run queries. Every time I click my c# button, I can see the query `CREATE PROCEDURE....` – Johnathan May 09 '16 at 16:21

1 Answers1

0

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.

susodapop
  • 402
  • 4
  • 10