How to return a Boolean Value on SQL Query... 2

[Homersim2600]

Hello,

I have an application where I need to check two different Datagrids to see if there is identical data in each of them. I simply need to know if there is identical data (true) or not (false) and have that return from a function. I will make this query quite a bit as data arrives in the two tables. I am new to this SQL stuff so sorry if this is so basic... Thanks though... Please let me know if I need to elaborate more on this.

LF

 

[jebenson]

I think perhaps we need some more elaboration. Here are some questions I have:

1) Are you comparing data for the entire datagrid, or just for individual rows?

2) At one point you mention datagrids, and then later you mention tables. A little more specificity would help. Is the data going into a datagrid for verification (error checking, etc.) and then into a table? Does the data go into the table first and then is reflected in the datagrid?

3) Are the two datagrids/tables identical (i.e., same field names, types, sizes, etc.)?

4) Is there really a Santa Claus? (Sorry, my son made me add this one!)

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[Homersim2600]

Jebenson,

lol! I am really sorry for the vagueness. I am just learning this database stuff... After I wrote this question, and subsequently submitted it, I learned what exactly a table was. I am laughing at myself as I re-read my question. Ok, since I learned some new things in the past 24-hours, I will elaborate more on what I am trying to do, and I promise, I will try to get it right this time.

I Currently have two seperate Datagrids, Datagrid1 and Datagrid2. What I want to do is check to see if any information in each, and every, row of Datagrid1 is equal to the data within one row of Datagrid2. In other words say I had the following in Datagrid1...


John adams
betsy smith
Homer simpson
superman
wonder woman

and I had this information in a single specific row of Datagrid2...

Homer simpson

What I want to do, is look for the data in the row of Datagrid2 on Datagrid1 and have the function return a True, or False, according to whether or not there was a match. I have the searching part of the code written, I just can't figure out how to make the function say true, or false.

Ok, now to clear up the answers to your other questions...

The program I am writing analyzes the text of webpages to pull out the HTTP links. Whenever the finding function locates a URL within the web page, it then inserts the link into Datagrid2 so that it can later search through that link. However, If I have already searched through that link, then I certainly do not want to upset anybody by searching again. The links which have been searched by my program are stored in Datagrid1, and so what I would like to do is check through Datagrid1 to see if that link is present, if it is, then I delete the link from Datagrid2, and if it's not, then the link has a happy home until it is searched and then deleted. I guess it would be possible to analyze the extracted link BEFORE it ever made it to Datagrid2, but still, I need to have a True or False answer to even do this; at least that's what I'm thinking.

Datagrid1 and Datagrid2 are pulled from totally seperate Databases. The only columns which are the same are the "SiteLink" column and the "Date" column. These two identical columns are the same in every respect.

Yes, There is such a thing as Santa Claus.

 

[jebenson]

Could you post the code for your search function, including the function definition line (e.g., Public Function ....)? That might help determine a solution.

I'll let my son know about the Santa info!


I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[Homersim2600]

This is the most valuable part of my code, hehe. Ok, for the sake of learning/teaching, here it is...

Function MyText(ByVal strText as string,ByVal strStarttag as string, ByVal strEndTag as string) as string
Dim intStart
Dim intend
Dim LenstrStartTag As Long
Dim LenstrEndTag As Long
Dim LenstrText As Long
Dim txtTmp1 As String
Dim txttmp2 As String
Dim txtDataLen As Long
Dim TxTData As String
Dim txttmp4 As String
Dim txttmp3 As String
Dim Inttmp1 As Long
Dim Inttmp2 As Long
Dim x As Integer
On Error Resume Next
Trim (strText)
LenstrText = Len(strText)
LenstrStartTag = Len(strStarttag)
LenstrEndTag = Len(strEndTag)
intStart = InStr(1, strText, strStarttag, vbTextCompare)
intStart = intStart + LenstrStartTag
intend = InStr(intStart + 1, strText, strEndTag, vbTextCompare)
txttmp2 = Mid(strText, intStart, intend - intStart - 1) & vbCrLf
txttmp3 = Mid(txttmp2, 1, 13)
'If txttmp3 = "

http://"

Then
If txttmp3 = "href=" & Chr(34) & "

http://"

Then
SrchIt (txttmp2) '***This is where I want to search to see if the link is already there.

AddRec2 (txttmp2) 'Here is where the link is added to the Datagrid
MyText = MyText & txttmp2
txttmp3 = vbNullString
Else
txttmp3 = vbNullString
End If

Do Until intStart >= LenstrText
DoEvents
On Error Resume Next
intStart = intend
intStart = InStr(intStart, strText, strStarttag, vbTextCompare)
intend = InStr(intStart + 1, strText, strEndTag, vbTextCompare)
If intStart = 0 Then
intStart = 1
Exit Function
End If

txtTmp1 = Trim(Mid(strText, intStart, intend - intStart)) & vbCrLf
txttmp3 = Mid(txtTmp1, 1, 13)
'If txttmp3 = "

http://"

Then
If txttmp3 = "href=" & Chr(34) & "

http://"

Then
txtDataLen = Len(Trim(txtTmp1))
TxTData = Mid(txtTmp1, 7, txtDataLen)
SrchIt (txttmp2)

AddRec2 (TxTData)
MyText = MyText & TxTData
txttmp3 = vbNullString
'ElseIf txttmp3 = "

http://"

Then
ElseIf txttmp3 = "HREF=" & Chr(34) & "

http://"

Then
txtDataLen = Len(Trim(txtTmp1))
TxTData = Mid(txtTmp1, 7, txtDataLen)
SrchIt (txttmp2)
AddRec2 (TxTData)
MyText = MyText & TxTData
txttmp3 = vbNullString

txttmp3 = vbNullString




End If

Loop
End Function


To call it I use this...

Hrefs = MyText(TmpPage, "href=" & Chr(34) & "

http://",

Chr(34) & ">")

Where TmpPage is the webpage source
"href=" & Chr(34) & "

http://"

is the starting string to search for

and

Chr(34) & ">" is the ending string

My function then returns the string between the two strings.

I want to also note that the reason I require the parsed text back to the program is because I have not yet set up the code to grab the first record of Datagrid2 as of yet. So currently, the links are saved to a txt file where they are later retrieved. I am sure you can understand why I want to get away from this method and make everything Database dependant. Anyway, Thank you for your time. I wait in anticipation...



LF

"As far as the laws of mathematics refer to reality, they are not certain; as far as they are certain, they do not refer to reality."--Albert Einstein

 

[jebenson]

Okay, here's my take on it:

Add a Boolean ByRef parameter to the function, like so:

Function MyText(ByVal strText as string,ByVal strStarttag as string, ByVal strEndTag as string, ByRef Found as Boolean) As String

Then to call it you would:

Dim HrefFound as Boolean

HrefFound = False

Hrefs = MyText(TmpPage, "href=" & Chr(34) & "

http://",

Chr(34) & ">", HrefFound)

Then in the function code, when you have determined if a URL is found you just set the boolean to True:

Found = True

Since the Boolean is passed ByRef, any changes made to it in the function will be reflected in the "original" variable. So, after you have called the function you can test the value of HrefFound to determine if one was found.

As to exactly where to put the Found=True code in the function, I'm not entirely sure. If a URL is not found, does the function return a blank string ("")? If so, you could do this:

If MyText="" then Found=True

I hope this helps. If you need any further assistance, clarification of this post, etc. just post again and I'll see it.


I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[Homersim2600]

Ok, let me back up a bit here. I get what you are saying here, but the help I need is where I check the DataGrid1 links, which holds all of the links the program has searched, to see if the link that has just been found has already been searched.

For Instance...(and please do excuse any errors I may make with the SQL)

Dim MySQL as string

MySQL = "SELECT * FROM BotData WHERE SiteLink = '" & URLFound & "'"

This is something like what I have now under my SrchIt() function. It will return a record, if it matches the variable "URLFound". What I need it to return is a boolean value, but I can't figure out how to test to see. I tried something like

If URLFound then
ItDid = true
else
ItDid = False
end if

but it didn't work out for me. I hope this clears this up a bit more. Thanks again...

LF

"As far as the laws of mathematics refer to reality, they are not certain; as far as they are certain, they do not refer to reality."--Albert Einstein

 

[jebenson]

Ok, I get it now. Sorry for the misunderstanding. Please post the code for the SrchIt function and I'll have a look.



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[Homersim2600]

Function SrchIt(ByVal URL As String) As Boolean
Dim MySQL As String
If URL <> "" Then
MySQL = "SELECT * FROM BotData WHERE SiteLink= " & Chr(39) & "

http://"

& URL & Chr(39)

Form2.Adodc3.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\spider\Botdata.mdb;Persist Security Info=False"
Form2.Adodc3.CommandType = adCmdText
Form2.Adodc3.RecordSource = MySQL
Form2.Adodc3.Refresh

Set Form2.DataGrid3.DataSource = Form2.Adodc3
Else
End If
I was just testing this out a bit, it needs work, but this is what I have; I'm not exactly sure that it's correct... I really appreciate your help; this SQL stuff is really giving me a headache! lol.

LF

"As far as the laws of mathematics refer to reality, they are not certain; as far as they are certain, they do not refer to reality."--Albert Einstein

 

[jebenson]

First of all, I don't think you're going to be able to do what you want to do using the ADODC control. I think you would get what you want by creating a connection and a recordset object in code. Here's how:

Function SrchIt(ByVal URL As String) As Boolean
Dim Conn as New Connection
Dim rs as New Recordset
Dim MySQL As String

If URL <> "" Then

Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\spider\Botdata.mdb;Persist Security Info=False"

Conn.CursorLocation = adUseClient

Conn.Open

MySQL = "SELECT * FROM BotData WHERE SiteLink= " & Chr(39) & "

http://"

& URL & Chr(39)

rs.Open MySQL, Conn

If rs.RecordCount = 0 Then
'no matching record found
SrchIt = False
Exit Function
ElseIf rs.RecordCount > 0
'matching record(s) found
SrchIt = True
Exit Function
EndIf

Conn.Close
Set Conn = Nothing

rs.Close
Set rs = Nothing

End Function

Now, for a little explanation of the code.

Dim Conn as New Connection
Dim rs as New Recordset

These lines create the Connection and Recordset objects. These objects are created implicitly when you use an ADODC.

Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\spider\Botdata.mdb;Persist Security Info=False"

Conn.CursorLocation = adUseClient

Conn.Open

These lines set the ConnectionString for the connection object (exactly the same as for the ADODC), set the CursorLocation and Open the connection to the database. Setting the CursorLocation to adUseClient enables you to get a RecordCount from the Recordset object. If you use adUseServer the RecordCount will always be -1.

rs.Open MySQL, Conn

This line opens the Recordset, using the SQL command you supply and the Connection object created earlier


If rs.RecordCount = 0 Then
'no matching record found
SrchIt = False
Exit Function
ElseIf rs.RecordCount > 0
'matching record(s) found
SrchIt = True
Exit Function
EndIf

If there are records returned by the SQL command, the RecordCount will be 1 or greater. If no records are returned, the RecordCount will be 0.


Conn.Close
Set Conn = Nothing

rs.Close
Set rs = Nothing

Terminating the database connection and clearing the recordset. Always do this explicitly when done with a connection and/or recordset to avoid multiple unused connections to the database.

Here's a link to the MSDN section on ADO. You can also find numerous book and online articles/tutorials on the subject.

[URL unfurl="true"]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmscsection1_ado.asp[/url]

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[Homersim2600]

WOW! You have gone above and beyond what I was asking... I really appreciate this. I have certainly learned a lot between my two postings here; I will be sure to pass it along. Thank you very much.

LF

"As far as the laws of mathematics refer to reality, they are not certain; as far as they are certain, they do not refer to reality."--Albert Einstein

 

[Homersim2600]

Just a side note here...

when I ran this code you provided to me, I got the error "Invalid Use of New Keyword". After about 3-hours of scouring the internet, I finally found that the references need to be checked in the proper order. Who woulda thought? Just in case anybody else comes across this, the correct order in which to check the references in the project is...

Microsoft ActiveX Data Objects 2.8 Library

and then...

Microsoft DAO 3.6 Object Library

Jebeson,
I must say, your code works like a dream, however, my SQL statement is not doing so great. I think I may be able to figure that one out though...

Also, I have successfully transfered control from those sloppy txt files to the two databases; the program works much better, it's not complete, but it works much better...

Have a Duff on me ;-)
Thanks.

LF



"As far as the laws of mathematics refer to reality, they are not certain; as far as they are certain, they do not refer to reality."--Albert Einstein

 

[johnwm]

Homersim2600
If you actually need to reference MDAC 2.8 (which only supports ADO) and DAO 3.6 in the same project then you should always ensure that your code is unambiguous. As in:

Dim rs As DAO.Recordset
Dim rs2 As ADODB.Recordset

rather than relying on the order in which references appear.

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

http://www.essexsteam.co.uk

for steam enthusiasts

 

[jebenson]

Oh yeah...I forgot about that. I never use DAO anymore and so have fallen out of the habit of qualifying my ADO object references with ADODB. Sorry about that.

I'm glad I've been able to help out with your project. If you need any help with the SQL, let me know.

Happy Holidays!

*pfishhh* (sound of a Duff opening)

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[Homersim2600]

*pfishhh* (sound of a Duff opening) "

LOL! It is well deserved...

I have figured out the SQL string... Instead of using "WHERE SiteLink = chr(39) & URL & chr(39)" I use "WHERE SiteLink LIKE chr(39) & URL & chr(39)" This works, but I am finding that as the database gets Larger, the more time it takes to search both databases to see if the link has already been searched. So while it is functional, I am going to see if I can figure something else out that is a bit faster; any ideas?

Also, If I moved the database to a seperate computer, which has SQL 2000 on it, would this free up enough resources for the program to run faster; I'm going to try this in the mean time and see what happens?

Currently, I keep the whole database open, is there a way to only open a portion of the database in order to speed things up a bit, without sacrificing the ability to grab the first record, and then save a record to the last position? I am going to experiment with this a bit. I think I could use similar code to what you wrote and change around the SQL statement to simply get the first record of the database without actually having to display it on my Datagrid. I will, of course, do away with the boolean expression at the bottom of the code and instead return the value from the query; I am thinking this might work.

JohnWM,
Thank you for that...What's nice about these kinds of errors is that I never tend to forget them . Once I get everything else worked out, then I am going to go back and get rid of my database making function and replace it with the ADO style that Jebenson pointed out to me. At one point I did insert the "ADODB.Connection" part into my dimension, but for some reason I wasn't sure if that was ok or not and so I pressed further.

This is really neat to see my program unfold the way it has. I really appreciate the help, and I will include both of your handles, and a reference to this website, in the copyright information for the parts you folks helped me with. Thanks a million...

LF

"As far as the laws of mathematics refer to reality, they are not certain; as far as they are certain, they do not refer to reality."--Albert Einstein

 

[jebenson]

If you can move the DB to SQL 2000 then by all means do so. I would also suggest you look into using stored procedures to do the "heavy lifting" in the searches. Just do a search on this forum or the VB 5 & 6 forum and you will find tons of good information about how to pass parameters (in this case the URL to find) to a stored proc and how to return results to your app. You should also look into Transact-SQL (T-SQL), Microsoft's version of SQL that is used in SQL Server. You also might want to look into triggers, because I think you might find them useful in this app.

Beyond that, look into indexing the tables to speed up your searches.

Cheers and Merry Christmas!

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[Homersim2600]

Merry Christmas to you and yours as well...

WOW! That is a lot of information. lol. I will certainly get some use out of it for sure.

I found a big bug in my program, which is why it was running so slow, I forgot to get rid of a variable that was holding all of the links to the webpages. Essentially what was happening was that this variable would get so loaded down that it would ultimately reduce the RAM to nearly nothing! Needless to say, once I found that, the program runs a bit faster, but not nearly as fast as I would like. I am going to look into the information you have given me to see what I can come up with.

I stepped away from this program for a bit so that I could let my mind rest. During this time I created an Active X webpage that would allow a user to add a link to the Queue of the database for searching. I even display the actual Queue on the website, which I thought was kinda neat. I am not sure if the site will work at this moment because I just entered the page into the DNS, but here is the link if you wish to take a look.
<

Http://spiderweb.veryfinephotos.com>

Also, I will be doing a bit of changing around with the database tonight so it may be unavailable for a bit. Anyway, Thanks for all of your help, and you too JohnWM!

Thanks and merry christmas to you both...

LF



"As far as the laws of mathematics refer to reality, they are not certain; as far as they are certain, they do not refer to reality."--Albert Einstein