Saumya Agarwal

Saumya Agarwal

  • NA
  • 21
  • 5.9k

Updating fields of database

Nov 8 2013 3:18 AM
I am getting this error: Input string was not in correct format.

This is my code where I am getting the error:

  public void UPDATEuser(int userID, string userName, string password, string emailAddress, List<int> preferences)
        {
            SqlConnection dbcon = new SqlConnection(conn);
            try
            {
                SqlCommand cmd = new SqlCommand();
             
                cmd.Connection = dbcon;
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.CommandText = "UPDATEuser";
                cmd.Parameters.AddWithValue("@userID", userID);
                cmd.Parameters.AddWithValue("@userName", userName);
                cmd.Parameters.AddWithValue("@password", password);
                cmd.Parameters.AddWithValue("@emailAddress", emailAddress);

                dbcon.Open();
                int i = Convert.ToInt32(cmd.ExecuteScalar());
                cmd.Parameters.Clear();
                cmd.CommandText = "tbl_pref";
                foreach (int preference in preferences)
                {
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddWithValue("@userID", Convert.ToInt32(i));
                    cmd.Parameters.AddWithValue("@preferenceID", Convert.ToInt32(preference));
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                dbcon.Close();
            }
        }

Stored Procedure:

ALTER PROCEDURE [dbo].[UPDATEuser]
    -- Add the parameters for the stored procedure here
(
    @userID int,
    @userName varchar(50),
    @password nvarchar(50),
    @emailAddress nvarchar(50)
)
AS
BEGIN
    update tbl_user
    set userName=@userName, password=@password, emailAddress=@emailAddress
    where userID=@userID
   
    delete from tbl_preferences
    where userID=@userID
   
    select stuff((select ',' + preferenceName from tbl_pref_master
    inner join tbl_preferences on tbl_pref_master.preferenceID = tbl_preferences.preferenceID
 where tbl_preferences.userID=tbl_user.userID
 FOR XML PATH ('')),1,1,' ' )
    AS Preferences
 from tbl_user
END

Pls help

Answers (5)