tjgrindsted
asked on
Will not write the relationship data, only from the first DB connection.
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
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
or this codebehind
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.
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>
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
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
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.
ASKER
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/
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/
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
ASKER
closed
Kat 1
- bla
- bla
Kat 2
- bla.
Kat 3
...
Can you post he *exact* output you are looking for...?
JeffCoachman