none
Updating code in C# not execute properly RRS feed

  • Question

  • USE [UserLogin]
    GO
    
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE PROCEDURE [dbo].[sp_SignUp_Update] 
    	(
    		@UserName NVARCHAR(50),
    		@Password NVARCHAR(50),
    		@CPassword NVARCHAR(50)
    	)
    AS
    	Update dbo.signup 
               set	UserName=@UserName,
    				Password=@Password,
    				CPassword=@CPassword
    	where UserName=@UserName
    	RETURN
    
    ////  C# Code for Update Statement   ////
    try
                {
                    Conn.ConnectionString = "Data Source=.\\SQLEXPRESS;Initial Catalog=UserLogin;Integrated Security=True";
                    Conn.Open();
                    Comm = Conn.CreateCommand();
                    Comm.CommandType = CommandType.StoredProcedure;
                    Comm.CommandText = "sp_SignUp_Update";
                    Comm.Parameters.Clear();
                    Comm.Parameters.AddWithValue("@UserName", txtUser.Text.Trim());
                    Comm.Parameters.AddWithValue("@Password", txtPassword.Text.Trim());
                    Comm.Parameters.AddWithValue("@CPassword", txtCPassword.Text.Trim());
                    Comm.ExecuteNonQuery();
                    MessageBox.Show("Record Updated Successfully");
                    txtUser.Text = "";
                    txtPassword.Text = "";
                    txtCPassword.Text = "";
                 }
    
                catch (Exception Ex)
                {
                    MessageBox.Show("Cannot Open Connection !" + Ex.Message);
                }
                finally
                {
                    Conn.Close();
                }
    Here I added Update Stored Procedure and C# code for all but not updated properly.
    Thursday, October 18, 2012 5:34 AM

Answers

  • Look 1st slide here I find user name which is displayed. After that I changed user name then clicked update button. Message shown Updated successfully but no change in my database. I already recode int i=Comm.ExicuteNonQuery();

    In order to change the UserName, you need to pass both the old and new UserName values.  Specify the old UserName in the WHERE clause and change the C# code accordingly.  Also, avoid using the "sp_" prefix for user stored procedure.  See http://msdn.microsoft.com/en-us/library/ms187926.aspx

    CREATE PROCEDURE [dbo].[usp_SignUp_Update] 
    	(
    		@OldUserName NVARCHAR(50),
    		@NewUserName NVARCHAR(50),
    		@Password NVARCHAR(50),
    		@CPassword NVARCHAR(50)
    	)
    AS
    	Update dbo.signup 
               set	UserName=@NewUserName,
    				Password=@Password,
    				CPassword=@CPassword
    	where UserName=@OldUserName
    	RETURN @@ERROR;


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/


    Saturday, October 20, 2012 12:22 PM

All replies

  • Your C# code looks fine. The reason it is not updating could be for many reason.

    1. You are pointing to a wrong database

    2. You are passing wrong User Name or the user name is not present in database. I am not sure why your update statement is updating user name. Are you trying to update a username against new user name?

    You can enable SQL profiler and trap what SQL statement is coming to SQL Engine by ADO.net.

    Modify your C# code and check the return value from database as below.

    int i= Comm.ExecuteNonQuery();


    Lingaraj Mishra


    Thursday, October 18, 2012 10:19 AM
  • Hi Prasanta,

    In addition to Lingaraj's suggestion, is there any error message?

    What do you mean by not updated properly? Could you please give more details about the update result?

    Does the store procedure execute successfully in SQL Server?

    Best Regards,


    Bob Wu [MSFT]
    MSDN Community Support | Feedback to us

    Friday, October 19, 2012 7:06 AM
    Moderator
  • Look 1st slide here I find user name which is displayed. After that I changed user name then clicked update button. Message shown Updated successfully but no change in my database. I already recode int i=Comm.ExicuteNonQuery();
    Saturday, October 20, 2012 5:14 AM
  • I am unable to add form image either you can understood what properly executing. Just shown message updated successfully but no effect in Database.
    Saturday, October 20, 2012 5:19 AM
  • Look 1st slide here I find user name which is displayed. After that I changed user name then clicked update button. Message shown Updated successfully but no change in my database. I already recode int i=Comm.ExicuteNonQuery();

    In order to change the UserName, you need to pass both the old and new UserName values.  Specify the old UserName in the WHERE clause and change the C# code accordingly.  Also, avoid using the "sp_" prefix for user stored procedure.  See http://msdn.microsoft.com/en-us/library/ms187926.aspx

    CREATE PROCEDURE [dbo].[usp_SignUp_Update] 
    	(
    		@OldUserName NVARCHAR(50),
    		@NewUserName NVARCHAR(50),
    		@Password NVARCHAR(50),
    		@CPassword NVARCHAR(50)
    	)
    AS
    	Update dbo.signup 
               set	UserName=@NewUserName,
    				Password=@Password,
    				CPassword=@CPassword
    	where UserName=@OldUserName
    	RETURN @@ERROR;


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/


    Saturday, October 20, 2012 12:22 PM