vb6 and crystal report 8.5 msslq 2008 r2

[KURANBK]

Please help me regarding vb6, crystal report 8.5 and mssql 2008 r2 compartibility. My old setup was vb6 , CR 8.5 and ms-sql 2000 was working fine, after migration from mssql 2000 to ms-sql 2008 r2 my report giving error lie '20599 and Cannot Open Sql Server ' message

[jggtz]

Welcome to vbforums!

Which DataSource type is using the rpt?
ODBC?
OLE DB?
Dis you create a new DSN?

[KURANBK]

Hi,

Thanks for reply, I am using OLEDB and DSN also created. My problemis for single table used in CR 8.5 report work fine in CR 8.5 preview as well as in VB codeing. But if I used multiple table in Crystal report its shows in preview but when i call thru VB codeing its giving error.

[jggtz]

Then post all the code when you call the rpt

[KURANBK]

Then post all the code when you call the rpt

If Option1.Value = True Then
    Rep_form.CR1.Reset
    Rep_form.CR1.LogonInfo(0) = rep_con
    Rep_form.CR1.WindowShowPrintSetupBtn = True
    Rep_form.CR1.WindowShowExportBtn = True
    Rep_form.CR1.WindowShowGroupTree = True
    Rep_form.CR1.WindowShowSearchBtn = True
    Rep_form.CR1.WindowShowRefreshBtn = True
    Rep_form.CR1.WindowShowZoomCtl = True
    Rep_form.CR1.WindowState = crptMaximized
    Rep_form.CR1.DiscardSavedData = True
    Rep_form.CR1.SQLQuery = ""
Rep_form.CR1.SQLQuery = "  SELECT   Mfg_ITMMAST.IMPN, Mfg_ITMMAST.IMDESC, Mfg_ITMMAST.IMCLAS5, Mfg_ITMMAST.IMCLAS7, Mfg_ITMMAST.IMDRWG, Mfg_ITMMAST.IMACCTNO, Mfg_ITMMAST.IMLTL, Mfg_ITMMAST.IMREF, Mfg_ITMMAST.IMFAMILY, Mfg_ITMMAST.IMFGROUP, Mfg_ITMMAST.IMCUBEFT, Mfg_ITMMAST.IMPKGTYP, Mfg_ITMMAST.IMECCN, Mfg_LOTITM.IMSHELF , Mfg_LOTITM.IMRETEST, Mfg_LOTITM.IMAVLDAY " & _
                        "  From   FSDBMR.dbo.Mfg_ITMMAST Mfg_ITMMAST,  FSDBMR.dbo.Mfg_LOTITM Mfg_LOTITM " & _
                        " Where    Mfg_ITMMAST._Row = Mfg_LOTITM._ITMLOT_OwnRow "
    
    Rep_form.CR1.ReportFileName = MRCREPORT & "MAST_ITEM.rpt"
    Rep_form.CR1.WindowTitle = "MASTER"
    Rep_form.CR1.ReportTitle = "ITEM MASTER LIST"
    Rep_form.CR1.Action = 1
    Screen.MousePointer = 0
 End If

[jggtz]

Could you attach the rpt ?
Because isn't clear to me how you design your rpt

[KURANBK]

Hi,

Please see the attached zip file contained .rpt. If I open this file in CR 8.5 and preview its work fine. but in vb its giving error '20599' Cannot open SQL server. If I try for single table in report and call in VB its work fine. The problem is with multiple table used in report.

If Option5.value = True Then
     Rep_form.cr1.Reset
     Rep_form.cr1.LogonInfo(0) = rep_con
     Rep_form.cr1.WindowShowPrintSetupBtn = True
     Rep_form.cr1.WindowShowExportBtn = True
     Rep_form.cr1.WindowShowGroupTree = True
     Rep_form.cr1.WindowShowSearchBtn = True
     Rep_form.cr1.WindowShowRefreshBtn = True
     Rep_form.cr1.WindowShowZoomCtl = True
     Rep_form.cr1.WindowState = crptMaximized
     Rep_form.cr1.DiscardSavedData = True
     Rep_form.cr1.SQLQuery = ""
     
    
   Rep_form.cr1.SQLQuery = " SELECT Mfg_PRICES.PQPRIQUO,    ITEM_ITEMCOST.ITEM,    ITEM_ITEMCOST.ITEM_DESC,    ITEM_ITEMCOST.FMLY_NAME,    ITEM_ITEMCOST.RL_MAT_CST,    ITEM_ITEMCOST.RL_LAB_CST,    ITEM_ITEMCOST.RL_VR_OVHD,    ITEM_ITEMCOST.RL_FX_OVHD,    ITEM_ITEMCOST.PACK_SIZE,    ITEM_ITEMCOST.ITEM_UOM,    ITEM_ITEMCOST.SP_GRVT,    ITEM_ITEMCOST.ITEM_DRWG " & _
                           " From   FSDBMR.dbo.Mfg_PRICES Mfg_PRICES INNER JOIN FSDBMR.dbo.ITEM_ITEMCOST ITEM_ITEMCOST ON   Mfg_PRICES._PRICQUOT_OwnRow = ITEM_ITEMCOST.ITEM_ROW "
    
    
    
    Rep_form.cr1.ReportFileName = MRCREPORT & "ITEM_SALES_PRICES.rpt"
    
    Rep_form.cr1.WindowTitle = "MASTER"
    Rep_form.cr1.ReportTitle = "ITEM SALES PRICES LIST "
    Rep_form.cr1.Action = 1
    Screen.MousePointer = 0
    
End If

[jggtz]

You could set the link between the 2 tables using the Links tab in menu/Database/VisualLinkingExpert instead of using a query

[KURANBK]

Hi,

thanks for helping , I am linking table thru visuallinkingexpert thru only. When I making report using CR 8.5 and previewing in cr 8.5 its work, but same report when calling thru above code in VB6 its giving error. Same code for single table report its work fine.

[jggtz]

If you are linking tables inside the rpt then why set the query in code?

[KURANBK]

If you are linking tables inside the rpt then why set the query in code?

If I try without query in code still same message ''20599' Cannot open sql server'. IF above same vb code use for single table crystal report its work fine.

[jggtz]

Rep_form.cr1.LogonInfo(0) = rep_con

What's rep_con?
Post the value

[KURANBK]

Rep_form.cr1.LogonInfo(0) = rep_con

What's rep_con?
Post the value

Hi,
thanks for your good co-operations

My question if their is problem inlink then report should not work for single user table in CR8.5.
I think so sowhere multi table used setting probelm

''''''MDIForm1.Show

Sub Main()
Set con = New ADODB.Connection
MRCREPORT = App.Path & "\REPORT\"
MACHINE_NAME = MDIForm1.Winsock1.LocalHostName
If con.State = 1 Then con.Close
con.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;password=Doehler#123;Initial Catalog=FSDBMR;Data Source=192.168.0.131"
rep_con = "DSN=DILFx;uid=sa;pwd=Doehler#123;dsq=FSDBMR"
DD = "select getdate() serverdate"
Set rs = New ADODB.Recordset
rs.Open DD, con, adOpenDynamic
serverdate = Format(rs!serverdate, "DD-MMM-YYYY")
DD = " SELECT INVOICE_FLAG,POST_DATED_INV_FLAG,INVOICE_DAYS,START_DATE,END_DATE,BOM_PWORD,OLD_DATA_VIEW FROM SAN_SYS_SETTING"
If rs.State = 1 Then rs.Close
rs.Open DD, con, adOpenStatic
If rs.RecordCount > 0 Then
G_INVOICE_FLAG = rs!INVOICE_FLAG
G_POST_DATED_INV_FLAG = rs!POST_DATED_INV_FLAG
G_INVOICE_DAYS = rs!INVOICE_DAYS
F_DATE = IIf(IsNull(rs!START_DATE) = True, "01-APR-2012", rs!START_DATE)
T_DATE = IIf(IsNull(rs!END_DATE) = True, "01-APR-2013", rs!END_DATE)
G_OLD_DATA_VIEW = rs!OLD_DATA_VIEW
BOMPASS = rs!BOM_PWORD
End If
Login.Show

End Sub

[gep13]

Hello KURANBK,

Welcome to the forums!

When you are posting code into the forum, can you please remember to surround it in [code][/code] or [highlight][/highlight] tags? It makes it a lot easier to read. I have done this for you in your above posts.

Thanks

Gary

[jggtz]

The OCX, and Crystal Reports 8.5 have been gone for a very long time. SQL Server 2008 came out well after the retirement of both CR8.5 and the OCX. If your VB6/OCX application was working with SQL Server 2000, and now it's not working with SQL Server 2008 then it's more likely that the database drivers aren't recognizing some change from SQL Server 2000 to SQL Server 2008.
The fact that you say about the rpt is working in CR ide and not when is called from the VB6 app tells me that the problem is in the OCX
I would recomend to upgrade your code to RDC and if it's possible to a newer version of CR

[KURANBK]

The OCX, and Crystal Reports 8.5 have been gone for a very long time. SQL Server 2008 came out well after the retirement of both CR8.5 and the OCX. If your VB6/OCX application was working with SQL Server 2000, and now it's not working with SQL Server 2008 then it's more likely that the database drivers aren't recognizing some change from SQL Server 2000 to SQL Server 2008.
The fact that you say about the rpt is working in CR ide and not when is called from the VB6 app tells me that the problem is in the OCX
I would recomend to upgrade your code to RDC and if it's possible to a newer version of CR

If in CR I used only one table and preview then report its work same in vb also work.If I used multi table and view in CR its work fine but in vb its shows error '20599 cannot open sql server' my predictin is that my code is correct but for multi table link some setting is required.Please let me no good solution for this.

[jggtz]

Make a small vb6 project and use RDC instead of OCX... and let's see what occurs...
(there are a few lines of code)