cannot display select statement

Question

1.00/5 (1 vote)

See more:

C#

try
                {

                    con.Open();
                    OleDbCommand cmnd = new OleDbCommand();
                    cmnd.Connection = con;
                    string query = "select FullName,datein,remarks from QAttendance where SubjDesc ='" + cBsubject.Text + "' AND datein= #" + lblcurdate.Text+"#";
                    cmnd.CommandText = query;
                    OleDbDataReader reader = cmnd.ExecuteReader();
                    while (reader.Read())
                    {
                        DataTable dt = new DataTable();
                        dt.Load(reader);
                        dtgviewatt.DataSource = dt;
                    }
                    con.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }

Posted 6-Dec-14 4:12am

markqui

Updated 6-Dec-14 4:15am

User 11060979

v2

Add a Solution

Comments

[no name] 6-Dec-14 10:17am

What means "cannot display select statement"?
Any exception while execute SQL?

markqui 6-Dec-14 10:20am

my datagridview can't display anything even if it doesn't return any error..

2 solutions

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)

OriginalGriff · Accepted Answer · 2014-12-06T04:34:00

Um.
If you want to load a DataTable, then you don't want to do it in a loop...
And you shouldn't be concatenating strings to form SQL statements, it's very, very dangerous - it means a user can damage or destroy your database just by typing in the text boxes. You should always use a parameterised query instead.

Try using a DataAdapter instead of a DataReader.

And the other reason it doesn't work is probably that the date doesn't work either - because SQL doesn't know what date format your user is entering data in, so assuming your user doesn't make any mistakes, it can still interpret it wrongly.

So, start by checking the date he entered - if it's valid, convert it to a DateTime value, if it isn't complain and do no more. (Or by preference, use a DateTimePicker control instead of a textbox, and use the Value property directly - it will always be a valid DateTime)
Then use a DataAdapter with a parameterised query:

C#

using (SqlConnection con = new SqlConnection(strConnect))
    {
    con.Open();
    SqlDataAdapter da = new SqlDataAdapter("SELECT FullName,datein,remarks FROM QAttendance WHERE SubjDesc = @SUBJ AND datein= @DATE", con);
    da.SelectCommand.Parameters.AddWithValue("@SUBJ", cBsubject.Text);
    da.SelectCommand.Parameters.AddWithValue("@DATE", userDateTimeValue);
    DataTable dt = new DataTable();
    da.Fill(dt);
    dtgviewatt.DataSource = dt;
    }

You should find it works.

Maciej Los · Accepted Answer · 2014-12-06T04:37:00

A bit description of your code:

C#

OleDbDataReader reader = cmnd.ExecuteReader();
//read data from OleDbReader
//loop through the collection of rows
while (reader.Read())
{
    //create new datatable
    DataTable dt = new DataTable();
    dt.Load(reader);
    //set datasource for dgv
    dtgviewatt.DataSource = dt;
}

So, instead of above, use this (skip While loop and use named parameters):

C#

OleDbCommand cmnd = new OleDbCommand();
                    cmnd.Connection = con;
                    string query = "PARAMETERS [subjectdesc] CHAR, [myDate] DATE; select FullName,datein,remarks from QAttendance where SubjDesc =[subjectdesc] AND datein=[myDate]";
                    cmnd.CommandText = query;
                    cmnd.CommandType = CommandType.StoredProcedure;
                    cmnd.Parameters.AddWithValue("subjectdesc", cBsubject.Text);
                    cmnd.Parameters.AddWithValue("myDate", lblcurdate.Text);
                    OleDbDataReader reader = cmnd.ExecuteReader();
                    DataTable dt = new DataTable();
                    dt.Load(reader);
                    dtgviewatt.DataSource = dt;
                    con.Close();

For furher information, please see:
OleDbCommand.CommandType Property[^]
OleDbParameterCollection.AddWithValue Method [^]
Commands and Parameters[^]
PARAMETERS Declaration (Microsoft Access SQL)[^]

cannot display select statement

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0