hello all!

I am currently working on porting over an old website into DNN however i have run into a snag that i cannot figure out for the life of me. the orriginal site stored a session variables CompanyID and UKey as depicted below

ASP Page

Set rs = cn.execute("EXEC ProcessLogon '" & Request.form("UserID") & "','" & request.form("Password") & "'")

If rs.eof = False then
    Session("CompanyID") = rs("CompanyID")
    Session("UKey") = rs("UKey")
end if

ProcessLogon Procedure

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[ProcessLogon]
@UserID varchar(20),
@Password varchar(20)

 AS
SELECT tblUser.UserID, tblUser.Password, tblUser.CompanyID, tblUser.UKey, tblUser.Active, tblUser.AccessLevel, tblCompany.Active  FROM tblUser INNER JOIN tblCompany ON tblUser.CompanyID = tblCompany.CompanyID WHERE (tblUser.UserID = @UserID) AND (tblUser.Password = @Password) AND (tblUser.Active = 1) AND (tblCompany.Active = 1)
if @@rowcount = 0
   begin
       SELECT Access = 0
   end
ELSE
  begin
    SELECT Access = 1
  end

My Delema is that i need to encorporat a SQL stored procedure that ties the existing CompanyID field to the appropriate user. so when a user places and order into the portal there specific company ID is tied to that order.  

I have already created a CompanyID field in the DNN Extended Profile properties. (dbo.userprofile.propertydefinitionID = 40).

i am in no way an expert with SQL but here are a couple of Procedures i have been messing with. but of course to no avail.

Procedure 1

Select CO.CompanyName, UP.PropertyValue, CO.CompanyID
From stcil.dbo.UserProfile UP inner join weborder.dbo.tblcompany CO
on co.companyid = up.Propertyvalue
Where UP.PropertyDefinitionID = 40

Procedure 2

BEGIN
    SELECT     US.Username, PR.PropertyValue
    FROM       stcil.dbo.Users US INNER JOIN stcil.dbo.UserProfile PR
    ON         US.UserID = PR.UserID
WHERE     (PR.PropertyDefinitionID = 40) and (PR.PropertyValue = @CompanyID)

Procedure 3

ALTER PROCEDURE [dbo].[CAH_CompanyID]
@UserID int,
@CompanyID int

AS
BEGIN
    SELECT     PR.PropertyValue
    FROM       stcil.dbo.Users US INNER JOIN stcil.dbo.UserProfile PR
    ON         US.UserID = PR.UserID inner join Weborder.dbo.tblCompany CO
    on         PR.PropertyValue = CO.CompanyName
WHERE     (US.UserID = @UserID) and (pr.PropertyValue = @CompanyID)

END

any help that anyone could give would be more than appriciated.

PS. Sorry ahead of time if this makes no sense.

You can use session variables with DNN -you just have to remember to use them carefully and account for web farm scenarios (if this is applicable in your case)...
The UserID of the current user (as well as username if memory serves me right) is already available to you from your module page (make sure you inherit from PortalModuleBase). Ping me if you need help figuring this out but there are methods like UserID which you should be able to reference from within your module page.

You can take a look at any of the core modules for an example. Basically make sure your Module inherits from PortalModuleBase like this

 Public MustInherit Class XXXXXModule
      Inherits Entities.Modules.PortalModuleBase
 

Once you do this, you have access to the UserInfo object - This object is for the currently logged in user. If you look at the properties of this object you should be able to get things like UserID, UserName, etc.

Pretty easy - if you still need help, take a look at the feedback module which uses the UserInfo object.
Sanjay