selection formula results from two unrelated databases

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

vb .net 200
crystal reports
Oracl
Acces

Hi, I have all selection formula results needed from the main crystal report, called in the vb form (Oracle db)

I now need to build in the logic in the vb form selection formula to obtain results also from the subreport (Access db)

The two databases are totally unrelated

How do I do this, please

Do I need to dim a second database, with all connection info, in the vb form
Do I then need to add the subreport table/column info to the FillDataset Sub? Is that how it's done

Thanks for any help on this

-Helen




Inappropriate post
If so, Red Flag it


Check out the FA
area for this forum



You
 
don't do it in code...either:

create an oracle tsn for your access db and create a view in oracle that
joins your two "un-related" tables

or,

create a dsn for oracle (sqlnet* required), link your oracle table in
access, then create a query that joins the two (either via your code or
natively in access).

hth,

steve

let db's do what they were designed to do...much less work for you.



| vb .net 2002
| crystal reports 9
| Oracle
| Access
|
| Hi, I have all selection formula results needed from the main crystal
report, called in the vb form (Oracle db).
|
| I now need to build in the logic in the vb form selection formula to
obtain results also from the subreport (Access db).
|
| The two databases are totally unrelated.
|
| How do I do this, please?
|
| Do I need to dim a second database, with all connection info, in the vb
form?
| Do I then need to add the subreport table/column info to the FillDataset
Sub? Is that how it's done?
|
| Thanks for any help on this.
|
| -Helen
|
|
|
|
|
| Inappropriate post?
| If so, Red Flag it!
|
|
| Check out the FAQ
| area for this forum!
|
|
|
| You
|
 
Hi Helen,

When I read your message well than Oracle and Access are two unrelated
databases.

So you retrieve the data from your Oracle database and as well from the
Access database for which you use OleDb.

I never tried it, however I think that you can even use the same dataset for
that (however only when needed of course when you have to set relations
between the two).

I cannot see with this what would be the problem.

Cor
 
you can do it in code...but what a pain.

if you do want to go the access linked table route, go to oracle's web site.
they have a "dev zone". download sqlnet* for oracle 7...install it. then use
it as the odbc driver. oracle should have instructions if you get stuck.
sqlnet* is the key...once you have your dsn setup for oracle, you just link
the table in access in the standard/normal fashion.

hth,

steve


| Thanks, Steve and Cor, for your replies.
|
| I think I'm very close to doing it in code with the dataset. Steve, I'd
really like to do it the db way, but there are complications. This project
is for historical purposes, the oracle is v 7 no longer being supported,
we're borrowing access to it so I don't have privileges for this project
other than the most basic. When I tried to follow your lead of linking to
oracle in a new Access project, via ODBC, I got "unrecognized database
format .......odbcad32.exe."
|
| So, I went back to coding, have access to both dbs working and I think the
only thing wrong at this point is my reference to the SUBREPORT field.
| ==============
| Subreport name ITEMDESC
| Subreport db swinventory.mdb
| Subreport table.field ITEM.ItemDesc1
| selection formula in subreport {Item.ItemDesc1} LIKE "%%"; (same as
formulae in main report for other fields that all work)
| ============
| Imports CrystalDecisions.CrystalReports.Engine
| Imports CrystalDecisions.Shared
|
| Public Class Form15
| Inherits System.Windows.Forms.Form
|
| Private m_cnADONetConnection As New OleDb.OleDbConnection()
| Private CBOPARTSDESCFORMULAReport As CBOPARTSDESCFORMULA
| Private ITEMDESCReport As itemdesc
|
| Dim crDatabase As Database
| Dim item As String
| Dim crTables As Tables
| Dim crTable As Table
| Dim crTableLogOnInfo As TableLogOnInfo
| Dim crConnectionInfo As ConnectionInfo
| ''''''''''''''''''''''''''''''''''''''''''''''
| Dim cnADONetConnection As New OleDb.OleDbConnection()
| Dim crDatabase2 As Database
| Dim item2 As String
| Dim crTables2 As Tables
| Dim crTable2 As Table
| Dim crTableLogOnInfo2 As TableLogOnInfo
| Dim crConnectionInfo2 As ConnectionInfo
| Dim Subreport As SubreportObject
|
| #Region " Windows Form Designer generated code "
|
| Public Sub New()
| MyBase.New()
|
| InitializeComponent()
| ConfigureCrystalReports()
| crConnectionInfo = New ConnectionInfo()
| crconnectioninfo2 = New ConnectionInfo()
|
| With crConnectionInfo
| .DatabaseName = ""
| .Password = "pd"
| .ServerName = "cbo_test.world"
| .UserID = "pd"
| End With
|
| With crConnectionInfo2
| .DatabaseName = "SWINVENTORY.MDB"
| .Password = "Admin"
| .ServerName = ""
| .UserID = ""
| End With
|
| crDatabase = CBOPARTSDESCFORMULAReport.Database
| crDatabase2 = ITEMDESCReport.Database
|
| crTables = CBOPARTSDESCFORMULAReport.Database.Tables
| crTables2 = ITEMDESCReport.Database.Tables
|
| For Each crTable In crTables
| crTableLogOnInfo = crTable.LogOnInfo
| crTableLogOnInfo.ConnectionInfo = crConnectionInfo
| crTable.ApplyLogOnInfo(crTableLogOnInfo)
| Next
|
| For Each crTable In crTables2
| crTableLogOnInfo2 = crTable.LogOnInfo
| crTableLogOnInfo2.ConnectionInfo = crConnectionInfo
| crTable.ApplyLogOnInfo(crTableLogOnInfo)
| Next
|
| CrystalReportViewer1.ReportSource = CBOPARTSDESCFORMULAReport
| End Sub
|
| Private Sub ConfigureCrystalReports()
| CBOPARTSDESCFORMULAReport = New CBOPARTSDESCFORMULA()
| ITEMDESCReport = New itemdesc()
| Dim CrystalReportViewer = CrystalReportViewer1
| CrystalReportViewer.ReportSource = CBOPARTSDESCFORMULAReport
| End Sub
|
| Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnDisplay.Click
| If txtDesc.Text = "" Then
| Dim msg As String
| Dim title As String
| Dim style As MsgBoxStyle
| Dim response As MsgBoxResult
| title = "Input for Description is Missing"
| msg = "Please enter a Description, or click Cancel to return
to the main menu"
| style = MsgBoxStyle.OKOnly
| response = MsgBox(msg, style, title)
| Exit Sub
| End If
|
| Me.ClientSize = New System.Drawing.Size(292, 273)
|
| StatusBar1.Controls.Add(ProgressBar1)
| With ProgressBar1
| .Visible = True
| .Value = 50 '/// just to test the progressbar
| .Location = New Point(0, 3)
| .Height = StatusBar1.Height - 3
| .Width = StatusBar1.Panels(0).Width
|
| End With
|
| Dim SelectFormula As String
| Dim myCrystalReportViewer = CrystalReportViewer1
|
| SelectFormula = "LowerCase({LINE_ITEM.CLIN_DESC}) LIKE '*" &
txtDesc().Text.ToLower & "*' or LowerCase({LINE_ITEM.CLIN_EXT_DESC}) LIKE
'*" & txtDesc().Text.ToLower & "*' or LowerCase({PROC_OBJECT_1.DOC_TITLE})
LIKE '*" & txtDesc().Text.ToLower & "*' or LowerCase({ITEM.ItemDesc1}) LIKE
'*" & txtDesc().Text.ToLower & "*'"
|
| ===========
| Just above, if I remove last piece, or LowerCase({ITEM.ItemDesc1}) LIKE
'*" & txtDesc().Text.ToLower & "*'", it works, so I must just need to
correct reference to subreport
| ==========
| CrystalReportViewer1.SelectionFormula = SelectFormula
|
| txtDesc.Visible = False
| btnDisplay.Visible = False
| lblDescFormat.Visible = False
| ' ListBox1.Visible = False
| btnCancel.Visible = False
| StatusBar1.Visible = True
|
| ' ProgressBar1.Visible = False
|
| CrystalReportViewer1.RefreshReport()
|
| Me.ClientSize = New System.Drawing.Size(292, 273)
|
| CrystalReportViewer1.Dock = DockStyle.Fill
| CrystalReportViewer1.Show()
| StatusBar1.Visible = False
| End Sub
|
| Private Sub FillDataset(ByVal myDataSet As DataSet)
| Dim DESCRIPTION(,) As String = {{"Workshop", "Book Subscriptions",
"Wilcox", "Caddy Ring"}}
| =============
| Just above, "Caddy Ring" is ref to subreport field
| ===============
| Dim myDataTable As DataTable = New DataTable("LINE_ITEM")
| Dim myDataColumn As DataColumn
| Dim myDataRow As DataRow
| Dim myDataTable2 As DataTable = New DataTable("PROC_OBJECT_1")
| Dim myDataColumn2 As DataColumn
| Dim myDataRow2 As DataRow
| '''
| Dim myDataTable3 As DataTable = New DataTable("ITEM")
| Dim myDataColumn3 As DataColumn
| Dim myDataRow3 As DataRow
|
| myDataColumn = New DataColumn()
| myDataColumn.DataType = System.Type.GetType("System.String")
| myDataColumn.ColumnName = "CLIN_DESC"
| myDataTable.Columns.Add(myDataColumn)
| 'Create second column if appropriate same way
| myDataColumn = New DataColumn()
| myDataColumn.DataType = System.Type.GetType("System.String")
| myDataColumn.ColumnName = "CLIN_EXT_DESC"
| myDataTable.Columns.Add(myDataColumn)
| myDataSet.Tables.Add(myDataTable)
|
| 'Create first column of table 2
| myDataColumn2 = New DataColumn()
| myDataColumn2.DataType = System.Type.GetType("System.String")
| myDataColumn2.ColumnName = "DOC_TITLE"
| myDataTable2.Columns.Add(myDataColumn)
|
| 'Create first column of table 3
| myDataColumn3 = New DataColumn()
| myDataColumn3.DataType = System.Type.GetType("System.String")
| myDataColumn3.ColumnName = "ITEMDESC1"
| myDataTable3.Columns.Add(myDataColumn)
|
| 'Copy the array into the datatable
| Dim ixLINE_ITEM As Integer
| For ixLINE_ITEM = 0 To 1
| myDataRow = myDataTable.NewRow
| myDataRow("CLIN_DESC") = DESCRIPTION(ixLINE_ITEM, 0)
| myDataRow("CLIN_EXT_DESC") = DESCRIPTION(ixLINE_ITEM, 1)
| myDataTable.Rows.Add(myDataRow)
| Dim ixPROC_OBJECT_1 As Integer
| For ixPROC_OBJECT_1 = 0 To 1
| myDataRow = myDataTable.NewRow
| myDataRow("DOC_TITLE") = DESCRIPTION(ixPROC_OBJECT_1, 0)
| myDataTable.Rows.Add(myDataRow)
| Dim ixITEM As Integer
| For ixITEM = 0 To 1
| myDataRow = myDataTable.NewRow
| myDataRow("ITEMDESC1") = DESCRIPTION(ixITEM, 0)
| myDataTable.Rows.Add(myDataRow)
| Next
| Next
| Next
| End Sub
|
| Where am I tripping up? Thanks,
| -H
|
|
 
Hi, Steve

It IS a pain, but getting closer

I'd like to try your way at least during down time so I know. When you say link to Access, do you mean link it to the physical access mdb used in the subreport, or just any Access new project? 'Cause I don't have rights to the .mdb other than read, YET.

-H :)
 
i mean...open access. then click on the "tables" tab. next, right click the
whitespace and select "link table". choose odbc datasource, then choose your
oracle/sqlnet dsn. all that s/b left is to select the oracle tables to which
you'd like to link.

if you don't have rights to do that then you would create a new mdb project,
link your currently used access tables in the new project as well as the
oracle tables (all as described above). then all you'd need to do in .net is
to change your connection string to point to the consolidated mdb.
so...there's the trouble w/ access "security"

hth,

steve

btw, hacking access and giving yourself godlike privilages is brutally easy
and there are a gazillion examples on the net...if you want to take the
"sledge hammer" approach.

;^)


| Hi, Steve:
|
| It IS a pain, but getting closer!
|
| I'd like to try your way at least during down time so I know. When you
say link to Access, do you mean link it to the physical access mdb used in
the subreport, or just any Access new project? 'Cause I don't have rights
to the .mdb other than read, YET.
|
| -H :)
 
Hi Helen,

Maybe I miss something what you want, to read an access database in a
dataset in ADONET is this
\\\
Dim conn as new OleDB.OleDBconnection _
("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=SWINVENTORY.MDB;"
Dim da as new OleDB.OleDbDataadapter("MySQLSelectString",conn)
Dim ds as new dataset
da.fill(ds)
////

I typed it in above so watch typos.

(I did not forgot the open close, command etc those are all not needed,
however nicer is to set the da.fill in a try catch block.)

What do I miss?

Cor
 
Thanks, Steve and Cor

I see, Steve, this would be easy to do linking in access. Searched oracle for correct sqlnet* for 7, haven't come up with it yet. Will keep looking

Cor, tks for the code

I have the connections working for both dbs now, crystal 9 quirky for calling selection from sureport, able to call if not subreport, no problemo ... almost there ...
 
try:

http://otn.oracle.com/software/tech/windows/odbc/index.html

this page has oracle 7 through 9 odbc drivers. that s/b all you need.

post you later,

me


| Thanks, Steve and Cor:
|
| I see, Steve, this would be easy to do linking in access. Searched oracle
for correct sqlnet* for 7, haven't come up with it yet. Will keep looking.
|
| Cor, tks for the code.
|
| I have the connections working for both dbs now, crystal 9 quirky for
calling selection from sureport, able to call if not subreport, no problemo
.... almost there ...
 
Back
Top