Search form

digita

Active member
Joined
Jul 8, 2004
Messages
29
Programming Experience
1-3
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

VB.NET:
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

Staff member
Joined
Jun 3, 2004
Messages
1,500
Location
Raleigh, NC - USA
Programming Experience
Beginner
Yes it is possible, and there are a myriad number of ways to do it. Here is some sample code that fills a dataTable with the results of the SQL statement:

VB.NET:
Private cnn As OleDbConnection = New OleDbConnection("connectionStringGoesHere")
'see www.connectionstrings.com
Private dt As New DataTable("categories")

Dim strSQL As String = "select * from myTable " & _ 
   "WHERE name = 'name' AND address = 'address'"
Dim da As OleDbDataAdapter = New OleDbDataAdapter(strSQL, cnn)
Try
    cnn.Open()
    da.Fill(dt)
Catch exc as Exception
    MessageBox.Show(exc.tostring)
Finally
    Try
        cnn.Close()
    Catch : End Try
    da.Dispose()
    cnn.dispose()
End Try

Now you have a dataTable filled with the results which you can bind to a dataGrid for example.
 

digita

Active member
Joined
Jul 8, 2004
Messages
29
Programming Experience
1-3
Thanks, but i am a newbie so i have a couple of questions. How do i bind it to a dataGrid or even better how do i fill those results in the form. and what's the cnn in this?
 

mzim

Well-known member
Joined
Jun 3, 2004
Messages
187
Location
Other side of the rock
Programming Experience
1-3
digita said:
Thanks, but i am a newbie so i have a couple of questions. How do i bind it to a dataGrid or even better how do i fill those results in the form. and what's the cnn in this?
hi,
Paszt code is already what you want.
to fill those results:
VB.NET:
 DataGrid1.Datasource = dt("categories")
 cnn<----- it is a connection string
 

digita

Active member
Joined
Jul 8, 2004
Messages
29
Programming Experience
1-3
When i try this code it tell me that some things are not defined, what can i do about that? And how can i add that code to an button?
 

Paszt

Staff member
Joined
Jun 3, 2004
Messages
1,500
Location
Raleigh, NC - USA
Programming Experience
Beginner
First, let us know what things aren't defined :).

But since my mind reading powers are working today; place this statement at the top of the code page: Imports System.Data.OleDb

To add the code to a button, add a button to your form then double click it. Copy the code and paste it inside of the event handler that the IDE created when you double clicked the button.
 

digita

Active member
Joined
Jul 8, 2004
Messages
29
Programming Experience
1-3
HI thanks,


But when i past it i get this:

VB.NET:
[size=2][color=#0000ff]Private[/color][/size][size=2][color=#0000ff]Sub[/color][/size][size=2] Button2_Click([/size][size=2][color=#0000ff]ByVal[/color][/size][size=2] sender [/size][size=2][color=#0000ff]As[/color][/size][size=2] System.Object, [/size][size=2][color=#0000ff]ByVal[/color][/size][size=2] e [/size][size=2][color=#0000ff]As[/color][/size][size=2] System.EventArgs) [/size][size=2][color=#0000ff]Handles[/color][/size][size=2] Button2.Click
 
[/size][size=2][color=#0000ff][u]Private[/u][/color][/size][size=2] cnn [/size][size=2][color=#0000ff]As[/color][/size][size=2] OleDbConnection = [/size][size=2][color=#0000ff]New[/color][/size][size=2] OleDbConnection("connectionStringGoesHere")
 
[/size][size=2][color=#008000]'see www.connectionstrings.com
 
[/color][/size][size=2][color=#0000ff][u]Private[/u][/color][/size][size=2] dt [/size][size=2][color=#0000ff]As[/color][/size][size=2][color=#0000ff]New[/color][/size][size=2] DataTable("categories")
 
[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] strSQL [/size][size=2][color=#0000ff]As[/color][/size][size=2][color=#0000ff]String[/color][/size][size=2] = "select * from myTable " & _
 
"WHERE name = 'name' AND address = 'address'"
 
[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] da [/size][size=2][color=#0000ff]As[/color][/size][size=2] OleDbDataAdapter = [/size][size=2][color=#0000ff]New[/color][/size][size=2] OleDbDataAdapter(strSQL, cnn)
 
[/size][size=2][color=#0000ff]Try
 
[/color][/size][size=2]cnn.Open()
 
da.Fill(dt)
 
[/size][size=2][color=#0000ff]Catch[/color][/size][size=2] exc [/size][size=2][color=#0000ff]As[/color][/size][size=2] Exception
 
MessageBox.Show(exc.ToString)
 
[/size][size=2][color=#0000ff]Finally
 
[/color][/size][size=2][color=#0000ff]Try
 
[/color][/size][size=2]cnn.Close()
 
[/size][size=2][color=#0000ff]Catch[/color][/size][size=2] : [/size][size=2][color=#0000ff]End[/color][/size][size=2][color=#0000ff]Try
 
[/color][/size][size=2]da.Dispose()
 
cnn.Dispose()
 
[/size][size=2][color=#0000ff]End[/color][/size][size=2][color=#0000ff]Try
 
[/color][/size][size=2][color=#0000ff]End[/color][/size][size=2][color=#0000ff]Sub
 
[/color][/size]

The underlined give an error saying that 'Private' is not valid on a local variable declaration. Am i doing something wrong?
 

Paszt

Staff member
Joined
Jun 3, 2004
Messages
1,500
Location
Raleigh, NC - USA
Programming Experience
Beginner
The two private declarations were meant to be class members (meaning you declare them outside of any procedure, that way they have class scope. Otherwise, you'll have trouble binding the dataTable to a dataGrid because it will no longer exist when the procedure has finished executing).
 

digita

Active member
Joined
Jul 8, 2004
Messages
29
Programming Experience
1-3
Paszt said:
The two private declarations were meant to be class members (meaning you declare them outside of any procedure, that way they have class scope. Otherwise, you'll have trouble binding the dataTable to a dataGrid because it will no longer exist when the procedure has finished executing).
Do you mean it like this:

VB.NET:
[size=2][/size][size=2][color=#0000ff]Private[/color][/size][size=2] dt [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]New[/color][/size][size=2] DataTable("categories")

[/size][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]New[/color][/size][size=2] OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=ktc.ktclid;User Id=admin;Password=;")

 

[/size][size=2][color=#0000ff]Private[/color][/size][size=2] [/size][size=2][color=#0000ff]Sub[/color][/size][size=2] Button2_Click([/size][size=2][color=#0000ff]ByVal[/color][/size][size=2] sender [/size][size=2][color=#0000ff]As[/color][/size][size=2] System.Object, [/size][size=2][color=#0000ff]ByVal[/color][/size][size=2] e [/size][size=2][color=#0000ff]As[/color][/size][size=2] System.EventArgs) [/size][size=2][color=#0000ff]Handles[/color][/size][size=2] Button2.Click

[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] strSQL [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]String[/color][/size][size=2] = "select * from Gegevens " & _

"WHERE Naam = 'Searchfield'"

[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] da [/size][size=2][color=#0000ff]As[/color][/size][size=2] OleDbDataAdapter = [/size][size=2][color=#0000ff]New[/color][/size][size=2] OleDbDataAdapter(strSQL, cnn)

[/size][size=2][color=#0000ff]Try

[/color][/size][size=2]cnn.Open()

da.Fill(dt)

[/size][size=2][color=#0000ff]Catch[/color][/size][size=2] exc [/size][size=2][color=#0000ff]As[/color][/size][size=2] Exception

MessageBox.Show(exc.ToString)

[/size][size=2][color=#0000ff]Finally

[/color][/size][size=2][/size][size=2][color=#0000ff]Try

[/color][/size][size=2]cnn.Close()

[/size][size=2][color=#0000ff]Catch[/color][/size][size=2] : [/size][size=2][color=#0000ff]End[/color][/size][size=2] [/size][size=2][color=#0000ff]Try

[/color][/size][size=2]da.Dispose()

cnn.Dispose()

[/size][size=2][color=#0000ff]End[/color][/size][size=2] [/size][size=2][color=#0000ff]Try

[/color][/size][size=2]DataGrid1.DataSource = [u]dt[/u]("categories")

 

[/size][size=2][color=#0000ff]End[/color][/size][size=2] [/size][size=2][color=#0000ff]Sub

[/color][/size]

This way it give the error that the underlined dt "Class 'System.Data.DataTable' cannot be indexed because it has no default property."

What does this mean?
 

digita

Active member
Joined
Jul 8, 2004
Messages
29
Programming Experience
1-3
Thanks that worked, no errors any more. But i have two mpre questions

When i press the button now it does not select from the database. and when i press the search button for the second time it gives me the error:

VB.NET:
System.InvalidOperationException: The ConnectionString-Proporty has not inalized.
 at System.DataOleDB.OleDbConnection.Open()
 at KTC_Ledenadministratie.Invoerenfrm.Button2_Click(Object sender, EvenArgs e)

This is the code i now use

VB.NET:
[size=2][/size][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]New[/color][/size][size=2] OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=ktc.ktclid;User Id=admin;Password=;")

[/size][size=2][color=#0000ff]Private[/color][/size][size=2] dt [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]New[/color][/size][size=2] DataTable("categories")

[/size][size=2][color=#0000ff]Private[/color][/size][size=2] [/size][size=2][color=#0000ff]Sub[/color][/size][size=2] Button2_Click([/size][size=2][color=#0000ff]ByVal[/color][/size][size=2] sender [/size][size=2][color=#0000ff]As[/color][/size][size=2] System.Object, [/size][size=2][color=#0000ff]ByVal[/color][/size][size=2] e [/size][size=2][color=#0000ff]As[/color][/size][size=2] System.EventArgs) [/size][size=2][color=#0000ff]Handles[/color][/size][size=2] Button2.Click

[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] strSQL [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]String[/color][/size][size=2] = "select * from Gegevens WHERE Geslacht = 'sf'"

[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] da [/size][size=2][color=#0000ff]As[/color][/size][size=2] OleDbDataAdapter = [/size][size=2][color=#0000ff]New[/color][/size][size=2] OleDbDataAdapter(strSQL, cnn)

[/size][size=2][color=#0000ff]Try

[/color][/size][size=2]cnn.Open()

da.Fill(dt)

[/size][size=2][color=#0000ff]Catch[/color][/size][size=2] exc [/size][size=2][color=#0000ff]As[/color][/size][size=2] Exception

MessageBox.Show(exc.ToString)

[/size][size=2][color=#0000ff]Finally

[/color][/size][size=2][/size][size=2][color=#0000ff]Try

[/color][/size][size=2]cnn.Close()

[/size][size=2][color=#0000ff]Catch[/color][/size][size=2] : [/size][size=2][color=#0000ff]End[/color][/size][size=2] [/size][size=2][color=#0000ff]Try

[/color][/size][size=2]da.Dispose()

cnn.Dispose()

[/size][size=2][color=#0000ff]End[/color][/size][size=2] [/size][size=2][color=#0000ff]Try

[/color][/size][size=2]DataGrid1.DataSource = dt

 

[/size][size=2][color=#0000ff]End[/color][/size][size=2] [/size][size=2][color=#0000ff]Sub

[/color][/size]
What am i doing wrong?
 

digita

Active member
Joined
Jul 8, 2004
Messages
29
Programming Experience
1-3
I just found out that it does select all data from the database with the datasource i used. but when i add
VB.NET:
where address = 'address'
then i does not select anything. that second address, that can be a textbox with the name address, or must that be the actuall address? Because i want that it uses the text that i entered in the textbox "searchfield".

That other problem is also not solved yet, does it make a difference that i already have an OleDbConnection to the same datasource, which i use to fill my form?
 
Last edited:

Paszt

Staff member
Joined
Jun 3, 2004
Messages
1,500
Location
Raleigh, NC - USA
Programming Experience
Beginner
To use the text entered in a textbox, use:
VB.NET:
... "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

Active member
Joined
Jul 8, 2004
Messages
29
Programming Experience
1-3
Paszt said:
To use the text entered in a textbox, use:
VB.NET:
... "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:
VB.NET:
[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?
 
Last edited:

Paszt

Staff member
Joined
Jun 3, 2004
Messages
1,500
Location
Raleigh, NC - USA
Programming Experience
Beginner
If you have an OleDbConnection setup in the designer named OleDbConnection1, forget about declaring the new connection (cnn) and use OleDbConnection1:

VB.NET:
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

Active member
Joined
Jul 8, 2004
Messages
29
Programming Experience
1-3
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

Staff member
Joined
Jun 3, 2004
Messages
1,500
Location
Raleigh, NC - USA
Programming Experience
Beginner
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:
VB.NET:
dt.Clear()
I'm not sure what you mean by put the result in a form.
 
Top Bottom