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]

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:

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]

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]

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:

 DataGrid1.Datasource = dt("categories")
 cnn<----- it is a connection string

 

[Paszt]

Actually cnn is an OleDbConnection. See this thread for example connection strings for Access Databases: application.startuppath database access

You can also goto http://www.connectionstrings.com for examples of connection strings for other types of databases.

 

[digita]

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]

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]

HI thanks,


But when i past it i get this:

[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?

 

[levyuk]

Try changing the word private to 'Dim' instead

 

[Paszt]

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]

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:

[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?

 

[Paszt]

do this:

DataGrid1.DataSource = dt

 

[digita]

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:

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

[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?

 

[Paszt]

Your Data Source parameter looks incorrect. The Data Source parameter is a path to the database (c:\Data\Database.mdb for example).

 

[digita]

I just found out that it does select all data from the database with the datasource i used. but when i add

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?