Inserting values of RFID,date,time and finding associated EMPID

  • (2 Pages)
  • +
  • 1
  • 2

15 Replies - 859 Views - Last Post: 08 March 2017 - 07:52 AM

#1 NazS  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 08-March 17

Inserting values of RFID,date,time and finding associated EMPID

Posted 08 March 2017 - 03:34 AM

Hi,

i am trying to bulid an Attendance system project and need a Query for inserting values into my dbTable which has two FIelds RFID and EmpID.
the RFID Field can be saved multiple times and need to find EmpID associated each time card is swiped.

the RFID which will be number of times must be saved at rumtime and i want associated EmpID next to it so that i can identify which user swipped card how many times.

code For saving into database :
 
SqlConnection con = new SqlConnection(str);
                con.Open();
                SqlCommand cmd = new SqlCommand("insert into EmpTable(TagID,Firstname,Lastname,Title) values (@TagID,@Firstname,@Lastname,@Title)", con);
              //   cmd.Parameters.AddWithValue("@EmpID", txtempid.Text);
                  cmd.Parameters.AddWithValue("@TagID", txttagid.Text);
                cmd.Parameters.AddWithValue("@Firstname", txtfirstname.Text);
                cmd.Parameters.AddWithValue("@Lastname", txtlastname.Text);
                cmd.Parameters.AddWithValue("@Title", txttitle.Text);
                cmd.ExecuteNonQuery();
                MessageBox.Show("Data Saved.");
                con.Close();



FOr reading RFID Tag from USB RFID Reader (not the whole code just dispalying)
 SqlConnection con = new SqlConnection(str);
            con.Open();
            SqlDataReader myReader = null;
            SqlCommand cmd = new SqlCommand("SELECT EmpID=MAX(EmpID)+1 FROM EmpTable", con);
            //    SqlCommand cmd= new SqlCommand("select  EmpID from SPTable where EmpID='" + txtempid.Text);
            //   cmd.Parameters.AddWithValue("@EmpID", txtempid.Text);
            myReader = cmd.ExecuteReader();
            while (myReader.Read())
            {
                txtempid.Text = (myReader["EmpID"].ToString());
            }
            con.Close();
            //      private SerialPort RFID;

            RFID.PortName = "COM";
            RFID.BaudRate = 9600;
            RFID.DataBits = 8;
            RFID.Parity = Parity.None;
            RFID.StopBits = StopBits.One;
            RFID.Open();
            RFID.ReadTimeout = 200;
            if (RFID.IsOpen)
            {
                DispString = "";
                txttagid.Text = "";
            }
            else
            {
                RFID.Close();
            }
            RFID.DataReceived += new SerialDataReceivedEventHandler(serialPort1_DataReceived);

        }
        private void serialPort1_DataReceived(object sender, SerialDataReceivedEventArgs e)
        {
            if (txttagid.Text.Length >= 12)
            {
                RFID.Close();

            }
            else
            {
                DispString = RFID.ReadExisting();
                this.Invoke(new EventHandler(DisplayText));
            } 
        }

        private void DisplayText(object sender, EventArgs e)
        {
            txttagid.AppendText(DispString);
        }




all i need is to save RFID,Date,Time fields at runtime to later calculate how many times employee swiped his card in a day.empid field i am using for identifying which employee swiped card how many times in a day.

Any suggestions?

Is This A Good Question/Topic? 0
  • +

Replies To: Inserting values of RFID,date,time and finding associated EMPID

#2 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 4136
  • View blog
  • Posts: 13,058
  • Joined: 08-June 10

Re: Inserting values of RFID,date,time and finding associated EMPID

Posted 08 March 2017 - 03:37 AM

Quote

the RFID Field can be saved multiple times and need to find EmpID associated each time card is swiped.

if the EmpID is dependent on the RFID, why do you want to save both in a table? this looks like a normalisation violation to me.
Was This Post Helpful? 0
  • +
  • -

#3 NazS  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 08-March 17

Re: Inserting values of RFID,date,time and finding associated EMPID

Posted 08 March 2017 - 03:41 AM

i am saving it just to identify the employee associated with the assigned tag.if not how to identify which employee swiped his card for how many times?
Was This Post Helpful? 0
  • +
  • -

#4 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 4136
  • View blog
  • Posts: 13,058
  • Joined: 08-June 10

Re: Inserting values of RFID,date,time and finding associated EMPID

Posted 08 March 2017 - 03:56 AM

Quote

if not how to identify which employee swiped his card for how many times?

Each employee has his own card. this info must be stored somewhere (employee data). once you have this relation, getting the employee id frome the swiped table is just a matter of a JOIN.
Was This Post Helpful? 0
  • +
  • -

#5 NazS  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 08-March 17

Re: Inserting values of RFID,date,time and finding associated EMPID

Posted 08 March 2017 - 04:00 AM

yes as i described in my question employee details are getting saved into EmpTable and have been trying to join but i am not getting the exact result what i want table is getting corrupted hence i need help with the query.
Was This Post Helpful? 0
  • +
  • -

#6 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 4136
  • View blog
  • Posts: 13,058
  • Joined: 08-June 10

Re: Inserting values of RFID,date,time and finding associated EMPID

Posted 08 March 2017 - 04:07 AM

what join have you tried?
Was This Post Helpful? 0
  • +
  • -

#7 NazS  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 08-March 17

Re: Inserting values of RFID,date,time and finding associated EMPID

Posted 08 March 2017 - 04:10 AM

INNER JOIN.

SELECT TagID,EmpID from EmpTable INNER JOIN dbTable ON EmpTable.EmpID = dbTable.EmpID

but then again i need to insert those three fields that is Date time and RFID at runtime so i dont know what to do and how to use this join with it.
Was This Post Helpful? 0
  • +
  • -

#8 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 4136
  • View blog
  • Posts: 13,058
  • Joined: 08-June 10

Re: Inserting values of RFID,date,time and finding associated EMPID

Posted 08 March 2017 - 04:15 AM

that query makes no sense. and as I said before EmpID is not necessary in dbTable.

Quote

i need to insert those three fields that is Date time and RFID at runtime

you only need to insert RFID and Date. the EmpID can be taken from the employee table.

SELECT
    e.EmpID,
    a.Date
FROM
    attendance a
INNER JOIN
    employee e
    ON
        a.RFID = e.RFID


Was This Post Helpful? 0
  • +
  • -

#9 NazS  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 08-March 17

Re: Inserting values of RFID,date,time and finding associated EMPID

Posted 08 March 2017 - 04:23 AM

RFID date and time those three at runtime i need and n number of times those things will happen and tried the above query as

SELECT e.EmpID FROM dbTable d INNER JOIN EmpTable e ON d.RFID = e.TagID

i got 175 results
Was This Post Helpful? 0
  • +
  • -

#10 NazS  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 08-March 17

Re: Inserting values of RFID,date,time and finding associated EMPID

Posted 08 March 2017 - 04:42 AM

and i need to insert rfid date time so insert will be there with join i made new table attendance with just one field RFID and without insert i have no data in the table
Was This Post Helpful? 0
  • +
  • -

#11 astonecipher  Icon User is offline

  • Too busy for this
  • member icon

Reputation: 2330
  • View blog
  • Posts: 9,381
  • Joined: 03-December 12

Re: Inserting values of RFID,date,time and finding associated EMPID

Posted 08 March 2017 - 06:33 AM

View PostNazS, on 08 March 2017 - 05:23 AM, said:

SELECT e.EmpID FROM dbTable d INNER JOIN EmpTable e ON d.RFID = e.TagID

i got 175 results



Well, if that employee scanned their card that many times, it would make sense that you have that many records.


How is the table corrupting? Is it repeatedly being corrupted? That is a bigger issue than an insert not working anyway.
Was This Post Helpful? 0
  • +
  • -

#12 NazS  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 08-March 17

Re: Inserting values of RFID,date,time and finding associated EMPID

Posted 08 March 2017 - 07:39 AM

View Postastonecipher, on 08 March 2017 - 07:03 PM, said:

View PostNazS, on 08 March 2017 - 05:23 AM, said:

SELECT e.EmpID FROM dbTable d INNER JOIN EmpTable e ON d.RFID = e.TagID

i got 175 results



Well, if that employee scanned their card that many times, it would make sense that you have that many records.


How is the table corrupting? Is it repeatedly being corrupted? That is a bigger issue than an insert not working anyway.

Was This Post Helpful? 0
  • +
  • -

#13 astonecipher  Icon User is offline

  • Too busy for this
  • member icon

Reputation: 2330
  • View blog
  • Posts: 9,381
  • Joined: 03-December 12

Re: Inserting values of RFID,date,time and finding associated EMPID

Posted 08 March 2017 - 07:41 AM

And the point of that quote was what exactly?
Was This Post Helpful? 0
  • +
  • -

#14 NazS  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 08-March 17

Re: Inserting values of RFID,date,time and finding associated EMPID

Posted 08 March 2017 - 07:45 AM

In that query there is no insertion of values whereas my need involves inserting values and keeping track of those fields.my database didnot have that much values yet by that query I got that many results.i double checked everything no way I have that many records hence that query is not working for me.

Nothing was trying to operate from mobile it just got pressed by mistake.
Was This Post Helpful? 0
  • +
  • -

#15 astonecipher  Icon User is offline

  • Too busy for this
  • member icon

Reputation: 2330
  • View blog
  • Posts: 9,381
  • Joined: 03-December 12

Re: Inserting values of RFID,date,time and finding associated EMPID

Posted 08 March 2017 - 07:48 AM

The only insert statement I see is when you create an employee. No where in what you posted are you inserting values from the card reader.


As for your 175 records, run the query in Management Studio. Your relationships are causing that many records to be returned, it isn't the query that is returning the wrong data.
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2