Look for people who were missing, determined by date range and by type of person C# and access

Question

1.00/5 (1 vote)

See more:

, +

C#

OleDbConnection connection = MSAConnection.getConnection();
OleDbDataAdapter adapter = new OleDbDataAdapter();

string query = @"SELECT * FROM Persona
            WHERE NOT EXISTS
            (SELECT * FROM MarcacionesAsistencia
            WHERE (MarcacionesAsistencia.codPersona = Persona.codPersona)

            AND (MarcacionesAsistencia.tipoPersona = @tipo ))
            AND(MarcacionesAsistencia.fecha >= @desde)
            AND(MarcacionesAsistencia.fecha <= @hasta);";

OleDbCommand command = new OleDbCommand(query, connection);

command.Parameters.AddWithValue("@tipo", "Docente");
command.Parameters.AddWithValue("@desde", dateTimePickerDesde.Value);
command.Parameters.AddWithValue("@hasta", dateTimePickerHasta.Value);

adapter.SelectCommand = command;

DataSetReporteFalta falta = new DataSetReporteFalta();
falta.Clear();

adapter.Fill(falta, "DTReporteFalta");
dataGridView1.DataSource = falta;
dataGridView1.DataMember = "DTReporteFalta";

What I have tried:

C#

AND (MarcacionesAsistencia.tipoPersona Like @tipo ))
AND(MarcacionesAsistencia.fecha >= @desde)
AND(MarcacionesAsistencia.fecha <= @hasta);";

Posted 25-Jul-17 5:38am

Rodrigo Alex Rodriguez

Updated 22-Aug-17 6:13am

Add a Solution

Comments

j snooze 25-Jul-17 17:33pm

what is happening that you are not getting what you want? All I see is you describing what you want to do. No issues, errors or problems have been stated.

Rodrigo Alex Rodriguez 26-Jul-17 10:17am

I have a school, as the students arrive, teachers and principal register (with the code, name, date).
The problem is that I can not find a way to generate a report of the students, teachers who did not arrive using a date range.

Rodrigo Alex Rodriguez 26-Jul-17 10:20am

to the query does not throw any data

2 solutions

Solution 2

This is my solution
I hope you help them

private void getFaltas()
        {


            string dateString = dateTimePickerFalta.Value.ToString("yyyy/MM/dd");

            adapter.SelectCommand = new OleDbCommand
                    ("select p.codPersona, p.tipoPersona, p.nombreCompleto, p.grado, p.paralelo from Persona p where not exists(select ma.codPersona from MarcacionesAsistencia ma where ma.codPersona = p.codPersona and ma.fecha = # " + dateString + " #)", connection);

            dataset.Clear();

            adapter.Fill(dataset, "DTFaltas");
            dataGridView1.DataSource = dataset;
            dataGridView1.DataMember = "DTFaltas";
            dataGridView1.Refresh();

            reportViewerFaltas.Clear();
            reportViewerFaltas.RefreshReport();

        }

Posted 22-Aug-17 6:13am

Rodrigo Alex Rodriguez

Comments

Richard Deeming 25-Aug-17 13:29pm

NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

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)

radha patil · Accepted Answer · 2017-07-31T23:55:00

Solution 1

hi try this
selct * from person(select * from person where person=@person and access=@access and date betweeen from and to)

Posted 31-Jul-17 23:55pm

radha patil

Updated 1-Aug-17 0:02am

Comments

Rodrigo Alex Rodriguez 22-Aug-17 12:10pm

Completely restructure my method.
The solution to my query is
"select p.codPersona, p.tipoPersona, p.nombreCompleto, p.grado, p.paralelo from Persona p where not exists(select ma.codPersona from MarcacionesAsistencia ma where ma.codPersona = p.codPersona and ma.fecha = # " + dateString + " #)"