Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.
Course Of The Month
11 days, 11 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
how do i show records from a DB from to tables.?
Posted on 2011-09-11
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)
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)
[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
-
9
5
3- +1
18 Comments
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.
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.
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.ConnectionStr
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
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.ConnectionStr
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
Hi
Thx, in into VB and Access DB, how will it look like if im using something like this
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
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.OleDbDa
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\AccessDBrela
My Default.aspx code:
My default.aspx.vb codebehind:
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.OleDbDa
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\AccessDBrela
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>
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
Active 2 days ago
@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
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
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("T
For Each RowTitle In myDataSet.Tables("Table_1"
lblRelation.Text &= RowTitle("tbl_1_text")
For Each RowSubline In RowTitle.GetChildRows("Tab
lblRelation.Text &= "<br />" & RowSubline("tbl_2_text")
Next
Next
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("T
For Each RowTitle In myDataSet.Tables("Table_1"
lblRelation.Text &= RowTitle("tbl_1_text")
For Each RowSubline In RowTitle.GetChildRows("Tab
lblRelation.Text &= "<br />" & RowSubline("tbl_2_text")
Next
Next
@Kishanzunjare
Thx, its almost there i get this error now
http://imageshack.us/photo/my-images/607/udklipw.jpg/
Thx, its almost there i get this error now
http://imageshack.us/photo/my-images/607/udklipw.jpg/
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"
table2Column = myDataSet.Tables("Table_2"
'relating tables
relation = New DataRelation("Table1_Table
'assign relation to dataset
myDataSet.Relations.Add(re
For Each RowTitle In myDataSet.Tables("Table_1"
lblRelation.Text &= RowTitle("tbl_1_text")
For Each RowSubline In RowTitle.GetChildRows("Tab
lblRelation.Text &= "<br />" & RowSubline("tbl_2_text")
Next
Next
Hope this will work, let me know if you still face any issue.
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"
table2Column = myDataSet.Tables("Table_2"
'relating tables
relation = New DataRelation("Table1_Table
'assign relation to dataset
myDataSet.Relations.Add(re
For Each RowTitle In myDataSet.Tables("Table_1"
lblRelation.Text &= RowTitle("tbl_1_text")
For Each RowSubline In RowTitle.GetChildRows("Tab
lblRelation.Text &= "<br />" & RowSubline("tbl_2_text")
Next
Next
Hope this will work, let me know if you still face any issue.
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"
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"
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
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
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)
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)
Active 2 days ago
So you have ruled out the use of nested repeaters(http:#36526374)?
Featured Post
Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.
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.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
ASP.Net to Oracle Connectivity
Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers to develop an ASP.NET client…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface.
Change Cu…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers.
Hopes this gives you ideas on visualizing your data in new ways ~
Create a calculated field in a query:
…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month11 days, 11 hours left to enroll
623 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.