VB 2010 Database Search

Alter-Ego

Hi guys,

I've recently started learning Visual Basic 2010 and I am currently working on a project that is a Book shop database. The database behind it is an Access database

I need to be able to search the database by Author, Book Title, ISBN etc...

So far I can get it to search one parameter at a time, but i'd like the program to be able to search using a number of parameters, while ignoring the text boxes that I leave blank. I.e. I'd like to be able to search for all the books from a certain author, by a certain publisher.

Here's a sample of the code I've used that allows me to search using just one parameter(Text box).

Dim query = From book In Book_ShopDataSet.Books
                    Where book.BookTitle = txtTitle.Text
                    Select book.ISBN, book.BookTitle, book.Author, book.Genre, book.Publisher, book.RRP, book.FirstPublished
        If query.Count = 1 Then
            frmResults.Show()
            frmResults.dgvOutput.DataSource = query.ToList
            frmResults.dgvOutput.CurrentCell = Nothing
        Else
            MessageBox.Show("Book Not Found in Database", "Not Found")
        End If

The results of the search have to be shown in a Data Grid View box, in a second form(frmResults)

Does anybody here have any experience in doing something like this and can you point me in the right direction?

mewso

It's simply a matter of extending the query with the keyword "And"

        Dim query = From book In Book_ShopDataSet.Books
        Where book.BookTitle = txtTitle.Text
        [B]And book.Author = mycontrol.Text[/B]
        Select book.ISBN, book.BookTitle, book.Author, book.Genre, book.Publisher, book.RRP, book.FirstPublished
        If query.Count = 1 Then
            frmResults.Show()
            frmResults.dgvOutput.DataSource = query.ToList
            frmResults.dgvOutput.CurrentCell = Nothing
        Else
            MessageBox.Show("Book Not Found in Database", "Not Found")
        End If

Also note that your check on query.count will only fill the grid if there is 1 result and no more. Consider checking if query.count > 0 so as to show all results found.

Alter-Ego

Thanks for the reply mewso!

I've tried to the add the "And" to it before, but I get a series of errors. It seems like it should be that simple, but I just can get it to work.

Here's the errors:

[B]Error	1[/B]	Syntax error.	L:\Windows Programming\Assignment21\Assignment 2\Assignment 2\Form1.vb	33	25	Assignment 2

[B]Error	2[/B]	'Select Case' must end with a matching 'End Select'.	L:\Windows Programming\Assignment21\Assignment 2\Assignment 2\Form1.vb	34	25	Assignment 2

[B]Error	3[/B]	'book' is not declared. It may be inaccessible due to its protection level.	L:\Windows Programming\Assignment21\Assignment 2\Assignment 2\Form1.vb	34	32	Assignment 2

[B]Error	4[/B]	End of statement expected.	L:\Windows Programming\Assignment21\Assignment 2\Assignment 2\Form1.vb	34	41	Assignment 2

[B]Error	5[/B]	Statements and labels are not valid between 'Select Case' and first 'Case'.	L:\Windows Programming\Assignment21\Assignment 2\Assignment 2\Form1.vb	35	13	Assignment 2

[B]Error	6[/B]	Statements and labels are not valid between 'Select Case' and first 'Case'.	L:\Windows Programming\Assignment21\Assignment 2\Assignment 2\Form1.vb	36	17	Assignment 2

[B]Error	7[/B]	Statements and labels are not valid between 'Select Case' and first 'Case'.	L:\Windows Programming\Assignment21\Assignment 2\Assignment 2\Form1.vb	37	17	Assignment 2

[B]Error	8[/B]	Statements and labels are not valid between 'Select Case' and first 'Case'.	L:\Windows Programming\Assignment21\Assignment 2\Assignment 2\Form1.vb	38	17	Assignment 2

[B]Error	9[/B]	Statements and labels are not valid between 'Select Case' and first 'Case'.	L:\Windows Programming\Assignment21\Assignment 2\Assignment 2\Form1.vb	39	13	Assignment 2

[B]Error	10[/B]	Statements and labels are not valid between 'Select Case' and first 'Case'.	L:\Windows Programming\Assignment21\Assignment 2\Assignment 2\Form1.vb	40	17	Assignment 2

Here's my code with line numbers if that helps.

30        If chkTitle.Checked Then
31            Dim query = From book In Book_ShopDataSet.Books
32                        Where book.BookTitle = txtTitle.Text
33                       And book.Author = txtAuthor.Text
34                     Select book.ISBN, book.BookTitle, book.Author, book.Genre,   35 book.Publisher, book.RRP, book.FirstPublished
36        If query.Count > 0 Then
37             frmResults.Show()
38             frmResults.dgvOutput.DataSource = query.ToList
39             frmResults.dgvOutput.CurrentCell = Nothing
40         Else
41             MessageBox.Show("Book Not Found in Database", "Not Found")
42         End If
43     End If

mewso

Looks like you have your select on 2 lines which might be the problem. Making the compiler think it's a select statement rather than a linq select. The other option here if you want all fields is to simply say "SELECT book" and leave it at that.

Alter-Ego

I tried just leaving it at Select book but I'm still getting errors. One less error though. :rolleyes:

[B]Error	1[/B]	Syntax error.	L:\Windows Programming\Assignment21\Assignment 2\Assignment 2\Form1.vb	33	25	Assignment 2

[B]Error	2[/B]	'Select Case' must end with a matching 'End Select'.	L:\Windows Programming\Assignment21\Assignment 2\Assignment 2\Form1.vb	34	25	Assignment 2

[B]Error	3[/B]	'book' is not declared. It may be inaccessible due to its protection level.	L:\Windows Programming\Assignment21\Assignment 2\Assignment 2\Form1.vb	34	32	Assignment 2

[B]Error	4[/B]	Statements and labels are not valid between 'Select Case' and first 'Case'.	L:\Windows Programming\Assignment21\Assignment 2\Assignment 2\Form1.vb	35	13	Assignment 2

[B]Error	5[/B]	Statements and labels are not valid between 'Select Case' and first 'Case'.	L:\Windows Programming\Assignment21\Assignment 2\Assignment 2\Form1.vb	36	17	Assignment 2

[B]Error	6[/B]	Statements and labels are not valid between 'Select Case' and first 'Case'.	L:\Windows Programming\Assignment21\Assignment 2\Assignment 2\Form1.vb	37	17	Assignment 2

[B]Error	7[/B]	Statements and labels are not valid between 'Select Case' and first 'Case'.	L:\Windows Programming\Assignment21\Assignment 2\Assignment 2\Form1.vb	38	17	Assignment 2

[B]Error	8	[/B]Statements and labels are not valid between 'Select Case' and first 'Case'.	L:\Windows Programming\Assignment21\Assignment 2\Assignment 2\Form1.vb	39	13	Assignment 2

[B]Error	9[/B]	Statements and labels are not valid between 'Select Case' and first 'Case'.	L:\Windows Programming\Assignment21\Assignment 2\Assignment 2\Form1.vb	40	17	Assignment 2

Could it have anything to do with the VB Defaults settings in the options? Option Strict, Option Explicit etc...

srsly78

Can you not just define your sql in a string and then execute it? Much simpler.

Dim sqlstring = "SELECT stuff from WHATEVER where something = 1 and somethingelse = 2

dim qry as Query

qry.sql = sqlstring
qry.execute

Something like that. Alternatively use a library like LINQ.

Examples here: http://msdn.microsoft.com/en-us/library/fksx3b4f(v=vs.100).aspx (uses ADO library).

What library are you using in the OP? There are several ways to access a database from .net.

mewso

srsly78 wrote: »

Something like that. Alternatively use a library like LINQ.

Examples here: http://msdn.microsoft.com/en-us/library/fksx3b4f(v=vs.100).aspx (uses ADO library).

What library are you using in the OP? There are several ways to access a database from .net.

It's linq from what I can see. I'm surprised you are getting these errors op when your first example was working. Or was it? Do you have linq referenced at the top of the file?

ChRoMe

I love it when people do others homework for them on this site.

Its exactly what should be happening on a programming forum.

mewso

ChRoMe wrote: »

I love it when people do others homework for them on this site.

Its exactly what should be happening on a programming forum.

I prefer to point people in the right direction. If I wanted to do their homework I would be asking for a download of the solution, opening it in Visual Studio and finishing it off. What is the definition of doing it for them versus helping them with their work be it an assignment or otherwise? Once I know I'll stick to just helping rather than "doing it for them".

Remember students cover your tracks better and don't post code with references to projects with Assignment/Homework in their name or the development cops will be in here calling you out on it.

srsly78

ChRoMe wrote: »

I love it when people do others homework for them on this site.

Its exactly what should be happening on a programming forum.

Well this is why the solutions I give are always really crap and have links to the manual Basically I like to troll lazy students.

ChRoMe

mewso wrote: »

Remember students cover your tracks better and don't post code with references to projects with Assignment/Homework in their name or the development cops will be in here calling you out on it.

You can smell it at 1000 meters, having assignment/homework in a path somewhere is the last thing to give the game away.

Earthhorse

In fairness, the man has shown his work and marked the thread [noob]. What more do you want?

Alter-Ego

Thanks again for the replies.

mewso wrote: »

It's linq from what I can see. I'm surprised you are getting these errors op when your first example was working. Or was it? Do you have linq referenced at the top of the file?

My original code was working, where I'm searching based on 1 criteria, (E.g. By Title or by Author). As below.

Dim query = From book In Book_ShopDataSet.Books
                    Where book.BookTitle = txtTitle.Text
                    Select book.ISBN, book.BookTitle, book.Author, book.Genre, book.Publisher, book.RRP, book.FirstPublished
        If query.Count > 0 Then
            frmResults.Show()
            frmResults.dgvOutput.DataSource = query.ToList
            frmResults.dgvOutput.CurrentCell = Nothing
        Else
            MessageBox.Show("Book Not Found in Database", "Not Found")
        End If

The problem arises when I try to add the "And" operator to be able to search using mulitple criteria. (E.g. Books by Penguin(Publisher) that are in Romance(Genre))

By the LINQ reference at the top of the file, do you mean this?

Me.BooksTableAdapter.Fill(Me.Book_ShopDataSet.Books)

ChRoMe wrote: »

You can smell it at 1000 meters, having assignment/homework in a path somewhere is the last thing to give the game away.

I'm not looking for people to do my assignment for me. Nor have I been hiding the fact that it is indeed college work.

I don't have classes this week and I'm trying to get ahead on stuff. After trying to do it, based what I've learned so far, I hit a brick wall and Google wasn't getting me anywhere. So, as a long time user of Boards I decided to try and get some help here, and my understanding of the charter was that it was OK to ask for help with homework/assignments as long as I showed my work.
I'm not looking for somebody to do my work for me, because I need/want to be able to understand what to do and where I was going wrong.

If I can't get the help I need here (Which has been fantastic so far!), my lecturer will probably help me next week, but as I said, the reason I'm here is because I want to get stuck into the assignment this week.

Apologies if it seems like I've come here to get somebody to do my work for me wholesale. It's not my intention

srsly78

Read the LINQ documentation, and copy some better examples. Don't use implicit variable declaration (dim query = asdasdasd)

54kroc

Is option infer set to On?

RealistSpy

Hey,

I did a project using MsAccess Database last year, it's in C# but hope it can help you out.
Google.Code
You can look for the MsAccessDatabase.cs file and let me know how you get on.