?
Solved

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

Posted on 2005-04-01
16
Medium Priority
?
229 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
  • +2
16 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

764 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