Monday 3 September 2018

Mysql - The stored procedure does not fit in the table

I have a simple stored procedure in a database which is called AddNewStudent:

    CREATE PROCEDURE dbo.AddNewStudent(
    @fName nvarchar(20),
    @sName nvarchar(20),
    @lName nvarchar(20),
    @faculty nvarchar(10),
    @specialty nvarchar(50),
    @OKS smallint,
    @StudentStat smallint,
    @fak nvarchar(50),
    @Course smallint,
    @Porok nvarchar(5),
    @Group int
    )
    AS
    INSERT INTO [Students] (FirstName, SecondName, LastName, Faculty,
    Specialty, OKS, StudentStatus, FakNumber, Course, Potok, [[Group]]])
    VALUES (@fName , @sName, @lName, @faculty, @specialty, @OKS,
    @StudentStat, @fak, @Course, @Porok, @Group)
    RETURN 2;

When i test the procedure through DatabaseExplorer (VS2013) everything is OK and the record is inserted into the table. But when i call the procedure in c# nothing happens. Bellow is the code for the method which I use to call the procedure:
    public static bool InsertStudent (Student student)
    {
        StudentDataClassesDataContext dc = new StudentDataClassesDataContext();
        try
        {
           int returnValue = dc.AddNewStudent(student.FirstName, student.SecondName, student.LastName, student.Faculty, student.Specialty, student.OKS, student.StudentStatus,
                student.FakNumber, student.Course, student.Potok, student._Group_);
            dc.SubmitChanges();
            MessageBox.Show("Return Value : " + returnValue, "Info", MessageBoxButtons.OK, MessageBoxIcon.Warning);
        }
        catch (Exception e)
        {
            MessageBox.Show("Exception : " + e.Message, "Info", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            return false;
        }
        return true;
    }

The returned value is 2 which means that the procedure does its work but why there is no record inserted into the table? I read that dc.SubmitChanges() is used instead of Commit.

You can start Sqlserver Profiler to see what happens, to see if there is any transaction start without commit? Also you can set a break point just after dc.submitchanges() and when your application hits break point, go to sql server and run this query
Select * from [Students] with (nolock)

and be sure that the data is in your table, after that continue running your application and run that query again, if the data was in your table and no it's gone there is an uncommited transaction. to solve that just use TransactionScope. if data is not in your table from the start you might running your code on another database. You can pass the connnctionstring in datacontext constructor.

0 comments:

Post a Comment