Procedure not working because of unresolved refere

问题:

I'm writing a WPF application where at some point I'm trying to add new row to my database through procedure like below:

CREATE PROCEDURE dbo.InsertStudent
    @IdStudent INT,
    @FirstName VARCHAR(50),
    @LastName VARCHAR(50),
    @Address VARCHAR(50),
    @IndexNumber VARCHAR(50),
    @IdStudies INT
AS
    SET NOCOUNT ON

    INSERT INTO [dbo].[apbd.Student]
           ([IdStudent]
           ,[FirstName]
           ,[LastName]
           ,[Address]
           ,[IndexNumber]
           ,[IdStudies])
    VALUES
           (@IdStudent
           ,@FirstName
           ,@LastName
           ,@Address
           ,@IndexNumber
           ,@IdStudies)

but whenever I'm about to use it, I'm getting error:

SQL71502: Procedure: [dbo].[InsertStudent] has an unresolved reference to object [dbo].[apbd.Student].

I was looking for solution but what I've found was only to add reference to database through right click on References and so on, but I do not have this option in my solution explorer.

Maybe I'm looking for it in wrong places but the only options I have after right click are something like this:

1. Add reference...
2. Add reference to service...
3. Add connected/concatenated/accumulative (or however should it be translated) service
4. Add analyzer...
5. Manage NuGet packets...

as for the code behind creation of the tables in database:

CREATE SCHEMA apbd;

GO

-- tables
-- Table: Student
CREATE TABLE apbd.Student (
    IdStudent int  NOT NULL IDENTITY,
    FirstName nvarchar(100)  NOT NULL,
    LastName nvarchar(100)  NOT NULL,
    Address nvarchar(100)  NOT NULL,
    IndexNumber nvarchar(50) NOT NULL,
    IdStudies int  NOT NULL,
    CONSTRAINT Student_pk PRIMARY KEY  (IdStudent)
);

-- Table: Student_Subject
CREATE TABLE apbd.Student_Subject (
    IdStudentSubject int  NOT NULL IDENTITY,
    IdStudent int  NOT NULL,
    IdSubject int  NOT NULL,
    CreatedAt datetime  NOT NULL,
    CONSTRAINT Student_Subject_pk PRIMARY KEY  (IdStudentSubject,IdStudent,IdSubject)
);

-- Table: Studies
CREATE TABLE apbd.Studies (
    IdStudies int  NOT NULL IDENTITY,
    Name nvarchar(100)  NOT NULL,
    CONSTRAINT Studies_pk PRIMARY KEY  (IdStudies)
);

-- Table: Subject
CREATE TABLE apbd.Subject (
    IdSubject int  NOT NULL IDENTITY,
    Name nvarchar(100)  NOT NULL,
    CONSTRAINT Subject_pk PRIMARY KEY  (IdSubject)
);

-- End of file.

回答1:

A MS SQL Server database, by default, only has a single schema (dbo). You can add schemas to group things for either security or organizational purposes.

In your case, the schema apbd was created and Student was created on that schema not the dbo schema. So, to reference that table, you need to use [apbd].[Student].

回答2:

I would run the following to determine the actual name and schema of the table:

SELECT
    CAST(
        MAX(
            CASE
                WHEN
                    TABLE_SCHEMA = 'apbd'
                    AND TABLE_NAME = 'Student'
                THEN 1
                ELSE 0
            END
        ) AS bit
    ) [The table is apbd.Student]
    ,
        CAST(
            MAX(
                CASE
                    WHEN
                        TABLE_SCHEMA = 'dbo'
                        AND TABLE_NAME = 'apbd.Student'
                    THEN 1
                    ELSE 0
                END
            ) AS bit
        ) [The table is dbo.apbd.Student]
FROM INFORMATION_SCHEMA.TABLES

I'm also wondering if you perhaps need a USE statement at the start of your CREATE script - are you creating the procedure on the right database?

If the table is on a different database you would need to reference the database in your stored procedure, i.e. [DatabaseName].[dbo].[apbd.Student].