CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 32
  1. #16
    Join Date
    Jul 2005
    Posts
    1,083

    Re: ListBox

    I posted the explanation in post #13

    The first mesage box is because of an instruction I wrote in the code, just to display the content of the sql query,remove it anytime you want...
    The instruction to remove is : MsgBox strSQL

    The second message is the error
    The reason for this error are the single quotes characters ['] that are included in the paragraph that you click in the list box... example ("and weave the spider's web...") the error is caused by the ' character in spider's

    Let me search for an alternative to find a record using single quotes in the text
    JG


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

  2. #17
    Join Date
    Aug 2013
    Posts
    17

    Re: ListBox

    Hello jggtz, this is frustrating I know...so here we go again. First I made a copy of my program so as not to mess with your fixes. In my copy I put code back the way it was if you see in the image I have the box loading with the verse when I click on it and there is no error appearing. Now the reason that is happening is because of this line of code text5.Text = List1 but as you can also see in the image the boxes at the top are NOT filling with the records from the database (the stuff in bold in the code below should be going into those top three boxes).

    Code:
     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
                   rs.Open "SELECT * from BibleTable WHERE [TextData] = '%" & txtName & "%'", Database, adOpenStatic, adLockPessimistic 'adLockReadOnly 'select loaction to search and cursor
                  
                         text5.Text = List1
                          text2.Text = rs.Fields("BookTitle")
                          text3.Text = rs.Fields("Chapter")
                          text4.Text = rs.Fields("Verse")
    
             Me.MousePointer = vbNormal
             MousePointer = 0
            
    
       
            rs.Close
            Set rs = Nothing
            Database.Close
            Set Database = Nothing
    
        Me.MousePointer = vbDefault
           
    End Sub
    Name:  circled.jpg
Views: 1055
Size:  68.2 KB

  3. #18
    Join Date
    Jul 2005
    Posts
    1,083

    Re: ListBox

    We are going back... you have several errors in this procedure already corrected...
    May we go ahead with the last procedure again, please
    JG


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

  4. #19
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    20,395

    Re: ListBox

    You have to duplicate the apostrophes within SQL query.
    Victor Nijegorodov

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

    Re: ListBox

    Quote Originally Posted by VictorN View Post
    You have to duplicate the apostrophes within SQL query.
    Thanks... I will try to explain how to use the Replace function over txtName
    Code:
    Replace(txtName, "'","''")
    JG


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

  6. #21
    Join Date
    Aug 2013
    Posts
    17

    Re: ListBox

    May we go ahead with the last procedure again, please
    Yes we are going ahead...sorry.

    I put the REPLACE thing in the code and ran it. I received the Run-time error '-2147217900 (80040e14)': as in post one. It breaks on the sql entry marked in bold red.


    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 & "*'"
        txtName = Replace(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")
              
              Else
       'MsgBox "not found"
       End If
       
            rs.Close
            Set rs = Nothing
            Database.Close
            Set Database = Nothing
    
        Me.MousePointer = vbDefault
           
    End Sub
    Last edited by Probie887; August 26th, 2013 at 10:58 AM.

  7. #22
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    20,395

    Re: ListBox

    And how does txtName look like after Replace(txtName, "'", "''") has been executed?
    Just set a brakpoit, start a debugger and you will be able to see all your variables' values in debugger window(s)
    Victor Nijegorodov

  8. #23
    Join Date
    Aug 2013
    Posts
    17

    Re: ListBox

    I did it twice first with WATCH

    Code:
    Watch :   :    Replace(txtName, "'", "''") : "They hatch cockatrice'''' eggs, and weave the spider''''s web: he that eateth of their eggs dieth, and that which is crushed breaketh out into a viper."
    Then with CHANGE
    Code:
    Change:   : txtName = Replace(txtName, "'", "''") : False : Boolean : Search.List1_Click
    Yes I still get the same errors as well.
    Last edited by Probie887; August 26th, 2013 at 11:34 AM.

  9. #24
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    20,395

    Re: ListBox

    Quote Originally Posted by Probie887 View Post
    Code:
    Private Sub List1_Click()
        ... 
        strSQL = "SELECT * FROM BibleTable WHERE [TextData] LIKE '*" & txtName & "*'"
        txtName = Replace(txtName, "'", "''")
    But why do you change the txtName after you have already inserted it in the strSQL query? Why not before?
    Victor Nijegorodov

  10. #25
    Join Date
    Aug 2013
    Posts
    17

    Re: ListBox

    When I place it above like the code below nothing moves from the list box to the textbox when it is click and I do not get the error message. But it skips past the following code.

    Code:
    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")
              
              Else

    Here you can see I moved the line txtName = Replace(txtName, "'", "''")

    Code:
      Database.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & App.Path & "\KJV.mdb;" 'open Database
       
           txtName = Replace(txtName, "'", "''")
        strSQL = "SELECT * FROM BibleTable WHERE [TextData] LIKE '*" & txtName & "*'"
     
        
        MsgBox strSQL
    This is the Watch with it change to above the strSQL line of code.

    Code:
    Break :   : strSQL = "SELECT * FROM BibleTable WHERE [TextData] LIKE '*" & txtName & "*'" : False : Boolean : Search.List1_Click
    
    Change:   : strSQL = "SELECT * FROM BibleTable WHERE [TextData] LIKE '*" & txtName & "*'" : False : Boolean : Search.List1_Click
    
    Watch :   : txtName : "They hatch cockatrice'' eggs, and weave the spider''s web: he that eateth of their eggs dieth, and that which is crushed breaketh out into a viper." : String : Search.List1_Click
    
    Watch :   : txtName = Replace(txtName, "'", "''") : False : Boolean : Search.List1_Click

  11. #26
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    20,395

    Re: ListBox

    Quote Originally Posted by Probie887 View Post
    This is the Watch with it change to above the strSQL line of code.

    Code:
    Break :   : strSQL = "SELECT * FROM BibleTable WHERE [TextData] LIKE '*" & txtName & "*'" : False : Boolean : Search.List1_Click
    
    Change:   : strSQL = "SELECT * FROM BibleTable WHERE [TextData] LIKE '*" & txtName & "*'" : False : Boolean : Search.List1_Click
    
    Watch :   : txtName : "They hatch cockatrice'' eggs, and weave the spider''s web: he that eateth of their eggs dieth, and that which is crushed breaketh out into a viper." : String : Search.List1_Click
    
    Watch :   : txtName = Replace(txtName, "'", "''") : False : Boolean : Search.List1_Click
    What the sh...t did you post?
    What text does the strSQL contain after this line
    Code:
        strSQL = "SELECT * FROM BibleTable WHERE [TextData] LIKE '*" & txtName & "*'"
    has been executed?
    Or IOW, what does MsgBox show you?
    Victor Nijegorodov

  12. #27
    Join Date
    Aug 2013
    Posts
    17

    Re: ListBox

    I do not know what you are asking.

    You said:
    But why do you change the txtName after you have already inserted it in the strSQL query? Why not before?
    So I moved the **** thing.

    the msg strSQL contains the same message as post12

    Here is the code for the click event.

    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
       
           txtName = Replace(txtName, "'", "''")
        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")
              
             Else
       MsgBox "not found"
      End If
       
            rs.Close
            Set rs = Nothing
            Database.Close
            Set Database = Nothing
    
        Me.MousePointer = vbDefault
           
    End Sub
    Last edited by Probie887; August 26th, 2013 at 12:41 PM.

  13. #28
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    20,395

    Re: ListBox

    Quote Originally Posted by Probie887 View Post
    I do not know what you are asking.

    You said:


    So I moved the **** thing.

    the msg strSQL contains the same message as post12

    Here is the code for the click event.
    It cannot be possible if you poated the real code that you had debugged.
    Please, debug it again and show us:
    1. txtName after you got it from the listbox
    2. txtName after you called the Replace
    3. strSQL
    Victor Nijegorodov

  14. #29
    Join Date
    Aug 2013
    Posts
    17

    Re: ListBox

    I don't understand the debug program. It makes nor sense to me what I am suppose to do.

    Code:
    Watch :   : strSQL = "SELECT * FROM BibleTable WHERE [TextData] LIKE '*" & txtName & "*'" : False : Boolean : Search.List1_Click
    Watch :   : txtName = Replace(txtName, "'", "''") : False : Boolean : Search.List1_Click
    Watch :   : MsgBox : <Expression not defined in context> : Empty : Search.List1_Click
    below is an image of the strSQL message box

    Name:  watch.jpg
Views: 1052
Size:  80.6 KB
    Last edited by Probie887; August 26th, 2013 at 04:44 PM.

  15. #30
    Join Date
    Jul 2005
    Posts
    1,083

    Re: ListBox

    -Remove the MsgBox statement
    -Change the * wildcards to %
    -Change the line where you ask for rs.BOF and rs.EOF... both must be false
    -Try
    JG


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

Page 2 of 3 FirstFirst 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