dataset with streamwriter

[langthanh]

hi

i have two datasets , one will return all employees belong to supervisor, and the other one will select all the documents belong to the temployees and write it to the files that have the filename start with EmpNo. what should i do ? ( i want to select all the file belong to each employee and write it to the file on server that has the filename start with the empNo )

Here is my code:

Dim objDataset As Data.DataSet
        Dim objRow As Data.DataRow
        Dim i As Long
        Dim ds2 As DataSet
        Dim ds As New DataSet
        Dim j As Long
        Dim dr As Data.DataRow
        Dim dr2 As Data.DataRow
        Dim sb As New System.Text.StringBuilder
        Dim wr As System.IO.StreamWriter
        Dim var As String = ""

        ds = SQLHelperObj.ExecuteDataset(strTrngConn, CommandType.StoredProcedure, "SelectEmployees")

        For Each dr In ds.Tables(0).Rows

            Response.Write(CStr(dr("EmpNo")))
            Response.Write("<br>")
            Response.Write("<br>")

            wr = File.CreateText("C:\" & CStr(dr("EmpNo")) & ".txt")

            Dim paramEmpNo As SqlClient.SqlParameter = New SqlClient.SqlParameter("@empno", CType(dr("empNo"), Int32))

            ds2 = SQLHelperObj.ExecuteDataset(strTrngConn, CommandType.StoredProcedure, "SelectDocs", paramEmpNo)

            For Each dr2 In ds2.Tables(0).Rows
                sb.Append(var & " " & CStr(dr2("Doc")) & _
                " " & CStr(dr2("Rev")) & _
                " " & CStr(dr2("Title")) & _
                " " & CStr(dr2("InfoCardID")))
                wr.Write(vbCrLf)
                wr.WriteLine(sb.ToString)
                Response.Write(CStr(dr2("Doc")))
                Response.Write("<br>")
            Next
            wr.Close()
        Next

Here is my store procedures

CREATE proc SelectEmployees
as
  
  SELECT distinct  tblEmployeeAlerts.EmpNo, tblEmployees.Supervisor 
   FROM       tblEmployeeAlerts
   INNER JOIN tblEmployees
   ON         tblEmployeeAlerts.EmpNo = tblEmployees.EmpNo
   WHERE      tblEmployees.Supervisor = 332

return
GO

CREATE proc SelectDocs
@EmpNo as int
as
     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
                      
       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  
return

GO