| Newbie | | Join Date: Oct 2008
Posts: 2
| |
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
-
|
