Help With Search Code

I have been using the following code on a search page for some time, is has worked very well. We recently changed our database to support multiple addresses for each client. So I added the INNER JOIN on the tblClientAddresses. But now when I try to search on a ID I get an ambiguous cloumn name error on the ID. Can anyone see how I could correct this?





Thanks for any suggestions,








Sub BindDataForPaging(ByVal sortExpr As String)


Dim MyConnection As SqlConnection


Dim MySQLAdapter As SqlDataAdapter


Dim DS As DataSet


Dim ConnectStr As String


Dim WhereClause As String


Dim SelectStatement As String





If SearchLastName.Text = "" And SearchFirstName.Text = "" And _


SearchID.Text = "" And SearchCompanyName.Text = "" And _


SearchSal1.Text = "" And SearchComment.Text = "" And SearchAddress.Text = "" Then


Message.Text = "You didn't enter any search parameters. Try Again."


Exit Sub


End If





WhereClause = "Where "


If SearchLastName.Text <> "" Then


WhereClause = WhereClause & "[LastName] Like '" & _


SearchLastName.Text & "%" & "' AND "


End If


If SearchFirstName.Text <> "" Then


WhereClause = WhereClause & "[FirstName] Like '" & _


SearchFirstName.Text & "%" & "' AND "


End If


If SearchID.Text <> "" Then


WhereClause = WhereClause & "[ID] = " & _


SearchID.Text & " AND "


End If


If SearchCompanyName.Text <> "" Then


WhereClause = WhereClause & "[CompanyName] Like '" & _


SearchCompanyName.Text & "%" & "' AND "


End If


If SearchSal1.Text <> "" Then


WhereClause = WhereClause & "[Sal1] Like '" & _


SearchSal1.Text & "%" & "' AND "


End If


If SearchComment.Text <> "" Then


WhereClause = WhereClause & "[Comments] Like '" & "%" & _


SearchComment.Text & "%" & "' AND "


End If


If SearchAddress.Text <> "" Then


WhereClause = WhereClause & "[Address] Like '" & "%" & _


SearchAddress.Text & "%" & "' AND "


End If


If ClientTypeDrop.SelectedItem.Text <> "" Then


WhereClause = WhereClause & "[CLientType] Like '" & "%" & _


ClientTypeDrop.SelectedItem.Text & "%" & "' AND "


End If


If Right(WhereClause, 4) = "AND " Then


WhereClause = Left(WhereClause, Len(WhereClause) - 4)


End If





SelectStatement = "Select *,A.Address FROM tblClients INNER JOIN dbo.tblClientAddresses A ON dbo.tblClients.ID = A.ID " & WhereClause & " ORDER BY " & sortExpr





Try


ConnectStr = ConfigurationSettings.AppSettings("ConnectStr")


MyConnection = New SqlConnection(ConnectStr)


MySQLAdapter = New SqlDataAdapter(SelectStatement, MyConnection)


DS = New DataSet


MySQLAdapter.Fill(DS)


MyDataGrid.DataSource = DS


MyDataGrid.DataBind()


Catch objException As SqlException


Dim objError As SqlError


For Each objError In objException.Errors


Response.Write(objError.Message)


Next


End Try





End Sub