ExecuteNonQuery()进行插入 [英] ExecuteNonQuery() for Insert

问题描述

您能告诉我这段代码有什么问题吗?

Can you please tell me what's wrong with this code?

我需要使用DataAdapter插入表格吗?

Do I need to use DataAdapter to insert into a table?

我知道connectionString没问题，因为我在服务器资源管理器中对其进行了测试.

I know the connectionString is ok, because I tested it on the Server Explorer.

    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

上面的代码仅在ExecuteNonQuery()之后失败，无法弄清原因.

The above code fails just after ExecuteNonQuery() and can´t figure why.

目标字段(SQL Server 2008):

TARGET FIELDS (SQL Server 2008):

AREA        varchar(100)  NOT NULL ,
USER        varchar(100)  NOT NULL 

我收到的异常是:Connection property has not initialized

推荐答案

此代码存在一些问题.

最重要的是，您没有设置命令的连接属性，因此该命令无法知道如何连接到数据库.

The most significant is that you aren't setting the Command's Connection property, so the command has no way of knowing how to connect to the database.

我也强烈建议您使用使用，以及参数化您的查询:

I would also strongly recommend utilizing using, and also parameterizing your 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

请注意，当前您将一直返回0.上面的示例无需检查从函数返回的值，而只是抛出异常.这使得代码更加简洁(因为调用者必须了解0是错误条件)，并且，如果您需要处理异常，只需将对此函数的调用包装在Try-Catch块中

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-Catch block

这篇关于ExecuteNonQuery()进行插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！