Results 1 to 6 of 6

Thread: Searching a db

  1. #1
    Join Date
    Feb 2003
    Posts
    107

    Unanswered: Searching a db

    I have a MS Access table called File
    It has fields called FileNum, Description and Comments. I want to enable the end user to type in one or more words / numbers to search for a particular file.

    My VB code looks like:

    gstrSQL = "SELECT * FROM PENDING WHERE"
    'if the Search File Name Check box is selected ...
    If bFileName Then
    gstrSQL = gstrSQL & " FILENUM LIKE '*" & strSearchText & "*'"
    End If

    As a test, I'm asking the program to search for "1110" in the file number field. I have a file number in the table with the value "1110-4-02" but the search comes back empty.
    ??

    Please advise.
    Thanks.
    Last edited by dotolee; 03-11-04 at 12:33.

  2. #2
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106

    Re: Searching a db

    Try %.


    Originally posted by dotolee
    I have a MS Access table called File
    It has fields called FileNum, Description and Comments. I want to enable the end user to type in one or more words / numbers to search for a particular file.

    My VB code looks like:

    gstrSQL = "SELECT * FROM PENDING WHERE"
    'if the Search File Name Check box is selected ...
    If bFileName Then
    gstrSQL = gstrSQL & " FILENUM LIKE '*" & strSearchText & "*'"
    End If

    As a test, I'm asking the program to search for "1110" in the file number field. I have a file number in the table with the value "1110-4-02" but the search comes back empty.
    ??

    Please advise.
    Thanks.

  3. #3
    Join Date
    Feb 2003
    Posts
    107
    Do you mean before the variable name insert a % sign?

  4. #4
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106
    gstrSQL = gstrSQL & " FILENUM LIKE '%" & strSearchText & "%'"

    Originally posted by dotolee
    Do you mean before the variable name insert a % sign?

  5. #5
    Join Date
    Feb 2003
    Posts
    107

    Thumbs up

    YIPEE!
    Thanks a bunch. That worked really well. One other question.
    I have two tables:
    one called Users and one called Resources. All users in the Users table are represented in the Resources table.
    I'm trying to select all users from Users and then select additional information for each of them in the Resources table.
    How do I add to my recordset?
    Code:

    gstrSQL = "Select * from Users where "
    If Connect Then
    'which parms are available?
    If UserNum <> "" Then
    gstrSQL = gstrSQL & "UserID='" & UserNum & "'"
    Else
    gstrSQL = gstrSQL & "UserID='" & UserID & "'"
    End If
    Set oRS = goConn.Execute(gstrSQL)
    'oRS.LockType = adLockBatchOptimistic

    If Not oRS.EOF Then
    'get email & telephone information from resource table.
    Dim objTemp As ADODB.Recordset, i As Integer
    Set objTemp = New ADODB.Recordset

    For i = 0 To oRS.RecordCount - 1
    gstrSQL = "Select email, Telephone, Ext from Resource Where FName='" & oRS.Fields(2).Value & "' AND LName='" & oRS.Fields(3).Value & "'"
    Set objTemp = goConn.Execute(gstrSQL)
    ' add to end result set
    Next
    oRS.MoveFirst
    oRS.AddNew "email", objTemp.Fields(0).Value
    oRS.AddNew "Telephone", objTemp.Fields(1).Value
    oRS.AddNew "ext", objTemp.Fields(2).Value

    Set objTemp = Nothing

    GetUserProfile = True
    End If
    Else
    mstrError = "Database currently not available. Retry later"
    GetUserProfile = False
    End If
    Exit Function

    When i do the above, I get an error that says "Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype." When I uncomment the line about the lock type, I get the error "Operation is not allowed when the object is open."

    Moving the lock type statement above "Set oRS = goConn.Execute(gstrSQL)" gives the error:
    Object variable or With block variable not set
    Last edited by dotolee; 03-11-04 at 16:24.

  6. #6
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106
    Why not have one select statement that joins users to resources? You benefit because you only make one connect and one pass through the data, getting the information you want in one result set. Also, your errors may go away.

    SELECT something FROM users u, resources r
    WHERE u.FName = r.FName AND u.LName = r.LName

    Originally posted by dotolee
    YIPEE!
    Thanks a bunch. That worked really well. One other question.
    I have two tables:
    one called Users and one called Resources. All users in the Users table are represented in the Resources table.
    I'm trying to select all users from Users and then select additional information for each of them in the Resources table.
    How do I add to my recordset?
    Code:

    gstrSQL = "Select * from Users where "
    If Connect Then
    'which parms are available?
    If UserNum <> "" Then
    gstrSQL = gstrSQL & "UserID='" & UserNum & "'"
    Else
    gstrSQL = gstrSQL & "UserID='" & UserID & "'"
    End If
    Set oRS = goConn.Execute(gstrSQL)
    'oRS.LockType = adLockBatchOptimistic

    If Not oRS.EOF Then
    'get email & telephone information from resource table.
    Dim objTemp As ADODB.Recordset, i As Integer
    Set objTemp = New ADODB.Recordset

    For i = 0 To oRS.RecordCount - 1
    gstrSQL = "Select email, Telephone, Ext from Resource Where FName='" & oRS.Fields(2).Value & "' AND LName='" & oRS.Fields(3).Value & "'"
    Set objTemp = goConn.Execute(gstrSQL)
    ' add to end result set
    Next
    oRS.MoveFirst
    oRS.AddNew "email", objTemp.Fields(0).Value
    oRS.AddNew "Telephone", objTemp.Fields(1).Value
    oRS.AddNew "ext", objTemp.Fields(2).Value

    Set objTemp = Nothing

    GetUserProfile = True
    End If
    Else
    mstrError = "Database currently not available. Retry later"
    GetUserProfile = False
    End If
    Exit Function

    When i do the above, I get an error that says "Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype." When I uncomment the line about the lock type, I get the error "Operation is not allowed when the object is open."

    Moving the lock type statement above "Set oRS = goConn.Execute(gstrSQL)" gives the error:
    Object variable or With block variable not set

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •