Search db [Sorted by Edneeis]

[Pirate]

I need any code that search access db . I looked around , I've tried to do it myself but all with no luck . . any help would be appreciated .
thanx

[Iouri]

Can you use SQL
"select * from Table where Field = 'YourCriteria'"

After that execute Reader if you expect to return sevral records or Scalar if it is only one record

[Pirate]

Originally posted by Iouri
Can you use SQL
"select * from Table where Field = 'YourCriteria'"
After that execute Reader if you expect to return sevral records or Scalar if it is only one record

I've tried a lot but all didn't work !!! if you have any example for this that would be great !
thanx for your reply

[Pirate]

Right now I am using this but error everywhere

Public Sub searchit()
        Dim str() As String
        Dim MyReader As OleDbDataReader
        MyConnection.Open()
        Dim comd As New OleDbCommand("SELECT * FROM MyTab WHERE C_URL = [url]www.hotmail.com[/url]", MyConnection)
        Try
            MyReader = comd.ExecuteReader()
        Catch x As Exception
            MsgBox(x.Message)
        End Try
        Try
            While MyReader.Read
                str(0) = MyReader("C_URL")
                str(1) = MyReader("C_Category")
                str(2) = MyReader("C_language")
                str(3) = MyReader("C_username")
 
            End While
        Catch x As Exception
            MsgBox(x.Message)
        End Try
end sub

[Pirate]

error msg say this : "no value given for one or more required parameters"

and this part is highlighted :

MyReader = comd.ExecuteReader()

same error same line . What am I doing wrong here !

[Pirate]

Originally posted by Iouri
Can you use SQL
"select * from Table where Field = 'YourCriteria'"

After that execute Reader if you expect to return sevral records or Scalar if it is only one record

When I use this SQL Statement the first error isn't shown :

("SELECT * FROM MyTab WHERE C_URL = 'www.hotmail.com'"

but another error is shown saying :
"Object reference not set to an instance of an object" and this part is highlighted :

MyReader.Close()

I think I have to instantiate new obj of datareader .I couldn't because new method is private .
any thoughts

[Edneeis]

Try moving the reader declaration intot he try block although it should work fine the way you have it.

Public Sub searchit()
        Dim str() As String
        Dim MyReader As OleDbDataReader
        MyConnection.Open() 'I assume this is intialized and given a connection string else where in our code
        Dim comd As New OleDbCommand("SELECT * FROM MyTab WHERE C_URL = 'www.hotmail.com'", MyConnection)'Yeah need the quotes since hotmail is a string
        Try
            Dim MyReader As OleDbDataReader= comd.ExecuteReader()
            While MyReader.Read
                str(0) = MyReader("C_URL")
                str(1) = MyReader("C_Category")
                str(2) = MyReader("C_language")
                str(3) = MyReader("C_username")
 
            End While
        Catch x As Exception
            MsgBox(x.Message)
        End Try
end sub

[Pirate]

Thanx Ed . What is stored procedure by the way !! I've read a lot of articles that talks about it !

[Edneeis]

A stored procedure is a lot like a query in an access database but WAY more powerfull. They are generally in SQL Server and probably Oracle too. They are compiled and so they run faster and you can do a lot more with them including run multiple queries or loop or a bunch of different things.

[Pirate]

Originally posted by Pirate
error msg say this : "no value given for one or more required parameters"

and this part is highlighted :

MyReader = comd.ExecuteReader()

same error same line . What am I doing wrong here !

[Pirate]

How can I instantiate new object from datareader .It keeps throwing this error :
"Object reference not set to an instance of an object" ?????
damn MS ! ADO.NET is a real pain in the ass .

[Edneeis]

Sounds like maybe your query isn't returning anything or something to that extent.

[Pirate]

Originally posted by Edneeis
Sounds like maybe your query isn't returning anything or something to that extent.

What do you think of "Find" method that exist in datarow object ??Isn't more easier than this crap although I've not used it be4 !

[Edneeis]

I started making an example for you and realized something. The problem is from the string array. You didn't initialize it to any length so you can't refer to different dimensions in it.
Change:
Dim str() as string
To:
Dim str(3) as string

And everything will work fine.

[Pirate]

I thought this is only in C# , You have to initialize it so you can use it. anyways , I will try it now

[Edneeis]

Anyway here is that example if you want it.

[Pirate]

Originally posted by Edneeis
Anyway here is that example if you want it.

Edneeis , you are best of the best in this forum . Thank you
dude !

[Pirate]

Now 90 % of the problem was solved what left is how can I search with initial letter of a word !!!!!!! I hate this part . It sucks .
Thanks a lot for your help so far .

[Edneeis]

Well I don't know about the best of the best but I'm glad I could help. Thanks.

[Pirate]

Originally posted by Pirate
Now 90 % of the problem was solved what left is how can I search with initial letter of a word !!!!!!! I hate this part . It sucks .
Thanks a lot for your help so far .

What about this part ? any ideas .

[Edneeis]

Try putting this in the textbox as of the DataReader example: www.h%
or
w%

Nevermind I screwed up on the example and forgot something. Give me a sec.

[Pirate]

Originally posted by Edneeis
Try putting this in the textbox as of the DataReader example: www.h%
or
w%

in the code . If yes . Then how if I want to look for another word stats in E for example ????
Thank you

[Edneeis]

Ok change the btnReader click event to this in the example:

Private Sub btnReader_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReader.Click
        'here is the code for using the data reader way
        'first setup the connection
        Dim MyConnection As New OleDbConnection(CnnStr)
        Dim result As New ArrayList()
        'setup the command to get the data
        'if you want to enter partial data then use Like instead of = and the % symbol as a wildcard
        Dim comd As New OleDbCommand("SELECT * FROM MyTab WHERE C_URL Like '" & txtReader.Text & "'", MyConnection) 
        'open the connection
        MyConnection.Open()
        Try
            'now the reader to read the results
            Dim MyReader As OleDbDataReader = comd.ExecuteReader()
            'read until the end
            While MyReader.Read
                result.Add(MyReader.Item("C_URL"))
                result.Add(MyReader.Item("C_Category"))
                result.Add(MyReader.Item("C_language"))
                result.Add(MyReader.Item("C_username"))
                result.Add(ControlChars.NewLine)
            End While
        Catch x As Exception
            'oops trouble show the exception
            MsgBox(x.StackTrace, MsgBoxStyle.Exclamation, x.Message)
        Finally
            'now clean up
            MyConnection.Close()
            MyConnection.Dispose()
            comd.Dispose()
        End Try
        'now show the result if any
        If result.Count > 0 Then
            Dim resultStr As String = Join(result.ToArray, " ")
            MsgBox(resultStr, , "It worked here is the results")
        End If
    End Sub

[Pirate]

I'm sorry for disturbing you but where should I put this sign "%" ??

[Pirate]

got it now . Thank you thank you Edneeis .

[Edneeis]

Its no disturbance. In SQL the % works like the * its a wildcard so you can place it at the end of a search criteria to match the start of a word or in the middle to match a pattern. So in the example change the www.hotmail.com text in the textbox of the data reader example to whatever search criteria you want.

w% would return all entries that start with w

www.h% will return all entries that start with www.h

Both of those don't care what comes after the % it just matches the start of the word.

If you want a pattern then you can put stuff after the % as well:

www.v%.com would return all entries that start with www.v and end in .com

w%.net would return all entries that start with w and end in .net

Likewise with things in the middle although this can be more tricky:

w%v% will return all entries that start with w and have a v somewhere after that

Oopps I was already writing my post when you responded. You can add more data to the database, especially similar entries, to get a better feel of using the % symbol in a query.

[Pirate]

One more quick question !!
Is it possible to add all possible matches to listbox and it deletes or adds as I type depending on the word I am looking for ? if so plz just give me a hint .
lot of thanks for those great posts .

[Edneeis]

Here is an updated example with what you are looking for. Is this for your web browser app?

[Pirate]

How did you know about that !!!!
Did you hack me ????

[Edneeis]

Yes I did

I remember a while ago you asked a bunch of questions on the subject and about the autocomplete feature of a combobox.

[Pirate]

Originally posted by Edneeis
Here is an updated example with what you are looking for. Is this for your web browser app?

exactly what I wanted to do .
That's enough Edneeis . You've done all the part concerning search db .
Thank you so so so so so much for those helpful posts .
have a nice day dude !

[Pirate]

Originally posted by Edneeis
Yes I did

I remember a while ago you asked a bunch of questions on the subject and about the autocomplete feature of a combobox.

that's right . Now I can keep my unique proj up .I will finish it soon.
see ya