LIKE Statements AND Datagrid

[wietmie]

Hai everyone, Can anyone help me with the LIKE statements which is the datagrid will show the data when the user input the search text, and the sql will search the record using the LIKE statements, when using the LIKE statements, there might be more than 1 result, for the first time, i want to bind the possible result to the label, where user can click the label then guide the user to the detail info in another page. But i think it might be complicate, and if anyone here now how to do it, i will appreciate it very2 much if they can teach me. Back to the problem, so i want to display the more that 1 result in the datagrid, but i dont know why it cannot display. here the code:

Private Sub cmdNameView_Click()
Dim strSQL As String

inputName = UCase(txtName.Text)
    'Set rs = New ADODB.Recordset
    Set rs = New ADODB.Recordset
    If inputName <> "" Then
        strSQL = "SELECT * FROM SUPPLIER WHERE sName LIKE '%" & inputName & "%'"
        MsgBox strSQL
        rs.Open strSQL, conn, adOpenKeyset, adLockPessimistic, adCmdText
        Frame2.Visible = True
        Set dgSupplyResult.DataSource = rs
    
        'fillfields
    End If

End Sub

the frame2 is where the datagrid is place. when i run the searching, the datagrid cannot display anything. If u notice, i commented the fillfields where the fillfields is use to check my sql is correct. when i use the fillfields it return a result, but when bind it to the datagrid, it cannot. can anyone help me.

thanks in advance

[wietmie]

SOLVED, Wrong in SQL statements

"SELECT sName, sAddress, City from SUPPLIER where sName LIKE '%" & txtViewLocat.text & "%'

But new problem arise which when i'm using rs.close, the data cannot be view in the datagrid, but when i detele the rs.close, then if i want to search twice, error occur that say, "Operation is now allowed when the object is open" and highlite in the rs.

Is there anyway to avoid this? i lack of idea to prevent this. below are the code:

Search to display in the datagrid

Private Sub cmdLocatView_Click()
    frameSResult.Visible = True
    frameSDetail.Visible = False
    Dim strSQL As String
    MsgBox Module1.conn
    If txtLocation.Text <> "" Then
        
        rs.CursorLocation = adUseClient
        rs.Open "SELECT sName, sAddress, City FROM SUPPLIER WHERE City like '%" & txtLocation.Text & "%'", Module1.conn, adOpenStatic, adLockOptimistic
        frameSResult.Visible = True
        Set dgSupplyResult.DataSource = rs
    End If
    closeRS
    
End Sub

Search to display in the textbox

Private Sub cmdNameView_Click()
frameSResult.Visible = False
frameSDetail.Visible = True
Dim strSQL As String
inputName = UCase(txtName.Text)
    Set rs = New ADODB.Recordset
    If inputName <> "" Then
        strSQL = "SELECT * FROM SUPPLIER WHERE sName LIKE '%" & inputName & "%'"
        rs.Open strSQL, conn, adOpenKeyset, adLockPessimistic, adCmdText
        fillfields
        rs.Close
    End If

End Sub

[wietmie]

SOLVED, Wrong in SQL statements

"SELECT sName, sAddress, City from SUPPLIER where sName LIKE '%" & txtViewLocat.text & "%'

But new problem arise which when i'm using rs.close, the data cannot be view in the datagrid, but when i detele the rs.close, then if i want to search twice, error occur that say, "Operation is now allowed when the object is open" and highlite in the rs.

Is there anyway to avoid this? i lack of idea to prevent this. below are the code:

Search to display in the datagrid

Private Sub cmdLocatView_Click()
    frameSResult.Visible = True
    frameSDetail.Visible = False
    Dim strSQL As String
    MsgBox Module1.conn
    If txtLocation.Text <> "" Then
        
        rs.CursorLocation = adUseClient
        rs.Open "SELECT sName, sAddress, City FROM SUPPLIER WHERE City like '%" & txtLocation.Text & "%'", Module1.conn, adOpenStatic, adLockOptimistic
        frameSResult.Visible = True
        Set dgSupplyResult.DataSource = rs
    End If
    closeRS
    
End Sub

Search to display in the textbox

Private Sub cmdNameView_Click()
frameSResult.Visible = False
frameSDetail.Visible = True
Dim strSQL As String
inputName = UCase(txtName.Text)
    Set rs = New ADODB.Recordset
    If inputName <> "" Then
        strSQL = "SELECT * FROM SUPPLIER WHERE sName LIKE '%" & inputName & "%'"
        rs.Open strSQL, conn, adOpenKeyset, adLockPessimistic, adCmdText
        fillfields
        rs.Close
    End If

End Sub

Problem solved Yey! I Just put one button, BACK button, and i close the recordset in that button, so it wont touch the datagrid in the datagrid sub function.