Binding 2 diffrent query in 1 DataGrid

S

shahab

Junior Contributor
Joined
Aug 14, 2003
Messages
206
Location
Iran(Middle East)
Hi there,
I have two tables.In the first one there are userAccounts and in the second one users urls.(Ms Sql server 2000)
Now I want to show both the Accounts Info and url for a user in a datagrid.(C# Please)

For one DataBinding I use this:
-----------------------------------------------------------
string selectCmd = "SELECT username,password,role,email,studentcode,SignDate FROM tblUserAccounts WHERE username=@Username";


SqlDataAdapter myCommand = new SqlDataAdapter(selectCmd, objConn);

myCommand.SelectCommand.Parameters.Add(new SqlParameter("@Username", SqlDbType.NVarChar, 50));
myCommand.SelectCommand.Parameters["@Username"].Value ="m1";
//Session["username"].ToString();

DataSet ds = new DataSet();
myCommand.Fill(ds, "tblUserAccounts");

MyDataGrid.DataSource= ds.Tables["tblUserAccounts"].DefaultView;
MyDataGrid.DataBind();
-------------------------------------------------------------------------
1-What should I do to show both of them?
2-Can i use join?How?
+" UNION SELECT * FROM tblUrl WHERE username=@Username";
3-So many thanks!:-\
 
I'd try something like this:
Code: 
SELECT
 userAct.username,
 userAct.password,
 userAct.role,
 userAct.email,
 userAct.studentcode,
 userAct.SignDate,
 tblUrl.URLValue
FROM 
 tblUserAccounts as userAct
 INNER JOIN
  tblUrl as URLs
 ON
  URLs.username = userAct.username
WHERE 
 username = @Username
 
Dear friend,
I changed your code to this:
-------------------------------------------------
Code: 
string selectCmd = "SELECT"+
 " userAct.username,"+
 "userAct.password,"+
 "userAct.role,"+
 "userAct.email,"+
 "userAct.studentcode,"+
 "userAct.SignDate,"+
 "URLs.username,"+
 "URLs.url"+
 " FROM"+ 
 " tblUserAccounts as userAct"+
 " INNER JOIN"+
 " tblUrl as URLs"+
 " ON"+
 " URLs.username = userAct.username"+
 " WHERE "+
 "username = @Username";
But this error has been showed:
  >>>> Ambiguous column name 'username'.
*********************************************
And this:
" userAct.username,"+
 "userAct.password,"+
 "userAct.role,"+
 "userAct.email,"+
 "userAct.studentcode,"+
 "userAct.SignDate,"+
 //"tblurl.username,"+
 "URLs.url,"+
 " URLs.username"+
// "tblUrl.url"+
 " FROM"+ 
 " tblUserAccounts as userAct"+
 " INNER JOIN"+
 " tblUrl as URLs"+
 " ON"+
 " URLs.username = userAct.username"+
 " WHERE "+
 "username = @Username";
>>>  Ambiguous column name 'username'.  
----------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblUserAccounts]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblUserAccounts]
GO

CREATE TABLE [dbo].[tblUserAccounts] (
	[UserId] [int] IDENTITY (1, 1) NOT NULL ,
	[Username] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Password] [binary] (16) NOT NULL ,
	[role] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[email] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[StudentCode] [int] NULL ,
	[SignDate] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

--------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblUrl]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblUrl]
GO

CREATE TABLE [dbo].[tblUrl] (
	[username] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[url] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

---------------------------------------------------------------------------
Important:For a username there are many urls!
So what should I do?
Thanks
 
Last edited:
ok i think that this might work:

the sql that mocella provides works fine, but just change where it says

"where username = @username"
to
"where userAct.username = @username"

cheers

foz
 
Oh guy,this workes well
string selectCmd =
"SELECT"+
" userAct.username,"+
"userAct.password,"+
"userAct.role,"+
"userAct.email,"+
"userAct.studentcode,"+
"userAct.SignDate,"+
"URLs.url,"+
" URLs.username"+
//"tblurl.username,"+
// "tblUrl.url"+
" FROM"+
" tblUserAccounts as userAct"+
" INNER JOIN"+
" tblUrl as URLs"+
" ON"+
" URLs.username = userAct.username"+
" WHERE "+
"userAct.username = @Username";
but this is not what i sm looking for because i can do the same with this simple code!!!!!:>>>
//"SELECT url from tblurl where username=@Username";

--------------------------------------------------------------------
I want to see the tblUserAccounts info and in front of each info(Complete from tblAccounts) URLs in <<<ONE>>> DataGrid.
(We can put the URLs in Hyperlinks!)
In fact I could have 2 DG but I like to learn in one DG!
 
i'm a little confused. are you trying to get all the info from the first table, plus the url field from the second table.

if so the following query returns all the information.

select *from tblUserAccounts inner join tblAccounts on tblUserAccounts.username = tblAccounts.username where tblUserAccounts.username = @username
 
Back
Top