how to iner join a table and display on a grid from a user dropdownbox
hello
i need to iner join 2 tables frojm a users input from a dropdownbox
1st table 2nd table
ID ID
CNAME CourseID
PNAME UserID
DATE RegistrationTime
TIME CancellTime
LOC CancelDate
i need the user to pick a value in the dropdownbox that displays the course name and the user clicks submit and a dynamic grid will popup.
on this grid will show all usere registered ifor that class that the user picked
thanks in advance
DESC
i need to iner join 2 tables frojm a users input from a dropdownbox
1st table 2nd table
ID ID
CNAME CourseID
PNAME UserID
DATE RegistrationTime
TIME CancellTime
LOC CancelDate
i need the user to pick a value in the dropdownbox that displays the course name and the user clicks submit and a dynamic grid will popup.
on this grid will show all usere registered ifor that class that the user picked
thanks in advance
DESC
ASP file
----------------
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="viewreport.aspx.vb" Inherits="viewreport" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Data" %>
<!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 id="PageHead" runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Human Resourses </title>
<link href="Default.css" rel="stylesheet" type="text/css" />
<style type="text/css">
.invertedshiftdown2{
padding: 0;
width: 78%;
border-top: 5px solid #000099; /*Highlight blue color theme*/
background: transparent;
voice-family: "\"}\"";
voice-family: inherit;
}
.invertedshiftdown2 ul{
margin:0;
margin-left: 40px; /*margin between first menu item and left browser edge*/
padding: 0;
list-style: none;
}
.invertedshiftdown2 li{
display: inline;
margin: 0 2px 0 0;
padding: 0;
text-transform:uppercase;
}
.invertedshiftdown2 a{
float: left;
display: block;
font: bold 12px Arial;
color: black;
text-decoration: none;
margin: 0 1px 0 0; /*Margin between each menu item*/
padding: 5px 10px 5px 10px; /*Padding within each menu item*/
background-color: lightblue; /*Default menu color*/
border-bottom: 8px solid white;
}
.invertedshiftdown2 a:hover{
background-color: #000099; /*Highlight red color theme*/
padding-top: 10px; /*Set padding-top value to default's padding-top + padding-bottom */
padding-bottom: 0; /*Set padding-bottom value to 0 */
border-bottom-color: #000099; /*Highlight red color theme*/
color: white;
}
.invertedshiftdown2 .current a{ /** currently selected menu item **/
background-color: #D10000; /*Highlight red color theme*/
padding-top: 10px; /*Set padding-top value to default's padding-top + padding-bottom */
padding-bottom: 0; /*Set padding-bottom value to 0 */
border-bottom-color: #D10000; /*Highlight red color theme*/
color: white;
}
#myform{ /*CSS for sample search box. Remove if desired */
float: right;
margin: 0;
margin-top: 2px;
padding: 0;
}
#myform .textinput{
width: 190px;
border: 1px solid gray;
}
#myform .submit{
font: normal 12px Verdana;
height: 22px;
border: 1px solid #000099;
background-color: black;
color: white;
}
</style>
</head>
<body style="background-color: white">
<form id="FormMain" runat="server">
<table width="770px" border="0" cellspacing="0" cellpadding="0">
<tr class="StandardPageHeader">
<td width="1" bgcolor="#FFFFFF" align="left">
<a href="http://www.tamu-commerce.edu"><img src="BlockLogo.jpg" alt="Passionate About Learning? You Belong Here." border="0" style="height: 111px"/></a>
</td>
<td align="left">
<img src="StudentsAtComputer.jpg" style="width: 555px; height: 114px" /> </td>
</tr>
</table>
<div class="invertedshiftdown2" style="height: 36px; text-align: center;">
<ul>
<li style="text-align: center"><a href="default.aspx" title="Home">Home</a></li>
<li style="text-align: center"><a href="addCourse.aspx" title="New">Add Course</a></li>
<li style="text-align: center"><a href="viewreport.aspx" title="Tools">Reports</a></li>
<li style="text-align: center"><a href="help.aspx" title="Help">Help</a></li>
</ul>
</div>
<table width="770px" border="0" cellspacing="0" cellpadding="0">
<tr>
<td colspan="2" style="height: 15px; text-align: center;">
<asp:PlaceHolder ID="PageBody" runat="server" />
<br />
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:TCPRODConnectionString3 %>"
SelectCommand="SELECT [CNAME] FROM [CHRTR]"></asp:SqlDataSource>
<br />
<asp:Label ID="show" runat="server" Width="193px" ForeColor="Red"></asp:Label><br />
<br />
<asp:DropDownList ID="course" runat="server" DataSourceID="SqlDataSource1" DataTextField="CNAME"
DataValueField="CNAME" AutoPostBack="True">
</asp:DropDownList>
<asp:TextBox ID="cn" runat="server" Visible="False"></asp:TextBox>
<asp:Button ID="Button1" runat="server" Text="Go" /><br />
</td>
</tr>
</table>
</form>
<asp:Label cssclass="StandardPageHeader" runat="server" ID="Footer" Width="768px" BackColor="#0000C0">.</asp:label>
</body>
</html>
VB file
-------------
Partial Class viewreport
Inherits System.Web.UI.Page
Dim ConnPortal As ADODB.Connection
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
cn.Text = ""
Dim r As TableRow ' create a new row
Dim c As TableCell ' create a new cell
Dim t As Table = New Table ' make a new table
Dim l As Label ' lable that shows the user thatrs loged in
Dim rs As ADODB.Recordset ' recorde set for the connection
Dim sqlstmt As String
Dim CNAME As String
Dim FNAME As String
Dim LNAME As String
Dim EMAIL As String
Dim PHONE As String
Dim ct As Integer = 0
Call localDBConnectionOpen()
' sqlstmt = "SELECT * FROM HRD WHERE ('" & cn.Text & "')= CNAME"
sqlstmt = "SELECT c.* FROM CHRTR c INNER JOIN Registrations r ON r.CourseID=c.ID "
rs = DBRecordSetNewFast(ConnPortal, sqlstmt)
l = New Label
l.Text = " <br />"
PageBody.Controls.Add(l)
l = New Label
l.Text = "Logged on user: " & Request.ServerVariables("AUTH_USER")
PageBody.Controls.Add(l)
t = New Table
t.Width = Unit.Percentage(100)
' creates the table for the diplay
r = New TableRow
r.VerticalAlign = VerticalAlign.Bottom
r.CssClass = "StandardRowHeader"
c = New TableHeaderCell
c.Text = "Course Name"
r.Controls.Add(c)
c = New TableCell
c.Text = "First name"
r.Controls.Add(c)
c = New TableHeaderCell
c.Text = "Last Name"
r.Controls.Add(c)
c = New TableHeaderCell
c.Text = "Email"
r.Controls.Add(c)
c = New TableHeaderCell
c.Text = "Phone"
r.Controls.Add(c)
t.Controls.Add(r)
While Not rs.EOF
CNAME = DBGetField(rs, "CNAME")
FNAME = DBGetField(rs, "FNAME")
LNAME = DBGetField(rs, "LNAME")
EMAIL = DBGetField(rs, "EMAIL")
PHONE = DBGetField(rs, "PHONE")
r = New TableRow
ct += 1
If ct Mod 2 = 0 Then
r.CssClass = "StandardRowEven"
Else
r.CssClass = "StandardRowOdd"
End If
'call to the varabkes that hold the data from the database
Call CellAdd(r, CNAME.ToString)
Call CellAdd(r, FNAME.ToString)
Call CellAdd(r, LNAME.ToString)
Call CellAdd(r, EMAIL.ToString)
Call CellAdd(r, PHONE.ToString)
t.Controls.Add(r)
rs.MoveNext()
End While
PageBody.Controls.Add(t)
rs = DBRecordSetDestroy(rs)
Call localDBConnectionClose()
End Sub
Private Sub CellAdd(ByRef r As TableRow, ByVal t As String)
Dim c = New TableCell
c.text = t
r.Controls.Add(c)
c = Nothing
End Sub
Private Sub localDBConnectionOpen()
ConnPortal = DBConnectionOpenSQLServer(ConnPortal, "TCPROD", "SQL01", "HRProd", "Ev3rgr8en!")
End Sub
Private Sub localDBConnectionClose()
ConnPortal = DBConnectionClose(ConnPortal)
End Sub
' bind the drop down to the text boxthat is unknown
Protected Sub course_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles course.SelectedIndexChanged
cn.Text = course.SelectedItem.Value.ToString
End Sub
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
End Sub
End Class
Last Comment
Bob Learned
Too much code, not enough time. I don't understand your question. What kind of help are you looking for?
ASKER
i need help iner joining my tables and displaying in my dynamic grid
ASKER
EXAMPLE
1. you are a user and a page has a dropdownbox
2. you pick a class in the dropdownbox
3. click submit
4. the grid displays with users registered for the class you picked in the dropdownbox
TABLES
------------------
1st table 2nd table
ID ID
CNAME CourseID
PNAME UserID
DATE RegistrationTime
TIME CancellTime
LOC CancelDate
ANSWER- i need to join the ID from table 1 and the COURSEID from table 2 from the users iinput from the dropdownbox
1. you are a user and a page has a dropdownbox
2. you pick a class in the dropdownbox
3. click submit
4. the grid displays with users registered for the class you picked in the dropdownbox
TABLES
------------------
1st table 2nd table
ID ID
CNAME CourseID
PNAME UserID
DATE RegistrationTime
TIME CancellTime
LOC CancelDate
ANSWER- i need to join the ID from table 1 and the COURSEID from table 2 from the users iinput from the dropdownbox
I see the inner join:
sqlstmt = "SELECT c.* FROM CHRTR c INNER JOIN Registrations r ON r.CourseID=c.ID "
Does this one not work?
sqlstmt = "SELECT c.* FROM CHRTR c INNER JOIN Registrations r ON r.CourseID=c.ID "
Does this one not work?
ASKER
no i was tetsing in page load just to see if i can display all the data but it gives an
error
-------
Item cannot be found in the collection corresponding to the requested name or ordinal.
error
-------
Item cannot be found in the collection corresponding to the requested name or ordinal.
You need to determine what the names are for the data that is selected, and make sure that you have the column names right in the binding.
ASKER
i need to select the course name ,that already works in the dropdownbox
its called cn.text
all by table names are correct i listed them above and you can see in my join statement that it matches
its called cn.text
all by table names are correct i listed them above and you can see in my join statement that it matches
ASKER
it kinds seems that you dont want to help me i understand just let me know so i can repost the question
I am fine, I guess your not. Is the column name that you get 'text', or 'cn.text'. You can tell that from some query. You aren't binding to the correctly column name in the HTML, or whereever you are binding.
I think TheLearnedOne is correct in saying: "You need to determine what the names are for the data that is selected, and make sure that you have the column names right in the binding."
Assuming you posted your full table structure:
TABLES
------------------
1st table 2nd table
ID ID
CNAME CourseID
PNAME UserID
DATE RegistrationTime
TIME CancellTime
LOC CancelDate
Then when you do this:
While Not rs.EOF
CNAME = DBGetField(rs, "CNAME")
FNAME = DBGetField(rs, "FNAME")
LNAME = DBGetField(rs, "LNAME")
EMAIL = DBGetField(rs, "EMAIL")
PHONE = DBGetField(rs, "PHONE")
then there is no "FNAME", "LNAME" etc.
Also, just a bit of advice...in ASP.Net, this would really be much easier using ADO.Net instead of ADODB and you could easily bind to a gridview instead of trying to create your table by html...
Assuming you posted your full table structure:
TABLES
------------------
1st table 2nd table
ID ID
CNAME CourseID
PNAME UserID
DATE RegistrationTime
TIME CancellTime
LOC CancelDate
Then when you do this:
While Not rs.EOF
CNAME = DBGetField(rs, "CNAME")
FNAME = DBGetField(rs, "FNAME")
LNAME = DBGetField(rs, "LNAME")
EMAIL = DBGetField(rs, "EMAIL")
PHONE = DBGetField(rs, "PHONE")
then there is no "FNAME", "LNAME" etc.
Also, just a bit of advice...in ASP.Net, this would really be much easier using ADO.Net instead of ADODB and you could easily bind to a gridview instead of trying to create your table by html...
ASKER
ok i already know what field i want to bind to
1st table 2nd table
ID ID NOT THIS
CourseID
i want to join in table1 the id witch is the class id and table2 courseID witch is the ID in table1
1st table 2nd table
ID ID NOT THIS
CourseID
i want to join in table1 the id witch is the class id and table2 courseID witch is the ID in table1
ASKER
ok so can someone help me with this query and iner join
The join you posted is correct; the problem probably isn't with the query..
SELECT FirstTable.*
FROM FirstTable INNER JOIN
SecondTable ON FirstTable.ID = SecondTable.CourseID
And technically, you don't even need the join.
Your dropdownlist has the CourseID's right?
To populate your table, all you really need is
SELECT * FROM FirstTable WHERE ID = xxx
where xxx is your dropdown value...while the join is better for data integrity...it technically isn't needed for the query to get the data to populate your table.
SELECT FirstTable.*
FROM FirstTable INNER JOIN
SecondTable ON FirstTable.ID = SecondTable.CourseID
And technically, you don't even need the join.
Your dropdownlist has the CourseID's right?
To populate your table, all you really need is
SELECT * FROM FirstTable WHERE ID = xxx
where xxx is your dropdown value...while the join is better for data integrity...it technically isn't needed for the query to get the data to populate your table.
ASKER
ok i was testing to see if i can pull data with my query with no dropdownbox for now
why does this give me an error
sqlstmt = "SELECT * FROM CHRTR INNER JOIN Registrations ON CHRTR.ID = Registrations.CourseID"
ERROR
Item cannot be found in the collection corresponding to the requested name or ordinal.
why does this give me an error
sqlstmt = "SELECT * FROM CHRTR INNER JOIN Registrations ON CHRTR.ID = Registrations.CourseID"
ERROR
Item cannot be found in the collection corresponding to the requested name or ordinal.
ASKER
ok i am sorry maybe i posted it wrong my dropdownbox only hasthe name pof the course not the id
>>Item cannot be found in the collection corresponding to the requested name or ordinal.
That error is not because of the sql, if you can run your query above in Query Analyzer and it runs correctly than you can be 100% sure it's not the sql.
What you posted, is that *all* the fields in the tables?
TABLES
------------------
1st table 2nd table
ID ID
CNAME CourseID
PNAME UserID
DATE RegistrationTime
TIME CancellTime
LOC CancelDate
That error is not because of the sql, if you can run your query above in Query Analyzer and it runs correctly than you can be 100% sure it's not the sql.
What you posted, is that *all* the fields in the tables?
TABLES
------------------
1st table 2nd table
ID ID
CNAME CourseID
PNAME UserID
DATE RegistrationTime
TIME CancellTime
LOC CancelDate
ASKER
yes, the tables are correct
i dont understand why it is not working
i dont understand why it is not working
if those tables are correct, when you do this in the code:
CNAME = DBGetField(rs, "CNAME")
FNAME = DBGetField(rs, "FNAME")
LNAME = DBGetField(rs, "LNAME")
EMAIL = DBGetField(rs, "EMAIL")
PHONE = DBGetField(rs, "PHONE")
The parts you have in quotes are database columns.
Again, if the tables you listed are correct, there is no FNAME column, no LNAME column, no EMAIL column etc.
In the error "Item cannot be found in the collection "..
the "collection" is the column names....
So when you do: DBGetField(rs, "FNAME")
it will throw the error you see because there is no FNAME column...
CNAME = DBGetField(rs, "CNAME")
FNAME = DBGetField(rs, "FNAME")
LNAME = DBGetField(rs, "LNAME")
EMAIL = DBGetField(rs, "EMAIL")
PHONE = DBGetField(rs, "PHONE")
The parts you have in quotes are database columns.
Again, if the tables you listed are correct, there is no FNAME column, no LNAME column, no EMAIL column etc.
In the error "Item cannot be found in the collection "..
the "collection" is the column names....
So when you do: DBGetField(rs, "FNAME")
it will throw the error you see because there is no FNAME column...
ASKER
oh wow you should slap me :)
ASKER
ok one more thing
my dropdownbox has course names in it and this will not work is it another programmer error
sqlstmt = "SELECT * FROM CHRTR WHERE '" & cn.Text & "')= CNAME INNER JOIN Registrations ON CHRTR.ID = Registrations.CourseID"
my dropdownbox has course names in it and this will not work is it another programmer error
sqlstmt = "SELECT * FROM CHRTR WHERE '" & cn.Text & "')= CNAME INNER JOIN Registrations ON CHRTR.ID = Registrations.CourseID"
Joins belong in the "FROM" clause.
"WHERE" clause would always follow the "FROM" clause...
sqlstmt = "SELECT * FROM CHRTR INNER JOIN Registrations ON CHRTR.ID = Registrations.CourseID WHERE CHRTR.CNAME= '" & cn.Text & "'"
"WHERE" clause would always follow the "FROM" clause...
sqlstmt = "SELECT * FROM CHRTR INNER JOIN Registrations ON CHRTR.ID = Registrations.CourseID WHERE CHRTR.CNAME= '" & cn.Text & "'"
ASKER
hmm maybe my logic is off
this will not pull anything it returns a blank table any ideal
this will not pull anything it returns a blank table any ideal
test your sql with a hardcoded CNAME you know exists in CHRTR
You're using sql server right?
ASKER
ok i have another problem its the same but i need to join the table that has the users infomation in it how can i do that
USER TABLE
CourseID
UserID
i want to match the user id with a value in the registrations table so do i need another iner join
USER TABLE
CourseID
UserID
i want to match the user id with a value in the registrations table so do i need another iner join
ASKER
sorry let me explain more it sounds as clear as mud when i read it
ok i have 3 tables that have infomation that i need to display i have 2 tables working correctly but i want user infomation from the user table to display all users registered for a class /////// cn.text ////////
ok i have 3 tables that have infomation that i need to display i have 2 tables working correctly but i want user infomation from the user table to display all users registered for a class /////// cn.text ////////
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
thank you this works great you are a great help better than the first guy
thanks agian
thanks agian
ASKER
great answer works with the user
I appreciate the compliment, but TheLearnedOne is one of the oldest members of EE and one of the highest ranking members.
He's helped me out on numerous occasions when no one else could or would.
So he deserves the utmost respect (and has it) from the EE members that use the site regularly.
Two things I *don't* like doing on Experts Exchange is:
1. Post a comment when I see another expert is working with an asker...but both you and TheLearnedOne seemed to be getting a little frustrated.
2. Answer a question "just to make it work" when I see there is bad programming going on.
No offense intended, but you really really should not be doing ADODB in .Net.
And while there are (rare) instances where building a dynamic table is necessary, this question is not one of them.
You made this comment to TheLearnedOne:
"it kinds seems that you dont want to help me i understand ..."
And if you truly want to understand and be good at .Net...
1. You have to stop using ADODB
2. Learn about the .Net data controls. What you're doing would be so much simpler if you used a Gridview or any other of the ASP.Net data controls...
If I have time later, I will try to work a similar example using .Net the way it should be used.
Again, I don't mean to be offensive at all...but if you're doing ASP.Net and continue using the technique you are here...it will be a serious detriment in the long run.
He's helped me out on numerous occasions when no one else could or would.
So he deserves the utmost respect (and has it) from the EE members that use the site regularly.
Two things I *don't* like doing on Experts Exchange is:
1. Post a comment when I see another expert is working with an asker...but both you and TheLearnedOne seemed to be getting a little frustrated.
2. Answer a question "just to make it work" when I see there is bad programming going on.
No offense intended, but you really really should not be doing ADODB in .Net.
And while there are (rare) instances where building a dynamic table is necessary, this question is not one of them.
You made this comment to TheLearnedOne:
"it kinds seems that you dont want to help me i understand ..."
And if you truly want to understand and be good at .Net...
1. You have to stop using ADODB
2. Learn about the .Net data controls. What you're doing would be so much simpler if you used a Gridview or any other of the ASP.Net data controls...
If I have time later, I will try to work a similar example using .Net the way it should be used.
Again, I don't mean to be offensive at all...but if you're doing ASP.Net and continue using the technique you are here...it will be a serious detriment in the long run.
Hi there...the below example is a simplified version of what you're doing, but with ADO.Net.
The example (tested and working) assumes you are using SQL Server and have a Northwind database. And that you have a connection string in your web.config to the Northwind database named: "NorthwindConnectionString
If you have all those things...create a blank page and put the code below into the page...and the sample should work for you...if not...just look through the code and see how it differs from yours, yet does the same thing...it's going to output a table based on a dropdownlist value...
The example (tested and working) assumes you are using SQL Server and have a Northwind database. And that you have a connection string in your web.config to the Northwind database named: "NorthwindConnectionString
If you have all those things...create a blank page and put the code below into the page...and the sample should work for you...if not...just look through the code and see how it differs from yours, yet does the same thing...it's going to output a table based on a dropdownlist value...
aspx:
===========================================
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString1 %>"
SelectCommand="SELECT [CustomerID], [CompanyName] FROM [Customers]"></asp:SqlDataSource>
<asp:DropDownList ID="DropDownList1" runat="server" AppendDataBoundItems="true" AutoPostBack="True"
DataSourceID="SqlDataSource1" DataTextField="CompanyName" DataValueField="CustomerID">
<asp:ListItem Text="Please Select" Value=""></asp:ListItem>
</asp:DropDownList><br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="true">
</asp:GridView>
codebehind:
===========================================
'(be sure the codebehind Imports system.data and system.data.sqlclient)
Private Function ShowOrdersByCustomerID(ByVal CustomerID As String) As DataTable
Try
Using MyConn As New SqlConnection(ConfigurationManager.ConnectionStrings("NorthwindConnectionString1").ConnectionString)
MyConn.Open()
Dim MyCmd As New SqlCommand("SELECT * FROM Orders WHERE CustomerID = @CustomerID", MyConn)
MyCmd.Parameters.Add(New SqlParameter("@CustomerID", CustomerID))
MyCmd.CommandType = CommandType.Text
Dim MyAdapter As New SqlDataAdapter
Dim MyDataset As New DataSet
MyAdapter.SelectCommand = MyCmd
MyAdapter.Fill(MyDataset)
Return MyDataset.Tables(0)
End Using
Catch ex As Exception
Response.Write("Error in ShowOrdersByCustomerID: " & ex.Message.ToString())
Dim dt As New DataTable
Return dt
End Try
End Function
Private Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DropDownList1.SelectedIndexChanged
Try
Me.GridView1.DataSource = Me.ShowOrdersByCustomerID(Me.DropDownList1.SelectedValue)
Me.GridView1.DataBind()
Catch ex As Exception
Response.Write("Error in DropDownList1_SelectedIndexChanged: " & ex.Message.ToString())
End Try
End Sub
ASP.NET
The successor to Active Server Pages, ASP.NET websites utilize the .NET framework to produce dynamic, data and content-driven web applications and services. ASP.NET code can be written using any .NET supported language. As of 2009, ASP.NET can also apply the Model-View-Controller (MVC) pattern to web applications
128K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
