We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
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.
Active 3 days ago
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
@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
Active 3 days ago
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/
Active 3 days ago
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"
Active 3 days ago
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)
So you have ruled out the use of nested repeaters(http:#36526374)?
Featured Post
This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
It’s quite interesting for me as I worked with Excel using vb.net for some time.
Here are some topics which I know want to share with others whom this might help.
First of all if you are working with Excel then you need to Download the Following
…
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, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment.
To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB.
To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month6 days, 3 hours left to enroll
688 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.