0

I am trying to perform an UPDATE Stored Procedure using .NET. Here is the C# part:

     public int spUpdate_Agent(int agentNum, string firstName, string lastNAme, string city, string phone
   , string eMail)
{
    string cStr = WebConfigurationManager.ConnectionStrings["bgroup33_prodConnectionString"].ConnectionString;
    using (SqlConnection con = new SqlConnection(cStr))
    {
        SqlCommand cmd = new SqlCommand("spUpdate_Agent", con);
        cmd.CommandType = CommandType.StoredProcedure;

        SqlParameter paramAgentNum = new SqlParameter("@agentNum", agentNum);
        SqlParameter paramAgentFirst = new SqlParameter("@FirstName", firstName);
        SqlParameter paramAgentLast = new SqlParameter("@lasttName", lastNAme);
        SqlParameter paramAgentCity = new SqlParameter("@city", city);
        SqlParameter paramAgentPhone = new SqlParameter("@phone", phone);
        SqlParameter paramAgentEmail = new SqlParameter("@eMail", eMail);

        cmd.Parameters.Add(paramAgentNum);
        cmd.Parameters.Add(paramAgentFirst);
        cmd.Parameters.Add(paramAgentLast);
        cmd.Parameters.Add(paramAgentCity);
        cmd.Parameters.Add(paramAgentPhone);
        cmd.Parameters.Add(paramAgentEmail);

        try
        {
            con.Open();
            int returenCode = (int)cmd.ExecuteScalar();
            return returenCode;
        }
        catch (Exception ex)
        {
            return -1;

        }
        finally
        {
            con.Close();
        }
    }
}

And this is the Stored Proc:

alter procedure [dbo].[spUpdate_Agent]
@agentNum int,
@firstName nvarchar(50),
@lasttName nvarchar(50),
@city nvarchar(50),
@phone nvarchar(50),
@eMail nvarchar(50)

    as
    Begin 

        update [dbo].[Agent]
        set first_name = @firstName,last_name = @lasttName, city = @city,phone = @phone,eMail = @eMail
        where agentNum = @agentNum
        return 1
    end

So I get this error as mentioned above, all the parameters are the right type. So what is my problem?

Rom Rosenblat
  • 1
  • 1
    Which **specific** line is raising the error? What does the debugger tell you when you step through the code to that point? – Ken White Apr 05 '16 at 16:53
  • 1
    On what line is .Net telling you there is an object reference that isn't set to an object? My guess FWIW is that your connection isn't being properly instantiated, probably because the connection string you've listed isn't being picked up, but we can give more help with an actual error line. – eftpotrm Apr 05 '16 at 16:53
  • You don't need that finally block. The using statement will close the connection for you. – rgvassar Apr 05 '16 at 16:54
  • Rename `@FirstName` to `@firstName` while adding parameter – Jaydip Jadhav Apr 05 '16 at 16:57
  • Sorry, the line is the actual execute int returenCode = (int)cmd.ExecuteScalar(); – Rom Rosenblat Apr 05 '16 at 16:57
  • I dispute the duplicate characterization. This is a more specific example of a very large category. It is probably happening because the input parameter agentNum is getting passed in as 0. This makes the SqlParameter constructor fail, and then when you try to add this parameter to your SqlCommand object (cmd) it results in a null exception. Try adding a cast to the paramAgentNum assignment like this: SqlParameter paramAgentNum = new SqlParameter("@agentNum", (Object)agentNum); I believe your null reference exception will go away. – quest4truth Apr 05 '16 at 18:42

0 Answers0