Hi,
I have a problem concerning to exporting data to an excel sheet.

I use 2 stored procedures (spStockReport1, spStockReport2): one to
pasta date to sheet 1, and other to paste dat to sheet 2.

The problem is that the first sp gets the result with no problem but
the second one gets the same results. I've already checked both in
query analyser and i don´'t see any problems.

This is the code i'm using:

Protected Overrides Function generateData(ByVal args As Hashtable,
ByVal serverSession As ServerSession) As ArrayList
            Dim totalItems As Integer = 0
            Dim sucess As Boolean = True
            Dim results As ArrayList = New ArrayList
            Dim hashRes As Hashtable = New Hashtable
            Dim status As String = ServiceResponse.STATUS_OK
            Dim description As String = ""

            Dim searchValue As String = System.DBNull.Value.ToString

            Try

                Dim cache As GenericCache = GenericCache.getInstance()

                Dim entity As String = ""

                If (Convert.ToString(args("entity")) <> "") Then
                    entity = args("entity")
                End If


                Dim database =
System.Configuration.ConfigurationSettings.AppSettings("database")

                Dim rsResult As RecordSet
                Dim iQueryB As New mysql.MySqlQueryBuilder
                Dim iConn As IConnection =
ConnectionManager.GetInstance.GetConnection
                Dim tmpData As New ArrayList
                Dim tmpData2 As New ArrayList

                Dim iCommtype As New
Command.CommandType(CommandType.StoredProcedure, "spStockReport1")

                Dim strQuery As String


                strQuery = "EXECUTE " & database & ".dbo.spStockReport1
'" + entity + "'"

                Dim iComm As New Command(iCommtype, strQuery)

                rsResult = iConn.Select(iComm)

                tmpData.Add(rsResult.GetResults)

                strQuery = "EXECUTE " & database & ".dbo.spStockReport2
'" + entity + "'"

                rsResult = iConn.Select(iComm)

                tmpData2.Add(rsResult.GetResults)


                'transform to connector format
                results = dataToSpreadSheet(tmpData, tmpData2,
serverSession)


                'Dim headerLine As New ArrayList
                'headerLine.Add("Cliente:" & entity & "")
                'results.Insert(0, headerLine)


            Catch ex As Exception
                Log.error("UmGenStockReport: Erro a gerar excel.", ex)
                Throw New Exception("Ocorreu um erro a gerar o mapa.
Por favor tente de novo")
            End Try

            generateData = results

        End Function


        Private Function dataToSpreadSheet(ByVal data As ArrayList,
ByVal data2 As ArrayList, ByVal serverSession As ServerSession) As
ArrayList


            Dim result As ArrayList = New ArrayList
            'construct invoice synopsis
            Dim invoiceSynopsis As ArrayList = New ArrayList

            Dim invoiceMapHeader As ArrayList = New ArrayList
            invoiceMapHeader.Add("COD ARTIGO")
            invoiceMapHeader.Add("ARTIGO")
            invoiceMapHeader.Add("DESCRIÇÃO")
            invoiceMapHeader.Add("DATA DE VALIDADE")
            invoiceMapHeader.Add("ESTADO")
            invoiceMapHeader.Add("MARCA")
            invoiceMapHeader.Add("LOTE")
            invoiceMapHeader.Add("ARMAZEM")
            invoiceMapHeader.Add("qt disp")
            invoiceMapHeader.Add("qt arm")
            invoiceMapHeader.Add("qtOk")
            invoiceMapHeader.Add("qt Danif")
            invoiceMapHeader.Add("qt disp tot")
            invoiceMapHeader.Add("qt arm tot")
            invoiceMapHeader.Add("qt Ok Total")
            invoiceMapHeader.Add("qt Danif Total")
            invoiceMapHeader.Add("Total de Lotes :")
            invoiceMapHeader.Add("a")
            invoiceMapHeader.Add("b")
            invoiceMapHeader.Add("Cliente")
            invoiceMapHeader.Add("Marca")
            invoiceMapHeader.Add("Armazem")
            invoiceMapHeader.Add("LotesPorArmazem")




            result.Add(invoiceMapHeader)

            For Each record As Hashtable In data

                Dim invoiceMapLine As ArrayList = New ArrayList
                If (record.ContainsKey("itemcode")) Then
                    invoiceMapLine.Add(record("itemcode"))
                Else : invoiceMapLine.Add(" ")
                End If

                If (record.ContainsKey("name")) Then
                    invoiceMapLine.Add(record("name"))
                Else
                    invoiceMapLine.Add(" ")
                End If

                If (record.ContainsKey("description")) Then
                    invoiceMapLine.Add(record("description"))
                Else
                    invoiceMapLine.Add(" ")
                End If

                If (record.ContainsKey("expirationdate")) Then
                    invoiceMapLine.Add(record("expirationdate"))
                Else
                    invoiceMapLine.Add(" ")
                End If

                If (record.ContainsKey("name")) Then
                    invoiceMapLine.Add(record("name"))
                Else
                    invoiceMapLine.Add(" ")
                End If

                If (record.ContainsKey("localcode")) Then
                    invoiceMapLine.Add(record("localcode"))
                Else
                    invoiceMapLine.Add(" ")
                End If

                If (record.ContainsKey("name")) Then
                    invoiceMapLine.Add(record("name"))
                Else
                    invoiceMapLine.Add(" ")
                End If

                If (record.ContainsKey("availableamount")) Then
                    invoiceMapLine.Add(record("availableamount"))
                Else
                    invoiceMapLine.Add(" ")
                End If

                If (record.ContainsKey("storderamount")) Then
                    invoiceMapLine.Add(record("storedamount"))
                Else
                    invoiceMapLine.Add(" ")
                End If

                If (record.ContainsKey("itemOk")) Then
                    invoiceMapLine.Add(record("itemOk"))
                Else
                    invoiceMapLine.Add(" ")
                End If

                If (record.ContainsKey("itemDanif")) Then
                    invoiceMapLine.Add(record("itemDanif"))
                Else
                    invoiceMapLine.Add(" ")
                End If

                result.Add(invoiceMapLine)

            Next

            'add blank line
            result.Add(New ArrayList)

            Return result
        End Function

See if this helps
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Default Values for a Column
case expression in where clause and null's
T-SQL problem.
Left Padding with Zeros on Count(*)
Using YEN sign in MONEY field