Using search command in the database. . .help

[nagatron]

Hello to all VB masters, I have a problem with the search command. I have a button name "btnSearch" a text field name "fldSearch" a table named MSFlexGrid and a database named db.mdb. The function of the search button is to search the data being entered in the fldSearch and display it in the MSFlexGrid.
Example:

If I put the name "Mark" in the fldSearch, then I press the button "btnSearch" the database will search for a name "Mark" and display it in the MSFlexGrid how many "Mark" being registered in the database.

PLS HELP ME. . .THANK YOU
REGARDS,
NEIL

[K.Vanlalliana]

hi,

Firstly, make a connection for your database 'db.mdb'
After calling the connection, you have to write VB code for your need:

Write the SQL command statement for searching in the btnSearch Click event as beow:

if fldSearch.Text is the textbox in which you write the string/ any word to search.

sql="Select * FROM Table_Name WHERE Column_Name like '" & Trim(fldSearch.Text) & "%'"

The above SQL will search all the like word written in the search text box.

If you want to write to search only 'Mark' then you rill mody this:
sql="Select * FROM Table_Name WHERE Column_Name ='Mark'"

To send you the complte code, you have to tell me the database name, Table anem and Table structure, and how you connect the database. I hope this will solve your problem.

[nagatron]

<strong>Private Sub cmdSearch_Click()

On Error GoTo errhan

Set rst = New ADODB.Recordset

With rst

    .CursorLocation = adUseClient
    .ActiveConnection = Con
    .CursorType = adOpenDynamic
    .LockType = adLockPessimistic
    
    SQL = "Select * from profile2 where name like '" & Trim(fldSearch.Text) & "'"

End With

Set rst = Nothing

Call dload
errhan:

If Err.Description <> vbNullString Then
    MsgBox Err.Description
End If
End Sub</strong>

IS THIS CODE FRAGMENT CORRECT? IT SEEMS THE PROGRAM STILL SAYS THE CONNECTION IS CLOSE AND I CAN'T TRACE THE PART WHY IT IS STILL CLOSE.

[aktharshaik]

Try this.

Private Sub cmdSearch_Click()

    On Error GoTo errhan

    Set rst = New ADODB.Recordset

    rst.CursorLocation = adUseClient
    SQL = "Select * from profile2 where name like '" & Trim(fldSearch.Text) & "%'"
    rst.Open SQL, con, adOpenKeyset, adLockReadOnly

    MSFlexGrid1.Clear
    While Not rst.EOF
        'replace field1,field2 etc with the fields u want
        MSFlexGrid1.addItem rst!field1 & vbTab & rst!field2
        rst.MoveNext
    Wend

    Call dload

exitPoint:
    If Not rst Is Nothing Then
         if rst.state then rst.close
        Set rst = Nothing
    End If
    Exit Sub

errhan:
    MsgBox "Err No : " & Err.Number & vbCrLf & Err.Description
    Resume exitPoint

End Sub

can u tell me what is this dload procedure?

[nagatron]

Well the dload is where the database and the MSFlexGrid are being connected. I think I received another penalty from takmaven.

[aktharshaik]

Ok. I think that you have the recordset declared globally and accessing the recordset in the dload procedure to fill the FlexGrid. then the problem with it is;
1. you are not opening the recordset in the cmdSearch_Click() event.
2. you are setting the recordset to nothing the the cmdSearch_Click() at the end which closes the recordset if open.

may be i can help u with this

Private Sub cmdSearch_Click()

    On Error GoTo errhan

    'Close the recordset if open first before searching
    If Not rst Is Nothing Then
         if rst.state then rst.close
        Set rst = Nothing
    End If
    'Instantiate again
    Set rst = New ADODB.Recordset

    rst.CursorLocation = adUseClient
    SQL = "Select * from profile2 where name like '" & Trim(fldSearch.Text) & "%'"
    'Open the recordset
    rst.Open SQL, con, adOpenKeyset, adLockReadOnly

    'Now u can call this procedure and get the recordset to work without error
    'because it is not yet closed
    Call dload

exitPoint:
    Exit Sub

errhan:
    MsgBox "Err No : " & Err.Number & vbCrLf & Err.Description
    'Put the error control code here to close recordset if any error occurs
    If Not rst Is Nothing Then
         if rst.state then rst.close
        Set rst = Nothing
    End If
    Resume exitPoint

End Sub


Private Sub dlload()

    MSFlexGrid1.Clear
    While Not rst.EOF
        'replace field1,field2 etc with the fields u want
        MSFlexGrid1.addItem rst!field1 & vbTab & rst!field2
        rst.MoveNext
    Wend

End Sub

hope it solves ur problem.

Regards
Shaik Akthar

[aktharshaik]

ok. sorting out the problem. just a few minutes plz.

Regards
Shaik Akthar

[nagatron]

I really appreciate this help, I will wait. . . thank you so much. Hope you can give me the solution coz it is been 3 days I stuck with this problem. . thank you so much.:icon_cry:

[QVeen72]

Hi,

I guess, You have not Declared a Connection Object.. and you need to open Conn object
before opening the recordset...

Check this out:

'Declare this Form-Level 
Dim Conn As New ADODB.Connection

'In FormLoad open the connection object:
With Conn
    .ConnectionString = "Provider=Microsoft.Jet.OleDb.4.0;Data Source = C:\MyDB.mdb"
    .Open
End With

You need to mention Connection object while opening recordset:

With rst
    .CursorLocation = adUseClient
    .ActiveConnection = Con
    .CursorType = adOpenDynamic
    .LockType = adLockPessimistic    
    SQL = "Select * from profile2 where name like '%" & Trim(fldSearch.Text) & "%'"
    .Open SQL,Conn
End With

Regards
Veena

[aktharshaik]

Hi,

Plz chk out the sample project i have given here as attachment. it may help u get some logic.
u need not query the database each time u want to search. fill the flexgrid once when form loads and then use that static data to find the rows with your search values af any required column.

Hope this may help u.

Regards
Shaik Akthar

[nagatron]

Thank you so much. . the problem is solved. . hehehe i know now what is the problem and it is all because of you all. . .thank you.

[aktharshaik]

:D did u mean to say that the u got to know the problem, and the problem is because of us all?

just kidding.

[esorison]

please how can i code a vb database using ms-acces to search for aname already saved in the database

[ChrisPadgham]

esorison, please start a new thread if you have a question. and you need to supply more info than above if you want a good answer