如何更新SQL数据库表值。 [英] How to UPDATE SQL database table values.

问题描述

好吧，我正在尝试更新我的SQL数据库值并给出一些错误。我有form1，它有ComboBox1.Text，有助于自动填充我的form2字段。但是当我使用MODIFY_BUtton从那里保存任何东西时它会说



关键字'from'附近的语法不正确。



请在我疯狂之前纠正我。



我尝试过的事情：

Well I'm trying to update my SQL database values and Gives some error. I have form1 which have ComboBox1.Text that helps to populate my form2 fields automatically. But when Im tyring to save anything from there using MODIFY_BUtton its says

Incorrect syntax near the keyword 'from'.

Please correct me before Im going crazy.

What I have tried:

Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        connection = New SqlConnection("SERVER=\MySQLserver;USER ID=MyID;PASSWORD=MyPASS;DATABASE=MyDATABASE")
        Dim reader As SqlDataReader
        Try
            connection.Open()
            Dim nDate As Date = Date.Now.ToString(" yyyy-MM-dd")
            Dim query As String = "UPDATE faculty SET id='" & TextBox1.Text & "',dept='" & ComboBox1.Text & "',sub='" & ComboBox2.Text & "',pre='" & ComboBox3.Text & "',name='" & TextBox2.Text & "',dob='" & DateTimePicker1.Value & "',sex='" & ComboBox4.Text & "',addr='" & TextBox3.Text & "',city='" & TextBox4.Text & "',state='" & TextBox5.Text & "',pin='" & TextBox6.Text & "',country='" & TextBox7.Text & "',nat='" & TextBox8.Text & "',mob='" & TextBox9.Text & "',email='" & TextBox10.Text & "',uname='" & TextBox11.Text & "',pword='" & TextBox12.Text & "',sec='" & ComboBox5.Text & "',ans='" & TextBox14.Text & "',mod='" & nDate & "' where id='" & TextBox1.Text & "'"
            command = New SqlCommand(query, connection)
            reader = command.ExecuteReader
            MsgBox("Record Updated")
            Me.Close()
            connection.Close()
            load_dgv()
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            connection.Dispose()
        End Try
    End Sub

推荐答案

Quote：

关键字附近的语法不正确来自'。

Incorrect syntax near the keyword 'from'.

你的代码中没有'from'。

There is no 'from' in your code.

Dim query As String = "UPDATE faculty SET id='" & TextBox1.Text & "',dept='" & ComboBox1.Text & "',sub='" & ComboBox2.Text & "',pre='" & ComboBox3.Text & "',name='" & TextBox2.Text & "',dob='" & DateTimePicker1.Value & "',sex='" & ComboBox4.Text & "',addr='" & TextBox3.Text & "',city='" & TextBox4.Text & "',state='" & TextBox5.Text & "',pin='" & TextBox6.Text & "',country='" & TextBox7.Text & "',nat='" & TextBox8.Text & "',mob='" & TextBox9.Text & "',email='" & TextBox10.Text & "',uname='" & TextBox11.Text & "',pword='" & TextBox12.Text & "',sec='" & ComboBox5.Text & "',ans='" & TextBox14.Text & "',mod='" & nDate & "' where id='" & TextBox1.Text & "'"

不是解决方案你的问题，但你有另一个问题。

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

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

SQL注入 - 维基百科 [ ^ ]

SQL注入 [ ^ ]

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

PHP：SQL注入 - 手册 [ ^ ]

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

Not a solution to your question, but another problem you have.
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[^]

由于您显示的代码中没有 FROM 子句，我怀疑单击按钮时执行的方法不是你想的那个。

请在编辑器中检查所说按钮的名称，它应该是 Button3 。

然后在方法的开头放置一个断点，然后按F5;这将启动调试会话。当您单击按钮时，应该调用该方法，应该到达断点，然后从那里按F10或F11逐行执行代码。如果没有命中断点，则意味着没有调用该方法，因此这意味着为按钮的Click事件注册的事件处理程序不是正确的。



作为一般规则，您应该为控件提供有意义的名称，而不是保留编辑器默认分配的名称。这可以完全避免您遇到的问题。



请深入了解解决方案1，因为它非常重要，如果不是必要的话，关于如何以安全的方式构建查询的观点。

Since there is no FROM clause in the code you showed, I suspect the method which is executing when you click on the button is not the one you think.
Please check the name of said button in the editor, it should be Button3.
Then put a breakpoint at the beginning of the method, and press F5; this will start a debugging session. When you will click on the button, the method should be called, the breakpoint should be reached, and from there you will be able to execute your code line-by-line by pressing F10 or F11. If the breakpoint is not hit, it means that the method is not called, and thus it means that the event handler which is registered for the Click event of the button is not the right one.

As a general rule, you should give your controls meaningful names, and not keep the names which are assigned by default by the editor. This would prevent exactly the kind of issue your are running into.

And please have a deep look at solution 1, as it makes a very important, if not essential, point about how building queries in a safe way.

这篇关于如何更新SQL数据库表值。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！