how get latest recordfrom mysql table

Question

0.00/5 (No votes)

See more:

hi,
am developing visitor tracking system, when visitor come ,we r giving a RFID card,and he swips the card enters into office, when he come out, that card handovers to watchmen.then again that card given to some other visitor.

then here the problem is how to write in time and out time. Here is the code am writing:

C#

String intime = "";
String outtime = "";

String indatetime=System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
String outdatetime = System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

using (MySqlConnection con = new MySqlConnection(ConnectionString))
{
   String query = "select * from visitor where card_id=@id ORDER BY card_id DESC LIMIT 1";
   MySqlCommand command = new MySqlCommand(query, con);
   command.Parameters.AddWithValue("@id", txtAddVisitorCardId.Text);
   con.Open();
   using (MySqlDataReader rdr = command.ExecuteReader())
   {
      if (rdr.Read())
      {
         intime = rdr["in_time"].ToString();
         outtime = rdr["out_time"].ToString();
      }
   }
}

if (((intime == "") && (outtime == "")) || ((intime!="")&&(outtime !="")))
{
   using (MySqlConnection con = new MySqlConnection(ConnectionString))
   {
      String query = "insert into visitor(card_id, name, age, address, id_proof, contact_person, purpose, in_time) values (@id, @name, @age, @address, @idproof, @contact, @purpose, @in)";
      MySqlCommand command = new MySqlCommand(query, con);
      command.Parameters.AddWithValue("@id", txtAddVisitorCardId.Text);
      command.Parameters.AddWithValue("@name", txtAddVisitorName.Text);
      command.Parameters.AddWithValue("@age", txtAddVisitorAge.Text);
      command.Parameters.AddWithValue("@address", txtAddVisitorAddress.Text);
      command.Parameters.AddWithValue("@idproof", txtAddVisitorIDProof.Text);
      command.Parameters.AddWithValue("@contact", txtAddVisitorContactPerson.Text);
      command.Parameters.AddWithValue("@purpose", txtAddVisitorPurpose.Text);
      command.Parameters.AddWithValue("@in", indatetime);
      con.Open();
      command.ExecuteNonQuery();
      MessageBox.Show("Visitor Added Successfully");
   }
}
else if ((intime != "") && (outtime == ""))
{
   using (MySqlConnection con = new MySqlConnection(ConnectionString))
   {
      String query = "update visitor set out_time=@out where card_id=@id ORDER BY card_id DESC LIMIT 1";
      MySqlCommand command = new MySqlCommand(query, con);
      command.Parameters.AddWithValue("@id", txtAddVisitorCardId.Text);
      command.Parameters.AddWithValue("@out", outdatetime);
      con.Open();
      command.ExecuteNonQuery();
   }
}

Posted 10-Apr-14 2:37am

Member 10263519

Updated 10-Apr-14 3:32am

phil.o

v2

Add a Solution

Comments

W∴ Balboos, GHB 10-Apr-14 11:46am

If your table had an identity field you could seek the max value for that field that matches the identity of the card being swiped. Since it's an identity field you'd use as a reference to qualify your UPDATE statement you know only one record will be updated and since it's the max relevant identity value, you know it's the most recent.

1 solution

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)

Maciej Los · Answer 1 · 2014-04-10T11:36:00

Solution 1

W&here4; Balboos[^] gave you valuable instruction in His comment to the question. I'd like to provide a link to the article about AUTO_INCREMENT[^]. There you'll find many useful information about your issue.

Posted 10-Apr-14 11:36am

Maciej Los

Comments

Member 10263519 11-Apr-14 5:58am

i didn't get

Maciej Los 11-Apr-14 6:31am

What did you not get?

Member 10263519 11-Apr-14 7:39am

ok, i was solved it another way, without using identity column.

Mysolution is here:
1)String query = "select * from visitor where card_id=@id and out_time is null";
2)String query = "update visitor set out_time=@out where card_id=@id and out_time is null";
3)String query = "insert into visitor(card_id,name,age,address,id_proof,contact_person,purpose,in_time) values(@id,@name,@age,@address,@idproof,@contact,@purpose,@in)";