Search A Database

[paulthepaddy]

Hi Guys, I Am looking For Some help And info On Searching Databases,This Is My Form



unfortunatly i have no code to start with. i have googled and youtubed mainly to find people search their datagrids for values, but it did come to me that this database will get very big quite quickly, so i have going to need to search the database then have it pass the values back to the datagrid.

sorry for rushing i have to go to work.

dont worry as much about putting the proper names and all in. im sure i will figure it out.

but i would like to be able to search by 4 or 5 values BUT(this is whats gets me) how can i search my say only 2 out of the 4-5

ay help is much apreaicated,thansk guys

 

[IanRyder]

Hi,

From what I can see, it seems to me that the bottom DataGridView(s), showing the Invoice Details and other information, will not need to be searched since these should be populated using Parent/Child data binding principals which therefore implies that you are only interested in narrowing down the Top DataGridView using some form of search criteria. Is that correct?

If so, you could do two things to get started with this:-

1) If not already done so, create a BindingSource between the DataTable and the top DataGridView and then bind the top DataGridViews DataSource property to the new BingingSource. This will then allow you to use the Filter method of the BindingSource to limit the visible records in the DataGridView.

2) You could then create a Routine which then uses conditional statements to identify that FieldName(s) you want to search on, along with the search criteria to be tested for in each field to be searched. You could then build a valid SQL WHERE clause which can then be applied to the Filter method of the BindingSource connected to the top DataGridView.

Hope that helps.

Cheers,

Ian

 

[paulthepaddy]

Hi Ian,

Thanks Very much For Replying.

Yes you Are Correct, but if i am honest i dont understand what Parent/Child data binding principals is. I'm self taught and alot of help from this forums and its wonderfully helpful members, For the very little i did in school we never covered anything on databases.

All i did for it to work like that was to add the relationship in the database and was able to drag the tables down from the data source windows in VS2010.

Also Im not sure how to do the SQL where clause, only for the reason how do i make a statement that will take a value or leave it blank cant use nullable as it will then be searching for null values :S

Could you give me an example of what kinda of stament i should be using, thanks

Again thanks very much

 

[IanRyder]

Hi,

From your explanation you have created a Strongly typed DataSet using the VS IDE which does all of the work for you when it comes to setting up Relationships and DataBindings within your project.

Due to this, when you drag your DataGridViews onto your form in the way you have the Parent/Child DataBindings are automatically done for you so what you will see currently is that as you move to each row in the Top DataGridView your Bottom DataGridView automatically filters to only show you the records related to the selected record in the Top DataGridView.

To learn more about Parent/Child DataBinding have a look at this thread created by jcmilhinney:-

Master/Detail (Parent/Child) Data-binding (.NET 2.0+ WinForms)-VBForums

As well as this, your DataGridViews have been setup to use a BindingSource that has been added to your project by the IDE so all you need to do now is create a routine to filter your Top DataGridView based on your needs.

Here is an example of how to build and apply a filter to the BindingSource of the Customers table of the Northwind database. Add two TextBox's to a form to represent the Criteria of two Fields in the DataTable and then you can say something like this:-

Private Sub txtCustIDSearch_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtCustIDSearch.TextChanged
  CreateAndApplySearchCriteria("CustomerID", txtCustIDSearch.Text)
End Sub
 
Private Sub txtCustNameSearch_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtCustNameSearch.TextChanged
  CreateAndApplySearchCriteria("CompanyName", txtCustNameSearch.Text)
End Sub
 
Private Sub CreateAndApplySearchCriteria(ByVal strDataTableFieldName As String, strSearchCriteria As String)
  If Not strSearchCriteria = String.Empty Then
    CustomersBindingSource.Filter = strDataTableFieldName & " LIKE '" & strSearchCriteria & "%'"
  Else
    CustomersBindingSource.Filter = String.Empty
  End If
End Sub

As you can see here, when the text is changed in any of the TextBox's it calls a routine to take the information entered, build a valid SQL Where clause, without using the word WHERE, and then applies this to the Customers BindingSource to filter the contents of the DataGridView.

I hope that helps.

Cheers,

Ian

 

[CharlieMay]

I tend reference these short videos from Beth Massey that I found were very helpful in dealing with this type of data access.

 

[paulthepaddy]

Hi Ian, thanks for the reply , sorry im only getting back to you now, thanks for better explaining that to me in a language i can understand

My only question is, does this require the full database to be fully loaded at start up, i would be worried about loading times as the cars tables will get quite big in a small amount of time, would their be big performance issues if the data set was loading say 1500 records into plus the other records related the that 1 record. should i worry about this or will the method you have given me still be fine for this.

as you can see from the picture the search windows are in an MDI, would it be easier and wiser to load the databases when the MDI parent loads then allow the childs to run of the 1 fully loaded DB, would this be easy and a better way of doing things, as this would be the place ALL heavy DB work would be getting done

the code looks good, easy to manage and understand, and would be easy to use if's and delegates so thanks for that

 

[IanRyder]

Hi,

My only question is, does this require the full database to be fully loaded at start up, i would be worried about loading times as the cars tables will get quite big in a small amount of time, would their be big performance issues if the data set was loading say 1500 records into plus the other records related the that 1 record. should i worry about this or will the method you have given me still be fine for this.

Good question since you should always consider performance when creating your applications but this is going to depend on what you really need to do. Consider this:-

If you need to be able to search for ALL cars that you have ever dealt with in the application then the answer would be YES. However, doing this, your application will degrade in performance over time due to the volume of data that would need to be loaded from the DataSource, as you have already guessed.

The thing for you to think about is "what do you actually need to filter on". If you can answer this by saying something like "by default, I want to be able to filter on all cars over the last 3 months but I should have the option to change this" then you could restructure your SQL strings, when loading your data, to retrieve only those Cars and their related records that match this criteria based on the current date. You could then add an option in your project to Re-query your DataSource based on different criteria if you need to.

as you can see from the picture the search windows are in an MDI, would it be easier and wiser to load the databases when the MDI parent loads then allow the childs to run of the 1 fully loaded DB, would this be easy and a better way of doing things, as this would be the place ALL heavy DB work would be getting done

No. I would not agree with this. As well as performance you need to consider your available resources. I know that windows has so much resource nowadays that this seems to get overlooked but it should still be considered.

If you were to load your full Database as part of loading your application then the amount of resources used for this would be a lot greater than if you just loaded what you needed with each child form. Also, if you did things this way then the resources would never be passed back to the system until your application ends whereas, if you loaded only what you needed in each child form then some resources would be used when the form loads but those resources would then be passed back to the system when you close the child form that you were using.

the code looks good, easy to manage and understand, and would be easy to use if's and delegates so thanks for that

Good to hear.

Hope that helps.

Cheers,

Ian

 

[paulthepaddy]

hi again Ian. Thanks For The Reply, you pretty much made my mind up on how i am doing it then, thanks for the solid info.

only 1 question :s (sorry its turning into question after question)

to fill my datagridview i am just using the auto 'fill ,Get Data' in the data set, i know how to change that(i think will find out) but how would i go about setting them up.

am going to use this setup

<3 Months (default)
<6 months
>6 & <12 Months
>12 & < 24
and so on

what would be the best way to switch between them

was thinking combobox and delegate

but how do i get the database to fill the tables from my search

the only code i have used where i am using SQl dirrectly is below

 Public Function INSERT_Car_To_DB(ByVal Car As car) As Boolean
        Dim con As New OleDb.OleDbConnection

        'Set connection String & open Connection 
        con.ConnectionString = My.Settings.DBConnectionString
        con.Open()

        'Car DB Componants
        Dim Car_da As New OleDb.OleDbDataAdapter
        Car_da.SelectCommand = New OleDb.OleDbCommand("SELECT * FROM cars WHERE Car_Reg = '" & Car.Reg & "'", con)
         Car_da.InsertCommand = New OleDb.OleDbCommand("INSERT INTO cars  (Car_Reg, Car_Make, Car_Model, Car_Colour, Car_Price, Car_Paid,  OrderNumber, Invoice_Number, Dealer_ID, Notes) VALUES (@Car_Reg,  @Car_Make, @Car_Model, @Car_colour, @Car_price, @Car_paid, @OrderNumber,  @Invoice_Number, @Dealer_ID, @Notes)", con)
         Car_da.UpdateCommand = New OleDb.OleDbCommand("UPDATE cars SET Car_Reg =  @Car_Reg, Car_Make = @Car_Make, Car_Model = @Car_Model, Car_Colour =  @Car_Colour, Car_Price = @Car_Price, Car_Paid = @Car_Paid, OrderNumber =  @OrderNumber, Invoice_Number = @Invoice_Number, Dealer_ID = @Dealer_ID,  Notes = @Notes WHERE (Car_Reg = @Car_Reg) ", con)



        'Search DB If Row Already Exists
        Select Case Car_da.SelectCommand.ExecuteScalar = Car.Reg
            Case False
                With Car_da.InsertCommand.Parameters
                    .AddWithValue("@Car_Reg", Car.Reg)
                    .AddWithValue("@Car_Make", Car.Make)
                    .AddWithValue("@Car_Model", Car.Model)
                    .AddWithValue("@Car_Colour", Car.Colour)
                    .AddWithValue("@Car_Price", Car.Price)
                    .AddWithValue("@Car_Paid", Car.Paid)
                    .AddWithValue("@OrderNumber", If(Car.OrderNum Is Nothing, DBNull.Value, Car.OrderNum))
                    .AddWithValue("@Invoice_Number", If(Car.Invoices Is Nothing, DBNull.Value, Car.Invoices))
                    .AddWithValue("@Dealer_ID", If(Car.CustomerID = Nothing, DBNull.Value, Car.CustomerID))
                    .AddWithValue("@Notes", If(Car.Notes Is Nothing, DBNull.Value, Car.Notes))
                End With
                Car_da.InsertCommand.ExecuteNonQuery()
                con.Close()
                Return True
                Exit Function
            Case True
                With Car_da.UpdateCommand.Parameters
                    .AddWithValue("@Car_Reg", Car.Reg)
                    .AddWithValue("@Car_Make", Car.Make)
                    .AddWithValue("@Car_Model", Car.Model)
                    .AddWithValue("@Car_Colour", Car.Colour)
                    .AddWithValue("@Car_Price", Car.Price)
                    .AddWithValue("@Car_paid", Car.Paid)
                    .AddWithValue("@OrderNumber", If(Car.OrderNum Is Nothing, DBNull.Value, Car.OrderNum))
                    .AddWithValue("@Invoice_Number", If(Car.Invoices Is Nothing, DBNull.Value, Car.Invoices))
                    .AddWithValue("@Dealer_ID", If(Car.CustomerID = Nothing, DBNull.Value, Car.CustomerID))
                    .AddWithValue("@Notes", If(Car.Notes Is Nothing, DBNull.Value, Car.Notes))
                End With
                Car_da.UpdateCommand.ExecuteNonQuery()
                con.Close()
                Return True
                Exit Function
        End Select
        Return False
    End Function

This is grand because their is no UI at all how do i go about passing them from the Db to the Datagrid, if you have any reference articals, that would be great thanks

 

[IanRyder]

Hi,

Ok, before we go any further:-

1) How experienced are you with the Access SQL Syntax?
2) Have you written what you have got so far yourself or is this something you have inherited that you need to deal with?
3) As I have already said in a previous post, you have created a Strongly Typed Dataset, so, is there any reason that you have a SEPARATE function to INSERT records to your Database? Why are you NOT doing this from your DataGridView?

I don't think I have missed what you are trying to do, but if so, then please explain.

Cheers,

Ian

 

[paulthepaddy]

Ahhh Good Question il start answering

1) How experienced are you with the Access SQL Syntax?

A1) not massively BUT i understand all the SQL code i have used

2) Have you written what you have got so far yourself or is this something you have inherited that you need to deal with?

A2) im not sure what you mean... the application its self is being done by me and most of the code, the SQL code came from jmcilhinney blog, i used one of this posts to get the info i needed to do it.

3) As I have already said in a previous post, you have created a Strongly Typed Dataset, so, is there any reason that you have a SEPARATE function to INSERT records to your Database? Why are you NOT doing this from your DataGridView?

A3) I have used separate functions for things because they are being used in places where this is no database UI, i was using code to simply insert it with execute non query buty decided to go down the route with DA and DS for consistency and effectiveness for paramaters
eg

creating car data that does need saved to DB


The code i am working on now is for Searching the records, thats why i am now stuck.

because before i was using the IDE to load the datatable with rows then search the rows, but now i need to filter the rows before they reach the dataset if i remember correctly

thanks for persisting with me on this, i cant explain how much help and how much i appreciate your time and help

 

[IanRyder]

Hi,

I am sorry, but the more you show us of this project you are working on, in addition to your other posts, the more I get the feeling that what you are asking is "Please write this application for me".

Well, I for one will go out of my way to try and help you with specific issues you have, as many others will, but I am not going to write your whole application for you.

So, my next comments would be:-

1) Regarding your comments in other posts to do with Serialisation. Forget that concept and add everything as Tables to your Database. This then becomes no more complicated than NESTED Parent/Child concepts.

2) You need to learn about the WHERE clause in SQL statements to be able to limit the records returned from your Database when setting the original data that you want to view. There are loads of examples on the web as to how to do this.

Cheers,

Ian

 

[paulthepaddy]

Thanks for the reply.

I am sorry, but the more you show us of this project you are working on, in addition to your other posts, the more I get the feeling that what you are asking is "Please write this application for me".

Well, I for one will go out of my way to try and help you with specific issues you have, as many others will, but I am not going to write your whole application for you.

i understand where you get the idea from, but this is not the case. i have a day of work so i am trying to get as much done as possible as fast as possible.

2) You need to learn about the WHERE clause in SQL statements to be able to limit the records returned from your Database when setting the original data that you want to view. There are loads of examples on the web as to how to do this.

i know what a WHERE clause is and how to use it. i learned the SQL code i have used from w3schools

but what i don't know is where to use the SQL code

i can add a WHERE clause into the table adapter fill query, so when the form loads it will only display the data
to use as the default 3 month date period

BUT when i want to switch from say a 3 month period to a 12-24 month period how do i go about it

where do i execute the SQL code

the data passes ion the following order please correct if wrong
Database > DataSet > DataAdapter > DataGrid

Where do i start to filter the data coming through?

 

[IanRyder]

Hi,

What you need to do here then is to Create Additional queries in the TableAdapter of the Strongly Typed DataSet that you have created in your project. To do this follow these guidelines:-

1) Double click your DataSet.xsd file in your Solution Explorer to open the DataSet in the IDE.

2) Select the Table (presumably Cars) that you need to limit the rows returned to the project.

3) At the bottom of the table you will see the TableNameTableAdapter. Right Click on the adapter and select Add Query.

4) Keep SQL statements selected and click Next, keep Select which returns rows selected and click Next, now add a Where clause to the end of SQL query that is displayed in the prompt. (I am using an orders table as an example) i.e:-

SELECT CustomerID, EmployeeID, Freight, OrderDate, OrderID, RequiredDate, ShipAddress, ShipCity, ShipCountry, ShipName, ShipPostalCode, ShipRegion, ShipVia, ShippedDate FROM Orders WHERE (ShippedDate >= DateAdd('m', - 3, NOW()))

This statement will return only those rows where the shipped date is less then 3 months old from today's date. Click Next

5) Give the Fill Method a name such as "Fill3MonthsOnly" and the Return Method a name such as "GetDataBy3MonthsOnly"

6) Click Next and finally Finish. You will now see that 2 new Methods have now been added to your DataAdapter as well as still having to original Fill and GetData methods.

7) Create as many of these additional statements as you need to cover all the combinations that you want to include in your project.

What you then need to do is load the "Default" data that you want to show when the form loads. So in the Load event of the child form you would change the typical fill statement of:-

Me.OrdersTableAdapter.Fill(Me.NorthwindDataSet.Orders)

to

Me.OrdersTableAdapter.Fill3MonthsOnly(Me.NorthwindDataSet.Orders)

Finally, you need to add a control to your form, lets say a ComboBox, to allow you to change your Data Selection. Lets say in your ComboBox you had 3 items being, Less Than 3 Months, Between 3 and 12 Months and Show all Records you could then say in the SelectedIndexChanged Event of the ComboBox:-

Private Sub ComboBox1_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
  Me.NorthwindDataSet.Orders.Rows.Clear()
  Select Case ComboBox1.Text
    Case "Less Than 3 Months"
      Me.OrdersTableAdapter.Fill3MonthsOnly(Me.NorthwindDataSet.Orders)
    Case "Between 3 and 12 Months"
      'Me.OrdersTableAdapter.Fill3to12Months(Me.NorthwindDataSet.Orders)
    Case Else
      Me.OrdersTableAdapter.Fill(Me.NorthwindDataSet.Orders)
  End Select
End Sub

That's it. What you will need to think about now is synchronizing the related (child) tables of the Cars table to make sure you have all the correctly related records for the current Cars selection. You would do this in exactly the same way as the above.

Hope that helps.

Cheers,

Ian

 

[paulthepaddy]

Hi, Ian

Thanks very much for the reply, the main question was where did i need to add the query, i don't know why, but i didn't think i could add multiple queries, and switch between them, i thought it would of need to disconnect and reconnected so the data set would have the new records, but thanks very much, at least i have solid info to work from.

i have taken a note of that north wind database, i didn't realize that was so big, i have seen a few people use it for examples, i didn't realize it was such a big thing so thanks.

can i ask a question, does the data set load a full copy of the database? and the data adapter and table adapters work from it?

Again big THANK YOU Ian

 

[IanRyder]

Hi,

can i ask a question, does the data set load a full copy of the database? and the data adapter and table adapters work from it?

No. You have misunderstood what the purpose of a TableAdaper is.

Simply explained, and thinking of reading data only at this point, your Database is the Source and Repository of all your information and the DataSet in your project is basically a Temporary storage facility which allows you to access your Database information in your application. However, what you need to remember is that a DataSet is always EMPTY until your have populated it with information from your Database.

So, how do you get the information from your Database into your Dataset? This is what the TableAdapter does. An Adapter is nothing more than a storage facility which holds SQL strings to interact with your Database. When you then use these SQL strings the information which match the criteria within those stings is read from the Database and stored in the Dataset for you to work with.

Now that you know that, you should be able to answer your own question.

The above is also true for your Action queries, Insert, Update and Delete, except that the information if passed from your DataSet back to the Database.

Hope that helps.

Cheers,

Ian