无法显示select语句 [英] cannot display select statement

问题描述

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

推荐答案

嗯。

如果你想加载一个DataTable，那么你就不要我想在一个循环中这样做...

你不应该将字符串连接起来形成SQL语句，这非常非常危险 - 这意味着用户只能破坏或破坏你的数据库通过键入文本框。您应该始终使用参数化查询。



尝试使用DataAdapter而不是DataReader。



它不起作用的另一个原因可能是日期也不起作用 - 因为SQL不知道用户输入数据的日期格式，所以假设您的用户没有犯任何错误，它仍然可以错误地解释它。



因此，首先检查他输入的日期 - 如果它有效，将其转换为DateTime值，如果它没有抱怨并且不做更多。 （或者首选，使用DateTimePicker控件而不是文本框，并直接使用Value属性 - 它将始终是一个有效的DateTime）

然后使用带有参数化查询的DataAdapter：

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:

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

您应该会发现它有效。

您的代码的一些描述：

A bit description of your code:

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

因此，使用此代码（跳过 while 循环，而不是使用命名参数）：

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

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

如需更多信息，请参阅：

OleDbCommand.CommandType Property [< a href =http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbcommand.commandtype%28v=vs.110%29.aspxtarget =_ blanktitle =New Window > ^ ]

OleDbParameterCollection.AddWithValue方法 [ ^ ]

命令和参数 [ ^ ]

参数声明（Microsoft Access SQL） [ ^ ]

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

这篇关于无法显示select语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！