tjgrindsted
asked on
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)
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)
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 ing = "PROVIDER=SQLOLEDB;DATA SOURCE=1xxx;DATABASE=xxx;U ser 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
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
ASKER
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
It is okay..
ASKER
yes but how will it look´like when i need data from to tables in an access db (relasionship)
ASKER
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 taAdapter' kan ikke konverteres til 'System.Data.OleDb.OleDbCo mmand'.
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 tionship\D efault.asp x.vb Line: 37
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
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 able1_Tabl e2", myDataSet.Tables("Table_1" ).Columns( "tbl_1_tex t"), 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("Tab le1_Table_ 2")
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
ASKER
@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/
ASKER
is there no one that can guide/help me !?
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_tex t")
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(re lation)
For Each RowTitle In myDataSet.Tables("Table_1" ).Rows
lblRelation.Text &= RowTitle("tbl_1_text")
For Each RowSubline In RowTitle.GetChildRows("Tab le1_Table_ 2")
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.
ASKER
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_tex t")
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"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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)?
ASKER
no i haven't.
Have you tried it?
ASKER
i have a little problem getting the solution to work
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.