在C＃中的多个SQL查询返回一个变量作为列 [英] Multiple SQL Queries in C# returning a variable as a column

问题描述

我工作的一所学校项目，以创建一个登记制度。我选择了DB的手段是使用T-SQL因为这件事情我已经熟悉了。

I'm working on a school project to create a registration system. My chosen means of a DB was to use T-SQL as it's something I'm already familiar.

我用下面的代码来查询数据库。

I'm using the below code to query a DB

    public void button3_Click(object sender, EventArgs e)
    {
        string StudentID = (textBox1.Text);
        string queryDOB = "SELECT DOB,LastName,Degree FROM Student Where StudentID = " + StudentID;

        SqlConnection con = new SqlConnection(Properties.Settings.Default.SRSConnectionString);
        con.Open();

        SqlCommand DOB = new SqlCommand(queryDOB, con);

        SqlDataReader stidreader = DOB.ExecuteReader();

        if (stidreader.Read())
        {
            textBox2.Text = stidreader["DOB"].ToString();
            textBox3.Text = stidreader["LastName"].ToString();
            textBox5.Text = stidreader["Degree"].ToString();
        }
        else
        {
            MessageBox.Show("Your ID is not recognised. Please try again.");
            textBox1.Clear();
            textBox2.Clear();
            textBox3.Clear();
            textBox5.Clear();
        }

        con.Close();
    }

    private void textBox5_TextChanged(object sender, EventArgs e)
    {
        string Degree = textBox5.Text;
        string degsql = "SELECT ModName FROM Module Where Degree = " + Degree;
        SqlConnection con = new SqlConnection(Properties.Settings.Default.SRSConnectionString);
        DataSet dsm = new DataSet();
        SqlDataAdapter connect = new SqlDataAdapter(degsql, con);

        con.Open();

        connect.Fill(dsm);
        this.listBox2.DataSource = dsm.Tables[0];
        this.listBox2.DisplayMember = "ModName";
}

该按钮点击第一部分完美地工作，但是当我运行查询下一个事件（文本框的变化），查询返回了无效的列中发现的错误消息。那么，发现列是可变的，它应该使用的查询，而不是列本身，这是非常令人困惑。

The first section for the button click works perfectly but when I run the query in the next event (textbox change) the query returns an error message that an invalid column is found. Well, the column found is the variable that it should be using for the Query, not the column itself which is very confusing.

我知道有可能利用所谓的问题MARS但DB似乎是正确的版本。

I know there can be issues with something called MARS but the DB seems to be the right version.

任何人都可以摆脱任何轻？

Can anyone shed any light?

欣赏它，

杰米

推荐答案

您缺少周围的变量名单引号。更改您的代码此修复SQL：

You are missing single quotes around your variable name. Change your code to this to fix the SQL:

string degsql = "SELECT ModName FROM Module Where Degree = '" + Degree + "'";

或者这看起来稍微好一点的：

Or this looks slightly better:

string degsql = string.Format(
    "SELECT ModName FROM Module Where Degree = '{0}'", 
    Degree);

不过，你不应该，因为它暴露了你的SQL注入攻击做这种方式。阅读这个问题明白这意味着什么：如何参数化查询有助于消除SQL ？注射

下面是一些代码做正确，并避免那些讨厌的安全问题：

Here is some code to do it properly and avoid those nasty security issues:

private void textBox5_TextChanged(object sender, EventArgs e)
{
    string Degree = textBox5.Text;
    string degsql = "SELECT ModName FROM Module Where Degree = @Degree";

    DataSet dsm = new DataSet();

    using(var con = new SqlConnection(Properties.Settings.Default.SRSConnectionString))
    {
        SqlCommand cmd = new SqlCommand(degsql, con);
        // use the appropriate SqlDbType:
        var parameter = new SqlParameter("@Degree", SqlDbType.Varchar);
        parameter.Value = Degree;
        cmd.Parameters.Add(parameter);

        SqlDataAdapter connect = new SqlDataAdapter(cmd);
        connect.Fill(dsm);
    }

    this.listBox2.DataSource = dsm.Tables[0];
    this.listBox2.DisplayMember = "ModName";

这篇关于在C＃中的多个SQL查询返回一个变量作为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！