Searching an ADODC

[dedo8816]

Hi, 3rd attempt to find away to do this...
Im building a form to search records from a database using an ADO control in Visual Basic, not VB.Net...
At the moment i can search the records but only using the Absoluteposition component. Problem here is i can only find the correct record if i know its exact position within the database i.e. line no. 123... will show me record 123
My table is called Locations and has 5 columns.
ID, PartNo, Location, Description, QOH
I need to search Column PartNo... how do i do this?

 

[dedo8816]

Sorry i should have shown the code i am using...
(
Private Sub cmdsearch_Click()
Adodc1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Dav\My Documents\Test form\RTA.mdb;Persist Security Info=False"
Adodc1.CommandType = adCmdTable
Adodc1.RecordSource = "SELECT PartNo FROM Locations WHERE PartNo LIKE '" & txSearch.Text & "'"
If Adodc1.Recordset.RecordCount > 0 Then
Adodc1.Recordset.AbsolutePosition = txSearch.Text
End If
Adodc1.Caption = "Record: " & Adodc1.Recordset.AbsolutePosition
End Sub
)

I think there may be a problem with the SQL statement as well but maybe not?

 

[johnwm]

You need to put a wildcard indicator into your querystring - try:

Adodc1.RecordSource = "SELECT PartNo FROM Locations WHERE PartNo LIKE '%" & txSearch.Text & "%'"
Adodc1.Refresh

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[dedo8816]

Thanks John, still wont work...

I get several errors.
1st. Syntax error in FROM Clause

2nd. Run-Time Error '-2147217900(80040e14)'
Method 'Refresh' of Object 'IAdodc' Failed

When i click debug, it highlights 'adodc1.refresh'

This is starting to seem like an impossible task, ive tried everything i know and somethings i didnt know to make this work, im gonna scream!

 

[johnwm]

1. As advised before, read faq222-2244 to see how the forum works. That way you will ask the right question first time.

2. Change your command type to adCmdText

3. Make sure that your VB is fully service packed - should be up to SP6

4. Check the spelling of your field names, table names, variable names and control names.

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[johnwm]

Also remember that eventually you will have to get away from ADODC controls and use ADO code if this is a new project rather than a maintenance project. That way you will get a decent level of control on what you are doing.


________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[dedo8816]

Thanks John, im in work at the moment so ill use your advise later today.
Also this is a new project and a learning project, im reasonably new to the programming community but hoping to make a good addition (have to start somewhere)
Thanks for your help, much appreciated

 

[BobRodes]

dedo, there are some other things you can do as well. Your ADODC1.Recordset object is an ADO Recordset, so you can do the same things to it that you can do to an ADO Recordset. So you might also try applying a filter. To do this, change this line:

    Adodc1.RecordSource = "SELECT PartNo FROM Locations WHERE PartNo LIKE '" & txSearch.Text & "'"

to:

    Adodc1.Recordset.Filter = "PartNo LIKE '" & txSearch.Text & "'"

(You should be able to avoid the refresh here.) If your cursor is set up correctly, this approach avoids a round trip to the database, which can be an advantage in some situations.

HTH

Bob

 

[Swi]

I believe the wildcard operator for Jet SQL is a '*'.

Try this link.

http://www.devguru.com/Technologies/jetsql/quickref/like.html

Swi

 

[johnwm]

Swi
Have a look at the bottom of the page that you referenced!

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[BobRodes]

:-D I'm glad to know I'm not the only person here who does things like that.

 

[Swi]

Sorry, you are correct.

Swi

 

[BobRodes]

I didn't mean to sound like I was laughing at you, swi. I just wanted you to know you're not alone. I get in trouble for that sort of thing all the time.

Bob

 

[Swi]

Didn't take it that way at all. I appreciate the post.

johnwm is 99.9% right when he posts so I should have taken more time and read the whole article that I posted.

Swi

 

[dedo8816]

Guys, thanks for all your tips! Seriously i mainly program in perl and am now trying to use VB to do some other kinds of programs for both my fellow employees and for the experience of doing it myself so i really am a complete novice in VB...
John - Your first advise did work, when i changed AdCmdText it picked everything up... Also thanks to a programmer called GOLOM i finally figured out the .Find statement (simple i know, but like i said we all have to start somewhere)
Swi - That article is good, i printed and kept it for future reference! Good man, cheers
Bob - Everyone makes mistakes in programs, you should see some of my flash stuff and some of the so-called "perl" ive done, trust me, if i had a £ for every mistake i made id be rich (Mistakes a meant to tech, if we didnt make them we'd never learn!)

Thanks guys, all the best!

 

[BobRodes]

Well, I'm glad you found the answers you were looking for, dedo. As I said in your other thread, the issue with .Filter here is the same one that you had with .Find there.