Need Help : (VB6 + MySql + Crystal Report) Inner Join

[morbid.ivan]

I am having a problem Joining 2 tables in crystal report 9.

Always showing : Remove Table

The database table "sql_relate" cannot be found.

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?

[techgnome]

Seems pretty simple... the table sql_relate doesn't exist in your database.

-tg

[morbid.ivan]

Seems pretty simple... the table sql_relate doesn't exist in your database.

-tg

The table exist. Check this out...

[terry002]

The table exist. Check this out...

Have you tried running your query in an Analyzer? or inside MySQL itself?

[techgnome]

What does your connectionstirng look like?

-tg

[morbid.ivan]

What does your connectionstirng look like?

-tg

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

[digitalShaman]

where does the error pop up? is it in this line:

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

[terry002]

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.

[morbid.ivan]

[QUOTE=digitalShaman;4753955]where does the error pop up? is it in this line:

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 "

crxReport.DiscardSavedData

" no message pop and all records appears
and didn't do the filter.

[terry002]

Have you tried running your query in Mysql itself?

Not in VB or in Crystal Report.

[morbid.ivan]

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?

[morbid.ivan]

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."

[techgnome]

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

[terry002]

Here's the tutorial for PHPmyAdmin:

http://www.siteground.com/tutorials/...ysql_query.htm

[morbid.ivan]

Here's the tutorial for PHPmyAdmin:

http://www.siteground.com/tutorials/...ysql_query.htm

Result I got :


Query still giving me same message. Cannot found

[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...

[morbid.ivan]

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.

[terry002]

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?

[terry002]

[QUOTE=morbid.ivan;4754449]

where does the error pop up? is it in this line:

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

rs_Report.Open rpt_Sql, conn

with

crxReport.QueryString = rs_rpt

Reference:

http://bytes.com/topic/visual-basic/...activex-viewer

[morbid.ivan]

[QUOTE=terry002;4756033]

yes remove the RECORDSET, it has nothing to do with your report...

Try to replace this

rs_Report.Open rpt_Sql, conn

with

crxReport.QueryString = rs_rpt

Reference:

http://bytes.com/topic/visual-basic/...activex-viewer

Take a look at this :

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.