Question about DataViews

[Beringer]

I am having a problem referencing data from a data view using a SQL query
that utilizes the same table twice using aliases for the tables as shown
below:

string command = "SELECT User1.Name, User2.Name FROM myTable " +
"JOIN [Users] AS User1 ON User1.ID = myTable.User1ID " +
"JOIN [Users] AS User2 ON User2.ID = myTable.User2ID " +
"WHERE...";

I perform the query and fill a dataset successfully! I then get a dataview
and access the data as follows:
DataView dv = dataSet.Tables[0].DefaultView
DataRowView drv = dv[0];
string user1Name = (string)drv["User1.Name"];
string user2Name = (string)drv["User2.Name"];

When this code is executed I get a runtime exception that says:
"User1.Name is neither a DataColumn or DataRelation in table myTable."

Can someone tell me what is wrong here? How do I reference the the
different names?
Thank you in advance,
Eric

 

[Sijin Joseph]

It might be that the columns are not named User1.Name and User2.Name, try
accessing the values by index

i.e.
string user1Name = (string)drv[0];
string user2Name = (string)drv[1];

If that works then you need to determine what is the correct name of the
columns, you can set a breakpoint and get that from the locals window, or
get it by looking at the DataColumn of the DataSet.

 

[Guest]

Hi,

Why don't u use alias names for the different columns. This will solve the
problem.

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

I think that the problem reside in what you get in your app from your SQL
server, try to run the same query in Query analyser and see the name of the
columns, I'm 100% sure you will not see User1.Name on it, but just an auto
generate name, the same thing happen in your app.
Solution:
1- reference the columns by index
string user1Name = drv[ 0].ToString();

2- set the name of the columns frmo code using ColumnName:
dataSet.Tables[0].Columns[0].ColumnName = "User1.Name";
string user1Name = drv[ "User1.Name"].ToString();

3- set the name of the columns in the query:

SELECT User1.Name AS "User1.Name", User2.Name AS "User2.Name"


Btw, I would advice to always use ToString() instead of a cast


cheers,