-
August 25th, 2013, 08:05 PM
#16
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 ...
-
August 26th, 2013, 09:27 AM
#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
-
August 26th, 2013, 09:55 AM
#18
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 ...
-
August 26th, 2013, 10:08 AM
#19
Re: ListBox
You have to duplicate the apostrophes within SQL query.
Victor Nijegorodov
-
August 26th, 2013, 10:29 AM
#20
Re: ListBox
Originally Posted by VictorN
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 ...
-
August 26th, 2013, 10:53 AM
#21
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.
-
August 26th, 2013, 10:58 AM
#22
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
-
August 26th, 2013, 11:20 AM
#23
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.
-
August 26th, 2013, 11:38 AM
#24
Re: ListBox
Originally Posted by Probie887
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
-
August 26th, 2013, 12:08 PM
#25
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
-
August 26th, 2013, 12:17 PM
#26
Re: ListBox
Originally Posted by Probie887
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
-
August 26th, 2013, 12:31 PM
#27
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.
-
August 26th, 2013, 01:29 PM
#28
Re: ListBox
Originally Posted by Probie887
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:
- txtName after you got it from the listbox
- txtName after you called the Replace
- strSQL
Victor Nijegorodov
-
August 26th, 2013, 03:46 PM
#29
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
Last edited by Probie887; August 26th, 2013 at 04:44 PM.
-
August 26th, 2013, 07:37 PM
#30
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 ...
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|