Only getting one row from the database?

[Sean04]

Hey,

So i'm trying to get all the rows from my database and put everything on an excel spreadsheet. The problem is, the output in the excel sheet is only showing 1 of the 3 rows i have. I'm not sure why this is happening. The code is kind of long but help would be greatly appreciated!

Imports Microsoft.Office.Interop
Imports System.Data.SqlClient
Imports System.IO

Public Class GenerateReports
    Public Shared Sub GetAll()

        Dim objExcel As Excel.Application = DirectCast(CreateObject("Excel.Application"), Excel.Application)
        Dim bkWorkBook As Excel.Workbook = DirectCast(objExcel.Workbooks.Add(), Excel.Workbook)
        Dim shWorkSheet As Excel.Worksheet = DirectCast(bkWorkBook.Sheets("Sheet1"), Excel.Worksheet)
        Dim rngRowStart As Excel.Range = DirectCast(shWorkSheet.Cells(1, 1), Excel.Range)
        Dim oPageSetup As Excel.PageSetup = DirectCast(shWorkSheet.PageSetup, Excel.PageSetup)
        Dim oRange As Excel.Range = DirectCast(shWorkSheet.Columns("A:BZ"), Excel.Range)
        Dim criteria As String
        Dim i As Integer = 0
        Dim sSQL As String = String.Empty
        Dim myConnection As String
        Dim ds As New DataSet
        Dim path As String
        criteria = ""
        path = System.Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)

        'set up database connection
        myConnection = "Data Source=DEVCON011\SQLEXPRESS;Initial Catalog=2LT_Tracker;Integrated Security=True"
        Dim objConnection As New SqlConnection(myConnection)

        'querys the database for the columns stated below. the following query retrieves data from more than one table
        sSQL = "SELECT m.StationName, coop.Name, field.Name, lines.LinesOperationCenter, provincial.ProvincialLinesZone, stationtype.StationType, status.Status, cooppriority.Priority, priority.Priority, m.QualityOfDrawing, m.StationkV, m.FeederkV, m.DateOPStartedRevision, m.DateOPCompletedDraftDrawing, m. DateOPVerifiesDrawings,DateSendForFieldVerification, m.DateDrawingCompletedByField, m.DateDrawingSuppliedToBit, m.ReleaseDate, m.CompletionDate, m.ConnectedLoad, m.KMSDone FROM [2LT_Main] m, [2LT_Coop] coop, [2LT_Fields] field, [2LT_LinesOperationCenter] lines, [2LT_ProvincialLineZones] provincial, [2LT_StationType] stationtype, [2LT_Status] status, [2LT_CoopPriority] cooppriority, [2LT_Priority] priority WHERE m.CoopName_ID = coop.CoopName_ID AND m.FieldName_ID = field.FieldName_ID AND m.LinesOperationCenter_ID = lines.LinesOperationCenter_ID AND m.ProvincialLineZone_ID = provincial.ProvincialLineZone_ID AND m.StationType_ID = stationtype.StationType_ID AND m.Status_ID = status.Status_ID AND m.CoopPriority_ID = cooppriority.CoopPriority_ID AND m.Priority_ID = priority.Priority_ID"

       
        Dim da As New System.Data.SqlClient.SqlDataAdapter(sSQL, myConnection)
        da.Fill(ds)
        Dim rowcount As Integer = ds.Tables.Item(0).Rows.Count
        MessageBox.Show(ds.Tables.Item(0).Rows.Count)
        'now, for the display, we need to use the datareader
        Dim command As New SqlCommand(sSQL, objConnection)
        objConnection.Open()
        Dim reader As SqlDataReader = command.ExecuteReader()
        MainForm.Cursor = Cursors.WaitCursor
        MainForm.ProgressBar1.Value = 0
        MainForm.ProgressBar1.Visible = True
        MainForm.lblProgress.Visible = True
        MainForm.ProgressBar1.Maximum = rowcount

        'set up excel page
        With oPageSetup
            .CenterHeader = "&""Arial,Bold""&16User Table List " & Chr(10)
            .PrintTitleRows = "$1:$5"
            .CenterFooter = "Page &P of &N"
            .RightFooter = "&8" & Format(Now, "Long Date") & vbCr & "AUP.NET Ver. " & My.Application.Info.Version.Major & "." & My.Application.Info.Version.Minor
            .FooterMargin = objExcel.InchesToPoints(0.35)
            .HeaderMargin = objExcel.InchesToPoints(0.35)
            .LeftMargin = objExcel.InchesToPoints(0.75)
            .RightMargin = objExcel.InchesToPoints(0.75)
            .TopMargin = objExcel.InchesToPoints(1.0#)
            .BottomMargin = objExcel.InchesToPoints(0.75)
            .FitToPagesWide = 1
            .FitToPagesTall = 10
            .Zoom = False
        End With

        'Set column titles
        shWorkSheet.Range("A1").Value = "......"
        shWorkSheet.Range("B1").Value = "......"
        shWorkSheet.Range("C1").Value = "......"
        shWorkSheet.Range("D1").Value = "......"
        shWorkSheet.Range("E1").Value = "......"
        shWorkSheet.Range("F1").Value = "......"
        shWorkSheet.Range("G1").Value = "......"
        shWorkSheet.Range("H1").Value = "......"
        shWorkSheet.Range("I1").Value = "......"
        shWorkSheet.Range("J1").Value = "......"
        shWorkSheet.Range("K1").Value = "......"
        shWorkSheet.Range("L1").Value = "......"
        shWorkSheet.Range("M1").Value = "......"
        shWorkSheet.Range("N1").Value = "......"
        shWorkSheet.Range("O1").Value = "......"
        shWorkSheet.Range("P1").Value = "......"
        shWorkSheet.Range("Q1").Value = "......"
        shWorkSheet.Range("R1").Value = "......"
        shWorkSheet.Range("S1").Value = "......"
        shWorkSheet.Range("T1").Value = "......"
        shWorkSheet.Range("U1").Value = "......"
        shWorkSheet.Range("V1").Value = "......"

        'Set the headings cell color
        shWorkSheet.Range("A1:V1").Interior.ColorIndex = 3

        'Set row/column features'
        shWorkSheet.Range("A1:V1").Font.Bold = True
        shWorkSheet.Range("A1:V1").Font.Italic = True
        shWorkSheet.Range("A1:V1").Font.Size = 12

        'Set column width
        shWorkSheet.Range("A1:V1").EntireColumn.ColumnWidth = 28
        shWorkSheet.Range("A1:V1").WrapText = True

        'Select column position
        oRange.VerticalAlignment = Excel.Constants.xlTop
        shWorkSheet.Range("A1:V1").HorizontalAlignment = Excel.Constants.xlCenter

        'Select the start position for the first record
        rngRowStart = shWorkSheet.Range("A1")

        'reads all the information from the database. the columns are in sequence with the query above
        While reader.Read()
            For i = 0 To 2
                rngRowStart.Offset(1, 0).Value = reader.GetString(0)
                rngRowStart.Offset(1, 1).Value = reader.GetString(1)
                rngRowStart.Offset(1, 2).Value = reader.GetString(2)
                rngRowStart.Offset(1, 3).Value = reader.GetString(3)
                rngRowStart.Offset(1, 4).Value = reader.GetString(4)
                rngRowStart.Offset(1, 5).Value = reader.GetString(5)
                rngRowStart.Offset(1, 6).Value = reader.GetString(6)
                rngRowStart.Offset(1, 7).Value = reader.GetInt32(7)
                rngRowStart.Offset(1, 8).Value = reader.GetInt32(8)
                rngRowStart.Offset(1, 9).Value = reader.GetString(9)
                rngRowStart.Offset(1, 10).Value = reader.GetValue(10)
                rngRowStart.Offset(1, 11).Value = reader.GetValue(11)
                rngRowStart.Offset(1, 12).Value = reader.GetValue(12)
                rngRowStart.Offset(1, 13).Value = reader.GetValue(13)
                rngRowStart.Offset(1, 14).Value = reader.GetValue(14)
                rngRowStart.Offset(1, 15).Value = reader.GetValue(15)
                rngRowStart.Offset(1, 16).Value = reader.GetValue(16)
                rngRowStart.Offset(1, 17).Value = reader.GetValue(17)
                rngRowStart.Offset(1, 18).Value = reader.GetValue(18)
                rngRowStart.Offset(1, 19).Value = reader.GetValue(19)
                rngRowStart.Offset(1, 20).Value = reader.GetValue(20)
                rngRowStart.Offset(1, 21).Value = reader.GetValue(21)

            Next

            'sets up the progress bar
            MainForm.ProgressBar1.Value = MainForm.ProgressBar1.Value + 1
            System.Windows.Forms.Application.DoEvents()
            MainForm.lblProgress.Text = Int((MainForm.ProgressBar1.Value / MainForm.ProgressBar1.Maximum) * 100) & "% Complete"
            rngRowStart = rngRowStart.Offset(1, 0)
        End While


        oRange = DirectCast(shWorkSheet.Columns("C:C"), Excel.Range)
        oRange.ColumnWidth = 15
        oRange = DirectCast(shWorkSheet.Columns("F:F"), Excel.Range)
        oRange.ColumnWidth = 15
        reader.Close()
        da.Dispose()
        MainForm.Cursor = Cursors.Default
        objExcel.Visible = True
        MainForm.ProgressBar1.Value = 0
        MainForm.ProgressBar1.Visible = False
        MainForm.lblProgress.Text = vbNullString
        MainForm.lblProgress.Visible = False
        objConnection.Close()

        If Not Directory.Exists(path & "/Reports") Then
            Directory.CreateDirectory(path & "/Reports")
        End If
    End Sub
End Class

Im not even totally sure if its the code but any suggestions would help!

And, im pretty sure its not the query because this DID work before.

[Shaggy Hiker]

There are lots of possible points to make with that much code. Here are a couple that might help:

1) You query the DB twice. Once fills a table, the other time a reader. Why do it twice? If you have the table, you certainly don't need the reader, and if you don't need the table, then the reader is faster. However, since you have the table, you also have a messagebox showing the number of rows. Is that correct?

2) In your loop through the reader, you loop on i = 0 to 2. Since i is not subsequently used anywhere in the loop, all this will do is overwrite the exact same information three times. I don't know what you wanted to do, but it sure isn't that.

[Sean04]

Thanks for the reply!

So I got rid of that loop because you are absolutely right that its pointless :P

As for your first point, yes I used a message box to view the number of rows and its only 1.
Based on what you said, do you have an idea of what I SHOULD change in my code? Maybe an example of what your saying about the reader/table? I do realize I query the DB twice though.

Thanks again,
Sean

[Shaggy Hiker]

Oh, if your message box is showing only 1, then that is the problem, as that means that your query is only returning a single row.

I have never seen a query like the one you have there. I'm surprised that it even runs. However, there is much more under the SQL sun than I have ever encountered. After looking at the query again, it looks like you are trying to build a large INNER JOIN through the back door by not using actual JOINs, but doing everything in the WHERE clause.

What I would do would be to open some query engine, whether in Access, SQL Server, or whatever you are working in, and copy the query into there. Then fiddle with it until it returns what you want.

[Sean04]

Thanks!

Yes i knew the query was returning a single row which is why i was stuck

And as for my query, it just gets data from other databases. I will although take your advice and mess around with the query. Hopefully work something out.

Thanks,
Sean