Visual Basic 6.0 and Crystal Report 8.5

[sirajahmed0303]

Dear Team,

I have a crystal report in my vb application, which is reflecting all the records on the report, even though I have a SQL query to retrieve only a specific record, But report keep displaying all the records in the table. below is my code, guide me where exactly I am going wrong.

Frm_SCHS_Renewal

Private Sub cmd_save_Click()
//few lines of code for validation and saving data into table

If MsgBox("Do you want to print the DataFlow Group & SCHS Renewal Form ?", vbYesNo, "Print Form") = vbYes Then

Form2.Show

Else
Exit Sub
End If

End Sub

Form2 is containing CRViewer

Form code is as below.

Dim Report As New Rpt_SCHS_RenewalDsr

Private Sub Form_Load()
GetLastID
Dim temp As Integer
temp = lbl_id.Caption
Screen.MousePointer = vbHourglass

SQL = " SELECT id, first_name, mid_name, last_name, date_of_birth, passport_no, email_id, mob_no, app_type, schs_acc_no, old_ref_no, md, phd, dhs, mem, fellowship, specialist, certificate, bachelor, master, diploma, " _
    & " ed_to_varify, major_subject, university_name, college_name, date_issued_certificate, others, ec_to_varify, employer_name, position, start_date, end_date, hl_to_varify, issuing_aut_name, license_attended, " _
    & " license_issue_date From dbo.tbl_schs_renewal WHERE dbo.tbl_schs_renewal.id='" & temp & "' "
connectDB
Debug.Print RS.Fields("id") 'just tried to see if query is returning correct row from table and it does return correct row of data.

With Report
        .Database.SetDataSource RS
        .PaperOrientation = 1
        .PaperSize = 2
        .DiscardSavedData
End With

With CRViewer1
        .ReportSource = Report
        .ViewReport
        .Zoom (2)
End With
    WindowState = 2
    Screen.MousePointer = vbDefault


End Sub

Private Sub Form_Resize()
CRViewer1.Top = 0
CRViewer1.Left = 0
CRViewer1.Height = ScaleHeight
CRViewer1.Width = ScaleWidth

End Sub

Public Sub GetLastID()
SQL = "Select MAX(id) from tbl_schs_renewal "
connectDB
With RS
    lbl_id.Caption = RS.GetString
End With
connClose
End Sub

I tried getting the result of query in immediate window and the result returned from query is absolutely correct, I mean it is reflecting MAX(id) and related fields from table.

I am not sure why the SetDatasource RS command is not working in my case.

Please guide.

[DataMiser]

Can't tell much from what you have posted. Apparently RS and SQL are both global or form level? I assume that you are opening RS using the sql in your connectdb but since that code is not shown there is no way to tell what you are actually doing.

I see a lot of people post code here where they use some sub routine to open a recordset, a connection and such then they have issues and never post that code.
IMO it is much better to open the recordset directly in the routine where it is to be used and not create some sub routine just to open a recordset. Havign the code in the same routine is faster and easier to debug when there is an issue and usually only requires a single line of code anyway.

[sirajahmed0303]

Can't tell much from what you have posted. Apparently RS and SQL are both global or form level? I assume that you are opening RS using the sql in your connectdb but since that code is not shown there is no way to tell what you are actually doing.

I see a lot of people post code here where they use some sub routine to open a recordset, a connection and such then they have issues and never post that code.
IMO it is much better to open the recordset directly in the routine where it is to be used and not create some sub routine just to open a recordset. Havign the code in the same routine is faster and easier to debug when there is an issue and usually only requires a single line of code anyway.

Dear Sir,

Below is my connectDB routine that is been created separately and is been called where ever and when ever it is required.

Public Sub connectDB()
conn.ConnectionString = "Provider = sqloledb;" & _
"Data Source=SIRAJAHMED\SQLEXPRESS;" & _
"Initial Catalog=HRMS;" & _
"Integrated Security = SSPI;"
conn.Open
With RS
    .ActiveConnection = conn
    .Open SQL, conn, 3, 3
End With
End Sub