0

For my application, I have created a stored procedure(taken from here):

Create proc spResetPassword
@UserName nvarchar(100)
as
Begin
Declare @UserId int
Declare @Email nvarchar(100)

Select @UserId = Id, @Email = Email 
from tblUsers
where UserName = @UserName

if(@UserId IS NOT NULL)
Begin
--If username exists
 Declare @GUID UniqueIdentifier
 Set @GUID = NEWID()

 Insert into tblResetPasswordRequests
(Id, UserId, ResetRequestDateTime)
Values(@GUID, @UserId, GETDATE())

Select 1 as ReturnCode, @GUID as UniqueId, @Email as Email
End

I have created a model and added the required properties:

[Table("tblResetPasswordRequests")]
public class RequestResetPasswordModel
{
    [Key]
    public Guid Id { get; set; }
    [ForeignKey("ADMIN_ID")]
    public  AdminLogin  UserId{ get; set; }
    public DateTime ResetRequestDateTime { get; set; }
}

In the controller, I have called the stored procedure inside a method:

var uniqueIdForUser = db.Database.SqlQuery<RequestResetPasswordModel>("spResetPassword {0}", user.ADMIN_USERNAME).FirstOrDefault();

The record is getting inserted into the db table, but when I debug the method,the Id is not being passed, it only shows 00000000-0000-0000-0000-000000000000 every time.What should I do?please help

Camilo Terevinto
  • 31,141
  • 6
  • 88
  • 120
Sumedha Vangury
  • 643
  • 2
  • 17
  • 43
  • what do you get if you dont use the `.FirstOrDefault()`?? does it give you a set of email, guid and 1?? – SamGhatak Apr 28 '16 at 07:53
  • @SamGhatak no it doesnt give the required output – Sumedha Vangury Apr 28 '16 at 07:57
  • I have no expertise in entity-framework...so I can only suggest you to do some research how to get the resultset here...The link you showed is described using ASP.NET, so the same mechanism does't work here.... – SamGhatak Apr 28 '16 at 08:00
  • @SamGhatak I used that link only for th stored proc..anyways thanks – Sumedha Vangury Apr 28 '16 at 08:02
  • Shouldn't you be adding the `parameter`? Like this `"("spResetPassword @param1 = {0}", param1)` . However we can't see much of your controller code to give the right guidance – jamiedanq Apr 28 '16 at 08:40
  • @jamiedanq syntax for calling the parameter is correct, because I had previously called a stored procedure following the same syntax and it worked fine. I have shown only one line of controller code because only that much is required to solve the problem, rest of the code is not related to my problem and I can't show it for security reasons – Sumedha Vangury Apr 28 '16 at 08:49
  • Well you can read this link, it should be helpful http://stackoverflow.com/questions/4873607/how-to-use-dbcontext-database-sqlquerytelementsql-params-with-stored-proced – jamiedanq Apr 28 '16 at 08:52
  • @jamiedanq it is still not attaching the guid – Sumedha Vangury Apr 28 '16 at 09:10
  • @sumedha don't really get why it wont work unless the error is somewhere else – jamiedanq Apr 28 '16 at 09:32

1 Answers1

1

Can you change the last line of proc to 

SELECT @GUID AS Id, @UserId AS UserId

And give it a try.... I am not sure, but this might be the case...

SamGhatak
  • 1,487
  • 1
  • 16
  • 27