无法从数据库中获取确切的值 [英] couldn't get the exact value from the database

问题描述

登录后，我想将label.Text更改为tom jerry，

但是输出显示m_fnm_sn两个列名而不是我想要显示的确切值。



感谢您的帮助，以下是表格和代码。



m_ac m_pw m_fn m_ln

测试测试tom jerry

After login,i want to change the label.Text to tom jerry,
but the output shows m_fnm_sn the two column names instead of the exact value that i wanted to show.

Thanks for the help, here are the table and code.

m_ac m_pw m_fn m_ln
test test tom jerry

private void Login_Button_Click(object sender, EventArgs e)
             {
MySqlConnection conn = new MySqlConnection(connectionString);
                 conn.Open();

                 MySqlCommand cmd = new MySqlCommand("select 'm_ac','m_pw','m_fn','m_ln' from member where m_ac='" + username_textBox.Text + "' and m_pw='" + password_textBox.Text + "'", conn);
                 MySqlDataReader dr;
                 dr = cmd.ExecuteReader();
                 while(dr.Read())
                 {
                     string fn = dr.GetString("m_fn");
                     string ln = dr.GetString("m_ln");
                     label1.Text = ac+ln;
                 }
}

推荐答案

在MySQL对象名称中（表名，列名.. 。）用反引号引用：`



你正在使用单引号，所以你实际上是在选择字符串常量而不是列。



所以这样做：

In MySQL object names (table names, column names...) are quoted with backticks: `

You're using single quotes, so you're actually selecting string constants instead of columns.

So do this instead:

select `m_ac`,`m_pw`,`m_fn`,`m_ln`  ...

其他建议：

- 使用SQL参数而不是连接查询字符串

- 使用直观的列名 - 列名可能的时间只有8个字符左右结束;）

- 使用使用 -Blocks作为Connection-，Command和DataReader- Objects

- 使用接口版本或抽象基类而不是数据库特定的类（例如IDbConnection或DbConnection而不是MySqlConnection） - 这将是al如果需要，可以轻松切换到另一个数据库系统，或者在另一个使用其他数据库系统的项目中重复使用您的代码：

Additional suggestions:
- Use SQL-Parameters instead of concatenating your query-string
- Use intuitive column names - the times where column names could only be 8 characters or so are over ;)
- Use using-Blocks for the Connection-, Command and DataReader-Objects
- Use either the Interface-versions or the abstract baseclasses instead of the database-specific classes (e.g. IDbConnection or DbConnection instead of MySqlConnection) - which will allow you to easily switch to another database system if required or re-use your code in another project where you'll use another database-system:

using (DbConnection conn = new MySqlConnection(connectionString))
{
    conn.Open();

    using (DbCommand cmd = conn.CreateCommand())
    {
        cmd.Connection = conn;
        cmd.CommandText = "select ....";

        using (DbDataReader dr = cmd.ExecuteReader())
        {
            // ...
        }
    }
}

在上面的示例中，您只需进行一次更改即可将其用于SQL-Server或其他一些数据库系统。如果您要使用 DbProviderFactory [ ^ ]创建你的Db ***** - 对象，你不需要改变任何东西。

获取DbProviderFactory [ ^ ]





评论后编辑：



cmd.CommandText =select`m_ac`，`m_pw`，`m_fn `，`m_ln`来自成员，其中m_ac =`+ username_textBox.Text +`和m_pw =`+ password_textBox.Text +`;



An MySql.Data.dll中发生未处理的异常

附加信息：'where'中的未知列'test'子句'



当在查询字符串中引用things时，你必须区分对象名（表名，列名）和字符串文字（你的文本框-输入）。对象名在MySQL中用反引号引用，字符串文字用单引号引用。对象名只有有引用如果它们否则会与MySQL-reserved-keyword冲突（例如名为select或from的表或列）。字符串文字有引用总是。



你用反引号引用你的TextBox输入，所以MySQL解释它作为列名，找不到名为test的列。



您的查询字符串构造可能如下所示：

In the above example you would only have to make one change to use it for SQL-Server or some other database system. If you would use a DbProviderFactory[^] to create your Db*****-Objects, you wouldn't have to change anything.
Obtaining a DbProviderFactory[^]


Edit after comment:

cmd.CommandText = "select `m_ac`,`m_pw`,`m_fn`,`m_ln` from member where m_ac=`" + username_textBox.Text + "` and m_pw=`" + password_textBox.Text + "`";

An unhandled exception occurred in MySql.Data.dll
Additional information: Unknown column 'test' in 'where clause'

When quoting "things" in a query string you have to differentiate between object names (table names, column names) and string literals (your TextBox-inputs). Object names are quoted with backticks in MySQL, string literals are quoted with single quotes. Object names only have to be quoted if they otherwise would collide with a MySQL-reserved-keyword (e.g. a table or column named "select" or "from"). String literals have to be quoted always.

You quoted your TextBox-inputs with backticks, so MySQL interpreted it as column-names and couldn't find the column named "test".

Your query string-construction can either look like this:

cmd.CommandText = "select `m_ac`, `m_pw`, `m_fn`, `m_ln` from `member` where `m_ac`='" + username_textBox.Text + "' and `m_pw`='" + password_textBox.Text + "'";

或者像这样：

or like this:

cmd.CommandText = "select m_ac, m_pw, m_fn, m_ln from member where m_ac='" + username_textBox.Text + "' and m_pw='" + password_textBox.Text + "'";

如果您使用SQL-Parameters，它可能看起来像这样，您不必费心引用字符串文字（除了避免SQL注入和更高可读性的风险等其他好处之外）：

If you would use SQL-Parameters, it could look like this and you wouldn't have to bother with quoting string literals (on top of other benefits like avoiding the risk of SQL-injection and better readibility):

cmd.CommandText = "select m_ac, m_pw, m_fn, m_ln from member where m_ac=?user and m_pw=?pw";

这篇关于无法从数据库中获取确切的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！