为什么我的更新查询不起作用？ [英] Why does my update query doesnt work ?

问题描述

Private Sub updateww()
        Dim con As New SqlConnection
        Dim cmd As New SqlCommand

        con.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Administrator\Documents\Visual Studio 2010\Projects\St. Peter Academy\St. Peter Academy\App_Data\St.mdf;Integrated Security=True;User Instance=True"
        con.Open()
        cmd.Connection = con
        cmd.CommandText = "update " & dbname.Text & " set percentage='" & edwwp.Text & "', description='" & edwwd.Text & "' where componentid=1 "
        cmd.ExecuteNonQuery()
        con.Close()

    End Sub

    Private Sub updatept()
        Dim con As New SqlConnection
        Dim cmd As New SqlCommand

        con.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Administrator\Documents\Visual Studio 2010\Projects\St. Peter Academy\St. Peter Academy\App_Data\St.mdf;Integrated Security=True;User Instance=True"
        con.Open()
        cmd.Connection = con
        cmd.CommandText = "update " & dbname.Text & " set percentage= '" & edptp.Text & "', description='" & edptd.Text & "' where componentid=2 "
        cmd.ExecuteNonQuery()
        con.Close()

    End Sub
    Private Sub updateqa()
        Dim con As New SqlConnection
        Dim cmd As New SqlCommand

        con.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Administrator\Documents\Visual Studio 2010\Projects\St. Peter Academy\St. Peter Academy\App_Data\St.mdf;Integrated Security=True;User Instance=True"
        con.Open()
        cmd.Connection = con
        cmd.CommandText = "update " & dbname.Text & " set percentage='" & edqap.Text & "', description='" & edqad.Text & "' where componentid=3 "
        cmd.ExecuteNonQuery()
        con.Close()

    End Sub

我的尝试：



我的更新无效。它没有显示任何错误但没有更新

What I have tried:

my update isnt working . it does not show any errors but not updating

推荐答案

这里有很多东西需要解决....



最重要的是：不要那样做。永远不要连接字符串以形成SQL命令 - 它让您完全接受SQL注入攻击，您的用户只需进入文本框就可以破坏或破坏您的数据库。始终使用参数化查询。



其次，不要硬编码连接字符串 - 始终将它们存储在配置文件或类似文件中，这样您就不必在开发和发布之间更改程序。



第三，不要经常附加数据库 - 让SQL自己处理它们 - 附加只是一个Express版本，是一种比SQL慢得多的特殊开发者模式管理。



第四，SqlConnection和SqlCommand对象是稀缺资源，当你完成它们时应该是Disposed。



现在你注意到的问题......我们说不出来！可能是您在文本框中的数据导致问题，可能是没有与您的条件匹配的错误。因此，首先要在整个应用程序中修复其他内容，如果问题仍然存在，请检查数据库并确保数据位于您认为的位置，以及您认为的数据。



但是如果你不先解决其他问题，你的数据库就会被破坏 - 你最好的伙伴只会看到你脸上的表情。 。

So many things to fix here....

The big one is: don't do it like that. Never concatenate strings to form an SQL command - it leaves you wide open to SQL Injection attacks where your users can damage or destroy your database just by going in textbox. Always use parameterized queries instead.

Secondly, do not hard code connection strings - always store them in a config file or similar so you don't have to change the program between development and release.

Thirdly, don't routinely attach databases - let SQL handle them itself instead - attaching is an Express version only and is a special developer mode that is much slower than SQL management.

Fourthly, SqlConnection and SqlCommand objects are scarce resources and should be Disposed when you are finished with them.

Now for the problem you have noticed ... We can't tell! It could be you data in the textboxes is causing a problem, it could be that there are no errors which match your condition. So start be fixing the other stuff throughout your application first, and if the problem is still happening after that, check your DB and make sure that the data is where you think it is, and what you think it is.

But if you don't fix the other stuff first, your DB will get destroyed - your best mate will do it just to see the look on your face...

如上所述，代码中存在很多问题

- 值的连接让你对SQL注入开放

- 值的concantenation引入转换问题

- 你不使用块，所以即使存在也可以省略Dispose

- 你没有任何错误处理

- 连接字符串静态嵌入到方法中

- 不一定是个问题，但如果这些方法在循环或其他DML语句中使用那么你就会丢失事务等等...



我建议通过正确执行数据库操作 [ ^ ]

As said there are a lot of problems in the code
- concantenation of values leaves you open to SQL injection
- concantenation of values introduces conversion problems
- you don't use using blocks so Dispose may be omitted even if present
- you don't have any error handling
- connection string is statically embedded into a method
- not necessarily a problem but if these methods are used in a loop or with other DML statements then you're missing transactions and so on...

I suggest going through Properly executing database operations[^]

这篇关于为什么我的更新查询不起作用？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！