Thread: cmd.Connection & ExecuteNonQuery

how do i set the connection property for the command instance?

Code:

    Private Sub cmdDeleteInvoice_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdDeleteInvoice.Click

        Dim cmd As New System.Data.SqlClient.SqlCommand
        Dim InvoiceNumber As String


        If lsvInvoices.SelectedIndices.Count < 0 Then Exit Sub
        InvoiceNumber = CInt(lsvInvoices.SelectedItems(0).SubItems(0).Text)
        cmd.CommandType = System.Data.CommandType.Text


        cmd.CommandText = "DELETE FROM Orders WHERE InvoiceID = @InvoiceNumber"
        cmd.Parameters.Add("@InvoiceNumber", SqlDbType.Int)
        cmd.Parameters("@InvoiceNumber").Value = CInt(lsvInvoices.SelectedItems(0).SubItems(0).Text)


        cmd.Connection
        cmd.ExecuteNonQuery()








    End Sub

Just as you're creating a SqlCommand object and configuring it, so you create a SqlConnection object and configure it and then assign it to the Connection property of the command.

Originally Posted by jmcilhinney

Just as you're creating a SqlCommand object and configuring it, so you create a SqlConnection object and configure it and then assign it to the Connection property of the command.

I have my connection object in a module listed below.... how do i assign it to the connection property of the command?

Code:

    Public Function VerifyConnection() As Boolean


        VerifyConnection = False


        conn = New SqlClient.SqlConnection
        connstrg = "Persist Security Info=False;User ID=" + user + ";Password=" + pwd + ";Initial Catalog=" + AppDB + ";Server=" + Servername
        conn.ConnectionString = connstrg
        Try
            conn.Open()
            If conn.State = ConnectionState.Open Then
                VerifyConnection = True
            End If
            conn.Close()


        Catch ex As Exception
            VerifyConnection = False
            MessageBox.Show(ex.Message)
        End Try
    End Function

Code:

   
 
Public Sub OpenSQLConnection()        If conn Is Nothing Then
            Try
                conn = New SqlClient.SqlConnection
            Catch ex As Exception


            End Try
        End If
        If conn.State = ConnectionState.Open Then
            Exit Sub
        End If
        Try
            conn.Open()
        Catch ex As Exception
            Console.WriteLine("ERROR: An error occurred while trying to connect to the database. " + vbCrLf + _
                    "Here is the error message returned from the system: " + vbCrLf + ex.Message)
        End Try
    End Sub

You already know how to assign an object to a property because you're already doing it in several places in the code you've posted. This instance is no different to any other.

Apart from that, there's no reason to have connection-related code in a module separate to command-related code. It's all data access so it should all be together. You should be creating the connection right where you're creating and executing the command.