Solved

how do i show records from a DB from to tables.?

Posted on 2011-09-11
18
166 Views
Last Modified: 2012-05-12
Hi im new to this funny world of ASP.NET (VB)

I know how to show records from a DB in a repeater, but now i need to do the following.
Table_1
main_title_ID
main_title_title

Table_2
price_ID
price_text
price_value
price_main_ID

i need to show all records as following.
show the main_title_title ordre by main_title_ID ASC
and then under that show the price lines where price_main_ID = main_title_ID

so i will get something like this in the repeater

Photo Album
- large  10$
- small 5$

NewYear Album
- color 5$
- small 7$
- large 10$

how do i get the main_title from one table in a DB and then the sublines from another table in a DB, where the price_main_ID (Table_2) = main_title_ID (Table_1)

now i use a ODB line like this. (like relation)
Dim cmd As New OleDbCommand("select * from tbl order by id asc", myAccessConnection)
0
Comment
Question by:tjgrindsted
  • 9
  • 5
  • 3
  • +1
18 Comments
 
LVL 16

Expert Comment

by:Easwaran Paramasivam
ID: 36520854
You have write a stored procedure to return tables from both tables. For that either you could use Join.
For each row from tableA you could get records from tableB using Cursors. Cursors are performance affective hence try to use advanced CTE. Please do refer below links.

http://www.sql-tutorial.net/SQL-JOIN.asp
http://msdn.microsoft.com/en-us/library/ms180169.aspx
http://blog.sqlauthority.com/2008/07/28/sql-server-simple-example-of-recursive-cte/


After getting the records as per your requirement then bind the result to repeater.
0
 
LVL 7

Expert Comment

by:Kishan Zunjare
ID: 36521964
You need to import this library
Imports System.Data.SqlClient


'VB.NET/ASP.NET function to return database value
Private Function SingleItem(ByVal Category As Integer, ByVal RowNumber As Integer) As String
            Dim strPartNo As String = String.Empty

            Dim myConnection As New SqlConnection
            Dim myCommand As SqlCommand
            Dim dr As SqlDataReader

            Try
                myConnection.ConnectionString = "PROVIDER=SQLOLEDB;DATA SOURCE=1xxx;DATABASE=xxx;User ID=sa;Password=xxx"
                myConnection.Open()
                'opening the connection
                Dim sSQLCmd As String = String.Empty
                sSQLCmd = "select * from tbl order by id asc"

                myCommand = New SqlCommand(sSQLCmd, myConnection)
                'executing the command and assigning it to connection
                dr = myCommand.ExecuteReader()

                While dr.Read()
                    strPartNo = CStr(dr("ColumnName"))
                    'displaying the data from the table
                End While
                dr.Close()
                myConnection.Close()
            Catch e As Exception
            End Try
            Return strPartNo
        End Function


Hope this will work
0
 

Author Comment

by:tjgrindsted
ID: 36523957
Hi

Thx, in into VB and Access DB, how will it look like if im using something like this

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Try
            myAccessConnection.Open()
            Dim cmd As New OleDbCommand("select * from tblPrice order by price_id asc", myAccessConnection)

            ' Mark the Command as a Text
            cmd.CommandType = CommandType.Text

            ' Add Parameters to Command

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

            RepeaterPrice.DataSource = myDataSet
            DataBind()

            myAccessConnection.Close()
        Catch exc As Exception
        End Try
    End Sub

Open in new window

0
 
LVL 7

Expert Comment

by:Kishan Zunjare
ID: 36524203
It is okay..
0
 

Author Comment

by:tjgrindsted
ID: 36525418
yes but how will it look´like when i need data from to tables in an access db (relasionship)
0
 

Author Comment

by:tjgrindsted
ID: 36525648
I have looked at it and have this code, but get an error:

Error:
Compilation Error
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: BC30311: En værdi af typen 'System.Data.OleDb.OleDbDataAdapter' kan ikke konverteres til 'System.Data.OleDb.OleDbCommand'.

Source Error:

Line 35:         Dim cmd As New OleDbDataAdapter("select * from Table_1", myAccessConnection)
Line 36:
Line 37:         Dim myAdapter As New OleDbDataAdapter(cmd)
Line 38:         Dim myDataSet As New DataSet()
Line 39:
 
Source File: C:\Users\Nitro Power\Documents\Visual Studio 2010\WebSites\AccessDBrelationship\Default.aspx.vb    Line: 37

My Default.aspx code:
<%@ 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


My default.aspx.vb 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 OleDbDataAdapter("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())
        Finally
            myAccessConnection.Close()
        End Try

        myDataSet.Relations.Add("Table1_Table2", myDataSet.Tables("Table_1").Columns("tbl_1_text"), myDataSet.Tables("Table_2").Columns("tbl_2_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 Sub

End Class

Open in new window

0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 36526374
@Kishanzunjare
Was that code not for another thread?

@tjgrindsted
You mentioned repeaters. You can still use repeaters, this time nested, to achieve your requirements

http://www.codeproject.com/KB/aspnet/AspNetNestedRepeaters.aspx

http://support.microsoft.com/kb/326338
0
 
LVL 7

Expert Comment

by:Kishan Zunjare
ID: 36527340
instead of OleDbDataAdapter use OleDbCommand in following line

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


Finally your code will look like this.

 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())
        Finally
            myAccessConnection.Close()
        End Try

        myDataSet.Relations.Add("Table1_Table2", myDataSet.Tables("Table_1").Columns("tbl_1_text"), myDataSet.Tables("Table_2").Columns("tbl_2_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
0
 

Author Comment

by:tjgrindsted
ID: 36527730
@Kishanzunjare
Thx, its almost there i get this error now
http://imageshack.us/photo/my-images/607/udklipw.jpg/
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:tjgrindsted
ID: 36541055
is there no one that can guide/help me !?
0
 
LVL 7

Expert Comment

by:Kishan Zunjare
ID: 36544567
Hi,

To add relation in Dataset use following
http://vb.net-informations.com/dataset/dataset-relations.htm

Your code will be like this;

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_1_text")
            table2Column = myDataSet.Tables("Table_2").Columns("tbl_2_kat_id")
            'relating tables
            relation = New DataRelation("Table1_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


Hope this will work, let me know if you still face any issue.
0
 

Author Comment

by:tjgrindsted
ID: 36545315
Hi thx

Now i get the image error (the same one) http://imageshack.us/photo/my-images/607/udklipw.jpg/
at the codeline
table1Column = myDataSet.Tables("Table_1").Columns("tbl_1_text")
0
 
LVL 7

Accepted Solution

by:
Kishan Zunjare earned 250 total points
ID: 36547124
I don't know hows your access table structure is, but i think "tbl_1_text" this column is absent in your table.

To deal with datatable very article you can see;
http://www.dotnetfunda.com/articles/article131.aspx
http://www.asp.net/data-access/tutorials/adding-additional-datatable-columns-vb
0
 

Author Comment

by:tjgrindsted
ID: 36547492
Hi Kishanzunjare

Thx. for ur time..
My table structure is like this.
Table_1
 tbl_1_ID (Autonum/Key)
 tbl_1_text (text, Title for Kat.)
 
Table_2
 tbl_2_ID (Autonum/Key)
 tbl_2_text (note, for the sub kat./titles)
 tbl_2_kat_id (number, for witch "tbl_1_ID" to use)
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 36570137
So you have ruled out the use of nested repeaters(http:#36526374)?
0
 

Author Comment

by:tjgrindsted
ID: 36595397
no i haven't.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 36595454
Have you tried it?
0
 

Author Closing Comment

by:tjgrindsted
ID: 37218879
i have a little problem getting the solution to work
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

A quick way to get a menu to work on our website, is using the Menu control and assign it to a web.sitemap using SiteMapDataSource. Example of web.sitemap file: (CODE) Sample code to add to the page menu: (CODE) Running the application, we wi…
Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

762 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now