Visual Basic

Public Function Results()
    Try
        objConn.Open()
        objDA.SelectCommand = New OleDbCommand
        objDA.SelectCommand.Connection = objConn
        objDA.SelectCommand.CommandText = "SELECT * from Companies"
        objDA.SelectCommand.CommandType = CommandType.Text
        objDA.SelectCommand.ExecuteNonQuery()
        objDS.Clear()
        objDA.Fill(objDS, "Companies")
        objConn.Close()
    Catch ex As Exception
        MessageBox.Show("fail connection in Client Search")
    End Try
 
    grdResults.DataSource = objDS
    grdResults.DataMember = "Companies"
    numRows = objDS.Tables("Companies").Rows.Count
 
End Function 

        Private Shared Function GetSystemParameter(ByVal sParamName As String, ByRef objReturnValue As Object) As ParameterStatus
            Dim oConnection As SqlConnection
            Dim oCommand As SqlCommand
            Dim retValue As Integer
 
            Try
                oConnection = GetDatabaseConnection()
                oCommand = New SqlCommand
 
                oCommand.Connection = oConnection
                oCommand.CommandText = "SQLGetSystemParameter"
                oCommand.CommandType = CommandType.StoredProcedure
 
                Dim sqlParam As New SqlParameter("@ParmName", SqlDbType.NVarChar, 25)
                sqlParam.Value = sParamName
                oCommand.Parameters.Add(sqlParam)
 
                Dim sqlpParamValue As New SqlParameter("@ParmValue", SqlDbType.NVarChar, 255)
                sqlpParamValue.Direction = ParameterDirection.Output
                oCommand.Parameters.Add(sqlpParamValue)

                sqlParam = New SqlParameter("RETURN_VALUE", SqlDbType.Int)
                sqlParam.Direction = ParameterDirection.ReturnValue
                oCommand.Parameters.Add(sqlParam)
 
                oConnection.Open()
                oCommand.ExecuteNonQuery()
                oConnection.Close()
 
                Dim iReturnCheck As Integer = Integer.Parse(oCommand.Parameters("RETURN_VALUE").Value)
                If iReturnCheck = 1 Then
                    retValue = ParameterStatus.ParameterInvalid
                Else
                    retValue = ParameterStatus.ParameterOK
                    objReturnValue = sqlpParamValue.Value
                End If
            Catch ex As Exception
                LogErrorEvent("SQLServices", "GetSystemParameter", ex.Message, ex.StackTrace)
                retValue = ParameterStatus.ParameterInvalid
                Throw ex
            Finally
                If Not oCommand Is Nothing Then
                    oCommand.Dispose()
                End If
                If Not oConnection Is Nothing Then
                    If Not oConnection.State = ConnectionState.Closed Then
                        oConnection.Close()
                    End If
                    oConnection.Dispose()
                End If
            End Try
            Return retValue
        End Function
 
        Public Shared Function GetSystemParameters() As DataSet
            Dim oConnection As SqlConnection
            Dim oCommandSelect As SqlCommand
            Dim oDataAdapter As SqlDataAdapter
            Dim oDataSet As DataSet
            Dim retValue As Integer
 
            Try
                oConnection = GetDatabaseConnection()
                oCommandSelect = New SqlCommand("SELECT * FROM System")
                oCommandSelect.Connection = oConnection
 
                oDataAdapter = New SqlDataAdapter(oCommandSelect)
                oDataSet = New DataSet("SystemParameters")
 
                oDataAdapter.Fill(oDataSet)
                oDataSet.Tables(0).TableName = "System"
            Catch ex As Exception
                LogErrorEvent("SQLServices", "GetSystemParameters", ex.Message, ex.StackTrace)
                Throw ex
            Finally
                If Not oCommandSelect Is Nothing Then
                    oCommandSelect.Dispose()
                End If
                If Not oConnection Is Nothing Then
                    If Not oConnection.State = ConnectionState.Closed Then
                        oConnection.Close()
                    End If
                    oConnection.Dispose()
                End If
            End Try
            Return oDataSet
        End Function
 
        Public Shared Function GetDatabaseConnection() As SqlConnection
            Dim sConnectionString As String
            Dim oConnection As SqlConnection
 
            Try
                sConnectionString = ConfigurationSettings.AppSettings("DataConnection")
                oConnection = New SqlConnection(sConnectionString)
            Catch ex As Exception
                LogErrorEvent("SQLServices", "GetDatabaseConnection", ex.Message, ex.StackTrace)
                Throw ex
            End Try
            Return oConnection
        End Function

CREATE PROCEDURE dbo.GetSystemParameter
		@ParmName	NVARCHAR(25),
		@ParmValue	NVARCHAR(255)	OUTPUT
AS
BEGIN
	SELECT @ParmValue = ParmValue FROM [System] WHERE ParmName = @ParmName
	IF @@ROWCOUNT = 0
		RETURN 1
	ELSE
		RETURN 0
END

CREATE PROCEDURE dbo.SearchForWhatever
    @client_number varchar(100),
    @client_name varchar(100),
    @contact_name varchar(100),
    @address varchar(100),
    @phone_number varchar(20)
AS
BEGIN
    DECLARE @SearchClientNumber varchar(100)
    IF LEN(@client_number) > 0
        SET @SearchClientNumber = '%' + @client_number + '%'
    ELSE
        SET @SearchClientNumber = '*'
 
    DECLARE @SearchClientName varchar(100)
    IF LEN(@client_name) > 0
        SET @SearchClientName = '%' + @client_name + '%'
    ELSE
        SET @SearchClientName = '*'
 
    DECLARE @SearchContactName varchar(100)
    IF LEN(@contact_name) > 0
        SET @SearchContactName = '%' + @contact_name + '%'
    ELSE
        SET @SearchContactName = '*'
 
    DECLARE @SearchAddress varchar(100)
    IF LEN(@address) > 0
        SET @SearchAddress = '%' + @address + '%'
    ELSE
        SET @SearchAddress = '*'
 
    DECLARE @SearchPhoneNumber varchar(20)
    IF LEN(@phone_number) > 0
        SET @SearchPhoneNumber = '%' + @phone_number + '%'
    ELSE
        SET @SearchPhoneNumber = '*'
 
    SELECT client_number, client_name, contact, phone_number, address_1
    FROM Companies
    WHERE status = 1
        AND client_number LIKE @SearchClientNumber
        AND client_name LIKE @SearchClientName
        AND contact LIKE @SearchContactName
        AND phone_number LIKE @SearchPhoneNumber
        AND address_1 LIKE @SearchAddress
END

Try
    ' setup our database connection.
    Dim conn As SqlConnection = GetConnection()
    Dim comm As New SqlCommand("blaa blaa blaa")
    Dim param As New SqlParameter(blaa blaa blaa)
    .
    .
    (more code to setup and execute our data access)
Catch ex As Exception
    ' Code to log the exception, throw a custom exception, or whatever to handle a failed request.
Finally
    ' Check to see if the connection is anything but closed!  Don't check for Open!
    If conn.State <> ConnectionState.Closed Then
        conn.Close()
    End If
    comm = Nothing
    conn.Dispose()
End Try