VS: Crosstab query syntax error for DataGridView

Question

question

VAer-4038 asked Jan 5, '21 VAer-4038 commented Jan 6, '21

VS: Crosstab query syntax error for DataGridView

I would like to display Access crosstab query in Visual Studio DataGridView (If I don't use crosstab query, simply a table query, it works, so other parts of code is fine).

Below crosstab query code is simply copied from Access SQL View after I set up crosstab query (via Access crosstab query wizard), and I don't quite understand the SQL Inner Join statement. But it returns what I want (use date as column heading name).

Below is the code. Apparently, it needs to be modified in order for compiling.

Here is Access file on Google drive (with space in-between in case of being blocked): https://drive. google. com/
drive/folders/1ihZ6OW0l5oJhlPgxWxJCRkJI5P4DEMt0?usp=sharing
1ihZ6OW0l5oJhlPgxWxJCRkJI5P4DEMt0

How to modify below SQL statement?

Thanks.

              private void btnCrosstabQuery_Click(object sender, EventArgs e)
              {
            
                  OdbcConnection Cn = new OdbcConnection(GlobalVariables.DatabaseConnectionString);
                  OdbcDataAdapter adapt;
                  DataTable dt;
                  Cn.Open();
                  //The code to query table Employee works fine
                  //adapt = new OdbcDataAdapter("SELECT * from Employee", Cn);
    
                adapt = new OdbcDataAdapter("TRANSFORM First(WorkNote.Note) AS FirstOfNote
 SELECT Teams.Username, Employee.FirstName, Employee.LastName
 FROM Teams INNER JOIN (Employee INNER JOIN WorkNote ON Employee.Username = WorkNote.Username) ON Teams.Username = WorkNote.Username
 WHERE (((Teams.TeamName)="team1") AND ((WorkNote.WorkDate)<Now() And (WorkNote.WorkDate)>=Now()-30))
 GROUP BY Teams.Username, Employee.FirstName, Employee.LastName
 ORDER BY Teams.Username
 PIVOT Format([WorkNote.WorkDate],"yyyy-mm-dd ddd");", Cn);
            
    
            
            
                  dt = new DataTable();
                  adapt.Fill(dt);
                  dgvUsers.DataSource = dt;
                  Cn.Close();
              }

sql-server-general windows-forms

qscreenshot.jpg (90.0 KiB)

access-tables.jpg (74.6 KiB)

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 1 · 2021-01-06T02:25:46.767Z

TimonYang-MSFT answered Jan 6, '21 VAer-4038 commented Jan 6, '21

Did you forget to add a space at the beginning or end of the line when concatenating strings?
That would cause certain words to be connected together, like this:

 TRANSFORM First(WorkNote.Note) AS FirstOfNoteSELECT Teams.Username, Employee.FirstName, Employee.LastNameFROM Teams INNER JOIN(Employee INNER JOIN WorkNote ON Employee.Username = WorkNote.Username) ON Teams.Username = WorkNote.UsernameWHERE(((Teams.TeamName) = 'team1') AND((WorkNote.WorkDate) < Now() And(WorkNote.WorkDate) >= Now() - 30))GROUP BY Teams.Username, Employee.FirstName, Employee.LastNameORDER BY Teams.UsernamePIVOT Format([WorkNote.WorkDate],'yyyy-mm-dd ddd')'

FirstOfNoteSELECT, UsernameWHERE, etc. are connected together.
Adding spaces should solve the problem:

                 string sqlString = "TRANSFORM First(WorkNote.Note) AS FirstOfNote"+
                                     " SELECT Teams.Username, Employee.FirstName, Employee.LastName"+
                                     " FROM Teams INNER JOIN(Employee INNER JOIN WorkNote ON Employee.Username = WorkNote.Username) ON 
                                          Teams.Username = WorkNote.Username"+
                                     " WHERE(((Teams.TeamName) = 'team1') AND((WorkNote.WorkDate) < Now() And(WorkNote.WorkDate) >= Now() - 
                                          30))"+
                                     " GROUP BY Teams.Username, Employee.FirstName, Employee.LastName"+
                                     " ORDER BY Teams.Username"+
                                     " PIVOT Format([WorkNote.WorkDate],'yyyy-mm-dd ddd')";
                 adapt = new OdbcDataAdapter(sqlString, Cn);

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

1.png (6.4 KiB)

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

VAer-4038 · Jan 06, 2021 at 03:56 AM

Thank you so much!

0 ·

question