We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

How to get datagridview in C sharp to display a relational sql query from SQL Server connection?

Medium Priority
812 Views
Last Modified: 2012-05-11
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!!!
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();

                
            }

Open in new window

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]

Open in new window

logger.png
Comment
Watch Question

Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
This is really perfect.  Please allow me to do tomorrow morning to try.

Thanks, mez4343.

Author

Commented:
Thanks again!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.