creating a search button

[crustache]

Hey,

I have an app that pulls data from an access database, everything is working fine, except i'm trying to set up a simple search function, where the user would enter a model number in cbModel and push a go button and have the data be displayed. I'm new to this. I don't know if the syntax is wrong for my select statement or what. It seems to try and then never bring back any results, even if is an exact result in the database. Here's the code.

Dim adapter AsNew OleDb.OleDbDataAdapter
Dim connection AsNew OleDb.OleDbConnection
Dim searchresults AsNew DataSet
Dim searchtime AsString
connection.ConnectionString = "provider=microsoft.jet.oledb.4.0;Data source=q:\data\isim.mdb"
connection.Open()
searchtime = "SELECT * FROM isimtable WHERE model = '*cbModel.text*'"
adapter = New OleDb.OleDbDataAdapter(searchtime, connection)
adapter.Fill(searchresults, "Search")
Try
cbModel.Text = searchresults.Tables("search").Rows(inc).Item(1)
tbSerial.Text = searchresults.Tables("search").Rows(inc).Item(2)
tbAsset.Text = searchresults.Tables("search").Rows(inc).Item(3)
rtbHistory.Text = searchresults.Tables("search").Rows(inc).Item(4)
rtbNotes.Text = searchresults.Tables("search").Rows(inc).Item(5)
tbUser.Text = searchresults.Tables("search").Rows(inc).Item(6)
cbLocation.Text = searchresults.Tables("search").Rows(inc).Item(7)
cbSchedule.Text = searchresults.Tables("search").Rows(inc).Item(8)
dtpDateSent.Text = searchresults.Tables("search").Rows(inc).Item(9)
dtpDateRec.Text = searchresults.Tables("search").Rows(inc).Item(10)
tbPrevUser.Text = searchresults.Tables("search").Rows(inc).Item(11)
Catch ex As Exception
MsgBox("There are no results")
btnGo.Visible = False
inc = 0
navigaterecords()

 

[JuggaloBrotha]

Moved to VB Data Access.

right off the bat, do I see a problem with your SELECT statement. Currently you have the name of the combo box in the SELECT statement, not the value of.

searchtime = "SELECT * FROM isimtable WHERE model = '*cbModel.text*'"

should be:
searchtime = "SELECT * FROM isimtable WHERE model = '*" & cbModel.text & "*'"

 

[crustache]

sorry about posting in the wrong place. Thanks for the correction, but it still doesn't seem to work. if I remove the TRY statement i get an error on:

cbModel.Text = searchresults.Tables("search").Rows(inc).Item(1)

error:
IndesOutOfRangeException was unhandled.
there is now row at position 0.

obviously the value of inc is 0, but there shoulud have been a value returned since my search was for a value i knew was present in the database. Is there something wrong with the way i've connected or filled my dataset? I'm don't understand.

 

[JuggaloBrotha]

try changing the WHERE part of the SELECT statement to something like this:

"WHERE model LIKE '*cbModel.text*'""

 

[crustache]

That seems to return the same error.

 

[davy_g]

I'm no expert but should this not be:

searchtime = "SELECT * FROM isimtable WHERE model = '"*" & cbModel.text & "*'"

The first * should be stated within the quotes also or not?

If you like, you can also use a like instead of =:

searchtime = "SELECT * FROM isimtable WHERE model like '"*" & cbModel.text & "*'"

Moved to VB Data Access.

right off the bat, do I see a problem with your SELECT statement. Currently you have the name of the combo box in the SELECT statement, not the value of.

searchtime = "SELECT * FROM isimtable WHERE model = '*cbModel.text*'"

should be:
searchtime = "SELECT * FROM isimtable WHERE model = '*" & cbModel.text & "*'"

 

[cjard]

Read the DW2 link in my signature, section "Creating a Form to Search Data"