#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2013
    Posts
    2
    Rep Power
    0

    Stored procedure not returning value


    Hello All,
    I wrote the stored procedure below;
    Code:
    DELIMITER $$
    
    CREATE DEFINER=`root`@`localhost` PROCEDURE `insertSemReg`(IN p_semesterID int ,
    	IN p_studentID int ,
    	IN p_indexNo nvarchar(50),
    	IN p_regDate datetime,
    	IN p_academicY int,
    	IN p_cLevel int,
    	IN p_userID int,
    	IN p_entrydatetime datetime,OUT RefCheck int)
    BEGIN
    DECLARE vTranscount int;
     SELECT vTranscount=semRegID FROM tbl_semesterregister WHERE semesterID=p_semesterID
    	AND studentID=p_studentID AND academicY=p_academicY AND cLevel=p_cLevel;
    if (vTranscount > 0) then
    	begin
    	UPDATE tbl_semesterregister SET
    			semesterID=p_semesterID, regDate=p_regDate,academicY=p_academicY,cLevel=p_cLevel
    			WHERE semRegID=vTranscount;
    	SET RefCheck =1;
    	end;
    else
    	begin
    	INSERT INTO tbl_semesterregister (semesterID,studentID,indexNo,
    			regDate,academicY,cLevel,userID,entrydatetime) VALUES
    			(p_semesterID,p_studentID,p_indexNo,p_regDate,p_academicY,p_cLevel,p_userID,p_entrydatetime);
    	SET RefCheck =1;
    	end;
    end if;
    END
    I try to call it from my ASP.net application using this function;

    Code:
    Public Function fncAddNewStudSemRegister(ByVal intSemID As Integer, ByVal intStudID As Integer, ByVal strIndexNo As String, ByVal strRegDate As String, _
                                      ByVal intAcademicY As Integer, ByVal intLevel As Integer, intUserID As Integer) As Integer
            Dim intRefCheck As Integer = 0
            Try
                If mySqlPortalConn.State = ConnectionState.Closed Then
                    mySqlPortalConn.Open()
                End If
                Dim cmdMySQL As New MySqlCommand("insertSemReg", mySqlPortalConn) With {.CommandType = CommandType.StoredProcedure}
                cmdMySQL.Parameters.AddWithValue("RefCheck", intRefCheck)
                cmdMySQL.Parameters.AddWithValue("p_semesterID", intSemID)
                cmdMySQL.Parameters.AddWithValue("p_studentID", intStudID)
                cmdMySQL.Parameters.AddWithValue("p_indexNo", strIndexNo)
                cmdMySQL.Parameters.AddWithValue("p_regDate", strRegDate)
                cmdMySQL.Parameters.AddWithValue("p_academicY", intAcademicY)
                cmdMySQL.Parameters.AddWithValue("p_cLevel", intLevel)
                cmdMySQL.Parameters.AddWithValue("p_userID", intUserID)
                cmdMySQL.Parameters.AddWithValue("p_entrydatetime", objUtils.ConvertToMySqlDateTime(Now.ToUniversalTime))
                cmdMySQL.Parameters("RefCheck").Direction = ParameterDirection.Output
                cmdMySQL.ExecuteNonQuery()
                intRefCheck = cmdMySQL.Parameters("RefCheck").Value
                Return intRefCheck
            Catch ex As Exception
                eSMSDAL.clsExceptions.fncLogError(ex.Message, ex.StackTrace.ToString)
                Return intRefCheck
            Finally
                mySqlPortalConn.Close()
            End Try
        End Function
    I am able to do a first insert. But however if I try to insert the same record by passing the same parameter values to the function, it inserts again. As per the logic in the stored procedure, I was expecting it to rather do an update, not insert.
    Anybody to help me figure this out.
    Thank you.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,388
    Rep Power
    392
    The code

    Code:
     SELECT vTranscount=semRegID ...
    will not assign a value to the variable vTranscount, instead it is interpreted as a boolean expression.

    To assign a variable with data from a table you can either use a select into statement or a set statement with a scalar subquery.

IMN logo majestic logo threadwatch logo seochat tools logo