使用C＃中的访问数据库中的递增值填充文本框 [英] Fill textbox with an incremented value from access database in C#

问题描述

您好，





我创建了一个C＃应用程序来查询和插入产品数据库。不过我在这里有一个小疑问，如果有人能帮助我，我马上就感谢你。



以下是：



我有一个表格可以将数据插入MS Access 2007中创建的数据库中，其中包含参考值，销售号，客户代码，客户名称，数量和位置编号;



我怎样才能这样做，而不是在文本框中手动输入存档中的位置编号，它可以自动填充存档中的新位置。例如，我插入的最后一个产品在存档中位置为50，新的产品将自动为51，依此类推......此数字应自动出现在文本框中，以便用户知道新注册的数量是多少产品。





谢谢，



我是什么尝试过：



这是我的代码，直到现在：

 private void btn_save_Click（object sender，EventArgs e）
 {
 OleDbConnection con = new OleDbConnection（@Provider = Microsoft.ACE.OLEDB.12.0; Data Source = product.accdb）; 
 OleDbCommand check_sn = new OleDbCommand（SELECT COUNT（*）FROM [product] WHERE（[sn] = @sn），con）; 
 OleDbCommand check_reference = new OleDbCommand（SELECT COUNT（*）FROM [product] WHERE（[reference] = @ref），con）; 
 OleDbCommand check_number = new OleDbCommand（SELECT COUNT（*）FROM [product] WHERE（[number] = @num），con）; 
 
 con.Open（）; 
 check_reference.Parameters.AddWithValue（@ ref，textBox_ref.Text）; 
 check_sn.Parameters.AddWithValue（@ sn，textBox_sn.Text）; 
 check_number.Parameters.AddWithValue（@ num，textBox_num.Text）; 
 
 int refExist =（int）check_reference.ExecuteScalar（）; 
 int SNExist =（int）check_sn.ExecuteScalar（）; 
 int numExist =（int）check_number.ExecuteScalar（）; 
 
 
 if（refExist> 0）
 {
 MessageBox.Show（带有此引用的产品已经存在......！）; 
} 
 else if（SNExist> 0）
 {
 MessageBox.Show（具有此销售号码的产品已经存在......！）; 
} 
 else if（numExist> 0）
 {
 MessageBox.Show（具有此存档号的产品已存在......！）; 
} 
 else 
 {
 try 
 {
 String reference = textBox_ref.Text.ToString（）; 
 String sn = textBox_ov.Text.ToString（）; 
 String cod_client = textBox_cod.Text.ToString（）; 
 String client = textBox_cliente.Text.ToString（）; 
 String qtd = textBox_qtd.Text.ToString（）; 
 String number = textBox_num.Text.ToString（）; //这将是递增的数字
 
 String my_querry =INSERT INTO product（reference，sn，cod_client，client，qtd，number）VALUES（'+ reference +'，'+ sn +'，'+ cod_client +'，'+ client +'，'+ qtd +'，'+ number +'）; 
 OleDbCommand cmd = new OleDbCommand（my_querry，con）; 
 cmd.ExecuteNonQuery（）; 
 
 MessageBox.Show（数据保存成功......！）; 
 
} 
 catch（exception ex）
 {
 MessageBox.Show（因+ ex.Message而失败）; 
} 
最后
 {
 
 con.Close（）; 
} 
 cleanTextBoxes（this.Controls）; 
} 
} 
 
 
 
 private void search_btn_Click（对象发送者，EventArgs e）
 {
表单搜索=新Form_search （）; 
 
 search.Show（）; 
 
 this.Hide（）; 
} 
} 

解决方案

在数据库中使用IDENTITY或自动编号字段并让它处理顺序值 - 在您的演示代码中预先分配增量值是一个危险的想法，因为它可能导致生产中的一些令人讨厌的错误，这些错误是PITA要找到的，不可能可靠地复制，并且难以修复。 />


理查德是绝对正确的：永远不要连接字符串来构建SQL命令。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏您的整个数据库。总是使用参数化查询。



连接字符串时会导致问题，因为SQL会收到如下命令：

  SELECT  *  FROM  MyTable  WHERE  StreetAddress = '  Baker' s Wood ' < span class =code-string>  

就SQL而言，用户添加的引号会终止字符串，并且您会遇到问题。但情况可能更糟。如果我来并改为输入：x'; DROP TABLE MyTable; - 然后SQL收到一个非常不同的命令：

  SELECT  *  FROM  MyTable  WHERE  StreetAddress = '  x';  DROP  表 MyTable;   -   ' 

哪个SQL看作三个单独的命令：

  SELECT  *  FROM  MyTable  WHERE  StreetAddress = '  x'; 

完全有效的SELECT

  DROP   TABLE  MyTable; 

完全有效的删除表格通讯和

   -   ' 

其他一切都是评论。

所以它确实：选择任何匹配的行，从数据库中删除表，并忽略其他任何内容。



所以总是使用参数化查询！或者准备好经常从备份中恢复数据库。您是否定期进行备份，不是吗？

如何在同一段代码中安装一个安全注入的SQL命令和另一个注射的命令？

 字符串 my_querry =   INSERT INTO product（reference，sn，cod_client，client，qtd，number）VALUES（' + reference +  '，' + sn +  '，' + cod_client +  '，' + client +  '，' + qtd +  ' ，' + number +  '）; 

永远不要通过连接字符串来构建SQL查询。迟早，您将使用用户输入来执行此操作，这会打开一个名为SQL注入的漏洞，这对您的数据库很容易并且容易出错。

名称中的单引号你的程序崩溃。如果用户输入像Brian O'Conner这样的名称可能会使您的应用程序崩溃，那么这是一个SQL注入漏洞，崩溃是最少的问题，恶意用户输入，并且它被提升为具有所有凭据的SQL命令。

SQL注入 - 维基百科 [ ^ ]

SQL注入 [ ^ ]

按示例进行SQL注入攻击 [ ^ ]

PHP：SQL注入 - 手册 [ ^ ]

SQL注入预防备忘单 - OWASP [ ^ ]

我该怎么办？解释没有技术术语的SQL注入？ - 信息安全堆栈交换 [ ^ ]

正如@OriginalGriff已经声明的那样，数据库表中的ID字段应该是一个AutoNumber字段。这样Access将自动为您增加您的ID，如果有多个用户，您将不会得到重复的ID。



一旦你设置了它，你可以使用@@ Identity构造，它允许你查询你插入的最后一个ID只要你使用相同的连接。



在您发布的即将删除的解决方案3中，您忽略了所有关于SQL注入并仍在尝试增加表上的最大ID - 该技术根本不起作用。



克服SQL注入问题（巧合地解决任何问题）您可能有列类型和格式不正确的字符串连接）执行类似这样的操作

 字符串 my_querry =   INSERT INTO产品（reference，sn，cod_client，client，qtd，number）VALUES（@ ref，@ sn，@ cod_client，@ client，@ qtd，@数）; 
 OleDbCommand cmd =  new  OleDbCommand（my_querry，con）; 
 cmd.Parameters.AddWithValue（  @ ref，reference）; 
 cmd.Parameters.AddWithValue（  @ sn，sn）; 
 cmd.Parameters.AddWithValue（  @ cod_client，cod_client）; 
 cmd.Parameters.AddWithValue（  @ client，client）; 
 cmd.Parameters.AddWithValue（  @ qtd，qtd）; 
 cmd.Parameters.AddWithValue（  @ number，number）; 
 cmd.ExecuteNonQuery（）; 

要获取最后输入的ID，请执行以下操作

 cmd.CommandText =  选择@@ Identity; 
  int  newID =（ int ）cmd.ExecuteScalar（）; 

注意：这与用于发出Insert命令的 cmd 对象相同。

在使用<$ c $之前不要关闭连接c>选择@@ Identity

在使用选择@@ Identity 之前，请勿在该连接上发出任何其他SQL命令

Hello,


I created a C # application to query and insert a product database. However I am here with a small doubt and if anyone can help me i thank you right away.

The following is:

I have a form to insert data into the database created in MS Access 2007, with the values of reference, sale number, client code, client name, quantity and position number in archive;

How can i make it so that instead of manually entering the position number in archive in the textbox it can be automatically filled with the new position in archive. For example, my last product inserted has the position 50 in archive, the new one will automatically be number 51 and so on ... and this number should appear automatically in the textbox so that the user knows what is the number of the new registered product.


Thank you,

What I have tried:

Here is my code until the moment:

private void btn_save_Click(object sender, EventArgs e)
    {
        OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=product.accdb");
        OleDbCommand check_sn = new OleDbCommand("SELECT COUNT(*) FROM [product] WHERE ([sn] = @sn)", con);
        OleDbCommand check_reference = new OleDbCommand("SELECT COUNT(*) FROM [product] WHERE ([reference] = @ref)", con);
        OleDbCommand check_number = new OleDbCommand("SELECT COUNT(*) FROM [product] WHERE ([number] = @num)", con);

        con.Open();
        check_reference.Parameters.AddWithValue("@ref", textBox_ref.Text);
        check_sn.Parameters.AddWithValue("@sn", textBox_sn.Text);
        check_number.Parameters.AddWithValue("@num", textBox_num.Text);

        int refExist = (int)check_reference.ExecuteScalar();
        int SNExist = (int)check_sn.ExecuteScalar();
        int numExist = (int)check_number.ExecuteScalar();


        if (refExist > 0)
        {
            MessageBox.Show("A product with this reference already exists....!");
        }
        else if (SNExist> 0)
        {
            MessageBox.Show("A product with this sale number already exists....!");
        }
        else if (numExist > 0)
        {
            MessageBox.Show("A product with this archive number already exists....!");
        }
        else
        {
            try
            {
                String reference = textBox_ref.Text.ToString();
                String sn = textBox_ov.Text.ToString();
                String cod_client = textBox_cod.Text.ToString();
                String client = textBox_cliente.Text.ToString();
                String qtd = textBox_qtd.Text.ToString();
                String number = textBox_num.Text.ToString(); //This will be the incremented number

                String my_querry = "INSERT INTO product(reference,sn,cod_client,client,qtd,number)VALUES('" + reference + "','" + sn + "','" + cod_client + "','" + client + "','" + qtd + "','" + number + "')";
                OleDbCommand cmd = new OleDbCommand(my_querry, con);
                cmd.ExecuteNonQuery();

                MessageBox.Show("Data saved successfully...!");

            }
            catch (Exception ex)
            {
                MessageBox.Show("Failed due to" + ex.Message);
            }
            finally
            {

                con.Close();
            }
            cleanTextBoxes(this.Controls);
        }
    }



    private void search_btn_Click(object sender, EventArgs e)
    {
        Form search = new Form_search();

        search.Show();

        this.Hide();
    }
}

解决方案

Use an IDENTITY or "auto number" field in the DB and let it handle the sequential values - it's a dangerous idea to "pre-assign" incremental values in your presentation code as it can lead to some nasty bugs in production which are a PITA to find, impossible to duplicate reliably, and incredibly hard to fix.

And Richard is absolutely right: Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:

SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'

The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:

SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'

Which SQL sees as three separate commands:

SELECT * FROM MyTable WHERE StreetAddress = 'x';

A perfectly valid SELECT

DROP TABLE MyTable;

A perfectly valid "delete the table" command

--'

And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

How can you have in same piece of code an SQL command safe to injection and an other one subject to injection?

String my_querry = "INSERT INTO product(reference,sn,cod_client,client,qtd,number)VALUES('" + reference + "','" + sn + "','" + cod_client + "','" + client + "','" + qtd + "','" + number + "')";

Never build an SQL query by concatenating strings. Sooner or later, you will do it with user inputs, and this opens door to a vulnerability named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input a name like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability, and the crash is the least of the problems, a malicious user input and it is promoted to SQL commands with all credentials.
SQL injection - Wikipedia[^]
SQL Injection[^]
SQL Injection Attacks by Example[^]
PHP: SQL Injection - Manual[^]
SQL Injection Prevention Cheat Sheet - OWASP[^]
How can I explain SQL injection without technical jargon? - Information Security Stack Exchange[^]

As @OriginalGriff has already stated, your ID field in your database table should be an AutoNumber field. That way Access will automatically increment your ID for you, and if there is more than one user you won't end up with duplicate IDs.

Once you have set that up you can use the @@Identity construct that allows you to query for the last Id that you inserted as long as you use the same connection.

In the soon to be deleted solution 3 that you posted you have ignored all of the advice about SQL Injection and are still trying to increment the max id on the table - that technique simply does not work.

To overcome your SQL injection problem (and coincidentally fix any issues you might have with column types and incorrectly formatted string concatenation) do something like this

String my_querry = "INSERT INTO product(reference,sn,cod_client,client,qtd,number)VALUES(@ref,@sn,@cod_client,@client,@qtd,@number)";
OleDbCommand cmd = new OleDbCommand(my_querry, con);
cmd.Parameters.AddWithValue("@ref", reference);
cmd.Parameters.AddWithValue("@sn", sn);
cmd.Parameters.AddWithValue("@cod_client", cod_client);
cmd.Parameters.AddWithValue("@client", client);
cmd.Parameters.AddWithValue("@qtd", qtd);
cmd.Parameters.AddWithValue("@number", number);
cmd.ExecuteNonQuery();

To get the last Id entered do this

cmd.CommandText = "Select @@Identity";
int newID = (int)cmd.ExecuteScalar();

Note: This is the same cmd object that was used to issue the Insert command.
Do not close the connection before using Select @@Identity
Do not issue any other SQL commands on that connection before using Select @@Identity

这篇关于使用C＃中的访问数据库中的递增值填充文本框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！