Results 1 to 9 of 9

Thread: [RESOLVED] Ef stored procedure return null

  1. #1

    Thread Starter
    Lively Member FunkySloth's Avatar
    Join Date
    Aug 2016
    Posts
    91

    Resolved [RESOLVED] Ef stored procedure return null

    Hi guys,

    I have a simple stored procedure that returns number of column:

    Code:
    BEGIN
    	SET FMTONLY OFF
    	SELECT A.EMPLOYEENO, A.FIRSTNAME, A.LASTNAME, A.MIDDLENAME, A.DEPARTMENT, B.DEPARTMENTDESC, A.SUPERVISORID, A.SUPERVISORNAME
    	  FROM [IPADDRESS].[MASTER].[dbo].[EMPLOYEES] AS A
    	 INNER JOIN [IPADDRESS].[MASTER].[dbo].[DEPARTMENT] AS B
    	    ON A.DEPARTMENT = B.DEPARTMENTCODE
    	 WHERE A.EMPLOYEENO = @ID
    END
    Then I did the following steps:

    Step A

    1. Update Model from Database
    2. Add the Stored Procedure

    Step B

    1. Add new Function Import
    2. Enter the name "Function Import Name"
    3. "Complex" as Returns a Collection of.
    4. Get Column Information


    And then this is my code in Controller:
    Code:
                        var empInfo = from item in db.getEmployeeDetails(id)
                                      select new
                                      {
                                          fullName = item.FIRSTNAME + " " + item.LASTNAME,
                                          department = item.DEPARTMENTDESC
                                      };
    
                        return Json(new { FullName = fullName, Department = department }, JsonRequestBehavior.AllowGet);
    However it returns null, I did the SET FMTONLY OFF but still no records.

    Is there anything I left undone?

    Thank you
    Last edited by FunkySloth; Oct 24th, 2017 at 06:52 AM.

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Ef stored procedure return null

    Start with the basics:
    1) Are you SURE you're connected to the right database? - I ask because you're using a FQN to access the table, which means the tables you're accessing may not be the tables in the database you're connected to... is there a reason you're using an FQN?
    2) Is the table empty? Because you're using a FQN, it's possible the table you're actually using could be empty.
    3) Does the ID you're passing in exist?

    -tg

    Edit...
    I was going to suggest changing
    [IPADDRESS].[MASTER].[dbo].[EMPLOYEES]
    to just dbo.EMPLOYEES ... but then I notice... you're using the master database? ?! O.O
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    Lively Member FunkySloth's Avatar
    Join Date
    Aug 2016
    Posts
    91

    Re: Ef stored procedure return null

    Hello techgnome,

    1. Yes I am connected to the right database, and as you notice I'm using the ip address due to I am accessing the database to other server.

    2. The table are not empty because it is the master data of all employees.

    3. The ID that im passing to the parameter does exist so it should return the record. However i didn't figure out why it isn't returning any data.
    Last edited by FunkySloth; Oct 24th, 2017 at 06:27 PM.

  4. #4
    PowerPoster PlausiblyDamp's Avatar
    Join Date
    Dec 2016
    Location
    Pontypool, Wales
    Posts
    2,458

    Re: Ef stored procedure return null

    Quote Originally Posted by FunkySloth View Post
    1. Yes I am connected to the right database, and as you notice I'm using the ip address due to I am accessing the database to other server.
    Is the stored procedure accessing a remote database? If you execute the stored procedure from within SSMS does it return the correct results? Is your connection string using integrated security or a hard coded username and password? If integrated security then you need to make sure the user your web application runs as can execute the stored procedure.

    Also to repeat techgnome.... you're using the master database? ?! O.O

  5. #5

    Thread Starter
    Lively Member FunkySloth's Avatar
    Join Date
    Aug 2016
    Posts
    91

    Re: Ef stored procedure return null

    Yes it is accessing a remote database and using a hard coded username and password connection string. When using SSMS it does return the correct record. Is there any problem about using the master database?
    Last edited by FunkySloth; Oct 24th, 2017 at 07:57 PM.

  6. #6
    PowerPoster PlausiblyDamp's Avatar
    Join Date
    Dec 2016
    Location
    Pontypool, Wales
    Posts
    2,458

    Re: Ef stored procedure return null

    Quote Originally Posted by FunkySloth View Post
    Yes it is accessing a remote database and using a hard coded username and password connection string. When using SSMS it does return the correct record. Is there any problem about using the master database?
    The master database is a system database, more accurately it is the system database as it is the database that controls and configures your SQL server, it isn't intended to be used as a database for your own things. https://docs.microsoft.com/en-us/sql...aster-database is possibly worth a quick look as it explains a bit more about it.

    A brief summary though is that it isn't intended for you to use as your own database, it has fairly specific options set (such as simple logging) which make it unsuitable for a normal database workload, problems with the master database can leave you SQL Server unable to start (which in this case would mean unable to get at your data, restoring your damaged master would prevent the server starting).

    The normal practice is to create your own database on the server to store your data in, not just shoving it into master.

    I must admit to having never used EF with a linked server before so I am not sure if that could be part of the problem, is there a need to use a linked server rather than access the server directly? Would it be possible to test connecting to the other server directly? Also if you have a linked server why not use the remote server's name rather than the IP Address?

  7. #7

    Thread Starter
    Lively Member FunkySloth's Avatar
    Join Date
    Aug 2016
    Posts
    91

    Re: Ef stored procedure return null

    So I guess I'd better use directly the server rather than linked it to my Stored Procedure, and oh, don't worry I'm not using the Master System Database, that's a different Master Database.

    And I think I'd better create the Model of the linked server instead, so I could access the data easier. Though having multiple EF Model does it slow the performance of the system?

  8. #8
    PowerPoster PlausiblyDamp's Avatar
    Join Date
    Dec 2016
    Location
    Pontypool, Wales
    Posts
    2,458

    Re: Ef stored procedure return null

    Quote Originally Posted by FunkySloth View Post
    So I guess I'd better use directly the server rather than linked it to my Stored Procedure, and oh, don't worry I'm not using the Master System Database, that's a different Master Database.

    And I think I'd better create the Model of the linked server instead, so I could access the data easier. Though having multiple EF Model does it slow the performance of the system?
    So you have a database called Master on your SQL server? I have honestly never even tried to do that!

    Having multiple EF models isn't really going to affect the performance, the only difference it is likely to make is in terms of the compiled application size and I doubt it will add that much impact. The bigger difference is going to be down to database and network speeds, accessing a database directly rather than going to one database and having it go to the other database is probably a faster option anyway.

  9. #9

    Thread Starter
    Lively Member FunkySloth's Avatar
    Join Date
    Aug 2016
    Posts
    91

    Re: Ef stored procedure return null

    Well I'm going to create an EF Model for now.

    Thank you very much PlausiblyDamp.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width