Cannot able to save a data while returning that value?

What i need is to save a data in a table and return that value at last..Here What is happening is it returns that value but it is not getting saved in the table..

Here is the code

using (SqlConnection Conn = new SqlConnection(constr_str)) {
    using (SqlCommand cmd = new SqlCommand()) {
        cmd.开发者_StackOverflow中文版Connection = Conn;
        Conn.Open();
        cmd.CommandText = "Declare @Len int Select @Len =LEN(@CustomerCode) Begin declare @Id varchar(20) select @Id = RequestId from Rode_Provisioning_Process if @Id is null Begin insert into Rode_Provisioning_Process(CustomerCode,RequestType,RequestId,Status,CreatedDate,ModifiedDate) values(@CustomerCode,@RequestType,@CustomerCode+'0001',null,getdate(),getdate()) return End Else declare @max varchar(20) select @max = max(cast(substring(RequestId , @len+1, 4) as int)) from Rode_Provisioning_Process (nolock) select @max = isnull(@max, 0) + 1 select @max = (case when len(@max) = 1 then '000' + @max when len(@max) = 2 then '00' + @max when len(@max) = 3 then '0' + @max else @max end) select @max\tdeclare @number varchar(20) Select @number = (Substring( @CustomerCode, 1, PatIndex( '%[0-9]%', RequestId) - 1 ) + @max) from Rode_Provisioning_Process select @number insert into Rode_Provisioning_Process(CustomerCode,RequestType,RequestId,Status,CreatedDate,ModifiedDate) values(@CustomerCode,@RequestType,@number,null,getdate(),getdate()) end";
        cmd.Parameters.Add("@CustomerCode", SqlDbType.VarChar).Value = CustomerCode;
        cmd.Parameters.Add("@RequestType", SqlDbType.VarChar).Value = RequestType;
        cmd.ExecuteNonQuery();
        cmd.CommandText = "Select RequestId from Rode_Provisioning_Process where CustomerCode=@CustomerCode";
        cmd.Parameters.Add("@CustomerCode", SqlDbType.VarChar).Value = CustomerCode;
        string RequestId = cmd.ExecuteScalar();
        Conn.Close();
        return RequestId;
    }
}

Any suggestion?

Here are a few things which are wrong, in my opinion:

• The CommandText should be shipped off into a stored procedure, it's difficult to read and understand.
• You are reusing the Command object, which serves very little purpose except to open you up for strange errors.
• RequestID is being stored as a varchar, as opposed to an int. If you need to keep a numeric value in the DB, always store it as the relevant type, and then convert it to a string when you need it (e.g. Select RIGHT('000' + CONVERT(VARCHAR(10), @RequestID), 4)).

None of these point to the actual error you describe, but should go a long way towards making your code easier to understand and debug.