CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Page 1 of 3 123 LastLast
Results 1 to 15 of 32
  1. #1
    Join Date
    Aug 2013
    Posts
    17

    [RESOLVED] ListBox

    Hi, I am new to coding so I am having a problem. I have a list box with bible verses loaded from a database. When I click on one of the verses to load it to a text window I get and error saying:

    Run-time error '-2147217900 (80040e14)':

    Syntax error (missing operstor) in query expression '[TextData] '%And now art thou cursed from the earth, which hath opened her mouth to recieve thy brothers blood from thy hand'.

    Here is the code for that:

    Code:
    Private Sub List1_Click()
    Dim Database As New ADODB.Connection
    Dim txtName As String
    
             txtName = List1
             MousePointer = vbHourglass
                         
            With Database
    
                 Database.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & App.Path & "\KJV.mdb;" 'open Database
        
           End With
        'On Error Resume Next
           Set rs = New ADODB.Recordset 'set the recordset
               
                     rs.Open "SELECT * from BibleTable WHERE [TextData] '%" & txtName & "%'", Database, adOpenStatic, adLockPessimistic 'adLockReadOnly 'select location to search and cursor
                        
             Text2.Text = rs("BookTitle")
             Text3.Text = rs("Chapter")
             Text4.Text = rs("Verse")
             Text5.Text = rs("TextData")
            
             Me.MousePointer = vbNormal
             MousePointer = 0
    
                    
                     '   rs.Close
                      '  Set rs = Nothing  'set them to nothing
                      '  Set cn = Nothing
            
        End Sub

  2. #2
    Join Date
    Jul 2005
    Posts
    1,083

    Re: ListBox

    The select query need the LIKE or = operator, as:
    Code:
    rs.Open "SELECT * from BibleTable WHERE [TextData] LIKE '%" & txtName & "%'", Database, adOpenStatic, adLockPessimistic 'adLockReadOnly
    ...and you need to change the wildcard character to *
    JG


    ... If your problem is fixed don't forget to mark your threads as resolved using the Thread Tools menu ...

  3. #3
    Join Date
    Aug 2013
    Posts
    17

    Re: ListBox

    Thanks for your reply.
    Sorry...still get the same error, driving me crazy. Here is the code:

    Code:
      End With
        'On Error Resume Next
           Set rs = New ADODB.Recordset 'set the recordset
               
                    
                       rs.Open "SELECT * from BibleTable WHERE [TextData] LIKE '%" & txtName & "%'", Database, adOpenStatic, adLockPessimistic 'adLockReadOnly
                         
                         'On Error Resume Next
                         
             Text2.Text = rs.Fields("BookTitle")
             Text3.Text = rs.Fields("Chapter")
             Text4.Text = rs.Fields("Verse")
             Text5.Text = rs.Fields("TextData")

  4. #4
    Join Date
    Aug 2013
    Posts
    17

    Re: ListBox

    Thanks for your reply.
    Sorry...still get the same error, driving me crazy. Here is the code:



    Code:
     End With
        'On Error Resume Next
           Set rs = New ADODB.Recordset 'set the recordset
               
                    
                       rs.Open "SELECT * from BibleTable WHERE [TextData] LIKE '*" & txtName & "*'", Database, adOpenStatic, adLockPessimistic 'adLockReadOnly
                         
                         'On Error Resume Next
                         
             Text2.Text = rs.Fields("BookTitle")

  5. #5
    Join Date
    Jul 2005
    Posts
    1,083

    Re: ListBox

    Same error number... and in which line stop the execution?
    Did you change the wildcard characters from % to * ?
    JG


    ... If your problem is fixed don't forget to mark your threads as resolved using the Thread Tools menu ...

  6. #6
    Join Date
    Aug 2013
    Posts
    17

    Re: ListBox

    Yes I changed the wildcards and yes it is the same error. Once the list box is full, I click on one of the lines in the listbox to have it open in a textbox on the same page and I get the error. I also tried using the "=" in the code...doesn't help.

  7. #7
    Join Date
    Jul 2005
    Posts
    1,083

    Re: ListBox

    Post the code where you add items to List1

    ...and insert this line of code before rs.Open...

    Code:
    MsgBox txtName
    execute
    JG


    ... If your problem is fixed don't forget to mark your threads as resolved using the Thread Tools menu ...

  8. #8
    Join Date
    Aug 2013
    Posts
    17

    Re: ListBox

    I hope this helps...I highlighted where the error occurs in red, and the load list box event is in blue. As you can see I changed the wildcard * but still nothing I tried "=" in the place of "LIKE" but still I get the error. I even tried messing with adOpenStatic, adLockPessimistic, adLockReadOnly stuff but nothing works. I tried to eliminated 3 of the text boxes I wanted filled but I still get the error.

    Thanks again for your assistance in this matter.

    Code:
    Option Explicit
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
     'couple o global vars to track the form minimum size
    Dim MinHeight As Long
    Dim MinWidth As Long
    
    Private Sub cmdClose_Click()
    
    If MsgBox("Are you sure you want to exit the Search Database ?", vbYesNo + vbQuestion, "Exit DataBase?") = vbNo Then      'check if you really want to exit
    
    Exit Sub 'exit the command
               
          Else
          
          If rs.State = adStateClosed Then  'if rs.close then resume next
                        On Error Resume Next
            
                        rs.Close
                     
                        Set rs = Nothing  'set them to nothing
                        Set cn = Nothing
                       
      
            End If
            End If
            
            Unload Me
    End Sub
    
    
    
    
    Private Sub cmdSearch_Click() 'THIS LOAD LIST1
       
       Dim sSearchText As String
       Dim Clientname As String
       Dim Database As New ADODB.Connection
    
               txtSearch.SetFocus
               List1.Clear
               sSearchText = txtSearch
               MousePointer = vbHourglass
              
               
                               
        With Database
    
                 Database.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & App.Path & "\KJV.mdb;" 'open Database
        
              End With
        
                 Set rs = New ADODB.Recordset 'set the recordset
                        rs.Open "SELECT * from BibleTable WHERE [TextData] LIKE '%" & sSearchText & "%'", Database, adOpenStatic, adLockPessimistic 'adLockReadOnly 'select loaction to search and cursor
                         
                If rs.EOF Then
                       MsgBox "No records found matching " & txtSearch.Text, vbInformation, "DataBase"
                       MousePointer = 0
    
                Else
                           rs.MoveFirst
    
                      End If
                           List1.Clear
    
               Do Until rs.EOF
                           List1.AddItem (rs.Fields("TextData"))
                           rs.MoveNext
                     Loop
    
                    If Not (rs.BOF = True) Then
                            rs.MoveFirst
                            MsgBox List1.ListCount - 1 + 1 & " Records found matching " & txtSearch.Text, vbInformation, "DataBase"
                    
                    If rs.BOF = True Then
                                 
                    Else
                          display
                          Me.MousePointer = vbNormal
                    End If
                           MousePointer = 0
                    End If
    
    End Sub
    
    
    
    
    
    Public Sub display()
    
                    'display text
                           Text2.Text = rs("BookTitle")
                           Text3.Text = rs("Chapter")
                           Text4.Text = rs("Verse")
                           Text5.Text = rs("TextData")
    
    End Sub
    
    
    
    
    
    Private Sub cmdFirst_Click()
    
                    If Not rs.BOF Then
                             rs.MoveFirst
                             display
    
                    End If
    
    End Sub
    
    
    
    
     Private Sub cmdLast_Click()
    
               If Not rs.EOF Then
                            rs.MoveLast
                            display
              Else
                            MsgBox "last"
              End If
    End Sub
    
    
    
    
    Private Sub cmdNext_Click()
    
                           rs.MoveNext
            
             If Not rs.EOF Then
                          'ClearFields
                          display
                
            Else
                
                         rs.MoveLast
                         If Not rs.BOF Then
                         'ClearFields
                         display
                         MsgBox "You are at the last record..", vbInformation, "Database"
                         
                  
            End If
            End If
            
    End Sub
    
    
    
    
    Private Sub cmdPrevious_Click()
    
    rs.MovePrevious
            
                        If Not rs.BOF Then
                        'ClearFields
                        display
                
            Else
                
                      '   rs.MovePrevious
                         If rs.BOF Then
                         'ClearFields
                         'display
                         MsgBox "You are at the First Record in your Search..", vbInformation, "Database"
                         
                  
            End If
            End If
            
    
    
    End Sub
    
    
    
    
    
    Private Sub Form_Load()
    
    'center the form
                     Me.Move (Screen.Width - Me.Width) / 2, (Screen.Height - Me.Height) / 2
    
                    Text2.Text = vbNullString
                    Text3.Text = vbNullString
                    Text4.Text = vbNullString
                    Text5.Text = vbNullString
                    txtSearch.Text = "Please Enter Search Criteria"
    
    End Sub
    
    
    
    
    
    Private Sub List1_Click()  'THIS IS THE LIST1 CLICK EVENT
    Dim Database As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim txtName As String
    
                    txtName = List1
                    MousePointer = vbHourglass
                         
            With Database
    
                    Database.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & App.Path & "\KJV.mdb;" 'open Database
        
           End With
        'On Error Resume Next 
            Set rs = New ADODB.Recordset 'set the recordset
    
                     'THIS IS WHERE THE ERROR OCCURS
                    rs.Open "SELECT * from BibleTable WHERE [TextData] = '*" & txtName & "*'", Database, adOpenStatic, adLockPessimistic 'adLockReadOnly 'select loaction to search and cursor 
     
                         On Error Resume Next
                          
                      Text2.Text = rs.Fields("BookTitle")
                      Text3.Text = rs.Fields("Chapter")
                      Text4.Text = rs.Fields("Verse")
                      Text5.Text = rs.Fields("TextData")
    
                      Me.MousePointer = vbNormal
                      MousePointer = 0
    
                rs.Close
                Set rs = Nothing  'set them to nothing
                Set cn = Nothing
            
        End Sub
    
    
    
    
    
    Private Sub txtSearch_Click()
    
                    txtSearch.Text = vbnullstring
    End Sub
    Last edited by Probie887; August 25th, 2013 at 07:05 AM. Reason: typo

  9. #9
    Join Date
    Aug 2013
    Posts
    17

    Re: ListBox

    Okay....making progress. In my code I got rid of the error by adding the stuff in BOLD, the one in DARK BLUE loads okay, the three in RED do not load. Now what do I do....any ideas???? I attached a .jpg file of the screen shot so you can see the boxes.

    Code:
    Private Sub List1_Click()
      Dim Database As New ADODB.Connection
      Dim rs As New ADODB.Recordset
      Dim sSearchText As String
             
             txtTextData.Text = List1
             MousePointer = vbHourglass
                         
            With Database
    
                 Database.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & App.Path & "\KJV.mdb;" 'open Database
        
           End With
         
           Set rs = New ADODB.Recordset 'set the recordset
                   rs.Open "SELECT * from BibleTable WHERE [TextData] = '%" & sSearchText & "%'", Database, adOpenStatic, adLockPessimistic 'adLockReadOnly 'select loaction to search and cursor
    
                    
    If Not rs.BOF And rs.EOF Then
                        
                           txtBookTitle.Text = rs("BookTitle")
                           txtChapter.Text = rs("Chapter")
                           txtVerse.Text = rs("Verse")
                           txtTextData.Text = rs("TextData")
    
       Else
       'MsgBox "not found"
       End If
                           
                    
    
             Me.MousePointer = vbNormal
             MousePointer = 0
            
    
                    
                   rs.Close
                '   Set rs = Nothing  'set them to nothing
                 '   Set cn = Nothing
            
        End Sub
    Name:  Image3.jpg
Views: 1784
Size:  71.1 KB
    Last edited by Probie887; August 25th, 2013 at 01:06 PM. Reason: added image

  10. #10
    Join Date
    Jul 2005
    Posts
    1,083

    Re: ListBox

    Try next code:
    (replace yours)
    Code:
    Private Sub List1_Click()  'THIS IS THE LIST1 CLICK EVENT
    
      Dim Database As New ADODB.Connection
      Dim rs       As New ADODB.Recordset
      Dim txtName  As String
      Dim strSQL   As String
    
        txtName = List1.Text
        MousePointer = vbHourglass
                         
        Database.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & App.Path & "\KJV.mdb;" 'open Database
    
        strSQL = "SELECT * FROM BibleTable WHERE [TextData] LIKE '*" & txtName & "*'"
        
        MsgBox strSQL
    
        Set rs = New ADODB.Recordset 'set the recordset
            rs.Open strSQL Database, adOpenStatic, adLockPessimistic
    	If Not rs.BOF And rs.EOF Then
              Text2.Text = rs.Fields("BookTitle")
              Text3.Text = rs.Fields("Chapter")
              Text4.Text = rs.Fields("Verse")
              Text5.Text = rs.Fields("TextData")
            End If
            rs.Close
            Set rs = Nothing
            Database.Close
            Set Database = Nothing
    
        Me.MousePointer = vbDefault
           
    End Sub
    JG


    ... If your problem is fixed don't forget to mark your threads as resolved using the Thread Tools menu ...

  11. #11
    Join Date
    Aug 2013
    Posts
    17

    Re: ListBox

    I get a syntax error on this line:

    Code:
            rs.Open strSQL Database, adOpenStatic, adLockPessimistic

  12. #12
    Join Date
    Aug 2013
    Posts
    17

    Re: ListBox

    I put a " , " after strSQL and the syntax error went away. I ran the program and end up with other stuff as in image below. After I clicked on the OKAY I received the Run-time error '-2147217900 (80040e14)' (same as before) It stops at the line rs.Open strSQL, Database, adOpenStatic, adLockPessimistic

    Name:  Image2.jpg
Views: 1766
Size:  81.5 KB

  13. #13
    Join Date
    Jul 2005
    Posts
    1,083

    Re: ListBox

    The syntax error is because of the ' characters in the text (single quotes)

    What's the name of the textbox control where says "viper" in your screenshot
    Maybe that's the word that you have to use in the search and not the whole paragraph
    JG


    ... If your problem is fixed don't forget to mark your threads as resolved using the Thread Tools menu ...

  14. #14
    Join Date
    Aug 2013
    Posts
    17

    Re: ListBox

    The word viper is just a word entered to search for in the database.

  15. #15
    Join Date
    Aug 2013
    Posts
    17

    Re: ListBox

    I tried to upload a zip file of my program but the site says its too big, I imagine that is because of the bible.db I still do not know how to fix this thing. The sql was a good idea but still brings up the error. The error occurs at the text in RED. Images show the errors in order I get the first message box (don't know why) click OKAY and the second error box appears. The images with the line of text highlightd should be listeste in the box above with the book, verse, chapter.

    Code:
    Private Sub List1_Click()
    
      Dim Database As New ADODB.Connection
      Dim rs As New ADODB.Recordset
      Dim txtName  As String
      Dim strSQL   As String
    
        txtName = List1.Text
        MousePointer = vbHourglass
                         
        Database.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & App.Path & "\KJV.mdb;" 'open Database
    
        strSQL = "SELECT * FROM BibleTable WHERE [TextData] LIKE '*" & txtName & "*'"
        
        MsgBox strSQL
    
        Set rs = New ADODB.Recordset 'set the recordset
            rs.Open strSQL, Database, adOpenStatic, adLockPessimistic
            
                    If Not rs.BOF And rs.EOF Then
              text2.Text = rs.Fields("BookTitle")
              text3.Text = rs.Fields("Chapter")
              text4.Text = rs.Fields("Verse")
              text5.Text = rs.Fields("TextData")
            End If
            rs.Close
            Set rs = Nothing
            Database.Close
            Set Database = Nothing
    
        Me.MousePointer = vbDefault
           
    End Sub
    Name:  first.jpg
Views: 1770
Size:  99.4 KB[ATTACH] 31727[/ATTACH]
    Attached Images Attached Images  
    Last edited by Probie887; August 25th, 2013 at 06:27 PM.

Page 1 of 3 123 LastLast

Posting Permissions

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





Click Here to Expand Forum to Full Width

Featured