creating a flexible search

[dotolee]

hi there. i'm just a newbie at data access with .net & sql.
i've managed to build forms that have datagrids bound to various data sources.
but now i want to create a search form that allows a user to create dynamic sql statements instead of running a canned sql statement.

so far i have the following:

    Private Sub btnExecuteSearch_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdExecuteSearch.Click


        Dim sqlCmd As New SqlClient.SqlCommand, SQL As String
        SQL = "select * from householder "
        SQL &= "where "
        'test for Last Name Parm
        If Not (txtLastName.Text Is Nothing) Then
            sqlCmd.Parameters.AddWithValue("@LastName", txtLastName.Text)
            'check for Like Clause
            If chkLikeLName.Checked Then
                SQL &= "lname like @LastName%, "
            Else
                SQL &= "lname = @LastName, "
            End If
        End If

        'test for First Name Parm
        If Not (txtFirstName.Text Is Nothing) Then
            sqlCmd.Parameters.AddWithValue("@FirstName", txtFirstName.Text)
            'check for Like Clause
            If chkLikeFName.Checked Then
                SQL &= "FName like @FirstName%"
            Else
                SQL &= "FName = @FirstName"
            End If
        End If
        sqlCmd.CommandText = SQL
        sqlCmd.CommandType = CommandType.Text
        Dim cnn As String
        cnn = "Data Source=.\sqlexpress;Initial Catalog=territorE;Integrated Security=True"
        sqlCmd.Connection.ConnectionString = cnn.ToString

        HouseHolderDataGridView.DataSource = sqlCmd.ExecuteReader
    End Sub

The system is failing on the line where I'm setting the connectionstring property on my sqlcmd. It says:
System.NullReferenceException was unhandled
Object reference not set to an instance of an object.

what am i doing wrong? thanks.

 

[jmcilhinney]

So where have you set the Connection property of your command? How can you set the ConnectionString property of the object stored in the Connection property if there's no object stored in the Connection property?

 

[cjard]

I normally get the DB to do this donkeywork:

SELECT * FROM people WHERE
(FirstName = @FirstName OR @FirstName IS NULL) AND
(LastName = @LastName OR @LastName IS NULL)

Now to wildcard a query, set the parameter to null

If youre only using strings its even easier:

SELECT * FROM people WHERE
FirstName LIKE @FirstName AND LastName = @LastName

To wildcard any parameter, set its value to "%"