ExecuteNonQuery() for Insert

问题:

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

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

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

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

TARGET FIELDS (SQL Server 2008):

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

The exception I receive is: Connection property has not initialized

回答1:

There's a few issues with this code.

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

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