DataSource Controls :: Unable To Retrieve Data From Two Tables While Using Inner Join?
May 25, 2010
I need to retirve data from two tables...when i click a value in dropdown list, i need to retrieve values from two tables based on that id( which i have selected in dropdown)..so when i have written query ( "Select assign_issue.issue_assigned_to, assign_issue.created_on,assign_issue.last_Date,
issue_register.issue_desc,issue_register.issue_Priority from assign_issue INNER JOIN Issue_register ON assign_Issue.Issue_ID = issue_register.Issue_ID where issue_register = '" + ddlissueid.selecteditem.text + "'")..query is executing ..when it comes to dr = cmd.executereader()..Dr is unable to read..it is showing false when i trace..unable to read into while loop.
I need to join some tables to get the matching records. I often have to ask around to see how certain tables could be joined. Is there any way to query the information schema or some thing else to see what columns/values match in certain tables in order to figure out how tables should be joined.
I have two tables namely "CategoryMapping" and "PartnerCategory"...The partnercategory table has foreign key with categorymapping on CategoryID...
let us say categorymapping should be filed first then partnercategory should be filed based on the categories defined in categorymapping. For example my two tables have the following data:
I would like to Select all entries in the GalleryAlbums table, but Order them (DESC) by date values in a column (upload_date) on the GalleryPhotos table. Both tables have a column "album_id"
A little confused how I might write this statement.
have table COLLECTIONS and table CLOTHES.As an example, when showing the items on Listview, collection HAWAII would be written on the top, and its texts and images on the itemTemplate.Then, collection WINTER and its texts and images on the itemTemplate.And so on.P.S: I´m connecting the Listview visually thru SQLDataSource, not programatically.How can i do it ? On the layout template using a JOIN on the SQL Statement ?
To write a join query with or condition. It means a query has two inner join, here it is possible to fetch the records, if one inner join is true and other is false. I got a record when only two join conditions are true.
I have a particular scenario where I am populating two data tables seperately. These datatables have one field which is unique. What I need is to join these two datatables on the basis of that one field and get one datatable. I do not want to merge, I need to join. And I can not join in SQL before populating the data Tables.
t1 : id FirstName Age 1 Joe 22 2 James 33 3 Bart 28 t2: id LastName 1 Coymer 2 Manes
I can combine the data from the two tables using an inner join.
1 Joe 22 CoyLap 2 James 33 Manes
The problem though is that the person "Bart" with an Id of 3 is left out because he does not have an entry in the second table.So how can I include Bart in the output in such a way that the output might look like this:
1 Joe 22 CoyLap 2 James 33 Manes 3 Bart null null
Or spaces can instead appear for Barts last name and age instead of null.Basically I just need Bart in the output as well even though he has no age and last name.
I have a gridview showing values from two tables accommodation and pic (left join on accommodation). If there is no pic uploaded I want to display the row without the pic. So in the gridview I first check to see if the picid is null, if it is i don't render any 'a href' code , if it's isn't i do.
The problem that i'm having is that instead of the image showing up in the gridview itemtemplate i get ','_blank','toolbar=no,menubar=no'))" >
Here is my .aspx code
SelectCommand="SELECT pic.picid, aspnet_Users.Username, accommodation.location, accommodation.type,accommodation.description, convert(varchar,accommodation.createdate,101) as createdate
I want to get data from Projects(which have CourseId) and related CourseName from Courses table.
I have written following code:
var projects = from n in db.Projects join c in db.Courses on n.CourseId equals c.ID orderby n.Name select new { Project = n, Course = c }; return View(projects.ToList());
and I get error:
The model item passed into the dictionary is of type 'System.Collections.Generic.List1[<>f__AnonymousType22[ProjectManager.Models.Project,ProjectManager.Models.Course]]', but this dictionary requires a model item of type 'System.Collections.Generic.IEnumerable`1[ProjectManager.Models.Project]'.
What I need to do in Controller and in View to display this data?
i am still stumped with this operation: i am trying to retrieve a GridView selected row data..here is my code.. the commented part that gives me the selectedindex works just fine....but the one giving the selected row cell value does't work and there is no error.. The update operation is done successfully...weired. i need to assigned selected row cells values to variables ..do some calculations and then re-assign the result calculation to cell[9]..
if you have two table inner join like SELECT* From A INNER JOIN B on A.ID = B.IDSay A and B tables both have Username values but A's username is Test, and B's username is Test2When I do Eval("Username") it always print out "Test", how can I configure it to print out "Test2"... meaning how to specify which table's value to put using eval.. is there something like Eval(B.("Username"))??
I was trying to find out how I can join more than 2 tables using the LINQ-to-SQL syntax. For instance, joining 2 tables in SQL:
SELECT * FROM Table1 AS T1 INNER JOIN Table2 AS T2 ON T1.Key=T2.Column1WHERE T2.Key='17';
can be expressed as:
var Result = from T1 in DbContext.Table1 join T2 in DbContext.Table2 on T1.Key equals T2.ForeignKey where T2.Key=17 select new { T1, T2 };
But how would I join 3 or more tables using LINQ? For example:
SELECT * FROM (Table1 AS T1 INNER JOIN Table2 AS T2 ON T1.Key=T2.Column1) INNER JOIN Table3 AS T3 ON T3.Key=T2.Column2 WHERE T2.Key='37';
I've been searching and experimenting and I cannot seem to find any informraiton on this. One example I found involves putting the result for the frist join into a temp object, and then performing the second join. I'm not sure performance-wise if that's the same as doing a 3-table join directly using a single SQL statement.
I have a scenario, I will pass a StatusID , According to that StatusID , The rows will be fetched from a table[tblEventStatus] and newly fetched row has to fecth the data from another table [tblEvent].
i got checkbox in gridview which put inside the itemtemplate. the problem is when i tick on the checkbox, the value for checked property still is a false value. Wat;s wrong?
If GridViewRDR1_Hidden.Rows.Count > 0 Then For Each row As GridViewRow In GridViewRDR1_Hidden.Rows Dim cb As CheckBox = CType(row.FindControl("chkstatus"), CheckBox ) If cb.Checked Then MsgBox("True") End If Next End If
I am trying to update the database with the edited data. However, it did not manage to read the edited data from the textbox. It returned the retrieve data that I have done in the Page_Load instead. Is there any part of the code that I have written wrongly? I did manage not to use <EditItemTemplate> in my past project and manage to update the database with the same logic.
<aspx> <!-- This DataList is created to view Personal Information --> <asp:DataList ID="personal_dl" runat="server"> <ItemTemplate> <table> <tr> <td style="width: 100px; text-align: left;"> <asp:Label ID="first_lbl" runat="server" ForeColor="Black" Font-Size="11px" Text="First:"></asp:Label></td> <td style="width: 350px"> <asp:TextBox ID="first_tb" runat="server" Width="341px" Font-Size="11px" Text='<%# Eval("FIRST") %>'> </asp:TextBox> </td> </tr> <tr> <td style="width: 100px; text-align: left"> <asp:Label ID="name_lbl" runat="server" Font-Size="11px" ForeColor="Black" Text="Name:"></asp:Label> </td> <td style="width: 350px"> <asp:TextBox ID="name_tb" runat="server" Width="341px" Font-Size="11px" Text='<%# Eval("NAME") %>'> </asp:TextBox> </td> </tr> <tr> <td style="width: 100px; text-align: left;"> <asp:Label ID="id_lbl" runat="server" ForeColor="Black" Font-Size="11px" Text="ID:"> </asp:Label> </td> <td style="width: 350px"> <asp:TextBox ID="id_tb" runat="server" Width="341px" Font-Size="11px" Text='<%# Eval("ID") %>'> </asp:TextBox> </td> </tr> <tr> <td style="width: 100px; text-align: left;"> <asp:Label ID="contact_lbl" runat="server" ForeColor="Black" Font-Size="11px" Text="Contact No.:"> </asp:Label> </td> <td style="width: 350px"> <asp:TextBox ID="contact_tb" runat="server" Width="341px" Font-Size="11px" Text='<%# Eval("TELEPHONE_HP") %>'> </asp:TextBox> </td> </tr> <tr> <td style="width: 100px; text-align: left;"> <asp:Label ID="add_lbl" runat="server" ForeColor="Black" Font-Size="11px" Text="Address:"> </asp:Label> </td> <td style="width: 350px"> <asp:TextBox ID="add_tb" runat="server" Width="341px" Font-Size="11px" Text='<%# Eval("ADDRESS") %>'> </asp:TextBox> </td> </tr> </table> </ItemTemplate> </asp:DataList> <br /> <div style="text-align: center; color: Red; width: 460px;"> <asp:Label ID="errorMsg_lbl" runat="server" /> <asp:Button ID="edit_btn" runat="server" Text="Update" /> <asp:Button ID="createBtn" runat="server" Text="Insert" /></div> <aspx.vb> Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load 'display information in datalist Dim login As String = Session("login") Dim id As String = Session("id") bindDataList(personal_dl, getPersonalContent(login, id)) 'display necessary DataList personal_dl.Visible = True 'declare fields Dim i As Integer Dim first_tb, name_tb, id_tb, contact_tb, add_tb As New TextBox For i = 0 To personal_dl.Items.Count - 1 first_tb = personal_dl.Items(i).FindControl("first_tb") name_tb = personal_dl.Items(i).FindControl("name_tb") id_tb = personal_dl.Items(i).FindControl("id_tb") contact_tb = personal_dl.Items(i).FindControl("contact_tb") add_tb = personal_dl.Items(i).FindControl("add_tb") 'set fields as ReadOnly - cannot edit first_tb.ReadOnly = False name_tb.ReadOnly = False id_tb.ReadOnly = False contact_tb.ReadOnly = False add_tb.ReadOnly = False Next End Sub
Protected Sub edit_btn_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles edit_btn.Click 'declare fields Dim i As Integer Dim first_tb, name_tb, id_tb, contact_tb, add_tb As New TextBox Dim id As String = Session("id") For i = 0 To personal_dl.Items.Count - 1 first_tb = personal_dl.Items(i).FindControl("first_tb") name_tb = personal_dl.Items(i).FindControl("name_tb") id_tb = personal_dl.Items(i).FindControl("id_tb") contact_tb = personal_dl.Items(i).FindControl("contact_tb") add_tb = personal_dl.Items(i).FindControl("add_tb") Next Dim myConnection As OleDbConnection = dbConnection() Dim cmd As New OleDbCommand Dim sqlStatement As String Dim condition As String condition = "UPDATE staff " + _ "SET ((FIRST= @FIRST), (NAME = @NAME), (ID= @ID), (TELEPHONE_NO = @CONTACT_NO), (ADDRESS = @ADDRESS)) " + _ "WHERE (ID= @id)" sqlStatement = condition cmd.CommandText = sqlStatement cmd.CommandType = CommandType.Text cmd.Parameters.AddWithValue("@FIRST", first_tb) cmd.Parameters.AddWithValue("@NAME", name_tb) cmd.Parameters.AddWithValue("@ID", id_tb) cmd.Parameters.AddWithValue("@TELEPHONE_NO", contact_tb) cmd.Parameters.AddWithValue("@ADDRESS", add_tb) cmd.Parameters.AddWithValue("@ID", id) cmd.Connection = myConnection Try myConnection.Open() cmd.ExecuteNonQuery() Catch ex As Exception Finally If myConnection.State = ConnectionState.Open Then myConnection.Close() End If End Try 'display information in datalist Dim login As String = Session("login") bindDataList(personal_dl, getPersonalContent(login, id)) End Sub
TABLE 1 SELECT I.STUNA ,S.SNO ,SUM(B.AMT * Y.YEAR) AS AMT FROM STUDENT S LEFT JOIN INFO I ON S.SNO = I.SNO LEFT JOIN BONUS B ON S.SNO = B.SSNO LEFT JOIN YEAR Y ON S.SNO = Y.SSNO STUNA SNO AMT JOHN A 10 LISA B 20 ALLEN C 100
TABLE 2 SNO AMT A 1 B 2 C 3 D 5
I WANT TO HAVE RESULT LIKE THIS (TABLE1 + TABLE2)
STUNA SNO AMT JOHN A 11 LISA B 22 ALLEN C 103 EDDIE D 5
I TRIED USE THIS QEURY
SELECT I.STUNA ,S.SNO ,SUM(B.AMT) AS AMT FROM STUDENT S LEFT JOIN INFO I ON S.SNO = I.SNO LEFT JOIN BONUS B ON S.SNO = B.SSNO LEFT JOIN ( SELECT SNO, SUM(AMT) AS AMT FROM TABLE2 GROUP BY SNO ) T2 ON S.SNO = T2.SNO GROUP BY I.STUNA,S.SNO THE RESULT IS STUNA SNO AMT JOHN A 11 LISA B 22 ALLEN C 103
THE "EDDIE" WAS MISS. ALSO I TRY USE CROSS JOIN... THE RESULT IS STILL NOT CORRECT.
User, Details, Optional User has fields: ID, FirstName, LastName Details has fields: ID, MyDetails1, MyDetails2, User_ID Optional has fields: ID, MyOptions1, MyOptions2, User_ID There is a 1 to many relationship with User & Detail (User ID & Detail User_ID) There is a 1 to many relationship with User & Optional (User ID & Optional User_ID)
With that said, one user can have many details and/or many optionals
Now I'm trying to build a linq query that will say:
"where user id = x, select FirstName, LastName, all MyDetails1 from every record where User_ID == x, all MyOptions1 from every record where User_ID == x"
After the merge I should have one string that lists the distict user & all of their many fields:
i need to join two tables in different databases, how it can be done??? and if i want to use sqldatasource control to bind results to some data-bound control how can I configure my sqldatasource control???
