Click here to Skip to main content
14,875,773 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Dear friends,

I have the following code which will retrieve the data to each rows in zigzag manner. Right now it is actually displaying only the last record from the database in each row.

VB
Dim intRows As Integer = TextBox2.Text
        Dim intColumns As Integer = TextBox3.Text
        Dim intValue As Integer = 0
        Dim intChkValue As Integer = 0
        DataGridView2.RowCount = intRows
        DataGridView2.ColumnCount = intColumns

        Dim intTotalStrength As Integer = 10
        Dim blnOdd As [Boolean], blnEven As [Boolean]
        Dim DTResult As New DataTable()
        For i As Integer = 0 To intColumns - 1
            'dataGridView1.Columns.Add = i;

            DTResult.Columns.Add("Column : " & i)
        Next



        For row As Integer = 0 To intRows - 1
            Dim drNew As DataRow = DTResult.NewRow()
            For col As Integer = 0 To intColumns - 1
                If col Mod 2 = 0 Then
                    blnOdd = True
                    blnEven = False
                Else
                    blnEven = True
                    blnOdd = False
                End If

                If row = 0 Then
                    If col = 0 Then
                        drNew(col) = row + 1
                        intValue = row + 1

                        'mycode
                        Dim s1 As String
                        Dim sk As String
                        Dim ik As Integer
                        s1 = "SELECT distinct TOP " + TextBox2.Text + " nominal.regno FROM course INNER JOIN nominal ON course.courseno = nominal.courseno INNER JOIN timetable2 ON course.coursename = timetable2.course where timetable2.dateofexam='" & DateTimePicker1.Value.Date & "' and timetable2.session='" & ComboBox2.Text & "' order by nominal.regno asc "
                        Dim cmd1 As SqlCommand
                        conn1 = New SqlConnection(Common.GetConnectionString)
                        cmd1 = New SqlCommand(s1, conn1)
                        Dim dr1 As SqlDataReader
                        If conn1.State = ConnectionState.Closed Then
                            conn1.Open()
                        Else
                            conn1.Close()
                        End If
                        dr1 = cmd1.ExecuteReader()

                        While dr1.Read()

                            DataGridView2.Rows(row).Cells(col).Value = dr1.GetValue(0)

                        End While
                        cmd1.Dispose()

                        'mycode

                    Else
                        If blnEven Then
                            intValue += intRows * 2 - 1
                        Else
                            intValue += 1
                        End If

                        drNew(col) = intValue
                        'mycode
                        Dim s1 As String
                        Dim sk As String
                        Dim ik As Integer
                        s1 = "SELECT distinct TOP " + TextBox2.Text + " nominal.regno FROM course INNER JOIN nominal ON course.courseno = nominal.courseno INNER JOIN timetable2 ON course.coursename = timetable2.course where timetable2.dateofexam='" & DateTimePicker1.Value.Date & "' and timetable2.session='" & ComboBox2.Text & "' order by nominal.regno desc "
                        Dim cmd1 As SqlCommand
                        conn1 = New SqlConnection(Common.GetConnectionString)
                        cmd1 = New SqlCommand(s1, conn1)
                        Dim dr1 As SqlDataReader
                        If conn1.State = ConnectionState.Closed Then
                            conn1.Open()
                        Else
                            conn1.Close()
                        End If
                        dr1 = cmd1.ExecuteReader()

                        While dr1.Read()

                            DataGridView2.Rows(row).Cells(col).Value = dr1.GetValue(0)

                        End While
                        cmd1.Dispose()

                        'mycode
                    End If
                Else
                    If blnOdd Then
                        intChkValue = Convert.ToInt32((DTResult.Rows(row - 1)(col).ToString())) + 1
                        'mycode
                        Dim s1 As String
                        Dim sk As String
                        Dim ik As Integer
                        s1 = "SELECT distinct TOP " + TextBox2.Text + " nominal.regno FROM course INNER JOIN nominal ON course.courseno = nominal.courseno INNER JOIN timetable2 ON course.coursename = timetable2.course where timetable2.dateofexam='" & DateTimePicker1.Value.Date & "' and timetable2.session='" & ComboBox2.Text & "' order by nominal.regno asc"
                        Dim cmd1 As SqlCommand
                        conn1 = New SqlConnection(Common.GetConnectionString)
                        cmd1 = New SqlCommand(s1, conn1)
                        Dim dr1 As SqlDataReader
                        If conn1.State = ConnectionState.Closed Then
                            conn1.Open()
                        Else
                            conn1.Close()
                        End If
                        dr1 = cmd1.ExecuteReader()

                        While dr1.Read()

                            DataGridView2.Rows(row).Cells(col).Value = dr1.GetValue(0)

                        End While
                        cmd1.Dispose()

                        'mycode
                    Else

                        intChkValue = Convert.ToInt32((DTResult.Rows(row - 1)(col).ToString())) - 1
                        '                        DataGridView2.Rows(row).Cells(col).Value = intChkValue
                        'mycode
                        Dim s1 As String
                        Dim sk As String
                        Dim ik As Integer
                        s1 = "SELECT distinct TOP " + TextBox2.Text + " nominal.regno FROM course INNER JOIN nominal ON course.courseno = nominal.courseno INNER JOIN timetable2 ON course.coursename = timetable2.course where timetable2.dateofexam='" & DateTimePicker1.Value.Date & "' and timetable2.session='" & ComboBox2.Text & "' order by nominal.regno desc "
                        Dim cmd1 As SqlCommand
                        conn1 = New SqlConnection(Common.GetConnectionString)
                        cmd1 = New SqlCommand(s1, conn1)
                        Dim dr1 As SqlDataReader
                        If conn1.State = ConnectionState.Closed Then
                            conn1.Open()
                        Else
                            conn1.Close()
                        End If
                        dr1 = cmd1.ExecuteReader()

                        While dr1.Read()

                            DataGridView2.Rows(row).Cells(col).Value = dr1.GetValue(0)

                        End While
                        cmd1.Dispose()

                        'mycode
                    End If
                    drNew(col) = intChkValue
                End If
            Next
            DTResult.Rows.Add(drNew)
        Next

        For row As Integer = 0 To intRows - 1
            For col As Integer = 0 To intColumns - 1
                If DTResult.Rows(row)(col).ToString() <> "" AndAlso Convert.ToInt32(DTResult.Rows(row)(col).ToString()) > intTotalStrength Then

                    DTResult.Rows(row)(col) = ""
                End If
            Next
        Next


Can you please help me to show all the records?

[Edit - Changed code block to vb formatting]
Posted
Updated 30-Mar-12 11:27am
v2
Comments
Shahin Khorshidnia 30-Mar-12 18:00pm
   
I think losmac's answer is ok.
But about your code:
Do not create SQL Commands with direct user entered values. It's very unsecured. (Injection Risk)
Use stored procedure or Use Command parameters.
Maciej Los 31-Mar-12 14:49pm
   
I agree with you.

1 solution

There are 2 reasons why you do not have all records:
1) becouse of TOP[^] instruction,
2) becouse of DISTINCT instruction.

You need to understand SELECT[^] instruction.

Let say, we have table Cars with 10 records and Models, where you have all models for each car.
To get all non-duplicate cars in Models table:
SQL
SELECT DISTINCT M.CarID, C.Name 
FROM Models AS M LEFT JOIN Cars AS C ON M.CarId = C.ID
ORDER BY M.CarID

To get all cars (with duplicates) in Models table you need to remove DISTINCT instruction.

To get first 10 records in Models table:
SQL
SELECT TOP(10) C.ID, C.Name
FROM Models AS M LEFT JOIN Cars AS C ON M.CarId = C.ID
ORDER BY M.CarID

To get all records in Models table, you need to remove TOP(10) instruction.
   
v2
Comments
Shahin Khorshidnia 30-Mar-12 17:54pm
   
+5
Maciej Los 31-Mar-12 14:48pm
   
Thank you ;)

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