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

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)
tjgrindstedAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
Advertise Here
 
Kishan ZunjareConnect With a Mentor Sr. Software EngineerCommented:
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
 
Easwaran ParamasivamCommented:
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
 
Kishan ZunjareSr. Software EngineerCommented:
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Access It Now
 
tjgrindstedAuthor Commented:
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
 
Kishan ZunjareSr. Software EngineerCommented:
It is okay..
0
 
tjgrindstedAuthor Commented:
yes but how will it look´like when i need data from to tables in an access db (relasionship)
0
 
tjgrindstedAuthor Commented:
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
 
CodeCruiserCommented:
@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
 
Kishan ZunjareSr. Software EngineerCommented:
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
 
tjgrindstedAuthor Commented:
@Kishanzunjare
Thx, its almost there i get this error now
http://imageshack.us/photo/my-images/607/udklipw.jpg/
0
 
tjgrindstedAuthor Commented:
is there no one that can guide/help me !?
0
 
Kishan ZunjareSr. Software EngineerCommented:
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
 
tjgrindstedAuthor Commented:
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
 
tjgrindstedAuthor Commented:
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
 
CodeCruiserCommented:
So you have ruled out the use of nested repeaters(http:#36526374)?
0
 
tjgrindstedAuthor Commented:
no i haven't.
0
 
CodeCruiserCommented:
Have you tried it?
0
 
tjgrindstedAuthor Commented:
i have a little problem getting the solution to work
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.