vb.net ExecuteNonQuery() 用于插入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17729200/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ExecuteNonQuery() for Insert
提问by phalanx
Can you please tell me what's wrong with this code?
你能告诉我这段代码有什么问题吗？
Do I need to use DataAdapterto insert into a table?
我需要使用DataAdapter插入到表中吗？
I know the connectionStringis ok, because I tested it on the Server Explorer.
我知道没问题connectionString，因为我在服务器资源管理器上测试过它。
Dim mydao As New Connection
Dim connectionString As String = mydao.GetConnectionString()
Dim connection As New SqlConnection(connectionString)
Dim cmd As New SqlCommand
Public Function add(ByVal area As String, ByVal user As String) As Integer
cmd.CommandText = "INSERT into Area (Area, user) VALUES ('" + area + "','" + user + "')"
Try
connection.Open()
Dim cant As Integer = cmd.ExecuteNonQuery()'it throws exception here
connection.Close()
Return cant
Catch ex As Exception
Console.WriteLine(ex.Message)
Return 0
End Try
End Function
The above code fails just after ExecuteNonQuery()and can′t figure why.
上面的代码就在之后失败了ExecuteNonQuery()，无法弄清楚为什么。
TARGET FIELDS (SQL Server 2008):
目标字段 (SQL Server 2008)：
AREA varchar(100) NOT NULL ,
USER varchar(100) NOT NULL
The exception I receive is: Connection property has not initialized
我收到的例外是： Connection property has not initialized
回答by dash
There's a few issues with this code.
这段代码有几个问题。
The most significant is that you aren't setting the Command's Connectionproperty, so the command has no way of knowing how to connect to the database.
最重要的是您没有设置 Command 的Connection属性，因此该命令无法知道如何连接到数据库。
I would also strongly recommend utilizing using, and also parameterizingyour query:
Finally, don't declare the connection and command outside of the function unless you need to. You should only keep the connection and command around for as long as you need them.
最后，除非需要，否则不要在函数外声明连接和命令。您应该只在需要时保持连接和命令。
So your function would end up looking like:
所以你的函数最终看起来像：
Public Function add(ByVal area As String, ByVal user As String) As Integer
Dim mydao As New Connection
Using connection As New SqlConnection(mydao.ConnectionString())
Using command As New SqlCommand()
' Set the connection
command.Connection = connection
' Not necessary, but good practice
command.CommandType = CommandType.Text
' Example query using parameters
command.CommandText = "INSERT into Area (Area, user) VALUES (@area, @user)"
' Adding the parameters to the command
command.Parameters.AddWithValue("@area", area)
command.Parameters.AddWithValue("@user", user)
connection.Open()
Return command.ExecuteNonQuery()
End Using ' Dispose Command
End Using ' Dispose (and hence Close) Connection
End Function
Note that currently, you will be returning 0 all the time. Rather than having to check the value returned from the function, the above example will simply throw an exception. This makes for slightly cleaner code (as the caller would have to understand that 0 is an error condition), and, if you needed to handle the exception, simply wrap the call to this function in a Try-Catchblock
请注意，目前，您将始终返回 0。上面的例子不需要检查函数返回的值，而是简单地抛出一个异常。这使得代码更简洁（因为调用者必须理解 0 是错误条件），并且，如果您需要处理异常，只需将对此函数的调用包装在一个Try-Catch块中
