Click here to Skip to main content
14,882,317 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi guys
I have a form with a custom datagridview.
With this code I tried to load data on it, but not correctly.

The sql server database has three columns

Improve---Status---Idate
Data1-------1------1/6/2017
Data1-------1------2/6/2017
Data1-------1------3/6/2017
Data2-------1------1/6/2017
Data2-------1------2/6/2017
Data2-------1------3/6/2017

with this code the result is

--------------Mon Tue Wen
Improve-------1----2---3

Data1 ---------1----1---1

Data2 is not shown in Datatgridview

The datagridview has columns days of Month.

What am i doing wrong?

What I have tried:

VB
Private Sub fill_plan()
        dataGridView1.Rows.Clear()
        Try
            Dim i As Integer = 0
            Dim improve As String = String.Empty
            Dim query As String = "SELECT Improve,Status,Idate FROM Improves"
            cmd = New SqlCommand(query, con)
            myDR = cmd.ExecuteReader
            If myDR.HasRows Then
                While myDR.Read
                    Dim myday As Integer = DatePart(DateInterval.Day, myDR.GetValue(myDR.GetOrdinal("Idate")))
                    If Not (myDR.GetString(myDR.GetOrdinal("Improve")) = improve) Then
                        dataGridView1.Rows.Add()
                        dataGridView1.Rows(i).Cells(0).Value = myDR.GetString(myDR.GetOrdinal("Improve"))
                        improve = myDR.GetString(myDR.GetOrdinal("Improve"))
                        dataGridView1.Rows(i).Cells(myday).Value = myDR.GetInt32(myDR.GetOrdinal("Status"))
                    Else
                        dataGridView1.Rows(i).Cells(myday).Value = myDR.GetInt32(myDR.GetOrdinal("Status"))
                    End If
                End While
            End If
            myDR.Close()
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical, "error")
        End Try
    End Sub
<pre>
Posted
Updated 5-Jun-17 7:33am
v3

1 solution

All you need to do is to pivot data on sql server site:
Using PIVOT and UNPIVOT | Microsoft Docs[^]
Pivot tables in SQL Server. A simple sample. – Tips and tricks from a Developer Support perspective.[^]
Pivot Transformation[^]
then load it into datatable and bind with datagridview.

Good luck!
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900