I know this has to be easy. I'm just not sure of the best method to use. Should I be using ViewData?
Table 1 has columns:
ID
Name
TypeID
Table 2 has columns:
TypeID
TypeDescription
The tables are linked by a key on the TypeID column. I want to display:
Table1.ID
Table1.Name
Table2.TypeDescription
I'm trying to display data which comes from a join on two tables (or more) in a gridview I want to be able to filter the result set via user given input (text boxes on page)I have tried the standard tutorials but cannot find something which goes beyond displaying one table result sets in the gridview. If I go through the configure gridview and configure datasource wizard for any datasource (sqlDatasource, object, entitydatasource), when I use multiple tables I cannot use the 'where' parameters in the wizard, and therefore need to have the selecting code in the code-behind, but i'm unsure where exactly to put this.
[code]...
Im having a go at doing a report for my asp.net application. I have 2 tables, one which lists a loan we have given to a customer, and another table which lists repayments against the loans.
I want to display each loan and how much is outstanding against it. My problem is that each loan is repeated in the table for each repayment against it. So when i try to sum up the loans awarded to a customer, if they only have one loan and have made 3 repayments against it, it sums up the amount awarded against each record.
eg, if i award a customer a loan of £200, and they make 3 repayments of £50, 3 records appear in the table. The amount awarded is repeated against each repayment, and when i total a customers loan amounts, it totals 3 x £200.
I have put the details into the grouping row, where it is grouped by loan_id, displaying the toal of repayments instead of each repayment. This stops it repeating in the table, but when i total the amount awarded field it still calculates 3 x £200.
I tried calculating the total on the report item after grouping but you cannot calculate on a report item.
Is there a way to stop the table from repeating the amount awarded against each repayment? Or even a way in the SQL statement of the dataset to make it display a total of repayments rather than each repayment. Cant see how to calculate a total repaid as i would need to pass a Loan_id in as a parameter.
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?
I have two tables: Projects and ProjectsData and I want to execute query with join and get the result in the View. In the Controller I have this code:
ViewBag.projectsData = (from pd in db.ProjectsData
join p in db.Projects on pd.ProjectId equals p.ID
where pd.UserName == this.HttpContext.User.Identity.Name
orderby p.Name, p.ProjectNo
select new { ProjectData = pd, Project = p });
What I should use in the View to extract this data. I tried that:
@foreach (var item in ViewBag.projectsData)
{
@item.pd.UserName
}
but it doesn't work.
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 using bulk insert concept to export the data from dat file to tables(Two tables). I am using the temporary table for to do the calculation and insertion to two tables. My problem is whenever I selecting the temp table data after the execution of bulk insertion , the order is changing .I need to get the order as it is in file order(csv,dat,txt).
View 5 RepliesI'm joining 2 tables and using the formview. When I insert a record, I need to place the value of the max id + 1 of the second table in a field in the first table so that the tables are linked. I'm not sure where to put the value. I try to put it in the insert command and I've tried the select command but that doesn't work.
Table 1 - Product: ID, ProductNum, Brand, Description
Table2 - Brand: ID, BrandName
(Brand from Table1 is linked to ID from Table 2)
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
DeleteCommand="DELETE FROM [Product] ..."
InsertCommand="INSERT INTO [Product] ([productNum], [Brand] VALUES (@productNum, @Brand)"
SelectCommand="SELECT Product.ID, Product.Brand, Product.Description, Brand.ID, Brand.BrandName From Product INNER JOIN Brand ON Product.Brand= Brand.ID"
UpdateCommand="UPDATE [Product] SET [productNum] = @productNum, [Brand] = @Brand ......"
I've Tried InsertCommand="INSERT INTO [Product] ([productNum], [Brand] VALUES (@productNum, MAX(@Brand.ID) +1)"
or setting BrandID =Max(@Brand)
I want to return two tables using a mySqlDataReader and load the results into two datatables.
using (MySqlConnection connMySql = new MySqlConnection(global.g_connString))
{
MySqlCommand cmd = connMySql.CreateCommand();
cmd.CommandText = @"
SELECT * FROM table1;
SELECT * FROM table2;
";
connMySql.Open();
using (MySqlDataReader dr = cmd.ExecuteReader())
{
DataTable dt1 = new DataTable();
dt1.Load(dr);
dr.NextResult();
DataTable dt2 = new DataTable();
dt2.Load(dr);
gridView1.DataSource = dt1;
gridView1.DataBind();
gridView2.DataSource = dt2;
gridView2.DataBind();
}
However, when I run this, only one gridView is populated. Can I use NextResult in this way, or is there a better way to acheive this?
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();
}
As I know, we can use INSERT INTO (....) SELECT FROM command to select data from one table and can insert into another table.
Now I need instead of 1 table, I want to select data from one table and Insert into another 2 tables.
Shall I write another INSERT INTO (..) SELECT FROM command or is there any other way?
I tried it and it only returns the first table.
View 2 RepliesI have an update function in my data layer which is defined as:
public int UpdateRBTable(parameters ...) This calls a SQL Server Stored Procedure to perform an update function on the database.
The process does its job for updating the table. However, the stored procedure has a return value (which indicates how many rows were updated), but this return value is not returned to the application. The application always shows that a zero was returned from the stored procedure.
I want to get the data 4 or more tables. In such a way that all SubTable ID are present In the ParenTable.
But for some columns the Child ID are Null in the Parent Table.
Table1
ID
Name
SUBID
SUBSUBID
Table 2
SubID
nAME
Table 3
SUBSUBID
nAME
Values In Table 1 are like this
ID Name SubID SUBSUBID
1 A nULL 1
2 B 2 nULL
Want to join these tables and get the data from table1
I want to take my return value from sql server to c#.
my code
SQL SERVER:
ALTER procedure [dbo].[userss](@email varchar(100),@password varchar(25),@name varchar(250),@DOB varchar(20),@acc_vari int,@ip_address varchar(50),@vari_code varchar(7) )
as
begin
declare @uid int
select @uid=uid from users where email='12we'
if(@uid>0)
return 0
else
insert into users values(@email,@password,@name,@DOB,@acc_vari,@ip_address,@vari_code )
end
In this i want to take return value "return 0 " from sql server to c#.
Im trying to use the following SQL statement to find every row in Orderable Test List where there is not a row in Results Individual Tests that has a matching entry in the TESTHISCD column. When I run the page everything freezes up.
"SELECT [Orderable Test List].*, [Results Individual Tests].TESTHISCD FROM [Orderable Test List], [Results Individual Tests] WHERE [Orderable Test List].TESTHISCD <> [Results Individual Tests].TESTHISCD"
I have 3 SQL tables where the first 3 colums are of same Data. I need to display data of the 3 tables together for the first 3 colums which are of same datatype.
View 6 RepliesSuppose I have two tables "t1" and "t2"
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 two sql tables.
- (Fields:PersonID, FirstName, LastName, Role, Department)
and
- (fields: PersonID, Unit).
I want to merge those with a single table ( Fields:PersonID, FirstName, LastName, Role, Department, Unit). How can I query to accomplish this so that same personID data go to same row ? ANY QUERY ?? (I have an idea : both export to excel and merge into a single excel file and import to SQL table but due to very long data it was truncated last time when importing so I don't want to reimport again.)
I have excel file with columns EmpName,Date1,Date2,...Date7 .I want to insert EmpName into Employee table after inserting i will get EmpNo(Identity column) with that i need to insert Date1,Date2...Date7 into TimeSheet table.
My Excel structure is like this
EmpName 8/1/2010 8/2/2010 8/3/2010 8/4/2010 8/5/2010 8/6/2010 8/7/2010
Naresh 17:00-2:00 17:00-2:00 14:00-12:00 7:00-12:00 7:00-12:00 .. Off
Similarly 500 employees data
I want to insert name i.e into Emp table after inserting i will get EMpNo i.e identity column with that i should insert Date1(8/1/2010) i.e excel header to date7 into Timesheet table Date column and corresponding time into TimeIn and TimeOut.I will split the time.I just want write Stored procedure for this task.
My database tables are like this
Emp-EmpNo,EmpName,CompaignId
Timesheet-TimeID,EMpNo,Date,TimeIn,TimeOut,TotalHours
Date in Timesheet should be like this
TimeId EmpNo Date TimeIn TimeOut T otalHours
1 100 8/1/2010 17:00 2:00 9hrs
2 100 8/2/2010 17:00 2:00 9 hrs
7
I just want this logic on how to insert Date1 to date 7 as 7 rows into Timesheet table and EMpName into EMployee table.while accesing each row i need to insert Date 1 to date7 as 7 rows into Timesheet table.
I have the following tables:
1.) PRODUCT_INFORMATION
2.) CUSTOMERS
3.) DISTRIBUTORS
4.) PRODUCT_NAME
5.) CONTROLLER
Each table has a primary key assigned. The customers table has an ID column and a CUSTOMER_NAME column with unique index property set to avoid any duplication. How do I insert values in the product_information table that has a foreign key column from the CUSTOMERS table if we insert a value in the CUSTOMER_NAME field of the CUSTOMERS table. I want to know how can I create a join between all the tables so that when I insert values in individual tables they are joined with a serial number column in the PRODUCT_INFORMATION table?
I have 3 tables. I need to insert data from 2 tables into the 3rd table.
The first table has 2 fields -- c_id, c_key
The second table has -- t_id, u_id, partial_c_key
I need to insert c_id, u_id in the 3rd table.
Problem is partial_c_key in the second table contains only a part of the c_key in the first table.
Ex:
First table:
c_id--c_key
1--200A
2--200B
3--200C
4--301A
Second Table:
t_id -- u_id -- partial_c_key
1--23--200
2--36--301
In the data above partial_c_key 200 has 3 c_ids in the first table (1,2,3 with values 200A, 200B, 200C) and 301 has just 1 which is 301A.
In the 3rd table I need to insert c_id, u_id so the data should be
u_id -- c_id
23 -- 1
23 -- 2
23 -- 3
36 -- 4
How do I write a query to accomplish that?
am in problem and cant sort it out... my code is somwat lyk dis:
[Code]....
end sub
i hav joined both table..but am not able to display it in those controls.
I am new to Sql server 2005. I have a table in which contains many data. I need to take particular data. For example, I want to take value of 110652.795813712 from FTEBASEPAY column. So i have wrote the sql statment like in below. SELECT * From tblEmployees where FTEBASEPAY='53842.7782655718' But i am not able to get the particular value. Manually i have seen the tblEmployees table, in which contains the particular data ('53842.7782655718'). When i execute the above select statement, there is no result for it. What i have to for overcome this issue?I have to give one more information, the FTEBASEPAY dataType is float in tblEmployees table.
View 4 RepliesI have a dynamic sql query which contains more than one table joined. Normally I get this query to a datatable and use it. For now I want to get result of this query to a list object.
View 2 Replies
