Last post Oct 19, 2010 06:14 PM by dkarantonis
Oct 11, 2010 12:46 PM|dkarantonis|LINK
Hi to all,
i try call a stored procedure from my linq code in order to validate a user credentials. I have tried via linq to sql and sql commands and everything works as expected. But when comes to calling a stored procedure via linq, i cannot get the results i expect.
This is my code:
ALTER PROCEDURE [dbo].[ValidateUser] (@UserName varchar(50), @UserCode varchar(4))
SET NOCOUNT ON;
SELECT ISNULL(FirstName, ''), ISNULL(LastName, '')
WHERE (UserName =@UserName AND UserCode=@UserCode)
dbContext = new MyDBDataContext();
var result = dbContext.ValidateUser("Jim", "1234");
if(result != null)
//true, so user is validated so set response status to OK
response.Status = ResponseStatus.OK;
//set returned params to response
var paramsReturned = result.FirstOrDefault();
response.FirstName = paramsReturned.Column1;
response.LastName = paramsReturned.Column2;
//false, so set respone to the corresponding error
response.Status = ResponseStatus.ERROR_LOGIN_FAILED;
Where, responce is a status that i return.
The Column1 and Column2 fields returned are null, so i cannot get the first name and last name, neither i know whether user is valid or not.
What is wrong?
thanks in advance
Oct 15, 2010 08:43 AM|Wencui Qian - MSFT|LINK
First, please debug the code line by line and check the returned result to ensure the data is returned as expected. Second, LINQ to SQL will automatically generate a type for you to store the returned result, please try that instead. Thanks.
Oct 15, 2010 09:29 AM|dkarantonis|LINK
Of course i have debugged the code many times.
There is a returned result and i also get the 2 columsn i want (First and Last names), but they are both nulls. When i use the same logic using SQL commands via SQL data reader, the columns are ok.
Oct 15, 2010 09:33 AM|riswadkarharshad|LINK
Please set the nocount to off in your procedure like following and see if it works:
SET NOCOUNT OFF;
Oct 15, 2010 09:44 AM|chandraprakash|LINK
Have you tried after changing logic in SP ,like
in place of isnull(firstname,'') just use isnull(firstname,'0')
may be it can help you ....
Oct 19, 2010 06:14 PM|dkarantonis|LINK
yes, i have already done both you suggested.
Anyway, customer is pushing me, so i decided to go with sql commands.