Sep 3rd, 2014, 04:07 AM
#1
Thread Starter
Hyperactive Member
Need Help : (VB6 + MySql + Crystal Report) Inner Join
I am having a problem Joining 2 tables in crystal report 9.
Always showing : Remove Table
The database table "sql_relate" cannot be found.
Code:
Dim rs_Report As ADODB.Recordset
Dim rpt_Sql As String
Set rs_Report = New ADODB.Recordset
rs_Report.CursorLocation = adUseClient
rs_Report.CursorType = adOpenForwardOnly
rs_Report.LockType = adLockReadOnly
rpt_Sql = "SELECT T1.AUTOINCREMENT, T1.BATCH, T1.NAME, " _
& "T2.BATCH,T2.ITEM " _
& "FROM sql_main AS T1 " _
& "INNER JOIN sql_relate AS T2 ON " _
& "T1.BATCH = T2.BATCH GROUP BY T2.BATCH"
Set crxReport = crxApp.OpenReport(App.Path & "\Report1.rpt", 1)
crxReport.DiscardSavedData
rs_Report.Open rpt_Sql, conn
crxReport.Database.SetDataSource rs_Report, , 1
With Me
' crxReport.PrinterSetup frm_Print.hWnd
.crVwr.ReportSource = crxReport
.crVwr.ViewReport
End With
Set rs_Report = Nothing
Any help?
Sep 3rd, 2014, 05:34 AM
#2
Re: Need Help : (VB6 + MySql + Crystal Report) Inner Join
Seems pretty simple... the table sql_relate doesn't exist in your database.
-tg
Sep 3rd, 2014, 11:49 PM
#3
Thread Starter
Hyperactive Member
Re: Need Help : (VB6 + MySql + Crystal Report) Inner Join
Originally Posted by
techgnome
Seems pretty simple... the table sql_relate doesn't exist in your database.
-tg
The table exist. Check this out...
Attached Images
Sep 4th, 2014, 12:09 AM
#4
Addicted Member
Re: Need Help : (VB6 + MySql + Crystal Report) Inner Join
Originally Posted by
morbid.ivan
The table exist. Check this out...
Have you tried running your query in an Analyzer? or inside MySQL itself?
If I had helped you...
Don't forget to mark your Inquiry as RESOLVED...
I will be glad if you can also give me some Reputation points in helping you (by Clicking Rate This Post)...
Happy VB Coding Everyone!
Sep 4th, 2014, 05:47 AM
#5
Re: Need Help : (VB6 + MySql + Crystal Report) Inner Join
What does your connectionstirng look like?
-tg
Sep 8th, 2014, 12:22 AM
#6
Thread Starter
Hyperactive Member
Re: Need Help : (VB6 + MySql + Crystal Report) Inner Join
Originally Posted by
techgnome
What does your connectionstirng look like?
-tg
Code:
Public Function konektodata(strServer As String, uName As String, uPass) As Boolean
On Error GoTo errHandle
Dim MyOdbcOption
MyOdbcOption = optFoundRows + optVB + optBigPacket + optDynamicCursor + _
optCompressedProto + optNoBigInt + optAutoReconnect
strConn = "DRIVER={MySQL ODBC 3.51 Driver};" _
& "SERVER=" & strServer & ";" _
& "DATABASE=mysql_TMS;" _
& "UID=" & uName & "; PWD=" & uPass & ";" _
& "OPTION=" & MyOdbcOption
Set conn = New ADODB.Connection
conn.CursorLocation = adUseClient
conn.ConnectionString = strConn
conn.Open
konektodata = True
Exit Function
errHandle:
MsgBox Err.Description & vbCrLf & vbCrLf & strConn
konektodata = False
End Function
Sep 8th, 2014, 07:15 AM
#7
Re: Need Help : (VB6 + MySql + Crystal Report) Inner Join
where does the error pop up? is it in this line:
Code:
rs_Report.Open rpt_Sql, conn
? then it has got nothing to do with crystal reports.
your query does also look a bit strange to me. it would not execute in mssql because you specify a GROUP BY but you do not use any aggregat functions nor are all fields in the select list also listen in the GROUP BY (the error would be different though).
to see if its really having problems to find the tables you may try something more simple like a "SELECT TOP 1 * FROM sql_main" first and then a "SELECT TOP 1 * FROM sql_relate" to see if you can actually select anything from these tables .... ahhh, mysql does not support TOP so someting like that:
SELECT * FROM sql_main LIMIT 0,1
SELECT * FROM sql_relate LIMIT 0,1
Sep 8th, 2014, 08:12 PM
#8
Addicted Member
Re: Need Help : (VB6 + MySql + Crystal Report) Inner Join
That's the problem with the OP, he / she should test the query first inside MySQL and not just rely in Crystal report, so that he / she can see clearly what was the error all about.
If I had helped you...
Don't forget to mark your Inquiry as RESOLVED...
I will be glad if you can also give me some Reputation points in helping you (by Clicking Rate This Post)...
Happy VB Coding Everyone!
Sep 9th, 2014, 03:24 AM
#9
Thread Starter
Hyperactive Member
Re: Need Help : (VB6 + MySql + Crystal Report) Inner Join
[QUOTE=digitalShaman;4753955]where does the error pop up? is it in this line:
Code:
rs_Report.Open rpt_Sql, conn
? then it has got nothing to do with crystal reports.
The report opens based on query but there's a message. "The database table "sql_relate" cannot be found."
But when I try to remove "
HTML Code:
crxReport.DiscardSavedData
" no message pop and all records appears
and didn't do the filter.
Sep 9th, 2014, 03:27 AM
#10
Addicted Member
Re: Need Help : (VB6 + MySql + Crystal Report) Inner Join
Have you tried running your query in Mysql itself?
Not in VB or in Crystal Report.
If I had helped you...
Don't forget to mark your Inquiry as RESOLVED...
I will be glad if you can also give me some Reputation points in helping you (by Clicking Rate This Post)...
Happy VB Coding Everyone!
Sep 9th, 2014, 04:01 AM
#11
Thread Starter
Hyperactive Member
Re: Need Help : (VB6 + MySql + Crystal Report) Inner Join
Originally Posted by
terry002
Have you tried running your query in Mysql itself?
Not in VB or in Crystal Report.
Nope, I don't know how.
Can please tell me how to do it?
Sep 9th, 2014, 04:10 AM
#12
Thread Starter
Hyperactive Member
Re: Need Help : (VB6 + MySql + Crystal Report) Inner Join
Code:
SELECT *
FROM SQL_MAIN
INNER JOIN SQL_RELATE ON SQL_MAIN.BATCH = SQL_RELATE.BATCH
WHERE SQL_MAIN.BATCH =1
LIMIT 0 , 30
I generated this from MySql, but when I put this on VB6 same error occurs.
"The database table "sql_relate" cannot be found."
Sep 9th, 2014, 07:30 AM
#13
Re: Need Help : (VB6 + MySql + Crystal Report) Inner Join
Then that's the problem... the table as you have it, does. not. exist.
So either you've got it misspelled somewhere along the line, or you're not connecting to the database you think you're connecting to.
-tg
Sep 9th, 2014, 08:40 PM
#14
Addicted Member
Re: Need Help : (VB6 + MySql + Crystal Report) Inner Join
If I had helped you...
Don't forget to mark your Inquiry as RESOLVED...
I will be glad if you can also give me some Reputation points in helping you (by Clicking Rate This Post)...
Happy VB Coding Everyone!
Sep 11th, 2014, 09:17 PM
#15
Thread Starter
Hyperactive Member
Re: Need Help : (VB6 + MySql + Crystal Report) Inner Join
Originally Posted by
terry002
Result I got :
Query still giving me same message. Cannot found
Sep 11th, 2014, 09:39 PM
#16
Addicted Member
Re: Need Help : (VB6 + MySql + Crystal Report) Inner Join
ok good, it means that your query is working in MySQL and the problem lies in the Crystal Report or your syntax in VB...
I'll check it...
If I had helped you...
Don't forget to mark your Inquiry as RESOLVED...
I will be glad if you can also give me some Reputation points in helping you (by Clicking Rate This Post)...
Happy VB Coding Everyone!
Sep 12th, 2014, 02:26 AM
#17
Thread Starter
Hyperactive Member
Re: Need Help : (VB6 + MySql + Crystal Report) Inner Join
Originally Posted by
terry002
ok good, it means that your query is working in MySQL and the problem lies in the Crystal Report or your syntax in VB...
I'll check it...
Thank you... I will wait for your respond.
Sep 12th, 2014, 03:03 AM
#18
Addicted Member
Re: Need Help : (VB6 + MySql + Crystal Report) Inner Join
Originally Posted by
morbid.ivan
Thank you... I will wait for your respond.
Question, do you know how to use the IMMEDIATE WINDOW?
put your rpt_sql in the immediate window then run it in your MYSQL...
2nd question:
Is the report design really includes the fields for SQL_RELATE table?
If I had helped you...
Don't forget to mark your Inquiry as RESOLVED...
I will be glad if you can also give me some Reputation points in helping you (by Clicking Rate This Post)...
Happy VB Coding Everyone!
Sep 12th, 2014, 03:10 AM
#19
Addicted Member
Re: Need Help : (VB6 + MySql + Crystal Report) Inner Join
[QUOTE=morbid.ivan;4754449]
Originally Posted by
digitalShaman
where does the error pop up? is it in this line:
Code:
rs_Report.Open rpt_Sql, conn
? then it has got nothing to do with crystal reports.
yes remove the RECORDSET, it has nothing to do with your report...
Try to replace this
Code:
rs_Report.Open rpt_Sql, conn
with
vb Code:
crxReport.QueryString = rs_rpt
Reference:
http://bytes.com/topic/visual-basic/...activex-viewer
If I had helped you...
Don't forget to mark your Inquiry as RESOLVED...
I will be glad if you can also give me some Reputation points in helping you (by Clicking Rate This Post)...
Happy VB Coding Everyone!
Sep 14th, 2014, 09:49 PM
#20
Thread Starter
Hyperactive Member
Re: Need Help : (VB6 + MySql + Crystal Report) Inner Join
[QUOTE=terry002;4756033]
Originally Posted by
morbid.ivan
Take a look at this :
Code:
Dim rpt_Sql As String
rpt_Sql = "SELECT * From SQL_MAIN " _
& "INNER JOIN SQL_RELATE ON SQL_MAIN.BATCH = SQL_RELATE.BATCH " _
& "Where SQL_MAIN.BATCH='1'"
Set CRXReport = crxApp.OpenReport(App.Path & "\Report1.rpt", 1)
CRXReport.DiscardSavedData
CRXReport.SQLQueryString = rpt_Sql
If crVwr.IsBusy Then
DoEvents
End If
With Me
.crVwr.ReportSource = CRXReport
.crVwr.ViewReport
End With
Set CRXReport = Nothing
Result I got.
Attached Images
Posting Permissions
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
Forum Rules
Click Here to Expand Forum to Full Width