Visual Basic 6.0 and Crystal Report 8.5
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
Code:
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.
Code:
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.
Re: Visual Basic 6.0 and Crystal Report 8.5
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.
Re: Visual Basic 6.0 and Crystal Report 8.5
Quote:
Originally Posted by
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.
Dear Sir,
Below is my connectDB routine that is been created separately and is been called where ever and when ever it is required.
Code:
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
