Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Complex Queries in ADO.NET, what is and isn't supported from Access?

Posted on 2005-04-01
16
Medium Priority
?
231 Views
Last Modified: 2010-05-19
Hi,

I have a query that works fine in Access, and I have experianced in the past that Access supports alot more features in it's queries in the way of running functions and structure wise.

However I really need to port this little bugger over to ADO.NET for an ASP.NET and VB.NET application.

Basically I put into a Temporary table search results for clients then have this query to process the results.

It only tells me it can't parse it, no other help on what is causing an error.


SELECT     ClientSearch.ClientID, (SUM(lettercount([Word])) / lettercount([Company])) * (SUM(lettercount([word])) / lettercount([SearchedFor])) AS Rating,
                      tblClientData.Company, tblClientData.FirstName AS [First Name], tblClientData.Surname, tblClientData.Street AS Address1,
                      tblClientData.Street2 AS Address2, tblClientData.Suburb, tblClientData.State, tblClientData.PostCode, COUNT(ClientSearch.Word) AS CountOfWord,
                      Format([Rating], '0.0%') AS Percentage, ClientSearch.SearchedFor, lettercount([SearchedFor]) AS LtrSearchedFor, lettercount([Company]) AS LtrCompany,
                       SUM(lettercount([Word])) AS LtrWord
FROM         tblClientData RIGHT JOIN
                      ClientSearch ON tblClientData.CLID = ClientSearch.ClientID
GROUP BY ClientSearch.ClientID, tblClientData.Company, tblClientData.FirstName, tblClientData.Surname, tblClientData.Street, tblClientData.Street2,
                      tblClientData.Suburb, tblClientData.State, tblClientData.PostCode, ClientSearch.SearchedFor
ORDER BY (SUM(lettercount([Word])) / lettercount([Company])) * (SUM(lettercount([word])) / lettercount([SearchedFor])) DESC;





THESE ARE THE FUNCTION I HAVE IN THE GENERAL MODULE

Public Function WordCount(strWord As String) As String
    Dim arrWords() As String
    Dim theWord As String
    arrWords = Split(Trim(strWord), " ") ' split it by " " so we have a 0 based count of words
    WordCount = UBound(arrWords) + 1 ' return the count of the array + 1
End Function

Public Function LetterCount(strWord As String) As String
    Dim strJoinedWord As String
    strJoinedWord = Replace(Trim(strWord), " ", "") ' if string contains spaces (we don't want to count em)
    LetterCount = Len(strJoinedWord) ' return the len of the string
End Function
0
Comment
Question by:delta-mp
  • 5
  • 4
  • 3
  • +2
14 Comments
 
LVL 9

Expert Comment

by:Wim_Bl
ID: 13682711
Hi,

I assume you connect to your Access DB using ADO.NET, so that means that your querystring can be exactly like the original one. All ADO.NET does is passing this string to your database, which is then responsible for returning the result. I therefor believe that there is a slight error in the string that you pass, maybe post how you build it. It could be that one of the functions from VB is literally in your final querystring, causing Access to give a failure. Just a guess, though.

greetings
0
 

Author Comment

by:delta-mp
ID: 13682827
thanks for the reply, unfortunately, I have copied that directly out of the Access database (works fine in Access). I can execute the query perfect in Access. However when I try to add that SQL statement to a oledbcommand.commandtext it says it cannot parse it. I built the SQL string with the Access SQL builder too just to make sure it was kosher.

There is of course another option and one I would love to be able to do which is this....

The only reason I need this to work is because I need to make a search system that gives me relavance on the results.

For example if I searched for clients like Test Company

it finds all clients with Text and all clients with company

then it works out the relavence by getting a lettercount of the SearchedString, MatchedString and ClientCompany

then does the calculation of LetterCount(WordsFound) / LetterCount(CompanyFound) * LetterCount(WordsFound) / LetterCount(Search String)

Example of this is:

Searching for "Test Company" (lettercount is 11)
Found a client "My New Test Company" (lettercount is 16)
Words found in Client "Test" & "Company" (lettercount is 11)

so it is 11/16 * 11/11 which is 0.6875 or 68.75% match

THE only reason I put it in a table is so I can group and sort the table to get the best related result.

If I was more confident in ADO.NET/VB.NET I would try and manually put the records into say a DataSet then create a DataView with the Calculated Fields then sort the sucker and bobs my uncle, but these are my weaknesses:

* How to create a datarow and insert it into the dataset
* How to create a dataview with calculated fields

If someone could help with that then I could change direction on this.

Kind Regards,

Michael Proctor
0
 
LVL 10

Expert Comment

by:NetworkArchitek
ID: 13684002
I hate trying to decipher the horrible nonsense Access generated SQL. Is there anyway you can give the tables you have and the query you are trying to make? Access turns very simple queries into complex Joins and all this other stuff.

One thing I will note is that I am not sure that the OleDBCommand.CommandText will parse it if it has a ";" at the end. I would just take that out if you haven't already. Otherwise  my suggestion is to give details about the tables and the query you are trying to make. Unless a masochist out there wants to read the Access generated SQL. =)
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 10

Expert Comment

by:NetworkArchitek
ID: 13684061
Well, I just modified one of my own programs and it seems to work with the ";." So again my suggestion is to just give us some info to write a regular query. Unless someone else knows the issue right off, I've never tried to use the generated ones.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13685182
It would help if you posted your .NET code, as well as the exact contents of commandtext.
0
 
LVL 10

Expert Comment

by:NetworkArchitek
ID: 13685322
Acperkins ... duh! =) Embarrassed I didn't think of that. I remember one problem I've had to solve. For some reason if I assign the commandtext to a string literal instead  of a string variable I will have problems every once in awhile. So if you assigning that string literal to the CommandText then you could also try assigning it to a variable and then assigning CommandText to that variable.
0
 

Author Comment

by:delta-mp
ID: 13694472
I have a table that contains


tblClientSearch
--------------------------------
ClientID (Long) <- Contains the number of the matching Company name Many to One relationship to tblClientData
Word(Text) <- Contains the word found in the above Company
SerachedFor(Text) <- Entire search string searched for
UserID <- the Current user that performed this search (allows me to have multiple searchs running at once, only one per user)


tblClientData
---------------------------------
ClientID (Long, Autonumber) <- Unique ID of client
Company (Text) <- Company name
many more fields that don't matter for this search


This is an example of the data:

tblClientSearch
----------------------------
ClientID    Word              SearchedFor               UserID
1             My                  My Test Company        1
1             Test                My Test Company        1
1             Company         My Test Company        1
2             Test                My Test Company        1
3             Company         My Test Company        1
5             Company         My Test Company        1
3             Test                My Test Company        1

tblClientData
----------------------------
ClientID    Company
1             My Test Company Inc
2             Testing your brain cells
3             Need more Test Brain Cells Company
5             The Company


Match % is this:

Searching for "My Test Company" (lettercount is 13)
Found a client "My Test Company Inc" (lettercount is 16)
Words found in Client "My" & "Test" & "Company" (lettercount is 13)

so it is 13/16 * 13/13 which is 0.6875 or 81.27% match

qryFindMatches (grouped on ClientID)
----------------------------
ClientID    Company                                          % Match
1              My Test Company Inc                        81.27% (13/16 * 13/13)
2              Testing your brain cells                      5.85% (4/21 * 4/13)
3              Need more Test Brain Cells Company  32.09% (11/29 * 11/13)
5              The Company                                    53.84% (7/10 * 10/13)

so what it did which is stated in that jumble of an SQL statement above is this:

<Column 1>
ClientID
tblClientSearch
Group By

<Column 2>
Rating: (Sum(lettercount([Word]))/lettercount([Company]))*(Sum(lettercount([word]))/lettercount([SearchedFor]))
Exression (sorted Descending, give me highest match first)

<Column 3>
Company (Many to One Relationship to tblClientSearch)
tblClientData
Group By

then all I want to do is databind that query to a listbox

I am wondering if I can't do the manual adding of this information into a dataset using code instead of a query?

Regards,

Michael Proctor
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13694619
Any chance you can post "your .NET code, as well as the exact contents of commandtext"?
0
 

Author Comment

by:delta-mp
ID: 13694750
What code? that is the problem, I want to use the original SQL statement from Access that included expressions that performed functions on the individual rows then sumed the results by ClientID. Worked like a charm.

Now since i can't use that SQL statement for some reason I am wondering what is the best approach, currently I am in the process of making my own processor for this. This is the basis of it


Dim strSQL As String
        Dim strSearchFor As String
        Dim arrWords As String()
        Dim arrWord As String
        Dim RecordCount As Integer
        Dim dcJMS As New OleDb.OleDbCommand
        strSearchFor = Request("txtClient")
        arrWords = (strSearchFor.Split(" "))
        'cnJMS.Open()
        'dcJMS.CommandText = "DELETE * FROM ClientSearch WHERE UserID=" & Session("UserID")
        'dcJMS.ExecuteScalar()
        'cnJMS.Close()
        'RecordCount = dcJMS.ExecuteNonQuery()

        For Each arrWord In arrWords

            dcJMS.CommandText = "INSERT INTO ClientSearch SELECT CLID As ClientID, '" & arrWord & "' AS Word, '" & strSearchFor & "' AS SearchedFor, " & Session("UserID") & " AS UserID FROM tblClientData WHERE Company like '%" & arrWord & "%'"
            dcJMS.Connection = cnJMS
            cnJMS.Open()
            dcJMS.ExecuteNonQuery()
            dcJMS.Connection.Close()
        Next

        dcJMS.CommandText = "SELECT * FROM FindRelaventClientJMS"


        daClientSearch.Fill(dsClientSearch)

        Dim match As Data.DataRow
        Dim FindMatches As Data.DataTable = dsClientSearch.Tables("FindMatches")
        Dim arrFields As New ArrayList(10)
        arrFields.Add("ClientID")
        arrFields.Add("Company")
        arrFields.Add("Firstname")
        arrFields.Add("Surname")
        arrFields.Add("Street")
        arrFields.Add("Street2")
        arrFields.Add("Suburb")
        arrFields.Add("State")
        arrFields.Add("PostCode")
        arrFields.Add("Area")
        Dim dvFindMatches As New Data.DataView
        Dim ltrFound As Integer
        Dim ltrSearchedFor As Integer
        Dim ltrCompanyFound As Integer

        dvFindMatches.Table = dsClientSearch.Tables("ClientSearch")
        dvFindMatches.RowStateFilter = DataViewRowState.CurrentRows
        dvFindMatches.Sort = "ClientID ASC"
        Do Until dvFindMatches.Table.Rows.Count = 0
            dvFindMatches.RowFilter = "ClientID = " & dvFindMatches(0)("ClientID")
            ltrFound = 0
            Dim newmatch = FindMatches.NewRow
            Dim dataloaded As Boolean
            For Each match In dvFindMatches.Table.Rows
                ltrFound = ltrFound + Len(match("Word"))
                If dataloaded = False Then
                    ltrCompanyFound = Len(Replace(match("Company"), " ", ""))
                    ltrSearchedFor = Len(Replace(match("SearchedFor"), " ", ""))
                    For Each field As String In arrFields
                        newmatch(field) = match(field)
                    Next
                    dataloaded = True
                End If
                match.Delete()
            Next
            newmatch("Match") = ltrFound / ltrCompanyFound * ltrFound / ltrSearchedFor
            FindMatches.Rows.Add(newmatch)
        Loop
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13697548
Sorry, I can't help you any more.

Good luck.
0
 

Author Comment

by:delta-mp
ID: 13914258
Sorry admin,

I haven't actually had any assistance on this matter, I left it open to see if someone did have an answer.

There was some activity and I thank the people who did at least look into my predicament but there wasn't really anything in the answer, especially not 500 points worth. The couple of suggestions didn't take into account that this string was indeed one from Access in a working query.

If you would like to close it down that is fine with me... I worked around the issue, it would have been nice to get a solution but as we all know in the programming world some things just have to be fiddled with until we find it.

;) Thanks for your help.

Kind Regards,

Michael Proctor
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13974087
>>Delete with points refunded<<
Please do.  This thread has already taken up too much space.
0
 

Author Comment

by:delta-mp
ID: 13974642
Just to let people know, in the end, one working solution for performing manual calculations on records and then ordering them.


        If lblSearched.Text <> "True" then ' This is first time search is pressed
            If Request("txtClient") = "" Then ' Is there a client name?
                Response.Redirect("wiznewjob.aspx")
            End If
            lblSearched.Text = "True" ' Set the searched field
            Dim strSQL As String
            Dim strSearchFor As String
            Dim arrWords As String()
            Dim arrWord As String
            Dim RecordCount As Integer
            Dim dcJMS As New OleDb.OleDbCommand
            strSearchFor = Request("txtClient")  ' Set teh string of searched client
            arrWords = (strSearchFor.Split(" "))
            cnJMS.Open()
            dcJMS.Connection = cnJMS
            dcJMS.CommandText = "DELETE * FROM ClientSearch WHERE UserID=" & Session("UserID") ' Delete any previous results out of the table
            RecordCount = dcJMS.ExecuteNonQuery()
            cnJMS.Close()


            For Each arrWord In arrWords ' For each word search for it and place results in ClientSearch tables

                dcJMS.CommandText = "INSERT INTO ClientSearch SELECT CLID As ClientID, '" & arrWord & "' AS Word, '" & strSearchFor & "' AS SearchedFor, " & Session("UserID") & " AS UserID FROM tblClientData WHERE Company like '%" & arrWord & "%'"
                dcJMS.Connection = cnJMS
                cnJMS.Open()
                dcJMS.ExecuteNonQuery()
                dcJMS.Connection.Close()
            Next

            dcJMS.CommandText = "SELECT * FROM FindRelaventClientJMS WHERE UserID=" & Session("UserID")


            daClientSearch.Fill(dsClientSearch)

            Dim match As Data.DataRowView
            Dim FindMatches As Data.DataTable = dsClientSearch.Tables("FindMatches")
            Dim arrFields As New ArrayList(10)
            arrFields.Add("ClientID")
            arrFields.Add("Company")
            arrFields.Add("Firstname")
            arrFields.Add("Surname")
            arrFields.Add("Street")
            arrFields.Add("Street2")
            arrFields.Add("Suburb")
            arrFields.Add("State")
            arrFields.Add("PostCode")
            arrFields.Add("Area")
            Dim dvFindMatches As New Data.DataView
            Dim ltrFound As Integer
            Dim ltrSearchedFor As Integer
            Dim ltrCompanyFound As Integer

            dvFindMatches.Table = dsClientSearch.Tables("ClientSearch")
            dvFindMatches.RowStateFilter = DataViewRowState.CurrentRows
            dvFindMatches.Sort = "ClientID ASC"
            Do Until dvFindMatches.Table.Rows.Count = 0
                dvFindMatches.RowFilter = "ClientID = " & dvFindMatches.Table.Rows(0)("ClientID")
                ltrFound = 0
                Dim newmatch = FindMatches.NewRow
                For Each match In dvFindMatches
                    ltrFound = ltrFound + Len(match("Word"))
                Next
                ltrCompanyFound = Len(Replace(match("Company"), " ", ""))
                ltrSearchedFor = Len(Replace(match("SearchedFor"), " ", ""))
                For Each field As String In arrFields
                    newmatch(field) = match(field)
                Next
                Do Until dvFindMatches.Count = 0
                    dvFindMatches.Delete(0)
                Loop
                dvFindMatches.Table.AcceptChanges()
                newmatch("Match") = ltrFound / ltrCompanyFound * ltrFound / ltrSearchedFor
                newmatch("DisplayName") = "(" & Format(newmatch("Match"), "%0.00") & ") " & newmatch("Company") & " | " & newmatch("Street")
                FindMatches.Rows.Add(newmatch)
            Loop
            DataView1.Sort = "Match DESC"
            lstMatches.DataBind()
        End If

    End Sub

    Private Function WordCount(ByVal strWord As String) As String
        Dim arrWords() As String
        Dim theWord As String

        arrWords = Split(Trim(strWord), " ")
        WordCount = UBound(arrWords) + 1
    End Function

    Private Function LetterCount(ByVal strWord As String) As String
        Dim strJoinedWord As String

        strJoinedWord = Replace(Trim(strWord), " ", "")
        LetterCount = Len(strJoinedWord)
    End Function
0
 

Accepted Solution

by:
modulo earned 0 total points
ID: 14005458
PAQed with points refunded (500)

modulo
Community Support Moderator
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses
Course of the Month11 days, 17 hours left to enroll

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question