Results 1 to 6 of 6

Thread: sp_addlogin

  1. #1
    Join Date
    Oct 2003
    Location
    PHILIPPINES
    Posts
    9

    Lightbulb Unanswered: sp_addlogin

    Hi,

    I have a problem to my stored procedure when I run my sp under my application an error msg appear and it was "Invalid character value for cast specification". Could you please help me on how to run properly my stored procedure.

    Here is the script API function & SP:
    // Function
    INTEGER li_ret
    DECLARE proc_login PROCEDURE FOR adduserlogin
    @c_userid = :as_userid,
    @c_password = :as_password,
    @c_dbname = :as_dbname,
    @c_rolename = :as_rolename ;

    execute proc_login;

    li_ret = sqlca.sqlcode
    Choose Case li_ret
    case 0, 100
    Close proc_login;
    case else
    MessageBox( "Warning", sqlca.sqlerrtext )
    End Choose

    return sqlca.sqlcode


    // Store Procedure
    ALTER proc adduserlogin
    @c_userid varchar(50)
    , @c_password varchar(50)
    , @c_dbname varchar(50)
    , @c_rolename varchar(50)
    as

    DECLARE @n_ret int, @n_rtn int, @n_retval int

    EXECUTE @n_ret = sp_addlogin @c_userid, @c_password, @c_dbname
    EXECUTE @n_rtn = sp_adduser @c_userid, @c_userid, @c_rolename

    RETURN ( @n_retval )


    Appreciate your help

    Thanks

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: sp_addlogin

    Sure...


    You can not return a null value.


    @n_retval has never been set...

    You probably want to add something like...


    SELECT @n_retval = @n_ret + @n_rtn

    And why aren't you doing error checking?

  3. #3
    Join Date
    Oct 2003
    Location
    PHILIPPINES
    Posts
    9

    Post Re: sp_addlogin

    Originally posted by Brett Kaiser
    Sure...


    You can not return a null value.


    @n_retval has never been set...

    You probably want to add something like...


    SELECT @n_retval = @n_ret + @n_rtn

    And why aren't you doing error checking?
    /--
    Thanks for your advice, I forgot to cut & paste my error checking on the script!!! Anyway, I try your suggestion but even I put the error checking it was the same error appear "Invalid chararcter value for cast specification".

    I am using my backend is MS Sql Server 7.0 and the frontend is Powebuilder. Do you think there is a conflict from ODBC SQL Driver?

    Thanks!

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: sp_addlogin

    I meant Error checking in your stored procedure...not the script..


    Did you add the:
    Code:
    SELECT @n_retval = @n_ret + @n_rtn
    If you did, I don't see anything wrong with the sproc...I might make some of the local variables a type of sysname though.

    Do you have access to SQL Server client side tools?

    Test the sproc in there...it should run no problem.

    If it does, you've eliminated it as the source of the problem.

  5. #5
    Join Date
    Oct 2003
    Location
    PHILIPPINES
    Posts
    9

    Re: sp_addlogin

    Originally posted by Brett Kaiser
    I meant Error checking in your stored procedure...not the script..


    Did you add the:
    Code:
    SELECT @n_retval = @n_ret + @n_rtn
    If you did, I don't see anything wrong with the sproc...I might make some of the local variables a type of sysname though.

    Do you have access to SQL Server client side tools?

    Test the sproc in there...it should run no problem.

    If it does, you've eliminated it as the source of the problem.
    Yes I put the code abovementioned to my store procedure and I have an access to SQL Server. I test the SP under Query Analyzer and it was successfully run without errors but when I put the SP inside my Apps calling the store procedure the Error msg will display "[MSSQL][ODBC SQL Driver]Invalid character value for cast specification"! What is the meaning of the given Error.

    Thanks for your patience.

  6. #6
    Join Date
    Oct 2003
    Posts
    1
    The error-message means something hasn't been cast right (Like varchar instead of int).

    Looking at your sp I asume that the user_id must be integer but you are using a varchar-type for it.

Posting Permissions

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