Visual Studio .NET - Datagrid Difficult: How does one bind the followi

Asked By matt cupryk on 02-Oct-04 01:59 PM
Say I have the following table.


CREATE TABLE TBUSERS (
  USERID    NUMBER(10)     NOT NULL,
  USERNAME  VARCHAR2(32)   NOT NULL,
  PASSWD    VARCHAR2(40)   NOT NULL,
  STOREID   NUMBER(10)     NOT NULL,
  FULLNAME  VARCHAR2(50)   NOT NULL,
  ACCESSLEVELID NUMBER(10) NOT NULL,
  REPORTTOID NUMBER(10)    NOT NULL);


I want to bind the the ReportToID, such that the ReportToID is the USERID of the person that user
reports to.

USERID  REPORTTOID   USERNAME
   41             42                 Mathieu
   42             41                 Fred
   43             42                 Joel

So in my data grid I want to display For Report To the USERNAME
That is for examples

USERID USERNAME REPORTTO
41           MATHIEU      FRED
42           FRED           MATHIEU
43           JOEL            FRED

Now in my datagrid I get the following:
USERID USERNAME REPORTTO
41           MATHIEU      MATHIEU
42           FRED           FRED
43           JOEL            JOEL

I think it has something to do with my query statement for bind.

//************************************************************
// Get Stores and populate dataset with UserName
private DataSet BindUserNames()
{
   // Populate the ddlDataSet
   OdbcConnection myConnection =  new OdbcConnection("DSN=POS;UID=system;PWD=system");
   myConnection.Open();
   const string strSQLDDL = @"SELECT USERID, REPORTTOID, USERNAME From TBUSERS ORDER BY USERNAME ASC";
   OdbcDataAdapter myDataAdapter = new OdbcDataAdapter(strSQLDDL, myConnection);
   ddlDataSet3.Clear();
   myDataAdapter.Fill(ddlDataSet3, "ddlReportTos");
   myDataAdapter.Dispose();
   myDataAdapter = null;
   myConnection.Dispose();
   myConnection.Close();
   return ddlDataSet3;
}
//************************************************************

with the main bind


//****************************************************************
private void BindData()
{
	OdbcConnection myConnection =  new OdbcConnection("DSN=POS;UID=system;PWD=system");
    myConnection.Open();
   	
    // get records from the table
    string commandString = @"SELECT t.USERID, p.STORE, pe.ACCESSLEVEL, t.USERNAME, t.PASSWD, t.FULLNAME, t.REPORTTOID From TBUSERS t LEFT JOIN TBSTORES p ON t.STOREID = p.STOREID LEFT JOIN TBACCESSLEVELS pe ON t.ACCESSLEVELID = pe.ACCESSLEVELID ORDER BY USERNAME ASC";	
    // Set the datagrid's datasource to the datareader and databind
    // Create a OdbcCommand object and assign the connection connect to the database 
    OdbcCommand command = myConnection.CreateCommand( ); 
    command.CommandText = commandString;
    command.Connection = myConnection;
	// Create the Reader and bind it to the datagrid
    OdbcDataReader reader =  command.ExecuteReader(CommandBehavior.CloseConnection);
    DataGridUserManagementID.DataSource=reader;
    DataGridUserManagementID.DataBind();
	reader.Close();
	reader=null; 
    myConnection.Dispose();
	myConnection.Close();
	     
}
//****************************************************************

Easiest way to do this? User-Defined Function - Asked By Robbe Morris on 02-Oct-04 02:41 PM

Open up SQL Server Enterprise Manager, expand your database, right click on User-Defined Functions and select New User-Defined Function.  Paste this code in and save it.
Refresh the list of User-Defined Functions.  Right click your new function and set permissions to it.  

CREATE  function dbo.GetFullName
(
 @UserID number(10)
)
returns varchar(00)
as
BEGIN

declare @MyName varchar(100)

 select @MyName = FullName from TBUsers where UserID = @UserID

return @MyName
END

Here's how to use it in your sql statement or stored procedure:

 select UserID,ReportToID,FullName,dbo.GetFullName(ReportToID) as ReportTo
   from tbUSERS
   where blah,blah,blah
  order by 4 asc  /* sorts by report to name in ascending order */


By the way, FullName only having 50 bytes is not very big.  Are you sure that is enough?  Unless you need certain types of numeric precision for your userid, you'll probably want to use an "int" datatype instead.

Here's another related article that might prove helpful:

http://www.eggheadcafe.com/articles/20030627.asp

This is very very nice but - Asked By matt cupryk on 02-Oct-04 04:14 PM

IS there a way I can do this in one sql statement?

Yes, but why? - Asked By Robbe Morris on 02-Oct-04 04:45 PM

You use a subquery.  Here's a sample from books online.  But, this type of function is likely to be used elsewhere for other displays or reports.  It is good design practice to write code once and use it over and over again.  If you ever need to change it, you change it one place.  That is precisely what User-Defined Functions are for.  If you are going to do something, do it right the first time.

USE pubs
SELECT title, price,
(SELECT AVG(price) FROM titles) AS average,
price-(SELECT AVG(price) FROM titles) AS difference
FROM titles
WHERE type='popular_comp'
No - Asked By matt cupryk on 02-Oct-04 04:59 PM
It is not that it is that I am working in oracle. I don't even have time to go to the washroom. That information u gave me before I will use on oracle I have to learn how to construct functions. Right now I am just doing some test.

Select USERID, USERNAME, REPORTTOID from TBUSERS
THEN

FROM 

I must then return the row username that the reporttoid report to.
So I can them put it in a datagrid. For now. Until I understand better.
I'm sorry Oracle also has User-Defined Function - Asked By Robbe Morris on 02-Oct-04 05:03 PM
I thought I'd remembered you working with SQL Server.  My bad.  Oracle has the exact same functionality with extremely similar syntax.  It is a breeze.  Here's a good little sample:

http://www.fors.com/orasupp/d2k/plsql/16346_1.HTM
Ok Robbe, I need help on this oracle - Asked By matt cupryk on 02-Oct-04 05:23 PM
This is the oracle I am trying to build? Need some help here.
CREATE FUNCTION GETUSERNAME (USERID IN NUMBER)
BEGIN 

declare @MyName varchar(100) 

SELECT USERNAME := MyName  
  FROM TBUSERS 
  WHERE UserID =: UserID 

  RETURN USERNAME;
END;


This is the sql
=============================
Ok CREATE function dbo.GetUserName 
( 
@UserID number(10) 
) 
returns varchar(00) 
as 
BEGIN 

declare @MyName varchar(100) 

select @MyName = UserName from TBUsers where UserID = @UserID 

return @MyName 
END
Wouldn't it be this? - Asked By Robbe Morris on 02-Oct-04 05:39 PM
I haven't written any Oracle UDF's since 2000 so my syntax is a little rusty.  But based on the syntax in the sample link I gave, I think it should be this:


CREATE OR REPLACE FUNCTION GETUSERNAME (USERID_IN IN NUMBER) 
  RETURN VARCHAR2(50) 
  IS
    BEGIN

      DECLARE MyName varchar2(50);

      select UserName into MyName
        from TBUSERS
        where UserID = USERID_IN

      RETURN MyName;

    END;

 This site looks like a good place to go:

 http://www.techonthenet.com/oracle/index.htm
Warning: Function created with compilation errors - Asked By matt cupryk on 02-Oct-04 05:55 PM
I have some compilation errors? What should I do?

CREATE OR REPLACE FUNCTION GETUSERNAME (USERID_IN IN NUMBER) 
  RETURN VARCHAR2(32) 
  IS 
    BEGIN 

      DECLARE MyName VARCHAR2(32); 

      SELECT USERNAME INTO MyName 
        FROM TBUSERS 
        WHERE USERID = USERID_IN 

      RETURN MyName; 

    END; 


Warning: Function created with compilation errors
Yoo hoo! - Asked By Dexter Dotnetsky on 02-Oct-04 06:08 PM
myDataAdapter.Dispose(); 
// following line performs no useful function, Dispose is quite sufficient:
   myDataAdapter = null; 
// In next 2 lines, Dispose removes connection string info, Close is better.
//Use one or the other, but not both...
        //  myConnection.Dispose(); 
   myConnection.Close();
Function created with compilation errors - Asked By matt cupryk on 02-Oct-04 06:25 PM
Checking the compilation errors.

CREATE OR REPLACE FUNCTION GETUSERNAME (USERID_IN IN NUMBER) 
  RETURN VARCHAR2(32) 
  IS 
    BEGIN 
      DECLARE MyName VARCHAR2(32); 

      SELECT USERNAME INTO MyName 
        FROM TBUSERS 
        WHERE USERID = USERID_IN 

      RETURN MyName; 
   END;

How do I determine Warning: Function created with compilation errors.

I am logged in as system?
Right, the Oracle syntax is off - Asked By Robbe Morris on 02-Oct-04 09:18 PM
Like I said, my syntax is rusty.  Just do a search on google for:

"CREATE OR REPLACE Function" and Oracle and VARCHAR and INTO

I'm sure you'll find the exact syntax that you need.

Try this instead for now.  Oracle may require you to put the DECLARE
first.

CREATE OR REPLACE FUNCTION GETUSERNAME (USERID_IN IN NUMBER) 
  RETURN VARCHAR2(32) 
  IS 
    DECLARE 
               MyName VARCHAR2(32); 
    BEGIN 
      
      SELECT USERNAME INTO MyName 
        FROM TBUSERS 
        WHERE USERID = USERID_IN 

      RETURN MyName; 
   END;