Fetching data From One Table to another with same ID

Question

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 11-May-12 6:19am

M Ali Qadir

Updated 11-May-12 6:46am

v4

Add a Solution

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.

5 solutions

Solution 3

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.

Posted 11-May-12 7:59am

Clifford Nelson

Updated 11-May-12 8:57am

Chris Maunder774.1K

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!

Solution 4

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();
    }

}

Posted 11-May-12 15:15pm

VJ Reddy

Solution 5

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[^]

Posted 11-May-12 22:26pm

Maciej Los

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Wendelius · Accepted Answer · 2012-05-11T06:27:00

Solution 1

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[^]

Posted 11-May-12 6:27am

Wendelius

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!

Wendelius 12-May-12 6:03am

Thanks Sandeep :)

Maciej Los · Accepted Answer · 2012-05-11T06:44:00

Solution 2

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.

Posted 11-May-12 6:44am

Maciej Los

Updated 11-May-12 6:45am

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!

Maciej Los 12-May-12 3:50am

Thank you, VJ ;)

P.Salini 12-May-12 4:35am

My +5

Maciej Los 12-May-12 5:08am

Thank you ;)

Sandeep Mewara 12-May-12 5:51am

Good example 5!

Maciej Los 12-May-12 6:04am

Thank you ;)