Hi guys,

A person can serach my database via a form, the problem is when the thing that the person is searching for is not in the database. When this happens, all the records are shown in my datagrid. I dont want this to happen. If the thing is not in the database I want the person to be told this. How would I go about doing this.
Here is my code: -

private void Button_Click(object sender, System.EventArgs e)
    {
       String strAuthor = Author.Text;
       String strTitle = Title.Text;

       Connect();
       String strSearch = "SELECT * FROM Computing";
       OleDbDataAdapter objAdapter = new OleDbDataAdapter(strSearch, objConnection);
       DataSet ds = new DataSet();
       objAdapter.Fill(ds, "dtFilteredComputing");
       Disconnect();

       DataView objDataView = new DataView(ds.Tables["dtFilteredComputing"]);
       objDataView.RowFilter = "BookAuthor LIKE '" + strAuthor + "' OR BookName LIKE '" + strTitle + "'";

       dgComputing.DataSource = objDataView;
       dgComputing.DataBind();
       }
    }

Thanks



Adz - Portsmouth Massive

__________________
Adz - Learning The J2EE Ways.

Because this is an ASP.Net application and you are making a call to the DB every time you search, I would recommend putting the search criteria into you SQL statement:

private void Button_Click(object sender, System.EventArgs e)
    {
       String strAuthor = Author.Text;
       String strTitle = Title.Text;

       Connect();
       String strSearch = String.Format("SELECT * FROM Computing WHERE BookAuthor LIKE '{0}' OR BookName LIKE '{1}'", strAuthor, strTitle);
       OleDbDataAdapter objAdapter = new OleDbDataAdapter(strSearch, objConnection);
       DataSet ds = new DataSet();
       objAdapter.Fill(ds, "dtFilteredComputing");
       Disconnect();

       DataView objDataView = new DataView(ds.Tables["dtFilteredComputing"]);

       dgComputing.DataSource = objDataView;
       dgComputing.DataBind();
       }
    }

I would further recommend looking into use a parameterized query using the Parameters collection of a command object. This takes care of the escape charactesr for you and makes building queries a bit cleaner. I'd offer up an example, but I haven't work with the OLEDB .Net classes. Sorry.

If you aren't familiar with the String.Format method, look into it. It's handy to use when you need to build small string concatenations (3 or less parts to add in).

Peter
------------------------------------------------------
Work smarter, not harder.

Peter,

why would you have to use Parameterization with Command objects when the select statement does all the filtering. Is there any need for it??




Adz - Portsmouth Massive

Peter,

I left out a line after creating the dataview object: -

DataView objDataView = new DataView(ds.Tables["dtFilteredComputing"]);
objDataView.RowFilter = "BookAuthor LIKE '" + strAuthor + "' OR BookName LIKE '" + strTitle + "'";

Is this the type of thing you were refering to. Could you please show me an eample using SQLClient objects??




Adz - Portsmouth Massive

Even though you are letting SQL do all the work, you still need to provide it with something useful to filter, hence the parameters.

Here's an example of parameters:

Dim objConn As New SqlConnection(sMyConnString)
Dim objCommand As New SqlCommand
Dim objDataReader As SqlDataReader

objConn.Open()
objCommand.Connection = objConn
objCommand.CommandType = CommandType.Text
objCommand.CommandText = "SELECT * FROM Users WHERE Username=@sUserName AND Password=@sPassword"
objCommand.Parameters.Add("@sUserName", SqlDbType.VarChar).Value = sUserName
objCommand.Parameters.Add("@sPassword", SqlDbType.VarChar).Value = sPassword
objDataReader = objCommand.ExecuteReader
If objDataReader.Read() Then
    'Found a record ...
End If


Peter
------------------------------------------------------
Work smarter, not harder.