Question help needed with updating a Crystal Report Dataset

[JohanZA]

Hi Guys,
Im really stuck and have been at this the whole day. I am running a crystal report that pulls out information for all the people, generate a crystal report and then emails it to them. Problem is, in creating the report(in a loop), it will work hundred percent for the first person. From the second person, the datatable gets created but SetDatasource does not set the dataset for my report, and the rest appear empty forms.
Heres the code in the loop:
Dim thissource As New DataTable
Dim sqlquery As String
Dim testreport As New NoANF7Days
Dim filename As String
ConnectionDB.Open()
sqlquery = "SELECT TOP (100) PERCENT dbo.AS_IMP_FILES.FILE_REF,dbo.AS_IMP_SUB_FILES.SUB_FILE_REF, dbo.AS_IMP_FILES.ETA_DATE,dbo.AS_IMP_SUB_FILES.FILE_CONTROLLER,dbo.AS_IMP_FILES.PORT_OF_LOADING_CODE,dbo.AS_IMP_FILES.ANF_PRINT_DATE,dbo.LT_CUSTOMS_LOCATIONS_SEA.PORT_NAME, DATEDIFF(d, dbo.AS_IMP_FILES.ETA_DATE, GETDATE()) AS Days, "
sqlquery = sqlquery + " dbo.USER_ACCESS.EMAIL_ADDRESS FROM dbo.AS_IMP_FILES INNER JOIN dbo.AS_IMP_SUB_FILES ON dbo.AS_IMP_FILES.FILE_REF = dbo.AS_IMP_SUB_FILES.FILE_REF INNER JOIN dbo.LT_CUSTOMS_LOCATIONS_SEA ON "
sqlquery = sqlquery + " dbo.AS_IMP_FILES.PORT_OF_LOADING_CODE = dbo.LT_CUSTOMS_LOCATIONS_SEA.LOCODE INNER JOIN dbo.USER_ACCESS ON dbo.AS_IMP_FILES.FILE_CONTROLLER = dbo.USER_ACCESS.USER_NAME WHERE (dbo.AS_IMP_FILES.ANF_PRINT_DATE IS NULL) AND (DATEDIFF(d,dbo.AS_IMP_FILES.ETA_DATE, GETDATE()) < 7) and (dbo.USER_ACCESS.EMAIL_ADDRESS = '" & emailad & "') ORDER BY dbo.AS_IMP_SUB_FILES.FILE_CONTROLLER, dbo.LT_CUSTOMS_LOCATIONS_SEA.PORT_NAME"
MsgBox(controller & ":" & thissource.Rows.Count)
thissource = FillDatatable(sqlquery)
testreport.SetDatabaseLogon("James", "james")
testreport.SetDataSource(thissource) ' <---------------- Problem here... returns hasrecords as false
MsgBox(thissource.Rows.Count)
If testreport.HasRecords Then
MsgBox("yes")
End If
filename = "c:\temp\ANF Report as on " & Format(Date.Today.Date, "Long Date") & "(" & controller & ").pdf"
testreport.ExportToDisk(ExportFormatType.PortableDocFormat, filename)
testreport.Close()
' EmailReport(filename, emailad)
ConnectionDB.Close()

its extremely urgent, thanx for your help in advance

 

[cjard]

That code doesnt really lend itself to other developers helping with it.. Ypu'd be better off reading the DW4 link in my signature, section "Creating a Simple Data App" for a tutorial on how to do data access, then pasting this query into the query builder wizard:

SELECT dbo.AS_IMP_FILES.FILE_REF,dbo.AS_IMP_SUB_FILES.SUB _FILE_REF, dbo.AS_IMP_FILES.ETA_DATE,dbo.AS_IMP_SUB_FILES.FIL E_CONTROLLER,dbo.AS_IMP_FILES.PORT_OF_LOADING_CODE ,dbo.AS_IMP_FILES.ANF_PRINT_DATE,dbo.LT_CUSTOMS_LO CATIONS_SEA.PORT_NAME, DATEDIFF(d, dbo.AS_IMP_FILES.ETA_DATE, GETDATE()) AS Days, dbo.USER_ACCESS.EMAIL_ADDRESS FROM dbo.AS_IMP_FILES INNER JOIN dbo.AS_IMP_SUB_FILES ON dbo.AS_IMP_FILES.FILE_REF = dbo.AS_IMP_SUB_FILES.FILE_REF INNER JOIN dbo.LT_CUSTOMS_LOCATIONS_SEA ON dbo.AS_IMP_FILES.PORT_OF_LOADING_CODE = dbo.LT_CUSTOMS_LOCATIONS_SEA.LOCODE INNER JOIN dbo.USER_ACCESS ON dbo.AS_IMP_FILES.FILE_CONTROLLER = dbo.USER_ACCESS.USER_NAME WHERE (dbo.AS_IMP_FILES.ANF_PRINT_DATE IS NULL) AND (DATEDIFF(d,dbo.AS_IMP_FILES.ETA_DATE, GETDATE()) < 7) and (dbo.USER_ACCESS.EMAIL_ADDRESS = @email ORDER BY dbo.AS_IMP_SUB_FILES.FILE_CONTROLLER, dbo.LT_CUSTOMS_LOCATIONS_SEA.PORT_NAME


It will write code that works very similar to what you have, only thing is it's guaranteed not to have any serious bugs in it and it's good OO