Return Multi-table Join Value From BLL?
Jul 26, 2010
This question is regarding the ASP.NET webservice that i am creating using the DAL-BLL architecture for my final school project.I have a stored procedure, which is a select query with an inner join for 2 tables.Hence the stored procedure returns multi-table value. One of my DAL tableAdapter methods accesses this stored procedure. How do i retrieve the return value in the BLL? Do i have to create a class structure similar to the one supposed to be returned by the stored proc? or is there a direct way to achieve the same?
Here is some more information:I am using the SQL dataset (.xsd) in DAL. So i have a datatable called "Insurance", which has a tableAdapter. One of the queries in the adapter references to a stored procedure, which has an inner join. So my SP looks like:
ALTER PROCEDURE dbo.GetInsurancesPaged
(
@startRowIndex int,
[code]...
View 2 Replies
Similar Messages:
Jan 21, 2011
I have faced the peculiar case in Sql server's Inner join. I have 2 tablesFor example:Table Name: A and B I have shown the below code.
Select
* From A
Inner
Join B
ON A.JobCode
=B.JobLookupID
This select statement return 500000 records but both table did not have that much recordsI have shown the Records in every table:A=19396 and B= 25366 records.How it will return more then 5 lakh records in above select statemenet?. Please let me know if any one faced this issue earlier?
View 5 Replies
Aug 3, 2010
I am new to MVC and am trying to join 2 SQL tables together and return them in a Json. One table is OutreachProgram. The other table is UserInfo. I want to return the rows from the OutreachProgram table that match on CenterID with the UserInfo table.
The code that exists now will pull all rows from the OutreachProgram table. I can't figure out how to add the UserInfo table to the query to make it work.
JsonGrid <OutreachProgram> grid =
new
JsonGrid<OutreachProgram>(db.OutreachProgram,
new
OutreachProgramFilter());
public Array GetItems(IQueryable<OutreachProgram>
source)
{ return (from r
in source.AsEnumerable()
select
new {
ID = r.OutreachProgramID, CenterID = r.CenterID, DateOutreachProvided = r.DateOutreachProvided.ToShortDateString(),
NameOfProvider = r.NameofProvider.ToString(),
}).ToArray();
}
View 2 Replies
Aug 18, 2010
I need to join two tables accross two different databases and return an ADO dataset.
ie: select * from database1.table1 a inner join database2.table2 b on
a.key=b.key where some_condition=true
My main problem is how to specify the SqlCommand parameters specifically the connection string for both databses. Is this possible?
View 4 Replies
Aug 5, 2010
I have 9 tables i need data out of.. 8 of those tables will always just return 1 row of fields that i need. the 9th table can contain many records.. So what i need is a few fields out of each of the 8 tables and then ALL records from the 9th table.. so i know that because there will be multiple records in the 9th table returned, that the fields i pull from the other 8 tables will contain duplicate information and thats fine since i will be displaying the individual records from table 9 on their own page..So everything could be for the same vendor, except for the date in the 9th table that contains specific dates and details about each record returned.When i review the execution plan im getting an yellow exclamation icon on a nested inner join and has this message as the warning: No Join Predicate
Here is what i have, but is returning incorrectly, way to may records, seems to be stuck in a loop.. then below that, i have one that returns the correct number of records, but everything except one column is duplicate, all the TR columns should be unique in the values stored in them.. the rest should all be duplicates, just because its the same vendor, so all the other information will be generic information about that record.
[Code]....
This FROM and WHERE return the correct number of records, but the fields are all duplicated except the last column with is transaction_date, seems that the 3 TR fields are being populated with the first records data or something.. not sure.
[Code]....
View 3 Replies
Mar 4, 2011
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?
View 2 Replies
Mar 18, 2011
I have this code in my controller:
public ActionResult Index()
{
MembershipUser currentUser = Membership.GetUser();
Guid UserId = (Guid)currentUser.ProviderUserKey;
using (var db = new MatchGamingEntities())
{
var MyAccount = from m in db.Accounts..........
I want to be able to do a join query between my two tables Accounts and BankTransactions. This is a one to many relationship, there can be multiple BankTransactions per Account. I want to query this and display the account information including all the bank statements that are associated with it. I did a join in order to get it, but I am having trouble handling the model. I keep getting this error:
LINQ to Entities does not recognize the method 'System.Collections.Generic.List1[MatchGaming.Models.BankTransaction] ToList[BankTransaction](System.Collections.Generic.IEnumerable1[MatchGaming.Models.BankTransaction])' method, and this method cannot be translated into a store expression.
View 2 Replies
Jan 18, 2011
Our current application is working fine but when you try to misbehave like we found out that When login with same user in multiple tab with different organization(there is a organization dropdown in the master page which sets the cookie whenever it is changed.) in tab one it is org 1 and tab 2 it is org2 , cookie has the later org 2 in it but when we go back in tab1(which had org1) and save the record org 2 will be saved with the record So can some one share some sort of a checklist with us which address these types of problem.
View 3 Replies
Oct 10, 2010
i use Default membership provider for my membership
i add a profile table for some more information from user and it related with UserId with aspnet_Users
to get data from my database i use Entity Data Model
now i want to write a Linq to get data from all 3 tables
View 1 Replies
Feb 13, 2010
I can join with a regular SQL table fine but this won't work:
SELECT normalTbl.*, tmp.fld1 FROM normalTbl
LEFT JOIN #Temp tmp ON tmp.joinID = normalTbl.joinID
All tmp.fld1 values are shown as NULL even though fld1 contains valid values and the joinID matches the normalTbl (when I call SELECT * from #Temp). If I do the same exact thing with a regular table, fld1 shows the correct value. What am I doing wrong?
View 4 Replies
Jun 9, 2010
my stored procedure is always extract some data into temp table such as"select name,address,content into #TEMP1 from table1,table2,table3"
finally, select another table together with #TEMP1 to extract all desire data from one queryselect T.name,T.address,T.content,C.other from #TEMP1 T, otherTable C
where T.ID=C.IDI would like to ask if i want to join one more table T2 into final query with "otherTable left C outer join on C.ID=T2.ID"
View 1 Replies
Mar 26, 2011
i want to delete data from two table using left outer join..
here is my query:
delete from projects p LEFT OUTER JOIN emp_project empp on p.serial_no=empp.projectid where p.serial_no=7
but i am getting error:
Incorrect syntax near 'p'.
View 6 Replies
Aug 5, 2010
I have set up Linq to sql. I now need to fill a formview with a select that does a join across a few tables. Have not been able to find any example or tuts that go beyond the simple select. Any suggestions? Can this even be done?
View 1 Replies
Nov 4, 2010
How to inner join 2 table with primary key by no duplicate data in sqlserver 2008 ?
View 5 Replies
Feb 25, 2010
I want to delete recodrs from based on some join condition between two tables.
One table belongs to my DB ( table name : FeeDetails)
and second table ( TargetTable) is on different server.
I have delete records from TargetTable table based on join condition between two tables.
I am able to select records
[Code]....
View 3 Replies
Aug 14, 2010
in my on-line shopping project i created a dynamic Category List (with Infinite Level Depth) Implemented in a Single Table in DB with Self join.the schema is like below:UpdateI want to use a JQuery plugin to make a Multi Level Menu bar. this plugin uses <ul> and <li> elements so I should transform the DB table to <ul> and <li>. the result should like this:
<ul>
<li>Clothing 1
<ul>
[code]...
View 2 Replies
Feb 18, 2010
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.
View 1 Replies
Mar 24, 2011
I'm trying to build a linq2Entity/gridview function for a forum, that draws data from multible tables.The result should be: Get the latest 10 threads that user(xx) has replyed in, that is not disabled or has a subject that is not allowed.Include information on the last post, thread owner user, and last post user, in each thread.
ere is my code... is returns the correct threads and some that should not be there.
[Code]....
View 1 Replies
Apr 6, 2010
I have two tables:
GalleryAlbums and GalleryPhotos.
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.
View 15 Replies
Jan 31, 2011
I have some text files I need to parse in order to display my data, and what I have now are two text files with lots of redundant data. Instead of this I would like to replace the redundant data with a number referencing the text in another text file.
View 1 Replies
Feb 4, 2010
I am a newbie on the subject. Seems I am missing something.
I have viewed and tried the samples in this site and tried to modify the moviesDB example with adding 2 more tables to my database. Tried to use other tables with different pages but didnt work. When I try to go to the page i created it redirects me to the index.aspx again and again. When I try to enter the direct adress to the explorer then I get an error. Is there any multi-paged and multi-tabled(sql i mean) example around? for the ppl who need to start from the begining....
View 2 Replies
Nov 22, 2010
I am working with asp.net 2.0 and sql server 2005. so trying to describe it below:
THE SITUATION
I have a few tables to hold data for billing information. The first table has each dedicated to each location, and a serial number that can be incremented. The concept is to update this data based on location, every time there is a new bill, so may be location A has serial 25 since it is doing well but location B has serial 5.
THE PROBLEM
SO based on this requirement I need to update a table to get the latest serial for a particular location and then use that serial for other tables which will be holding the billing details. The second part is as regular as it can be, but generating a new number, and getting this value is my problem. Since this will be multi user system, so I want some robust solution. I do not know whether I can use a identity field for this purpose or not.
View 1 Replies
Mar 19, 2011
i suppose this is the place to write select statements? I have two tables
table 1
id productid product
1 555 games
2 556 other
table 2
id productid (fk) filepath Date
1 555 images/golf.jpg 10/12/2010
2 555 images/tennis.jpg 11/12/2010
3 555 images/football.jpg 13/12/2010
4 556 + +
i require all values from table1 plus filepath from table2 to get image from directory with the latest date. table 1 also has a control parameter on id
current select statement:
[Code]....
View 6 Replies
Jun 6, 2010
I am making use of multi line textbox to display multiple table of 2 , but it is only showing the last value i.e 20. I made the following code:
public partial class _Default : System.Web.UI.Page
View 6 Replies
Oct 1, 2010
im tryin to do something without success. I have some SQL table with few columns (fileId(int), fileName, copies, color and finish), the page is build in a way, that the user upload few files (the files uploads to some folder, and the fileId and fileName are write to the above SQL table - so the other columns (copies, color, finish) are stay blank - which is OK!!!) after he finished uploading the files he can see the files names in GridView and in that grid view i put some textbox for copies, and 2 DDL for color and comments. I need to update the rows with the new data after the user click some button (the all rows) this is the code:
aspx
[Code]....
the aspx.cs
[Code]....
View 1 Replies
