Search form

[digita]

Hi,

Is it possible to use sql queries in vb?

I ask this because i need to make a search function to search an Access Database.

This is what i had i mind. i have made a form with field on it, say name and address. is it then possible to make a button and add the action

select * from mydatabase WHERE name = ."name". AND address = ."address".

Is this possible? If yes is this the right code, if no, how should i do it then?

 

[Paszt]

To use the text entered in a textbox, use:

... "WHERE address ='" & searchField.Text & "'"

If you already have a OleDbConnection (I suppose set up in the designer?), use that one. No need to have two connections to the same database.

 

[digita]

To use the text entered in a textbox, use:

... "WHERE address ='" & searchField.Text & "'"

If you already have a OleDbConnection (I suppose set up in the designer?), use that one. No need to have two connections to the same database.

Thanks, that code helped.
But how do i use the OleDbConnection that i already have. Indeed, i set it up in the designer, and i believe that is is called OleDbConnection1

I tried it this way:

[size=2][color=#0000ff]Private[/color][/size][size=2] cnn [/size][size=2][color=#0000ff]As[/color][/size][size=2] OleDbConnection = [/size][size=2][color=#0000ff]Me[/color][/size][size=2].OleDbConnection1[/size]
[size=2][/size] 
[size=2]and[/size]
[size=2][/size] 
[size=2]Dim cnn As OleDbConnection = Me.OleDbConnection1
 
[/size]

But the first one gives me an error:

System.NullreferenceException

With the second one it seems to be working. The first search works, but then when i try to search for a second time, i have the same problem as i say two or three post ago:

System.InvalidOperationException: The ConnectionString Property has not inalized (I translated this from dutch)


So i think that something should be different, but i don't know what?

 

[Paszt]

If you have an OleDbConnection setup in the designer named OleDbConnection1, forget about declaring the new connection (cnn) and use OleDbConnection1:

Dim da As OleDbDataAdapter = New OleDbDataAdapter(strSQL, OleDbConnection1)

You'll also want to do away with the line 'cnn.Dispose' (or it would be OleDbConnection1.Dispose in this case). That statement frees the resources taken by the connection. In the case where you have created it in the designer, the 'Dispose' procedure handles the disposing of resources for all the controls and components created in the designer. (Look in the region " Windows Form Designer generated code " for the Dispose procedure, it's created by the form designer as the name implies).

With the first example, the connection was a class member which is declared when the class was instantiated (a form is a class in VB.NET). When the Button2_Click procedure was executed, the connection was disposed (cnn.Dispose). Therefore the second time you tried to execute it, the connection had been disposed.

 

[digita]

Thanks, That helped, it's now working and filling the datagrid, and i can search multiple times. Is there also a way to empty the datagrid if i do a new search, because now it just add's the second search result to the first result. And is it also possible to put the result in a form?

 

[Paszt]

By binding it to the DataTable, the dataGrid is displaying the data in the dataTable. What you need to do is clear the dataTable before filling it:

dt.Clear()

I'm not sure what you mean by put the result in a form.

 

[digita]

I'm not sure what you mean by put the result in a form.

I mean that i have a couple of fields, say Name and Address. How can i put the name i search for in a textbox. Because i search by name, but the rest (address, phonenumber, etc) is also selected. I want to put those in the corresponding textboxes.

I thought something like this

Name1.Text = dt("Name") 
Address1.Text = dt("Address")

I know that this is wrong, but how should it be?

 

[levyuk]

I tried doing something like this for a university project, didn't quite get it to work. You are on the right tracks with the code you have showed here. If I was you I would take a look at this web site that accompanies an ASP.net book I bought, it has all the code samples fromt he book, very useful.

http://www.superexpert.com

From chapter 9 onwards is databases. Hope this helps

 

[Paszt]

I would definitely take levyuk's advice and read about ADO.NET. There are also many good tutorial on the MSDN website: Using ADO.NET

In the meanTime, I have put together a sample app that uses an OleDbDataReader. A dataReader is good when you only need to retreive one record from a dataBase; it uses less overhead than a dataSet/DataTable and is quicker.

Find the solution attached.

 

[digita]

Thanks that has been very usefull, Thanks again.

 

[serialhobbyist]

Book suggestion

Can I recommend Microsoft ADO.NET Step by Step by Rebecca Riordan, 0-7356-1236-6 by microsoft press? It's for .net 1.0 rather than 1.1 but it does start with the basics.