none
Assistance with getting an Update issue solved RRS feed

  • Question

  • Using ADO.Net 2.0, I have the follwing method to update the database:

      public static void Update( long id, string oldFirstName, string oldLastName, string oldEmail, string firstName, string lastName, string email, string phone, string cellPhone, string bestTimeToCall, bool emailed, bool called )
      {
       string    sql       = "Update [Contacts] set [FirstName] = @firstName, [LastName] = @lastName, [Email] = @email, [Phone] = @phone, [CellPhone] = @cellPhone, [BestTimeToCall] = @bestTimeToCall, [Emailed] = @emailed, [Called] = @called Where [Id] = @id and [FirstName] = @oldFirstName and [LastName] = @oldLastName and [Email] = @oldEmail";
       SqlConnection conn      = new SqlConnection( getConnection() );
       SqlCommand  cmd       = new SqlCommand( sql, conn );
       SqlParameter pId       = new SqlParameter( "@id",       id       );
       SqlParameter pOldFirstName  = new SqlParameter( "@oldFirstName",  oldFirstName  );
       SqlParameter pOldLastName  = new SqlParameter( "@oldLastName",  oldLastName  );
       SqlParameter pOldEmail    = new SqlParameter( "@oldEmail",    oldEmail    );
       SqlParameter pFirstName   = new SqlParameter( "@firstName",   firstName   );
       SqlParameter pLastName    = new SqlParameter( "@lastName",    lastName    );
       SqlParameter pEmail     = new SqlParameter( "@email",     email     );
       SqlParameter pPhone     = new SqlParameter( "@phone",     phone     );
       SqlParameter pCellPhone   = new SqlParameter( "@cellPhone",   cellPhone   );
       SqlParameter pBestTimeToCall = new SqlParameter( "@bestTimeToCall", bestTimeToCall );
       SqlParameter pEmailed    = new SqlParameter( "@emailed",    emailed    );
       SqlParameter pCalled     = new SqlParameter( "@called",     called     );
    
       cmd.Parameters.Add( pBestTimeToCall );
       cmd.Parameters.Add( pId );
       cmd.Parameters.Add( pFirstName );
       cmd.Parameters.Add( pLastName );
       cmd.Parameters.Add( pEmail );
       cmd.Parameters.Add( pPhone );
       cmd.Parameters.Add( pCellPhone );
       cmd.Parameters.Add( pEmailed );
       cmd.Parameters.Add( pCalled );
       cmd.Parameters.Add( pOldFirstName );
       cmd.Parameters.Add( pOldLastName );
       cmd.Parameters.Add( pOldEmail );
    
       try
       {
        conn.Open();
    
        cmd.ExecuteNonQuery();
       }
       catch ( Exception ex )
       {
        throw ex;
       }
       finally
       {
        if ( cmd != null )
         cmd.Dispose();
    
        if ( conn != null )
         conn.Dispose();
       }
      }
    
    I'm seeing the following error everytime this method is called: 
    System.Data.SqlClient.SqlException: The parameterized query '(@bestTimeToCall nvarchar(4000),@id bigint,@firstName nvarchar(4' expects the parameter '@bestTimeToCall', which was not supplied.
    Any suggestions whould be greatly appreciated. I've tried changing the order of the Parameter.Add calls to no effect.
    Is there a known issue with the number of Parameters ADO.Net 2.0 can support? That is the only thing I can think of, since the @bestTimeToCall parameter is there! 
    Sunday, June 6, 2010 12:25 PM

Answers

  • I'd recommend using the AddWithValue method on the SqlParameters collection.  I'd also recommend reproducing the problem in a small sample app to simplify the process of drilling into the issue.  For example, if you tried the approach you're working with now on a parameterized query (either UPDATE or SELECT) using just a single parameter for bestTimeToCall, do you still get the same behavior?
    David Sceppa
    Monday, June 7, 2010 8:42 PM
    Moderator
  • Hi Eric,

    You can insert some breakpoints in your code and debug to see whether the paremeter value you passed in is correct. As David said, you can use AddWithValue method to add values for parameters, you can try like this:

    string sql = "Update [Contacts] set [BestTimeToCall] = @bestTimeToCall Where [Id] = @id and [FirstName] = @oldFirstName and [LastName] = @oldLastName and [Email] = @oldEmail";
    SqlConnection conn = new SqlConnection(getConnection());
    SqlCommand cmd = new SqlCommand(sql, conn);
    
    cmd.Parameters.AddWithValue("@bestTimeToCall", bestTimeToCall);
    cmd.Parameters.AddWithValue("@id", id);
    cmd.Parameters.AddWithValue("@oldFirstName", oldFirstName);
    cmd.Parameters.AddWithValue("@oldLastName", oldLastName);
    cmd.Parameters.AddWithValue("@oldEmail", oldEmail);
    
    try
    {
      conn.Open();
      cmd.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
      throw ex;
    }
    finally
    {
      if (cmd != null)
        cmd.Dispose();
    
      if (conn != null)
        conn.Dispose();
    }
    
    Best regards,
    Alex Liang
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, June 8, 2010 7:00 AM
    Moderator

All replies

  • Can you try this instead?

          SqlParameter pBestTimeToCall = new SqlParameter("@bestTimeToCall", System.Data.SqlDbType.NVarChar);
          pBestTimeToCall.Value = bestTimeToCall;
    Monday, June 7, 2010 5:11 AM
  • I put your suggestion into the code, built the web site, compressed the directory, uploaded it to the server, copied the files from the zip file over the web directory and tested the function. Got the same result. For some strange reason, this parameter is not being seen. I don't think it's a reserved word issue as this is just a string value. But what else could it be?
    Monday, June 7, 2010 12:58 PM
  • I'd recommend using the AddWithValue method on the SqlParameters collection.  I'd also recommend reproducing the problem in a small sample app to simplify the process of drilling into the issue.  For example, if you tried the approach you're working with now on a parameterized query (either UPDATE or SELECT) using just a single parameter for bestTimeToCall, do you still get the same behavior?
    David Sceppa
    Monday, June 7, 2010 8:42 PM
    Moderator
  • Can you use sql profiler to monitor what is executed on the sql server database? Can you also verify that bestTimeToCall has a value and you are not passing null
    Monday, June 7, 2010 9:23 PM
  • Okay, I've simplified the method (as much as I can with the db), meaning that I still have 4 selector values and the one update value.

     

    public static void Update2( long id, string oldFirstName, string oldLastName, string oldEmail, string bestTimeToCall )
      {
       string sql = "Update [Contacts] set [BestTimeToCall] = @bestTimeToCall Where [Id] = @id and [FirstName] = @oldFirstName and [LastName] = @oldLastName and [Email] = @oldEmail";
       SqlConnection conn = new SqlConnection( getConnection() );
       SqlCommand cmd = new SqlCommand( sql, conn );
       SqlParameter pId = new SqlParameter( "@id", id );
       SqlParameter pOldFirstName = new SqlParameter( "@oldFirstName", oldFirstName );
       SqlParameter pOldLastName = new SqlParameter( "@oldLastName", oldLastName );
       SqlParameter pOldEmail = new SqlParameter( "@oldEmail", oldEmail );
       //SqlParameter pBestTimeToCall = new SqlParameter( "@bestTimeToCall", bestTimeToCall );
       SqlParameter pBestTimeToCall = new SqlParameter( "@bestTimeToCall", System.Data.SqlDbType.NVarChar ); pBestTimeToCall.Value = bestTimeToCall;
    
       cmd.Parameters.Add( pId );
       cmd.Parameters.Add( pOldFirstName );
       cmd.Parameters.Add( pOldLastName );
       cmd.Parameters.Add( pOldEmail );
       cmd.Parameters.Add( pBestTimeToCall );
    
       try
       {
        conn.Open();
    
        cmd.ExecuteNonQuery();
       }
       catch ( Exception ex )
       {
        throw ex;
       }
       finally
       {
        if ( cmd != null )
         cmd.Dispose();
    
        if ( conn != null )
         conn.Dispose();
       }
      }
    

    And I'm still getting the same error from the db:

    System.Data.SqlClient.SqlException: The parameterized query '(@id bigint,@oldFirstName nvarchar(4),@oldLastName nvarchar(4),@' expects the parameter '@bestTimeToCall', which was not supplied. at My_MSI.DB.Contacts.Update2(Int64 id, String oldFirstName, String oldLastName, String oldEmail, String bestTimeToCall) 

    I wouldn't have thought so, but it does appear that the value of the parmeter is null. I would expect another error message for a null in a non nullable field.

    I'll have to defer tracking this one down to tomorrow.

    Eric

     

    Tuesday, June 8, 2010 1:55 AM
  • Hi Eric,

    You can insert some breakpoints in your code and debug to see whether the paremeter value you passed in is correct. As David said, you can use AddWithValue method to add values for parameters, you can try like this:

    string sql = "Update [Contacts] set [BestTimeToCall] = @bestTimeToCall Where [Id] = @id and [FirstName] = @oldFirstName and [LastName] = @oldLastName and [Email] = @oldEmail";
    SqlConnection conn = new SqlConnection(getConnection());
    SqlCommand cmd = new SqlCommand(sql, conn);
    
    cmd.Parameters.AddWithValue("@bestTimeToCall", bestTimeToCall);
    cmd.Parameters.AddWithValue("@id", id);
    cmd.Parameters.AddWithValue("@oldFirstName", oldFirstName);
    cmd.Parameters.AddWithValue("@oldLastName", oldLastName);
    cmd.Parameters.AddWithValue("@oldEmail", oldEmail);
    
    try
    {
      conn.Open();
      cmd.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
      throw ex;
    }
    finally
    {
      if (cmd != null)
        cmd.Dispose();
    
      if (conn != null)
        conn.Dispose();
    }
    
    Best regards,
    Alex Liang
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, June 8, 2010 7:00 AM
    Moderator