?
Solved

Will not write the relationship data, only from the first DB connection.

Posted on 2011-09-17
8
Medium Priority
?
294 Views
Last Modified: 2012-05-12
Hi

I having some problems with a relationship connection, i can only get it to write the first connection/selecting output.

What i want it to make a list of things from 2 tables.

I have the following code.
default.aspx
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <asp:Label ID="lblRelation" runat="server" Text="Label"></asp:Label>
    </div>
    </form>
</body>
</html>

Open in new window


Then i have these to codebehind and its the same problem i get, i get same output with codebehind 1 and codebehind 2.

default.aspx.vb
Imports System.Data
Imports System.Data.OleDb
Imports System.IO
Public Class _default
    Inherits System.Web.UI.Page
    Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("App_Data/relationship.mdb")
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If (Not IsPostBack) Then
            Dim RowTitle As DataRow
            Dim RowSubline As DataRow
            Dim cmd As New OleDbCommand("select * from Table_1", New OleDbConnection(connStr))
            Dim myAdapter As New OleDbDataAdapter(cmd)
            Dim myDataSet As New DataSet()             'myAccessConnection.Open()   Since you are using Adapter, don't need to use this             
            myAdapter.Fill(myDataSet, "Table_1")
            myAdapter.SelectCommand = New OleDbCommand("select * from Table_2", New OleDbConnection(connStr))
            myAdapter.Fill(myDataSet, "Table_2")
            myDataSet.Relations.Add("Table1_Table2", myDataSet.Tables("Table_1").Columns("tbl_12_kat_id"), myDataSet.Tables("Table_2").Columns("tbl_12_kat_id"))
            For Each RowTitle In myDataSet.Tables("Table_1").Rows
                lblRelation.Text &= RowTitle("tbl_1_text")
                For Each RowSubline In RowTitle.GetChildRows("Table1_Table_2")
                    lblRelation.Text &= "<br />" & RowSubline("tbl_2_text")
                Next
            Next
        End If
    End Sub
End Class

Open in new window


or this codebehind
Imports System.Data
Imports System.Data.OleDb
Imports System.IO

Public Class _default
    Inherits System.Web.UI.Page

    Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("App_Data/relationship.mdb")

    ' Object created for Oledb Connection
    Dim myAccessConnection As New OleDbConnection(connStr)

    Public Sub openAccessConnection()
        ' If condition that can be used to check the access database connection
        ' whether it is already open or not.
        If myAccessConnection.State = ConnectionState.Closed Then
            myAccessConnection.Open()
        End If
    End Sub

    Public Sub closeAccessConnection()
        ' If condition to check the access database connection state
        ' If it is open then close it.
        If myAccessConnection.State = ConnectionState.Open Then
            myAccessConnection.Close()
        End If
    End Sub

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim RowTitle As DataRow
        Dim RowSubline As DataRow
        Dim cmd As New OleDbCommand("select * from Table_1", myAccessConnection)

        Dim myAdapter As New OleDbDataAdapter(cmd)
        Dim myDataSet As New DataSet()

        Try
            myAccessConnection.Open()
            myAdapter.Fill(myDataSet, "Table_1")
            myAdapter.SelectCommand = New OleDbCommand("select * from Table_2", myAccessConnection)
            myAdapter.Fill(myDataSet, "Table_2")
        Catch ex As OleDbException
            Response.Write(ex.ToString())
        End Try

        myAdapter.Dispose()
        cmd.Dispose()
        myAccessConnection.Close()

        'creating data relations
        Dim relation As DataRelation
        Dim table1Column As DataColumn
        Dim table2Column As DataColumn
        'retrieve column
        table1Column = myDataSet.Tables("Table_1").Columns("tbl_12_kat_id")
        table2Column = myDataSet.Tables("Table_2").Columns("tbl_12_kat_id")
        'relating tables
        relation = New DataRelation("Table_1_Table_2", table1Column, table2Column)
        'assign relation to dataset
        myDataSet.Relations.Add(relation)

        For Each RowTitle In myDataSet.Tables("Table_1").Rows
            lblRelation.Text &= RowTitle("tbl_1_text")
            For Each RowSubline In RowTitle.GetChildRows("Table1_Table_2")
                lblRelation.Text &= "<br />" & RowSubline("tbl_2_text")
            Next
        Next

    End Sub

End Class

Open in new window


the output is the following:
LabelKat 1Kat 2Kat 3Kat 4Kat 5

my Access DB is like this.
http://imageshack.us/photo/my-images/833/tblo.jpg/

what i want is that it make a output like this

Kat 1
- bla
- bla

Kat 2
- bla.

Kat 3
and so on, can someone help me, im stuck and new to this.
0
Comment
Question by:tjgrindsted
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36555437
To me, this is not clear enough:
Kat 1
- bla
- bla

Kat 2
- bla.

Kat 3
...

Can you post he *exact* output you are looking for...?

JeffCoachman
0
 

Author Comment

by:tjgrindsted
ID: 36555648
yes sry..

Kat 1
- dette er en lille
- dette jjsdjfk
- gdgjsdfd

Kat 2
- dette er emn t
- dette er en

Kat 3
- hfgdgyfgasdfn

it the output i want, if we look at my access db.
http://imageshack.us/photo/my-images/833/tblo.jpg/
0
 

Assisted Solution

by:tjgrindsted
tjgrindsted earned 0 total points
ID: 36555935
Hi
I found an usefull ex. and it almost work as i want it to
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim custAdapter As OleDbDataAdapter = New OleDbDataAdapter("select * from Table_1", myAccessConnection)

        Dim ordAdapter As OleDbDataAdapter = New OleDbDataAdapter("select * from Table_2", myAccessConnection)

        Dim customerOrders As DataSet = New DataSet()
        custAdapter.Fill(customerOrders, "Table_1")
        ordAdapter.Fill(customerOrders, "Table_2")

        Dim relation As DataRelation = _
          customerOrders.Relations.Add("CustOrders", _
          customerOrders.Tables("Table_1").Columns("tbl_12_kat_id"), _
          customerOrders.Tables("Table_2").Columns("tbl_12_kat_id"))

        Dim pRow, cRow As DataRow
        For Each pRow In customerOrders.Tables("Table_1").Rows
            Response.Write(pRow("tbl_1_text").ToString() & "<br />")

            For Each cRow In pRow.GetChildRows(relation)
                Response.Write(vbTab & cRow("tbl_2_text").ToString() & "<br />")
            Next
        Next

    End Sub

Open in new window


If i use this code i get this in the output.
Kat 1
dette er en lille
dette jjsdjfk
gdgjsdfd
Kat 2
dette er emn t
dette er en
Kat 3
hfgdgyfgasdfn
Kat 4
Kat 5

My question is now, how do i get an ekstra <br /> tag between the Kat, if there is no ekstra recorde so i get this
Kat 1
dette er en lille
dette jjsdjfk
gdgjsdfd

Kat 2
dette er emn t
dette er en

Kat 3
hfgdgyfgasdfn

Kat 4

Kat 5

And how can i hide the Kat's where there is no records in !?
So i will end up with

Kat 1
dette er en lille
dette jjsdjfk
gdgjsdfd

Kat 2
dette er emn t
dette er en

Kat 3
hfgdgyfgasdfn
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36557046
Then this all depends on what approach you take and what the exact output will be.

With your code, you would have to detect when the "Kat" changes and inset another "<br>"
This may be a simple thing to do, but since my skillset revolves around MS Access, I would use a slightly different approach.
(Note, the code is a bit sloppy, but is does produce the desired output)
I will presume here that the output you are looking for here is HTML...


Examine this sample *carefully*
1. I creates an output table ("tblList")
2. It creates two HTML files in the root of your C: drive.  One from the Access OutputTo command, the other from the raw HTML built from the loop

Again, I will admit that the code is a bit sloppy and probably inefficient, (because I threw it together quickly) but it produces the desired output.

And again, finally, there may very well be a one line fix to make the code you posted here do the same thing, but I don't know how that would be structured.
:-(

In any event, have fun playing with this sample.
(I even created a Report that simulates the desired output, and I display the HTML raw string on the form, for reference)
;-)

JeffCoachman



EEQ-27313632-OutputOneToManyPare.mdb
0
 

Accepted Solution

by:
tjgrindsted earned 0 total points
ID: 36595399
im not understand this, I'm new to this, but i almost have what i want, can't it be made easier.
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 300 total points
ID: 36597178
Remember, you have asked a question that *requires* that you be fairly well versed in VBA...
In other words, there is no "simple" way to do what you are asking.

My post does as you have requesting in three ways...
This is basically a one to many relationship, (this can be represented in a report as well)

Perhaps you could explain why this is needed?

0
 

Author Closing Comment

by:tjgrindsted
ID: 37242260
he dident look at my code at all, and was not helping me, i explained that i was new to this, and now he talked about VBA
0
 

Author Comment

by:tjgrindsted
ID: 37218884
closed
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses
Course of the Month13 days, 17 hours left to enroll

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question