Click here to Skip to main content
15,877,675 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hi,

I have two table I Want to fetch Data from one table to another, In fact there are two tables,

ID Name ImageUrl And ID EmpID Name

I want that Name that is in the First Table with ID say that 2 should be Placed to the Name in the second table with EmpID= 2.

One thing both tables are connected by FK between ID And EmpID
Lets say
ID Name ImageUrl

2 Ali Images/1.jpg
3 Kelly Images/2.jpg

And in other table

ID EmpID Name CheckInTime CheckOutTime

20 2
21 3

So I want that When the ID of first Table And EmpID of second table are same then there should also be name there in the second table that is Showing in first Table. In fact Images are bind by a list and place in the Image button. So when i click on the Image button it adds an entry in the DB with current CheckInTime. Problem with the Name I am not able to insert it. Code is here
C#
public partial class Employee
    

public CheckInCheckOut LastCheckInCheckOut
        {
            get
            {
                return this.CheckInCheckOuts.Last();
            }
        }

    }

    public partial class _Default : System.Web.UI.Page
    {

        DataClasses1DataContext db = new DataClasses1DataContext();

        protected void Page_Load(object sender, EventArgs e)
        {
             if(!IsPostBack) BindData();
        }

       public void BindData()
        {
            
                DataClasses1DataContext db = new DataClasses1DataContext();

                var data = from emp in db.Employees
                           select emp;

                var items = data.ToList();
          
                    ListView1.DataSource = data;
                    ListView1.DataBind();
        }


        protected void UpdateTime(int EmpId)
        {
           DateTime dte = DateTime.Today;
            DataClasses1DataContext db = new DataClasses1DataContext();

            var chkInOut = (CheckInCheckOut)(from r in db.CheckInCheckOuts
                                             where r.EmpID == EmpId && r.CheckInTime.Value.Date.CompareTo(dte) == 0
                                             select r).FirstOrDefault();

            if (chkInOut == null)
            {
                chkInOut = new CheckInCheckOut();
                chkInOut.EmpID = EmpId;
                 
                chkInOut.CheckInTime = DateTime.Now;
                db.CheckInCheckOuts.InsertOnSubmit(chkInOut);
                db.SubmitChanges();
            }
            else
            {
                chkInOut.CheckOutTime = DateTime.Now;
                db.SubmitChanges();
            }
 
        }
 
        protected void ImageButton_Command(object sender, CommandEventArgs e)
        {
            int EmpId = Int32.Parse(e.CommandArgument.ToString());
                UpdateTime(EmpId);
                 
        }

        protected void Timer1_Tick(object sender, EventArgs e)
        {
            Label1.Text = DateTime.Now.ToLongTimeString();  
        }

    }

}{


So what trick I can use here.

regards

Ali
Posted
Updated 11-May-12 6:46am
v4
Comments
Sandeep Mewara 11-May-12 12:29pm    
Confusing. Can you rephrase it please. Put a sample data to illustrate whats in DB and what end result you are looking for.

Use the "Improve question" link to edit your question and provide better information.

No trick, if you do this in your SQL statement, you use a join (most likely an inner join in this case).

For a tutorial, see: SQL Joins[^]
 
Share this answer
 
Comments
Maciej Los 11-May-12 12:44pm    
+5!
Wendelius 11-May-12 13:58pm    
Thanks :)
VJ Reddy 11-May-12 20:07pm    
Good reference. 5!
Wendelius 12-May-12 4:15am    
Thanks :)
Sandeep Mewara 12-May-12 5:51am    
My 5!
Try this
SQL
SELECT t1.ID, t1.Name, t1.ImageUrl, t2.ID, t2.EmpID, t2.Name
FROM Table1 AS t1 LEFT JOIN Table2 AS t2 ON t1.Id = t2.EmpId
WHERE t1.Id = 2

but carefully read answer 1.
 
Share this answer
 
v2
Comments
M Ali Qadir 11-May-12 12:47pm    
I want to do Through C#
Maciej Los 11-May-12 14:21pm    
Where is a problem? C# + SQL = ADO.NET ;)
Using-ADO-NET-for-beginners
Wendelius 11-May-12 13:58pm    
Good example
Maciej Los 11-May-12 14:16pm    
Thanks ;)
VJ Reddy 11-May-12 20:08pm    
Good answer. 5!
If you want to do it in C#, then you probably want to use LINQ:

C#
var result = from i1 in Table1
   join i2 in Table2
   on i1.Id equals i2.EmpId
   select new
    {
      Name1=t1.Name,
      ImageUr=t1.ImageUrl,
      Id=t2.ID,
      EmpId=t2.EmpID,
      OtherName=t2.Name
    };


Note: This is an inner join, so will not have entries for records that do not match.
 
Share this answer
 
v3
Comments
VJ Reddy 11-May-12 21:01pm    
Good answer. 5!
Maciej Los 12-May-12 4:21am    
It's very interesting, my 5!
The answers 1 to 3 are good.

For the case shown in the question, since the Name is required to be inserted in the chkInOut record in the UpdateTime method, I think the following statements can be used in the else clause of UpdateTime method.


C#
protected void UpdateTime(int EmpId)
{
//statements
    if (chkInOut == null)
    {
        //statements
    }
    else
    {
        chkInOut.CheckOutTime = DateTime.Now;

        var employee = db.Employees.FirstOrDefault ( emp => emp.Id == chkInOut.EmpId);
        chkInOut.Name = employee == null ? string.Empty : employee.Name;
        
        db.SubmitChanges();
    }

}
 
Share this answer
 
As Clifford Nelson wrote, you can use LINQ to get data from your database.

Here you are some useful links:
Language INtegrated Query[^]
Using LINQ to SQL (Part 1)[^]
101 LINQ Samples for Visual C#[^]
LINQ Introduction Part 1 Of 3[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900