SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    Jun 2011
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy ASP vs MySQL Stored Procedures - Incorrect number of arguments for PROCEDURE

    Howdy All,

    I am very happy to see that ASP is still on the go.

    I hope this is the correct place to ask this question.

    Have a stored procedure in MYSQL:

    Code:
    CREATE PROCEDURE `insert_custdetails`(IN p_Email VARCHAR(100), IN p_Fname VARCHAR(50), IN p_Lname VARCHAR(50), IN p_IP VARCHAR(40), IN p_OS VARCHAR(10), IN p_Browser VARCHAR(20), OUT ID INTEGER(11))
        NOT DETERMINISTIC
        CONTAINS SQL
        SQL SECURITY DEFINER
        COMMENT ''
    BEGIN
    INSERT INTO
      `customerdetails`(
      Email,
      Fname,
      Lname,
      IP,
      OS,
      Browser)
    VALUES(
      p_Email,
      p_Fname,
      p_Lname,
      p_IP,
      p_OS,
      p_Browser);
    
    
    Set ID = LAST_INSERT_ID();
    
    END;
    Then My Code in ASP:

    Code:
    	SQL="CALL insert_custdetails (1," & _
    		"'2'," & _
    		"'3'," & _
    		"'4'," & _
    		"'5'," & _
    		"'6');"
    	If Database(SQL) Then
    		Response.Write Rs("ID")
    	End If
    Error is:

    -2147467259:
    [MySQL][ODBC 5.1 Driver][mysqld-5.1.34-community]Incorrect number of arguments for PROCEDURE mytable.insert_custdetails; expected 7, got 6
    Does not make sense. Since the 7th is declared as an OUT.

    Any brainiacs around that can help?

    Thanks.

    PS! Yes I have only now started to use sp's. This is my very first one.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    in your calling program, you still have to have a variable to receive the output value

    you have to code this variable as the 7th parameter in the CALL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Apr 2009
    Posts
    358
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Just a guess, you might need text quotes ' around your first test argument
    Doug G
    =====
    "If you ain't the lead dog, the view is always the same - Anon

  4. #4
    SitePoint Member
    Join Date
    Jun 2011
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    in your calling program, you still have to have a variable to receive the output value

    you have to code this variable as the 7th parameter in the CALL
    Nope now its worse I think or I am doing it wrong.

    My Code:

    Code:
    	SQL="CALL insert_custdetails ('1'," & _
    		"'2'," & _
    		"'3'," & _
    		"'4'," & _
    		"'5'," & _
    		"'6',0);"
    	If Database(SQL) Then
    		Response.Write Rs("ID")
    	End If
    -2147467259:
    [MySQL][ODBC 5.1 Driver][mysqld-5.1.34-community]OUT or INOUT argument 7 for routine mytable.insert_custdetails is not a variable or NEW pseudo-variable in BEFORE trigger

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you cannot feed the results of the procedure (which is a number which is determined by LAST_INSERT_ID) into the constant 0 -- constants cannot take values

    you have to have a variable to receive the value

    heck, i don't even do asp, i do coldfusion, but it's the same concept, right?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Member
    Join Date
    Jun 2011
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    you cannot feed the results of the procedure (which is a number which is determined by LAST_INSERT_ID) into the constant 0 -- constants cannot take values

    you have to have a variable to receive the value

    heck, i don't even do asp, i do coldfusion, but it's the same concept, right?
    You been here tooooooo long mate!!!! If you cannot have something nice to say then do not say anything.

    For those who want to know. Asked the same question on stackoverflow and got a answer in 20minutes. THE RIGHT ANSWER...NO COCKY REMARKS.

    Here it is as I got it now:

    Code:
    	SQL="CALL insert_custdetails ('1'," & _
    		"'2'," & _
    		"'3'," & _
    		"'4'," & _
    		"'5'," & _
    		"'6',@ID);"
    	If Database(SQL) Then
    	
    		SQL="SELECT @ID"
    		If Database(SQL) Then
    			Response.Write Rs("@ID")
    		End If
    	End If
    And here is a link that explains it all:

    SqlCommand.Parameters Property (System.Data.SqlClient)

  7. #7
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,276
    Mentioned
    119 Post(s)
    Tagged
    1 Thread(s)
    And what Rudy told you was correct - in your example, you had a 0 (which is a constant) for the last parameter - in the stackoverflow answer, it has @ID (which is a variable).
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse


Tags for this Thread

Bookmarks

Posting Permissions

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