stephenlecomptejr
asked on
How to get datagridview in C sharp to display a relational sql query from SQL Server connection?
OK. New to C Sharp and data connections. Still trying to forget ADO and absorb ADO.NET and its table adapters, datasets, etc. Many training samples provided online have one table (if lucky maybe two) but according to the following SQL statement I have three that have a relationship between such and also has expressions - like Database Name parses out the fieldname DBName and gives only the file name and not the entire path.
So how do I tie such to a datagridview using Visual Studio 2010 C Sharp?
I did make an attempt using a data reader as the first code snippet show - but do not know how to take my datagridview (called dgvLogger) and apply each column as getting the data.
I did also try to do the dataset via the wizard as the image shows but after I go to the dsLogger dataset and make that the datasource for the datagridview - when I go to the datapropertyname no fields show up like they are supposed to?
Any help is appreciated!!!
So how do I tie such to a datagridview using Visual Studio 2010 C Sharp?
I did make an attempt using a data reader as the first code snippet show - but do not know how to take my datagridview (called dgvLogger) and apply each column as getting the data.
I did also try to do the dataset via the wizard as the image shows but after I go to the dsLogger dataset and make that the datasource for the datagridview - when I go to the datapropertyname no fields show up like they are supposed to?
Any help is appreciated!!!
string sSQL = "";
// had to adjust CHARINDEX('\\'
sSQL = "SELECT REPLACE(REVERSE(LEFT(REVERSE(DBName),CHARINDEX('\\', REVERSE(DBName),1)-1)), '.accdb', '') AS [Database Name], Access_USERS.[Full Name] as [User], Access_USERS.EXt as Ext, Access_USERS.CPU, substring(convert(varchar(20), Access_LOGGER.InDate, 9), 13, 5) as [Time In]";
sSQL = sSQL + " FROM (Access_LOGGER INNER JOIN Access_DBLIST ON Access_LOGGER.fDBID = Access_DBLIST.ID) INNER JOIN Access_USERS ON Access_LOGGER.fUserID = Access_USERS.ID";
sSQL = sSQL + " WHERE Access_USERS.WinSign<>'slecompte' AND Access_LOGGER.OutDate Is Null";
sSQL = sSQL + " ORDER BY [Time In]";
// do not put \\EC-SQL front of
string conn = "Data Source=EC-SQL;Initial Catalog=NEWEQ;User Id=NEWEQ;Password=n3w3@!;";
using (SqlConnection connection =
new SqlConnection(conn))
{
SqlCommand command =
new SqlCommand(sSQL, connection);
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
MessageBox.Show(String.Format("{0}, {1}, {2}, {3}, {4}",
reader[0], reader[1], reader[2], reader[3], reader[4]));
}
reader.Close();
}
SELECT REPLACE(REVERSE(LEFT(REVERSE(DBName),CHARINDEX('\\', REVERSE(DBName),1)-1)), '.accdb', '') AS [Database Name], Access_USERS.[Full Name] as [User], Access_USERS.EXt as Ext, Access_USERS.CPU, substring(convert(varchar(20), Access_LOGGER.InDate, 9), 13, 5) as [Time In]
FROM (Access_LOGGER INNER JOIN Access_DBLIST ON Access_LOGGER.fDBID = Access_DBLIST.ID) INNER JOIN Access_USERS ON Access_LOGGER.fUserID = Access_USERS.ID
WHERE Access_USERS.WinSign<>'slecompte' AND Access_LOGGER.OutDate Is Null
ORDER BY [Time In]
logger.png
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks again!
ASKER
Thanks, mez4343.