Simple search site help

[Mike C]

Re: Simple search site help

Dim config As CommitCRM.Config

config = New CommitCRM.Config
config.AppName = "CommitWebITF"
config.CommitDllFolder = "\\...\...\CommitCRM\ThirdParty\UserDev"
config.CommitDbFolder = "\\...\...\CommitCRM\Db\"


This is my setup and it was working just fine till yesterday. Verified that there were no changes to permissions, etc.

[Support Team]

Re: Simple search site help

All looks good. As it was working it has to be related to a change that was made, of some sort. Unfortunately this is not something we can probably help with... please do update here once you find the cause. Thanks!

[Mike C]

Re: Simple search site help

The error I am getting is "Unable to load DLL 'CmtDbQry.dll': The specified module could not be found" I am running this from a webserver on the same network as the CommitCRM application. I verify that the DLL exists in the given path. Any thoughts?

[Support Team]

Re: Simple search site help

This is all external and related to your configuration so it's hard to say. Our guess is permissions where your program, or the user it runs under, does not have the privileges to access the folder or the dll file itself over the network. Please try to first make it work locally on the server and only then try to see how it goes with running it from other machines on your network pointing to the dll on the server.

Hope this helps.

[Mike C]

Re: Simple search site help

Issue was found and I am once again connecting, however I am still having issues with the enumLink.linkOR.

Dim ticketSearch1 As New CommitCRM.ObjectQuery(Of Ticket)(CommitCRM.LinkEnum.linkOR)

ticketSearch1.AddCriteria(Ticket.Fields.Descriptio n, OperatorEnum.opLike, "%" & SearchString & "%")
ticketSearch1.AddCriteria(Ticket.Fields.Resolution , OperatorEnum.opLike, "%" & SearchString & "%")
ticketSearch1.AddCriteria(Ticket.Fields.Notes, OperatorEnum.opLike, "%" & SearchString & "%")

The quantify this, there is a ticket with monkey in the Description and Resolution but not in Notes. When I test the results I get nothing. If I comment the AddCriteria for notes I get the result I expected.

I have scoured the web for information on AddCriteria and/or LinkEnum.linkOR and can find NOTHING. Any help is appreciated or a push to find out more info on AddCriteria or linkOR.

[Support Team]

Re: Simple search site help

Thanks for posting. Apparently you'll need to apply the following fix to the VB.NET API version that you have, this should resolve this issue:

In the file QueryCommand.vb, the function at line 14:


Protected Sub New(ByVal dataKind As DataKind, ByVal linkEnum As LinkEnum, ByVal nMaxRecordCount As Integer)

objWhere_ = New CriteriaExpressionGroup(linkEnum_)
objSort_ = New SortExpressionGroup()

queryRequest_ = New QueryRequest(dataKind, nMaxRecordCount)
End Sub


should become:


Protected Sub New(ByVal dataKind As DataKind, ByVal linkEnum As LinkEnum, ByVal nMaxRecordCount As Integer)

linkEnum_ = linkEnum
objWhere_ = New CriteriaExpressionGroup(linkEnum_)
objSort_ = New SortExpressionGroup()

queryRequest_ = New QueryRequest(dataKind, nMaxRecordCount)
End Sub

[Mike C]

Re: Simple search site help

That didn't work, and actually broke the search completely. No return results at all.
Below is my code for this search.

Imports CommitCRM
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Text.RegularExpressions
Imports System.Web.UI

Partial Class _Default
Inherits Page

Private SearchString As String = ""

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Me.Load

End Sub



Public Sub cSearchbtn_Click(sender As Object, e As EventArgs) Handles cSearchbtn.Click
SearchString = Ctxt.Value.ToString()
'MsgBox(SearchString)
Try

Dim ticketSearch As New CommitCRM.ObjectQuery(Of Ticket)(CommitCRM.LinkEnum.linkOR)
Dim HistorySearch As New CommitCRM.ObjectQuery(Of HistoryNote)(CommitCRM.LinkEnum.linkOR)
Dim tickets As New List(Of CommitCRM.Ticket)
Dim history As New List(Of CommitCRM.HistoryNote)

ticketSearch.AddCriteria(Ticket.Fields.Description , OperatorEnum.opLike, "%" & SearchString & "%")
ticketSearch.AddCriteria(Ticket.Fields.Resolution, OperatorEnum.opLike, "%" & SearchString & "%")
ticketSearch.AddCriteria(Ticket.Fields.Notes, OperatorEnum.opLike, "%" & SearchString & "%")
ticketSearch.AddSortExpression(Ticket.Fields.DueDa te, SortDirectionEnum.sortDESC)
If tickets.Count > 0 Then
For Each tic In ticketSearch.FetchObjects()
tickets.Add(tic)
HistorySearch.AddCriteria(HistoryNote.Fields.RelLi nkREC_ID, CommitCRM.OperatorEnum.opEqual, tic.TicketREC_ID)
For Each hNote In HistorySearch.FetchObjects()
history.Add(hNote)
Next
histNotes.DataSource = history
histNotes.DataBind()
Next
End If

rptTicket.DataSource = tickets
rptTicket.DataBind()


Catch ex As Exception
Console.Out.Write(ex.Message)
End Try
SearchString = ""
End Sub



End Class

[Support Team]

Re: Simple search site help

We cannot debug this source code. Please ignore the change suggestion then and roll back any change you performed to the library.
Things will work as they have before and instead of querying using the OR condition run three separate searches, merge the resultset into one (e.g. merge the three list of RECIDs into one) and then fetch the relevant ticket records. This will actually implement the OR condition in a different, kinda longer, way though it should work well.

[Mike C]

Re: Simple search site help

Again Thanks for your support. I am finally coming back to this project and have since moved some of the config from the Global.aspx to the CommitCRM dll, primarily the config info. Am I missing something? When I attempt a connection it is not returning anything and then giving me a NULL reference error.

DLL (config.vb) private information removed

Public Class Config
Public AppName As String = "CommitCrm"
Public CommitDllFolder As String = "\\...\CommitCRM\ThirdParty\UserDev\"
Public CommitDbFolder As String = "\\...\CommitCRM\Db\"
Public InitCommitApiDll As Boolean = True
Public InitCommitQryDll As Boolean = True
Public Params As New List(Of KeyValuePair(Of String, String))
Public RaiseExceptionIfDatabaseFieldTruncated As Boolean = False
Public DbEngPassword As String = ""
Public DbQryPassword As String = ""
Public UseWebAPI As Boolean = False
Public WebAPIUrl As String = ""
End Class
End Namespace

search vb

Try
'Commit Database
Dim ticketSearch As New CommitCRM.ObjectQuery(Of CommitCRM.Ticket)(LinkEnum.linkOR)
Dim tickets As New List(Of CommitCRM.Ticket)
'Add Search Criteria
ticketSearch.AddCriteria(Ticket.Fields.Description , OperatorEnum.opLike, "%" & SearchString & "%")
ticketSearch.AddCriteria(Ticket.Fields.Resolution, OperatorEnum.opLike, "%" & SearchString & "%")
ticketSearch.AddCriteria(Ticket.Fields.Notes, OperatorEnum.opLike, "%" & SearchString & "%")
ticketSearch.AddSortExpression(Ticket.Fields.DueDa te, SortDirectionEnum.sortDESC)

For Each tic In ticketSearch.FetchObjects()

I get to this stage when the NULLReference error is thrown. Can you see anything that I am missing or have backwards? That will at least allow me to know the connection coding is solid and I can focus my troubleshooting elsewhere. I'm not getting the "cannot access *.dll errors so I am assuming the connection is good but something else is the issue.

Thanks

[Support Team]

Re: Simple search site help

From a quick review it seems to be in order, however, it's hard to tell, it might be related to privileges accessing the folders, something in your RAD setup and plenty of other stuff. Maybe you can start from scratch and follow the exact and detailed samples, accessed from API page.

[Mike C]

Re: Simple search site help

Thanks. At least I know the coding is good on the CommitCRM portion. I'll come back with questions and answer when I get it.


Thanks

[Mike C]

Re: Simple search site help

Back to working on this. I have been able to get all aspects working, however the return is unbearably slow. If I create a linked server and use that connection then the resultset return is almost instantaneous and I also get a more consistent dataset. However I frequently run out of users using this method (assumed as I can log off a user and the page starts working again). So two questions.
1- Can I somehow limit to one user when using ODBC connections
2- Is there a way to use a SQL query with the API. (not finding it in the WIKI). I would be willing to use the API if I can use a query and improve the speeds I get when using it.

[Support Team]

Re: Simple search site help

Thank you for the update. What do you refer by linked-server?
Also, the API itself converts your query into SQL.

If you query with the API then our main tip would be to select which fields you want to query, as when you do not it uses * (e.g. all fields from all related tables of the item your querying, like tickets).

For example, here is how you could fetch the accounts for only two fields:

lstAccounts = objQuery.FetchObjects(RangerMSP.Account.Fields.Acc ountREC_ID.Key + "," + RangerMSP.Account.Fields.FileAs.Key)

So, basically you need to pass a comma separated string with the field names that you need access to.

Hope this helps.

[Mike C]

Re: Simple search site help

a linked server is something used in SQL Server to link to a remote database and still use in queries on the local database instance. For me at least they seem to perform better than a direct ODBC connection in .net, but again I am a .net novice. But as I mentioned we seem to be running out of users when using the search. The server also says that we have 4 fewer users than what we purchased.

[Support Team]

Re: Simple search site help

Thank you. From what we can tell you use SQL and have up to 10 concurrent database connections. Each machine or remote session that connects to the database consumes a single connection and this includes ODBC connections. In case you do not free the connection some might get wasted.

In case you wish to discuss this specific issue further please email us directly and this has stuff to do with licensing.

Thanks!