Updating fields of database

Saumya Agarwal

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 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

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Saumya Agarwal13y ago
Well thanks for the link
0
Saumya Agarwal13y ago
I tried putting the code just as u said this way:

cmd.Connection = dbcon;
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.CommandText = "UPDATEuser";

                cmd.Parameters.Add("@userID", SqlDbType.Int);
                cmd.Parameters["@userID"].Value = userID;
                cmd.Parameters.AddWithValue("@userName", userName);
                cmd.Parameters.AddWithValue("@password", password);
                cmd.Parameters.AddWithValue("@emailAddress", emailAddress);

But the error remains the same.
0
vivek kulkarni13y ago
Try using add method as below by giving the parameter datatype.

command.Parameters.Add("@ID", SqlDbType.Int);
command.Parameters["@ID"].Value = customerID;

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters(v=vs.110).aspx
0
Saumya Agarwal13y ago
The preferenceID is a variable used in another stored procedure whose name is tbl_pref. Pls see the stored procedure once again. That preference ID is for storing the ID of the checked items from the checkbox list.
0
Priyank Khare13y ago
Hi,

You are using parameter "@preferenceID" in following line of code-

cmd.Parameters.AddWithValue("@preferenceID", Convert.ToInt32(preference));

But in your stored procedure, there is no variable called with the name "@preferenceID".

So, you will have to add a variable "@preferenceID int" into your Sp, save it and then try to execute your code.
0