Im Using ADO need help making a Find Record in my database access

Const DBPATH = "C:bio\medata.mdb"
Dim cn As New ADODB.Connection, rs As ADODB.Recordset
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBPATH
rs.Source = "biodata"
rs.Open , cn
Dim myname As String
searchlist.Clear
If myname = "" Then
InputBox (UCase("please type the name you are looking"))
search.searchlist.AddItem (rs("lname") & "" & "," & rs("fname") & "")
rs.MoveFirst
End If


help thanks

Hi,

To find multiple records use the Filter method on the recordset.

Const DBPATH = "C:bio\medata.mdb" Dim cn As New ADODB.Connection, rs As ADODB.Recordset ' Vars moved to top Dim myname As String cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBPATH rs.Source = "biodata" rs.Open , cn ' Removed If myname = "" Then ' modified text a bit myname = InputBox ("please type the start of the lastname you are looking for") ' convert to uppercase myname = ucase(myname) ' Only show records with similar surname (starts with what the user entered) rs.Filter = "Lname like '" & myname & "*'" rs.FilterOn=true searchlist.Clear ' Loop through the found records do while not rs.eof   ' add item to the list   searchlist.AddItem (rs("lname") & "" & "," & rs("fname") & "")   ' move to next record   rs.MoveNext ' jump to the dowhile bit loop ' turn off filter rs.filteron=false

Hope this helps,
Kieron
PS. I normally don't use the filter or find first (I put it in the query) like:

rs.open "Select * from biodata where lname like '" & myname & "*'"

[Edited by stickleprojects on 28-12-2007 at 09:03 AM GMT]

____________________________
Build it better, faster, quicker, easier.. then fix it (non-offical MS mission statement)

when i use that code i received error

Runtime error 91

Object variable or with block variable not set

and when i click debug to find out it trigers to

rs.Source = "biodata"


I dont know what to do,,

by the way if i use Select what should i do

Hello. You didn't paste all the code kieron have posted to you, did you?

quote:

---

noknok wrote:

Dim ..... , rs As ADODB.Recordset ' [....] rs.Source = "biodata"

---

Apologies.. I missed the "set rs = new adodb.recordset" line.

try

set rs = new adodb.recordset rs.Open "select * from biodata", cn

Kieron

PS. Am still waiting for my pc to have vb6 again, so i have an excuse for missing out these simple errors...


____________________________
Build it better, faster, quicker, easier.. then fix it (non-offical MS mission statement)

I didn't refer to your code, kieron. I referred to noknok's first post code, in which he declared:

Dim cn As New ADODB.Connection, rs As ADODB.Recordset

Maybe you pasted his code to modify it, and forgot to correct the declaration.
Anyway...

____________________________
Real Programmer can count up to 1024 on his fingers

yronium  actually sort of it i copied some of my code according to my research in some newbie guide visual basic and there's a lot of find method approach so it comes out that i have to make the expeirmentation so suddenly that was the out put, ^^ anyway thanks for the explanation yronium i really apriciate it and to stickleprojects and currently got confuse with the ado dao lols kinda ________ hehehe anyway ill try the sugest of you two thanks,


T_T i realy dont get it why i got error object requierd when pres debug trigers to filter.on

[Edited by noknok on 03-01-2008 at 12:34 AM GMT]

Hi
rs.filteron = true not rs.filter.on.
does that help?


____________________________
Build it better, faster, quicker, easier.. then fix it (non-offical MS mission statement)

im realy realy confuse ^^ im geting pissed dont know the code to put lols first i did put a name in a searchlist but it display all the name of person, and now i seen lots of code T_T and approach,

when finding a record in a database

first declare the connection

dim blablah as new adodb.connection, dim next as new adodb.recordset

2nd must declare the connection provider
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBPATH

and declare some

dim myname as string to use in finding record

now what im going to do in this next step T_T
why others use select
and other use adodc.blablah.recordset

im getting confuse

can u guys explain it again to me thanks

because i dont realy get it thanks again

quote:

---

stickleprojects wrote:
Hi
rs.filteron = true not rs.filter.on.
does that help?

---

I've written the code to illustrate the .filter method and the .open method. I recommend the later, you'll see why in the demo.

Hope this helps make it all a bit clearer.
Extract the files into the same folder, then open the VBP and run the app.
Click on the first button, and you'll be prompted to enter the starting characters of the lname.
Look at the code to see the differences.

Please note, I haven't neatened this code very much so that it more closely resembles what you have already seen.

Hope this helps,
Kieron


____________________________
Build it better, faster, quicker, easier.. then fix it (non-offical MS mission statement)

____________________________
Attached:
biotest.zip 9 KB (Downloads: 2)

Btw i put a public declaration to a module

in my module i put this one

Public const dbpath = "c:\bio\biodata.mdb"

is this affect my code in the form badly

i notice that when i change that dbpath into Ucase

then in my dbpath in form change to ucase also

geze


^^ thanks for the tip and help kieron i owe u a lot

Public Sub OpenDB()
Dim dbpath As String
dbpath = App.Path & "\" & DBNAME
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbpath
End Sub

i don't get this one

dbpath = App.Path & "\" & DBNAME <---- i dont get what does it mean
dbpath as string and use for  dbpath = App.Path & "\" & DBNAME


ps will while ago i try the search query as u sample it, dbpath = App.path   & "\" & DBNAME  <-- meaning that u dont need to specify like const dbpath =C:\biotest\biodata.mdb

am i corect sir,
and i have problem with the filter im still making on it how to work it out,

and i come out with another question how can i put a string that to inform the user that the existing Lasname or name of person does not exist.

thanks

[Edited by noknok on 04-01-2008 at 05:43 AM GMT]

Hi,

Dim dbpath As String  ' declare memory space for the full path to the mdb dbpath = App.Path & "\" & DBNAME ' construct the path based on the location of this VBP (or EXE file) ' if DBNAME is a full path, then change to ' dbpath=DBNAME cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbpath

Hope that's clearer. I assumed that the MDB would be in the same folder as the VBP, so yes, you are correct if DBPATH contains the full path, you don't need to use app.path (don't forget our quotes around the path!)

What's the problem with the filter? The VBP i sent you applies the filter 2 different ways, you can choose.

If no records, the rs.eof will be true before you movenext. As in..

if rs.eof then     msgbox "None found"   else ' Loop through the found records     Do While Not rs.EOF       ' add item to the list       searchlist.AddItem (rs("lname") & "" & "," & rs("fname") & "")       ' move to next record       rs.MoveNext          ' jump to the dowhile bit     Loop   end if

Hope that helps
Kieron



____________________________
Build it better, faster, quicker, easier.. then fix it (non-offical MS mission statement)

quote:

---

stickleprojects wrote:If no records, the rs.eof will be true before you movenext. As in..

if rs.eof then     msgbox "None found"   else ' Loop through the found records     Do While Not rs.EOF     ' [... other code...]

---

I wasn't aware of any debate on this one, so i'll raise it as a seperate topic :-)


____________________________
Build it better, faster, quicker, easier.. then fix it (non-offical MS mission statement)

hey im back again ^^ thanks for the nice code kieron but stil i dont know why the rs.filter wont work

regarding with the

set rs = rs.execute(select * from blah blah ) <-- how this thing work select was from sql right so i see the example why it goes like that plss be guided me thanks. im expecting also reply of mr yronium ^^

[Edited by noknok on 11-01-2008 at 07:43 AM GMT]

Hi,
How do you mean

quote:

---

rs.filter won't work

---

Hello noknok.
The Execute method is an ADODB.Connection's method, not a Recordset object's method. So your instruction "set rs = rs.execute(select * from blah blah )" is wrong: it had to be "set rs = cn.execute(select ...)" instead.

But though you can retrieve an entire recordset as an Execute method result, this is not the best way to do it: rather, you should open a recordset object. The recordset you retrieve by an Execute method is a read-only client-side keyset recordset (...er, I'm not sure at the moment if these are the correct defaults, and I have no books here, sorry...), so you can't customize it. If you use Recordset object's Open method instead you can set its properties before opening in order to obtain a recordset that's better fit for your purposes.
There are plenty of sample code in the internet, and in this forum too (click the Search link above), but I post a sample code to you:

' assume that cn is an open ADODB.Connection object Dim rs As ADODB.Recordset Dim sql As String sql = "SELECT * FROM biodata" Set rs = New ADODB.Recordset rs.Open sql, cn, adOpenForwardOnly, adLockReadOnly, adCmdText

When you open a recordset with this technique you can customize every recordset's property before opening, moreover the lock type, the cache size, the cursor location (not relevant with MSJet), the cursor type, and so on.

Once you have open the recordset, you can set its Filter property according to your purposes, like following

rs.Filter = "lname = " & Chr(39) & myname & Chr(39)

You already know how to manage the Filter property, and you can find more details in this page, but if you have to show only those specific records during the entire recordset lifetime you might want to consider opening the already filtered recordset (it seems to me that stickleproject already told it to you once) opening it with an appropriate sql instruction like following:

' assume that cn is an open ADODB.Connection object Dim rs As ADODB.Recordset Dim sql As String sql = "SELECT * FROM biodata" sql = sql & " WHERE lname = " sql = sql & Chr(39) & myname & Chr(39) Set rs = New ADODB.Recordset rs.Open sql, cn, adOpenForwardOnly, adLockReadOnly, adCmdText

This technique has the advantage of leaving the DBMS the filtering job: when using a large network server-client database this method can reduce the network data flow, with better performances. And usually a network server is a more performant machine, so it can execute queries faster than the client. Unfortunately these advantages don't apply to Access/Jet based systems, but the query execution is anyway a bit faster due to the more specialized and less busy Access' engine.

This is only a suggestion, and has nothing to do with your error (the "rs.Execute" wrong line) but you should consider to improve your technique.
Hope it helps.

____________________________
Real Programmer can count up to 1024 on his fingers

hello tthanks

kieron i found my error and its works fine now ^^ thanks to you, 2 days to find out why lols,  i understand little bit  the rs.filter thing, but not that broad im still searching the other site for more example about the filter

and yroniumi im still reading it 3 times and its not getting in my head ^^ anyway im still studying it.

and one more thing about the kieron code i try to expirement it in module why its not working ill post it tomorow the error im not in my computer now im in school now and my thought on my computer about the code why it 's not working ^^
when you put it in the module the module will be read first right? before the form will be run am i right?

so i did code the ff:

in module

option explicit
Public Const DBNAME ="biotada.mdb"


' next i put

Public Sub OpenDB()
Dim dbpath As String

dbpath = App.Path & "\" & DBNAME

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="&_ dbpath

end sub


' in my search form

Dim rs As Recordset
Dim myname as string

myname = Inputbox(" Enter Name Here")

set rs = new Recordset
rs.Open "Select * from biodata where lname like'" & myname _ & "%'", cn

searchlis.clear

Do while Not rs.EOF

searchlist.additem (rs("lname") & "" & "," & rs("fname") & "")
rs.movenext

loop

rs.close
set rs= nothing


this all the code i put so whats the problem i got error or my coding is wrong

when declare public u can use it anytime right ?




[Edited by noknok on 12-01-2008 at 10:02 AM GMT]

Please put the error that you recieved

____________________________
Build it better, faster, quicker, easier.. then fix it (non-offical MS mission statement)

Are you sure your OpenDB sub has been executed at first? What is in a module is loaded first. but it doesn't mean all the subs in the module are executed too. Only the global variable are loaded in memory, and that's all. Is the error you get related to the Connection state?

And more (where have you declared and initialized your cn object?), when you use ADO you have got to declare the Connection and the Recordset objects by the complete ADODB library name, like "Dim rs As ADODB.Recordset". If you declare it "... As Recordset" VB assumes that you are instancing a DAO object, so it doesn't recognize its properties and methods.

Let us know more about the errors you get.

____________________________
Real Programmer can count up to 1024 on his fingers

Just a comment. VB will resolve object names based on the order that they are defined in the project references, so if you did have both DAO and ADO referenced. The recordset would default to whichever was first.
That is how you can easily override the built-in vb functions by writing a dll and placing it above vb in the references.

In short. IF you don't have a reference to Data Access Objects, you won't have a problem

Regards
Kieron

____________________________
Build it better, faster, quicker, easier.. then fix it (non-offical MS mission statement)

here i am again stil dont know

this are the code i create

' in my module

Public Const DBNAME = "biodata.mdb"

Public Sub Opendb(db)
Dim DBPATH As String
DBPATH = App.Path & "\" & DBNAME
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBPATH
End Sub

'in my search box
Private Sub infocmd_Click()
Dim myname As String
Dim rs As Recordset

myname = InputBox("Please Type The Name Here")
Set rs = New Recordset
rs.Open "Select * from biodata where lname like '" & myname & "%'", cn

searchlist.Clear
Do While Not rs.EOF
searchlist.AddItem (rs("lname") & "" & "," & rs("fname"))
rs.MoveNext
Loop
rs.Close
Set rs = Nothing

End Sub


'and i got the following error
arguments are of the wrong type , are out of acceptable range, or are in conflict with one another

and when i debug it triggers to my

rs.Open "Select * from saudi where lname like '" & myname & "'", cn


please help

[Edited by noknok on 15-01-2008 at 02:50 AM GMT]

Hi Have you trie da "*" instead of "%"?
Also,
Make sure you DO NOT have a reference to DAO in your project


____________________________
Build it better, faster, quicker, easier.. then fix it (non-offical MS mission statement)

yes and its still the same

and one thing is i don't know how to identify the DAO on my coding ^^ hehehe can u help me know that

Noknok
Check the project references (it's a menu item)
Also, this message usually means you are trying to access something with the incorrect locktype and cursorlocation combination.
I assumed that you have alread added Active Data Objects to your project, apparently I was wrong. Apologies.


____________________________
Build it better, faster, quicker, easier.. then fix it (non-offical MS mission statement)

I dont Get what u Mean ^^ sory im still a student in my reference i already added the Microsoft active object library

or  i forgot something else and DAO is old technology right so i started practicing my VB on a Book and A website vbtutor.net and profsr.com  and jumpt it here.

what im going to do now?

Open your VBP
Look in the Project menu
Click the References section
Untick Data Access Objects
Click Ok

That will remove DAO from your project.


____________________________
Build it better, faster, quicker, easier.. then fix it (non-offical MS mission statement)

Hello noknok. Another test: I notice that into your Jan, 4th post you put the complete mdb filename and path into a constant, but into the next post you build the connection string with the App.Path instruction. So it raises me a doubt: are you specifying the right target for the connection?
Modify your OpenDB function as following:

Public Sub OpenDB()     Dim dbpath As String     dbpath = App.Path & "\" & DBNAME     ' add these lines to debug     Debug.Print "dbpath: " & dbpath     Debug.Assert False     cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbpath End Sub

When you run the code, execution stops on the Debug.Assert instruction, so you can read into the Debug window the value of your dbpath variable: check if it actually points to the correct path of you mdb file.

quote:

---

noknok wrote:'and i got the following error
arguments are of the wrong type , are out of acceptable range, or are in conflict with one another

and when i debug it triggers to my

rs.Open "Select * from saudi where lname like '" & myname & "'", cn

---