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:
Expand|Select|Wrap|Line Numbers
- 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
Expand|Select|Wrap|Line Numbers
- 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