Updating fields of database
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