如何使用文本框和按钮搜索数据库，然后在标签中显示结果？ [英] How to use a textbox and button to search a database, then show results in a label?

问题描述

好吧问题是我不知道我在做什么，我编写了php和html并且使用过MySQL数据库，但我正在尝试学习新的东西，而我正在努力项目，以帮助我学习和发展技能，但我已经碰壁了。我尝试了各种各样的方法来取得成功，但每次都失败了，我只是不知道该怎么做......

Ok so the problem is I don't know what I'm doing really, I have coded php and html and have worked with MySQL databases, but I am trying to learn something new, and I'm working on a little project to help me learn and develop skills, but I have hit a wall. I have tried a variety of ways to get a success but failed each time and I just don't know what to do...

<br />
form - bonus_ball<br />
database - playersdb<br />
dataset - playersdbDataSet<br />
database table - player_tbl<br />
textbox - num_input<br />
button - input_btn<br />
label(Name return) - win_name<br />
label(num return) - win_num<br />

所以在我的表格（bonus_ball）左边我把桌子放在< b> DataGridView（player_tblDataGridView）并且不可编辑，但如果手动向下滚动，则可以看到所有记录。但我想我有一个文本框（num_input），我可以输入一个数字，这是一个 int ，然后按按钮（input_btn）搜索数据库（playersdb）并搜索与该数字匹配的记录。如果/当它找到与该数字匹配的行时，我希望它生成与该行匹配并在标签中显示的名称和编号。我希望数字显示在标签 - win_num 中，然后是与标签中的数字相关联的名称 - win_name 。同时，如果数据库中的 NO 名称与数字匹配，仍然会在标签（win_num）中显示数字，然后生成否结果在标签（win_name）中......我为长篇大论的解释道歉。而且我不仅仅是在找人写代码给我，我想知道怎么做以及发生了什么...



我不喜欢我知道这是不是正确的地方，或者它应该在下面的位置。但是我已经尝试了很多不同的东西，尽管我可以解决的是按钮点击我必须连接到数据库然后我已经搜索了一个查询...

从player_tbl中选择*其中Id ='+ num_input.text +'

然后我需要返回显示 column-name <的结果/ b>在 win_name 标签和 win_num 标签中的数字



这是我看似的代码从tuts中汇总

So on my form(bonus_ball) the on the left I have my table in a DataGridView(player_tblDataGridView) and that is not editable but all the records can be seen if you manually scroll down. But I would like is I have a textbox(num_input) that I can enter a number, which is an int, then press the button(input_btn) to search the database(playersdb) and as it searches for a record that matches the number. if/when it finds a row that matches the number, I would like it to produce the name and number that matched the row and displayed in labels. I would like the number to be displayed in the label - win_num and then the name that's associated with the number to be in the label - win_name. Meanwhile, if there Is NO name in the database when it matches the number, still show the number in the label(win_num) then it to produce "NO Result" in the Label(win_name)... I apologise for the longwinded explanation. and I am not just looking for someone to write the code and give it me, I would like to know how and what is going on...

I don't know if this is the right place or it should be in the bit below. But I have tried many different things around this and as much as I can work out is on the button click I have to connect to the db then I have search a query as so...
Select * from player_tbl where Id='"+num_input.text+"'
then I need to return the results showing the column-name in the win_name label and number in the win_num label

This is the code I have seem to put together from the tuts

private void input_btn_Click(object sender, EventArgs e)
       {
           SqlConnection conn = new SqlConnection("Data Source=(LocalDB\\MSSQLLocalDB;AttachDbFilename=C:\\Users\\ric_c\\OneDrive\\Documents\\Visual Studio 2015\\Projects\\Lotto Bingo and Bonus Ball\\Lotto Bingo and Bonus Ball\\playersdb.mdf;Integrated Security=True");
           DataTable dt = new DataTable();
           SqlDataAdapter SDA = new SqlDataAdapter("Select name from player_tbl where Id='" + int.Parse(num_input.text), conn);
           SDA.Fill(dt);
           win_name.DataSource = dt;
       }

我尝试过：



我从视频中尝试了很多教程，一步一步地阅读说明。我试图自己解决这个问题，但有几次我真的搞砸了，不得不重新启动整个事情。

What I have tried:

I have tried many many tutorials from videos, to reading step by step instructions. I have tried to work it out myself but a couple of times I have really messed up and have to start the whole thing again.

推荐答案

你的问题是文本框和标签没有DataSource。但是你可以为它们的.Text属性赋值（比如来自@FeroseKhan的链接）

@FARONO也注意到你很容易受到SQL注入攻击。您永远不应该通过连接用户输入的字符串来构建查询。改为使用参数。



例如在你的情况下它会是这样的：

Your problem is that textboxes and labels do not have a DataSource. However you can assign values to their .Text property (like the link from @FeroseKhan)
@FARONO has also noted that you are vulnerable to SQL Injection. You should never build queries by concatenating strings of user input. Use Parameters instead.

For example in your case it would be something like this:

private void input_btn_Click(object sender, EventArgs e)
{
    var connectString = Properties.Settings.Default.SandboxConnectionString;

    SqlConnection conn = new SqlConnection(connectString);
    DataTable dt = new DataTable();

    string query = "Select Id, name from player_tbl where Id=@id";
    SqlDataAdapter SDA = new SqlDataAdapter(query, conn);
    var parameter = SDA.SelectCommand.Parameters.AddWithValue("@id", num_input.Text);
    SDA.Fill(dt);

    if (dt.Rows.Count > 0)
    {
        win_num.Text = dt.Rows[0]["Id"].ToString();
        win_name.Text = dt.Rows[0]["name"].ToString();
    }
    else
    {
        win_num.Text = "";
        win_name.Text = "No Result";
    }
}

注意查询中的差异 - 我在同一个选择中得到两个值。我没有使用Select * from player_tbl，因为我想控制返回的内容以及列的呈现顺序。



我是还用替换，其中Id ='+ int.Parse（num_input.text），其中Id = @ id - 如果 Id 是一个字符串（提示，它可能不应该是）然后我不必担心单引号。我也不必担心转换文本框首先将文本转换为整数 - 该参数为我处理所有这些。



一个更好的解决方案是用使用阻止例如

Note the differences in the query - I'm getting both values in the same select. I haven't used "Select * from player_tbl" because I want to be in control of what is returned and in what order the columns are presented.

I've also replaced where Id='" + int.Parse(num_input.text) with where Id=@id - If Id is a string (hint, it probably shouldn't be) then I don't have to worry about the single quotes. Nor do I have to worry about converting the textbox text to an integer first - the parameter handles all of that for me.

An even better solution would be to wrap the connection and dataadaptor with a using block e.g.

using (var conn = new SqlConnection(connectString))
{
    string query = "Select id, name from player_tbl where Id=@id";

    DataTable dt = new DataTable();
    using (var SDA = new SqlDataAdapter(query, conn))
    {

        var parameter = SDA.SelectCommand.Parameters.AddWithValue("@id", num_input.Text);
        SDA.Fill(dt);
    }

    if (dt.Rows.Count > 0)
    {
        win_num.Text = dt.Rows[0]["id"].ToString();
        win_name.Text = dt.Rows[0]["name"].ToString();
    }
    else
    {
        win_num.Text = "";
        win_name.Text = "No Result";
    }
}

这篇关于如何使用文本框和按钮搜索数据库，然后在标签中显示结果？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！