dataset with streamwriter

[langthanh]

hi,

i have a dataset and i want to select all the record belong to each empNo and write it to the filename start with that empNo.i can write the file but i don know whay it just write the last record belong to that empNo instead of write all records belong to each empNo. does anybody can help me out ?

here my code:

 objDataset = SQLHelperObj.ExecuteDataset(strTrngConn, CommandType.StoredProcedure, "Test")

Dim i As Integer

Dim var As String = ""

For i = 0 To objDataset.Tables(0).Rows.Count - 1
        var = CStr(objDataset.Tables(0).Rows(i).Item("EmpNo"))
        Dim sw As StreamWriter = New StreamWriter("C:\\Inetpub\\wwwroot\\Training\\Training\\Attachments\\" & CStr(objDataset.Tables(0).Rows(i).Item("EmpNo")) & ".txt")
        sw.WriteLine(CStr(objDataset.Tables(0).Rows(i).Item("Doc")) & " " & CStr(objDataset.Tables(0).Rows(i).Item("Rev")) & " " & CStr(objDataset.Tables(0).Rows(i).Item("Title")))
        sw.WriteLine(sw.NewLine)
        sw.Close()

        Dim fileName As String = (CStr(objDataset.Tables(0).Rows(i).Item("EmpNo")) & ".txt")
        Dim physicalPath As String = ("C:\\Inetpub\\wwwroot\\Training\\Training\\Attachments\\" + fileName)

        Me.Response.WriteFile(physicalPath)

        'strBody = strBody & vbTab & CStr(objRow("Doc")) & Space(10 - CStr(objRow("Doc")).Length) & CStr(objRow("Rev")) & Space(5 - CStr(objRow("Rev")).Length) & CStr(objRow("title")) & vbCrLf
        'strBody = "Different EmpNo"
        Next

Here is my store proc:

CREATE proc Test
as
DECLARE @EmpNo INT, 
        @Supervisor int, 
        @Doc VARCHAR(30),
        @Rev VARCHAR(10),
        @Title varchar(255),
        @InfoCardID varchar (18),
        @Info_Card_ID varchar(255)

DECLARE @Result TABLE (EmpNo INT, Doc VARCHAR(50), Rev varchar(255), Title varchar(255))

DECLARE supervisor_cursor CURSOR 
FOR
   SELECT     tblEmployeeAlerts.EmpNo, tblEmployees.Supervisor 
   FROM       tblEmployeeAlerts
   INNER JOIN tblEmployees
   ON         tblEmployeeAlerts.EmpNo = tblEmployees.EmpNo
   WHERE      tblEmployees.Supervisor = 332
  
OPEN supervisor_cursor
FETCH NEXT FROM supervisor_cursor INTO @EmpNo, @Supervisor
PRINT 'OUTER LOOP START'

WHILE (@@FETCH_STATUS = 0)
BEGIN
    SET @Doc = ''
    DECLARE curDetailList CURSOR 
    FOR
       SELECT distinct dbo.tblCurrentRev.tdc_doc_Num as doc, 
                       dbo.tblCurrentRev.Rev as Rev, 
                       MP_MPI.dbo.tdc_doc_infocard.title_nm as Title, 
                       dbo.tblCurrentRev.InfoCardID, 
                       dbo.tblTrainingRecord.info_card_id 
       FROM            dbo.tblCurrentRev 
       INNER JOIN      MP_MPI.dbo.tdc_doc_infocard 
       ON              dbo.tblCurrentRev.InfoCardID = MP_MPI.dbo.tdc_doc_infocard.info_card_id
       INNER JOIN      dbo.tblJobDocs 
       ON              dbo.tblCurrentRev.tdc_doc_Num = dbo.tblJobDocs.tdc_Doc_Num 
       INNER JOIN      dbo.tblAssignments 
       ON              dbo.tblJobDocs.JobProfileID = dbo.tblAssignments.ProfileID 
       LEFT OUTER JOIN dbo.tblTrainingRecord 
       ON              dbo.tblAssignments.EmpNo = dbo.tblTrainingRecord.empNo 
       AND             dbo.tblCurrentRev.InfoCardID = dbo.tblTrainingRecord.info_card_id  
		      
       WHERE           dbo.tblAssignments.EmpNo = @EmpNo
       AND             dbo.tblTrainingRecord.info_card_id IS NULL
       ORDER BY        tblCurrentRev.tdc_doc_num  
    
    OPEN curDetailList
    FETCH NEXT FROM curDetailList INTO @Doc, @Rev, @Title, @InfoCardID, @Info_Card_ID

    PRINT 'INNER LOOP START'
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
         INSERT INTO @Result VALUES (@EmpNo, @Doc, @Rev, @Title)
         FETCH NEXT FROM curDetailList INTO @Doc, @Rev, @Title, @InfoCardID, @Info_Card_ID
         PRINT 'INNER LOOP'
    END
    CLOSE curDetailList
    DEALLOCATE curDetailList

FETCH NEXT FROM supervisor_cursor INTO @EmpNo, @Supervisor

PRINT 'OUTER LOOP'
END

CLOSE supervisor_cursor
DEALLOCATE supervisor_cursor

 
-- Publish result

SELECT * FROM @Result
return

[DrBunchman]

Hi langthanh,

Welcome to Bytes.com! I hope you find the site useful.

You've posted your question in the ASP Forum which is for Classic ASP only - I've moved it for you but in future please post all ASP.NET questions in the .NET Forum.

Please don't forget to wrap your code in CODE tags - it makes your posts much easier to read - and please read the Posting Guidelines if you have not done so already.

Thanks and I hope somebody can help you with your problem.

Dr B