Alpha Video Training
Results 1 to 5 of 5

Thread: Help with MS SQL INSERT Stored Procedure

  1. #1
    Member
    Real Name
    Joseph Williamson
    Join Date
    Aug 2015
    Posts
    73

    Default Help with MS SQL INSERT Stored Procedure

    Hey All,

    I have been wracking my head with this one. I would like to use a SP for security reasons but I can't get this thing to run! The full SQL statement below works but the SP won't execute without error. I get a .F. on the flag but 'Success' in my call result text.

    Code:
    	Dim conn  as SQL::Connection
    	Dim argz as SQL::Arguments
    	Dim SQL as c 
    	Dim SQLStatement as c
    	dim cLoginType as c = ""
    	dim cResultText as c
    	
    	'flag to test if connection opens
    	dim flag as l
    	SQLStatement = <<%sql%
    	INSERT INTO WebUsersLogins (Username, IPaddress, LoginError, LoginType) OUTPUT INSERTED.Id VALUES('test3','1001001003','gridinitialize3','in')
    	%sql%
    	SQL = <<%sql%
    	EXEC jwspWebUsersLoginsInsert(:username,:ip,:loginerror,:logintype)
    	%sql%
    	
    	argz.set("username","testuser")
    	argz.set("ip","101010101")
    	argz.set("loginerror","OnGrid")	
    	argz.set("logintype","in")	
    	
    	conn.Open("::name::RaTSTest")
    	cResultText = conn.CallResult.Text							
    	'flag = conn.execute(SQLStatement)
    	flag = conn.execute(SQL,argz)
    	debug(1)
    Code:
    Use RaTSTest
    GO
    ALTER PROCEDURE jwspWebUsersLoginsInsert 
    @username nvarchar(250), @ip nvarchar(50), @error nvarchar(250), @logintype nvarchar(3) 
    /*This procedure adds a record everytime a user logs in to the Reportal and is called by the Web User Security function 'saveLoginLog' */
    AS
    INSERT INTO WebUsersLogins (Username, IPaddress, LoginError, LoginType) 
    --OUTPUT INSERTED.Id
    VALUES(@username, @ip, @error, @logintype);

  2. #2
    "Certified" Alphaholic glenschild's Avatar
    Real Name
    Glen Schild
    Join Date
    Apr 2000
    Location
    Frome, Somerset, UK
    Posts
    1,505

    Default Re: Help with MS SQL INSERT Stored Procedure

    Code:
    EXEC jwspWebUsersLoginsInsert(:username,:ip,:loginerror,:logintype)
    Have you tried replacing the above with

    Code:
    EXEC jwspWebUsersLoginsInsert @username = :username,@ip = :ip, @error = :loginerror, @logintype = :logintype
    Glen Schild



    My Blog


  3. #3
    Member
    Real Name
    Ken Shapiro
    Join Date
    May 2015
    Location
    NY Metro
    Posts
    50

    Default Re: Help with MS SQL INSERT Stored Procedure

    Hi Joseph,

    It doesn't look like you executed the procedure. This is clunky, but it works for me. :)

    dim cn as sql::connection
    cn.open("::Name::xxx")

    dim flag as l
    flag = cn.Execute("exec fnow_edit_attendance @pactionid=:pactionid, @pactivityid=:pactivityid," + \
    "@pactivitydetailid=:pactivitydetailid, @pdos=:pdos, @pstarttime=:pstarttime, @pendtime=:pendtime, @pduration=:pduration," +\
    "@pgroupsize=:pgroupsize, @plocation=:plocation, @prate=:prate, @punits=:punits, " +\
    "@ptotalamt=:ptotal_amt, @preturn_message=:preturn_message",args)
    if cn.CallResult.text <> "Success" then
    on_validate.hasError = .t.
    on_validate.errortext = "Server error reported was: " + cn.CallResult.text
    cn.close()
    end
    end if

  4. #4
    Member
    Real Name
    Joseph Williamson
    Join Date
    Aug 2015
    Posts
    73

    Default Re: Help with MS SQL INSERT Stored Procedure

    Thanks! Got it working! I had a syntax error. I don't know why I thought I needed parenthesis around the parameters, but I think that was the issue.

    I didn't need to do
    Code:
    mySProc @parameter = :argument
    because I was passing all the parameters in the same order as listed in the SP and could just do
    Code:
    mySProc :arguments
    but obviously that isn't the case in every scenario

    Thanks for the help!

  5. #5
    "Certified" Alphaholic glenschild's Avatar
    Real Name
    Glen Schild
    Join Date
    Apr 2000
    Location
    Frome, Somerset, UK
    Posts
    1,505

    Default Re: Help with MS SQL INSERT Stored Procedure

    Glad you got it to work, I must admit I have got into the habit of expressly naming the parameters as it helps when debugging and future reference.
    Glen Schild



    My Blog


Similar Threads

  1. CRUD Grid using SQL Server Stored Procedure
    By kirank in forum Mobile & Browser Applications
    Replies: 2
    Last Post: 03-25-2015, 10:00 PM
  2. Report based on a sql stored procedure
    By jcarrallo in forum Mobile & Browser Applications
    Replies: 0
    Last Post: 12-31-2014, 02:11 PM
  3. Running a SQL Server stored procedure
    By Timbo in forum Application Server Version 9 - Web/Browser Applications
    Replies: 4
    Last Post: 01-19-2011, 01:57 PM
  4. Calling sql stored procedure with output parameter
    By cmkivio in forum Application Server Version 10 - Web/Browser Applications
    Replies: 0
    Last Post: 10-19-2010, 03:54 PM
  5. how to use sql server stored procedure?
    By arkeshtk in forum Application Server Version 9 - Web/Browser Applications
    Replies: 2
    Last Post: 08-04-2009, 12:54 AM

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
  •