Login SP getting value returned

[MartinaL]

I have a login form in a vb.net windows app and I run a stored procudure that returns the count for username and password sent in to see if someone is a valid user.

how do i get this count that is returned for an if else statement in my windows form so I can tell it whether to error or continue into the application?

 

[cjard]

Er, from what I was aware, stored procedures dont really return values, they just modify the values of whatever you have passed in

The command that you use to call your sproc, has a Parameters collection. Parameters can have a direction of IN, INOUT, OUT or RETURN

In oracle the parameter declare would look like:

[SIZE=2][COLOR=#0000ff]someCommand[/COLOR][/SIZE][SIZE=2].Parameters.Add("theName", OracleType.VarChar)[/SIZE]
[SIZE=2][SIZE=2]someCommand[SIZE=2].Parameters("theName").Direction = [/SIZE][/SIZE][SIZE=2]ParameterDirection.Input
[SIZE=2]someCommand[SIZE=2].Parameters("theName").Value = [/SIZE][/SIZE][SIZE=2]"Hello world"[/SIZE]
 
[SIZE=2]someCommand[SIZE=2].Parameters("theOut").Direction = [/SIZE][/SIZE][SIZE=2]ParameterDirection.Output[/SIZE]
...
[/SIZE][/SIZE][SIZE=2]

Intellisense will help you do one for whatever db youre using.. it wont be a huge amount different to what we have here.

Use whichever direction is appropriate. Someone once told me the parameter you declare for ReturnValue has to be declared first. I never believed that, but then I use the UI designer for my stored procedure stuff, so I dont write the low level stuff any more


[/SIZE]

 

[cjard]

ps, youre on .net 2.0 - the easiest way for you to do this, is (assuming youre using typed datasets) to go to your tableadapter that does your login stuff (becuase its the most apt place), right click it, and add a query. run through the wizard for a stored procedure (if you use oracle, dont use the wizard, just finish it quickly, and edit the properties of the query directly)

it will query the database and find out the input and output types, sizes etc for you..


all you do to call it is create and instance of the tableadapter and say:

ReturnValue mode:
dim i as int = myTA.CountUsernameAndPassword("fred", "passw");

Output parameter mode:
dim i as integer
myTA.CountUsernameAndPassword("fred", "passw", byref i)



it makes things a lot nicer and all the yucky parameter stuff is done automatically, hidden in the dataset designer thereby avoiding cluttering your code with db access stuff

 

[MartinaL]

Works perfectly, as always you are a fantastic help!

Is there some way to store the username used to login in a session variable or something to save in tables to show the last person who edited a record?

I already have the column in the tables and the sp's (SQL Server) but at the moment it is sending in Null, can I set a variable when the person successfully logs in that will last the whole time they are in the system?

 

[cjard]

I work in oracle, and i'd do this on a trigger on the actual table.

Using a few function calls I can find out the username of the current person executing any sql..

SELECT sys_context('USERENV', 'OS_USER') from dual

returns my windows logon name. If I attached a trigger to a table so that before insert or update of any row, i can do this:

TRIGGER trg_tablename_audit_trail
BEFORE INSERT OR UPDATE ON tablename
FOR EACH ROW
DECLARE
tempVariable varchar2(16);
BEGIN
 
   SELECT sys_context('USERENV', 'OS_USER') INTO tempVariable FROM DUAL;
 
   --this updates the new row going in (its not in yet) automatically
   :new.audit_trail := tempVariable;
 
END trg_tablename_audit_trail;

While youre not working in oracle, other databases that support stored procedures also support triggers such as this. You can drop some questions in a relevant forum and find out ways to interrogate for the user info at the server side?.