Passing parameter to a sql store procedure

What is the data type of the parameter in your stored proc? Are you able to tell if they filter value is being truncated by Access before it is passed?

Hi,

first of all I recommend to stay using a view instead of a stored procedure if you do not have a really good reason to use a stored procedure. The really fastest way of getting data from the server using an ADP form is that you create a view which loads all the records from the desired table and then use the ServerFilter property of the form to pass the user's input to it. Set the form's record source to the name of this view. On this way you should never have any problem, because Access assembles the ServerFilter to a WHERE string together with a SELECT on this view and you only get the data back which is desired through the filter settings. This works indeed faster as a stored procedure.
The only thing you must really make sure is that there is not the smallest error in the filter string as the ServerFilter property will not be checked by Access before passing it to the server and in case of an error you get masses of errors back and you can only stop that by using the task manager to close Access.

To answer your question in case of stored procedures: If you have for example an nvarchar(MAX) parameter used for the filter string and try to use Paramaters.Refresh in ADO to fill the command's parameter list ADO has the problem to not correctly identify an nvarchar(MAX), it will always use a 255 (or 512) characters string which truncates of course a longer string passed from VBA. I guess that's the case because SQL Server handles nvarchar(MAX) strings as nvarchar(255) as long as the string is not longer than 255 characters and only creates a bigger size if the string is longer than that. The simple solution is to create the parameters manually or set the size of the parameter to a higher value (i.e. 64000) after using Parameters.Refresh to change the parameter type in ADO to the correct type so that nvarchar(MAX) can be used completely. In this case the parameter will no longer be truncated.

Cheers,

Christian

Hi and thanks for your responses,

@carl_tawn
the data type in procedure is varchar(5000), while in the access side i already tried a few with the same results(varchar, nvarchar(max), text..), re where is being truncated, i can only tell that the control on the form has its full value, therefore it must be that sql truncates them..

@Bitsqueezer
about your first statement, that the view is a better option re performance, i can only say that the fact is (at least in our case) showing the opposite, that i was giving the task to work in order to improve performance, and at first glance it did wonders, as a matter of fact, i timed and in certain cases it worked 500% faster!!!
 up till this filter string limitation point,

now about ADO, i would really need more in details if i have to modify the way its currently programmed, actually there is not coding at all, its just defining a specific control on the form to be used as the parameter, and after the user select the filter options, building the filter sql, assigning it to the control and then requering the form.

Hi,

it depends on what you're doing on the server to produce the output, if you have good indexes and so on. A stored procedure is better if you must assemble complicate data from x resources which you do not need to change. If it is a simple layer between table and form a view normally outperforms a stored procedure (in case of Access as frontend). But, it depends, as always.

OK, what I said about ADO was the case if you create a recordset with VBA and assign it to the form. What you do is set the sp as recordsource and use the Input Parameters property of the form to pass the parameters.

In that case you must change the way you pass the parameters. The normal simple way is:

@p1=1000,@p2='abcde'

and so on.

To use typed parameters you can change that in this way:

@p1 int=1000,@p2 nvarchar(5000)='abcde'

Access use that hints to change the datatype of the parameter in the desired way (for all other readers of this thread: Don't try that at home, that doesn't work with ACCDB/MDB, it only works in ADP).

It is indeed not SQL which has the problem, it is ADO and it's automatisms which tries to guess the right datatype or get some information from the server and match it to an own datatype. Similar problems are also the case in DAO so you must often fight with strange automatisms and use tricks and workarounds to get that working in the way you want.

Cheers,

Christian

Hi
the following lines are the peace of code currently used to assign and send the parameter, please let me know how to modify it according to your suggestion, when I tried just copy the example (@strwhere nvarchar(5000)=Mid(strFilter, 5)), the vba compiler didn't recognize the parameter name as a valid syntax..

    If strFilter & "" <> "" Then
        Me.TextstrWhere = Mid(strFilter, 5)
        'Me.ServerFilter = Mid(strFilter, 5)
    Else
       ' Me.ServerFilter = ""
       Me.TextstrWhere = "ALL"
    End If
    
    Me.Requery

Select allOpen in new window

   If strFilter & "" <> "" Then
        Me.TextstrWhere = Mid(strFilter, 5)
        'Me.ServerFilter = Mid(strFilter, 5)
    Else
       ' Me.ServerFilter = ""
       Me.TextstrWhere = "ALL"
    End If
   
    Me.Requery

Hi,

you cannot use the ServerFilter property with stored procedures, this one works only with views as form data source.

In case of stored procedures you must use the syntax I wrote above and assign it to the "InputParameters" property of the form. You can do that with VBA or directly in the form properties.

You don't need to use VBA to set the value because the InputParameters property has the advantage that it can use Access syntax. So you can directly assign the textbox to the InputParameters like this:

@strWhere nvarchar(5000)=[TextstrWhere]

(if "TextstrWhere" is the textbox containing the wanted filter)

The only code you would need is to use "Me.Requery" in the AfterUpdate event of the textbox.

If you assemble the filter string with VBA in strFilter then you would need:

Me.InputParameters = "@strWhere nvarchar(5000)='" & Replace(strFilter,"'","''") & "'"
Me.Requery

If you do not want the InputParameters method you can of course also create an EXECUTE statement like this:

Me.RecordSource = "EXEC NameOfSP @strWhere = '" & Replace(strFilter,"'","''") & "'"

This one doesn't need a requery as it automatically requeries the form. Here you can't use a typed parameter but as this is directly passed to SQL Server as a kind of "pass through query" and the text is a literal (in the eyes of the database server) I would say it should work but I didn't test that.

But that's not all. You can also just return all records without any filter and filter it locally using the normal Filter property of the form. But I cannot recommend that because the Access filters are really slow in comparison to what SQL Server can do and they have the disadvantage that they crash if the recordset is too long (in my case around 100,000 records with around 20 columns can crash Access).

Cheers,

Christian

PS: You can find further informations and examples if you look into the "InputParameters" and "ServerFilter" help of VBA.

Hi Bitsqueezer,
1, sorry I left that server filter in the attached sample, however it was commented out, just was there to demonstrate the diff from current code and previous code I had in place.
2- tried applying what you suggested (Me.InputParameters = "@strWhere nvarchar(5000)='" & Replace(strFilter,"'","''") & "'") and only when had a short filter it worked, when tried with a longer one I immediately got the attached.
Doc1.doc

ps: just to update, the same case happens when I try the below

    Me.RecordSource = "EXEC proc_name @strWhere = '" & Replace(strFilter,"'","''") & "'"

Hi,

that looks a little bit strange to me. You do not really send all this what I see in the Word document as string to SQL Server, or do you?

Normally the application should not do anything more than getting the user's input and send only that to the server and then the server executes a command which returns the wanted data. So that means: If a user wants i.e. some group of employees he enters for example a salary value to see all employees greater or equal than this value. The only informations need to be sent to SQL Server are the value, the field and the operator, that means: "Salary", "30000", ">=". Now it's the job of the stored procedure to assemble the SQL String to return the wanted data. So any text like "Coalesce" or "SELECT" or anything like that would NEVER be part of a filter string passed to the server. This is important also because it would open the server for SQL injection if you simply add that to a SELECT command using dynamic SQL.
So I would wonder if any filter string would be longer than 100 characters or so if you only pass the desired values as parameters.
Next thing is that you can also dramatically shorten the string if you use a view inside of the SP and then use some short aliases instead all this stuff. Instead of "dbo.OrientationNotes.Day" use a view and an alias of i.e. "ONDay" and your string would be a lot shorter than before (it would be of course helpful if you would write a complete filterstring, the code of the stored procedure you use and the complete code which assembles the filter string and pass it to the server to help you more with this instead of passing the informations in little bits...).

The method with "InputParameters" is the one which Access has built in but it's of course not the only one. If that do not work (I didn't test that, I use that method very rarely) you can also return a recordset from the SP and assign the recordset to the form's recordset property (as in ADP as you surely knows all forms/reports are working with ADO).

I have an ADO class module which has some procedures to handle that, here's one which works with Parameters.Refresh and uses a size parameter to make sure that ADO passes a value without cutting it before:

'---------------------------------------------------------------------------------------
' Procedure   : ADOExecuteSP
' Date        : 21.09.2011
' Last Change : 29.10.2012
' Purpose     : Executes a Stored Procedure with optional parameters and single return value
' Parameters  : Parameters: strSPName: Name of the stored procedure on the server
'               strParamNameList : Comma-separated list of parameters for the SP
'               strParamValueList: Comma-separated list of parameter values for the SP
'               strParamSizeValueList: Comma-separated list of parameter sizes for the SP
'                                      (empty values for default size). Example for
'                                      3 parameters with last one as nvarchar(MAX): ",,10000"
' Example     : ADOExecuteSP "NameOfSP" , "@P1,@P2,@P3" , "V1,V2,V3"
' Returns     : ADODB.Command (to read out the OUTPUT parameters)
'               Returns the parameter in "@RETURN_VALUE" in class variable only if exists.
'               THIS IS ALWAYS A LONG VALUE!
'---------------------------------------------------------------------------------------
'
Public Function ADOExecuteSP_OUTPUT(ByVal strSPName As String, _
                                    Optional ByVal strParamNameList As String = "", _
                                    Optional ByVal strParamValueList As String = "", _
                                    Optional ByVal strParamSizeValueList As String = "", _
                                    Optional ByVal strDelimiter As String = ",", _
                                    Optional ByVal bolOutputRecordset As Boolean = False, _
                                    Optional ByVal intLockType As ADODB.LockTypeEnum = adLockReadOnly, _
                                    Optional ByVal intOpenMode As ADODB.CursorTypeEnum = adOpenForwardOnly, _
                                    Optional ByVal bolFormRecordset As Boolean = False) As ADODB.Command
    Dim cmdADO As ADODB.Command
    Dim strParameterList() As String
    Dim strValueList() As String
    Dim strSizeValueList() As String
    Dim i As Long
    clsvar_strObjectError = "OK"

    On Error GoTo ADOExecuteSP_Error

    If Me.ADOOpenConnection = "OK" Then
        Set cmdADO = New ADODB.Command
        With cmdADO
            .CommandText = strSPName
            .CommandType = adCmdStoredProc

            If Not bolFormRecordset Then
                .ActiveConnection = clsvar_objADOConnection
            Else
                ' Updatable form recordsets needs the Access OLEDB driver to be updatable
                .ActiveConnection = CurrentProject.AccessConnection
            End If
            ' Automatically load the parameters for the stored procedure
            ' into the parameters collection
            .Parameters.Refresh

            If Not strParamNameList = "" Then
                strParameterList = Split(strParamNameList, strDelimiter)
            End If

            If Not strParamValueList = "" Then
                strValueList = Split(strParamValueList, strDelimiter)
            End If

            If Not strParamSizeValueList = "" Then
                strSizeValueList = Split(strParamSizeValueList, strDelimiter)
            Else
                If strParamNameList <> "" Then
                    ReDim strSizeValueList(UBound(strParameterList))
                End If
            End If

            If Not strParamNameList = "" And _
               Not strParamValueList = "" Then
                If .Parameters.Count > 0 Then
                    ' fill the parameters with values
                    For i = 0 To UBound(strParameterList)
                        If strParamSizeValueList <> "" And strSizeValueList(i) <> "" Then
                            .Parameters(Trim(strParameterList(i))).Size = Val(strSizeValueList(i))
                        End If
                        .Parameters(Trim(strParameterList(i))).Value = IIf(strValueList(i) = "NULL", Null, strValueList(i))
                    Next
                Else
                    clsvar_strObjectError = "No Parameters"
                End If
            End If

            .CommandTimeout = cCommandTimeout
            If bolOutputRecordset Then
                Set Me.rsADO = New ADODB.Recordset
                With Me.rsADO
                    .CursorLocation = adUseClient
                    .CursorType = intOpenMode

                    .LockType = intLockType     ' Updatable recordsets are only usable in VBA, not as form recordset
                    .Open cmdADO
                End With
            Else
                .Execute clsvar_lngADORecordCount
            End If

            On Error Resume Next
            clsvar_strLastSQL = .CommandText

            ' If an error occurs in the next line the return value will have a value of -1
            clsvar_lngReturnValueSP = -1
            clsvar_lngReturnValueSP = .Parameters("@RETURN_VALUE")
            ' @Return_Value is ALWAYS a LONG value!
            ' to read out the OUTPUT-values from the parameters collection
            ' return the command object.
            ' Calling sub must close the connection on its own!
            Set ADOExecuteSP_OUTPUT = cmdADO
        End With
    End If

ADOExecuteSP_Exit:
    ' cmdADO is returned, so it must not be closed
    Exit Function

ADOExecuteSP_Error:
    Select Case Err.Number
    Case Else
        If Not clsvar_bolNoMsgBox Then ObjErr(clsvar_objADOConnection).fnErr "Class: " & cMODULENAME, "Function: ADOExecuteSP_OUTPUT"
        Me.ADOCloseConnection
        clsvar_strObjectError = "ERROR"
    End Select
    Resume ADOExecuteSP_Exit
End Function

Select allOpen in new window

You cannot use that function 1:1, if you want to use it you must adjust it to your needs as it uses some external objects like an error object or a procedure to open the connection. But in general it should show how you can use the size parameter to execute a stored procedure and keep the size of the parameter. As you can see in the example in the comments you can use the parameters of this function on that way:

Dim cmd As ADODB.Command
Set cmd = ADOExecuteSP_OUTPUT("NameOfSP" , "@strWhere" , strFilter, "64000", , True)

Select allOpen in new window

You must of course have a "rsADO" variable on module level (in this function) where the output recordset would be passed to. The return value here is a command object to be able to read out an OUTPUT parameter of a stored procedure.

After getting the recordset back to (here) rsADO you can set that to the form:

Set Me.Recordset = NameOfClassVariable.rsADO

Select allOpen in new window

Caution: If you want to have an updatable recordset for a form you are forced to use the SQLOLEDB driver in the connection object as forms do not produce updatable results if you use the SQLNCLI driver as usual. If your form do not need to be updatable you can use the SQLNCLI driver.

The trick of the function is a simple workaround for the ADO automatism to get the right parameter type: After using "Parameters.Refresh" ADO always set the type to "adVarChar" which is wrong in case of nvarchar(MAX) for example. Setting the size to a value greater than 256 (or 512) let ADO change the type to "adVarWChar" (or "adLongVarWChar", I don't remember, you can see that in the local window when you use the debugger and stop after the Parameters.Refresh in the function if you inspect the command object and it's parameters collection).
In this case the value is not cutted and the SP executes normally.

But there is also another problem using the direct assigning of a recordset to an ADP form: Access has some automatisms to assemble the needed SQL for refreshing the form. That means: If you press F5 to refresh this happens automatically. If you do not use the methods Access want to have (like directly assigning a view or sp name as record source and use ServerFilter or InputParameters) then this will end in an error message. To work around that you need to test all these standard functions, turn them off and replace them with own methods if you need them. That means: Disable the F5 key for example by using the AUTOKEYS macro and assign that to an own procedure which uses your own method to refresh the data in the desired way.

By the way: Have a merry christmas now...;-)

Cheers,

Christian

Hi Bitsqueezer,

I knew from past experience that you will come up with some nice write up, an excellent talent, i wish i had this sort..

1- the reason i didn't want to create a view and have the store procedure filter is, I was skeptical that this may create another layer adding to the performance cost.

2- the way its currently setup that we use server filter, therefore while testing a quick convert to sql store proc, i just transformed it into a long parameter.

3-this what you mention, was my backup plan to define all filter types as separate parameters and have the procedure build the filter string, (however, since i am not so familiar with pl_sql language, i was trying to avoid hampering with the sql code as much as possible...)

4-before i move onto the change you're suggesting about ADO, that as you mentioned its not as take out of the box and use it, i would like you to view the current code (attached), and see if by any means there is a way to continue this route.

5-I came up with an idea how to overcome that size limitation, by saving the filter in a table and have the store procedure pick up the value from there...Tested it and so far worked... also included this logic into the attached code, however this will cause trouble if more then one user is trying to filter the form at the same time, please let me know if you have a solution for this,

Thanks,
And Wish you all the best
Ben
proc-code.doc

hi Bitsqueezer,

I just want to express my sincere appreciation for your willingness to help in all those topics (it just that i feel you deserve more than the designated 500 points by helping me improve the app with all those above, maybe i should split this in several threads..so you can be awarded properly).

1-

if that would not be a complete filter string...

I assume you are concerned about the sql injection, first its an in-house db, not accessible outside the network that we have to be concerned about malicious coders, secondly just wondering how can something bad be inserted here (like truncate, delete etc), if its being added after the where clause?
also how do you suggest to have it without being a complete filter string?

2-fyi, why i have this 99.9999 percent there is, because earlier when i had it as 100 percent, users were complaining its not always being sorted correctly, then someone suggested this 99 as a solution and it works...

3-the reason i am refraining from uploading the entire db/code, is that i need approval from the manager prior of doing that, however what you feel its necessary let me know and will work on it.

4- attaching the form and underlying code as requested, everything involving this filter process should be included, if you find it otherwise, let me know.
its the button with the filter image on top of form that triggers this function in discussion.

Thanks,
Ben
adp1.zip

>>users were complaining its not always being sorted correctly, then someone suggested this 99 as a solution and it works... <<
That is because doing the following is pointless (and another reason never to use the "designer" in SSMS)
SELECT TOP (100) PERCENT ... ORDER BY
SQL Server is smart enough to know the senselessness of it and will promptly ignore the ORDER BY clause:  The whole point of the TOP clause is to limit the total number of rows returned, usually based on some ORDER BY clause.  If you set it to TOP (100) PERCENT than the TOP clause is redundant.

So, yes you will see kludges like SELECT TOP (99.99) PERCENT or my favorite SELECT TOP (9999999999), but they are usually indicative of a bad design.  It also turns out in your case that SELECT TOP is not even needed and is a total waste of effort, so the sooner you lose it the better.

Hi,

here are my suggestions regarding your form, the things I saw, in no special order:

 - in case of SQL Server we're talking about T-SQL, PL/SQL is Oracle SQL...;-)
 - if you want to always see all records, set the "MaxRecords" value to 0, no need to set it to 50,000,000.
 - instead of using =[LastName] & " " & [FirstName] in the frontend, simply create a computed persisted column in the table and set the formula there to:
   [LastName] + (CASE WHEN ISNULL([FirstName],'')='' THEN '' ELSE ' ' + [FirstName] END)
   With this you always have the field computed on the backend which saves time on the frontend as formulas in Access will always be computed with a little bit delay which doesn't look good.
 - avoid white fonts on grey backgrounds, really hard to read.
 - the form is REALLY chaotic, my users would beat me if I would present them such form to search something...:-)
 - you have a lot of comboboxes which are able to select the same value again and again. That makes no sense, the user should not be able to select a value which he selected already in another combobox anymore in the following ones. But as you cannot control which one is entered first you would have to check a lot with this kind of comboboxes. So I recommend to use the MSForms ListBox in this case which allows you to add a checkbox as first column. I've made an example in the attached database.
 - I've cleaned up the form to show you how I would setup a form with a clean user interface. Maybe you must sort that a little bit, moreover you must set the tab order to your needs, really important (I also forget that point often as I mainly work with the mouse).
 - the naming of controls: You should ALWAYS give ALL objects a meaningful name, not "Command123" or "Label14" and so on. That makes the code very much easier to read and it is much easier to find the right control if you apply tab control order.
 - avoid any object names which can conflict with keywords like "Day", "Date", "Name" and so on.
 - don't use "ORDER BY" on the server. Access always uses lazy loading to make the appearance of the first records faster and loads the rest in the background. If you use ORDER BY on the server than Access is forced to wait until all records where loaded and ordered by the server which makes the appearance of the first records a lot slower. If you need an ordered output let Access order the records on it's own using the "OrderBy" properties of the form.
 - the "TOP 99.999 PERCENT" is nonsense since SQL Server 2005, the "TOP 100 percent" was the trick to force SQL Server 2000 to order the records in a view, SQL Server 2005 does not support that anymore. In SQL Server 2005 you can do the same using "TOP 1000000" for example (without percent). But: This is NOT a guarantee for an ordered output as this is only the same workaround as with the percent trick in SQL Server 2000. It is always possible that this one does NOT return an ordered output although you specify an ORDER BY clause!
To quote Itzik Ben-Gan from "Inside SQL Server:Programming T-SQL": "When the ORDER BY clause is used with the TOP option in a table expression (for example, in a view's query), it is guaranteed to serve only one purpose - determining for the TOP option which rows to pick."
That means: The records are sorted in the internal SELECT process to find out which of the records should be returned following the TOP clause - but the end result don't need to be sorted anymore! So to be sure that they are in a specific order you need to apply an ordering at the end of the chain and that's normally the frontend.
 - Views and additional layer: A view does not make anything slower, the opposite is true. A view is similar to a query in Access: It is mainly the saved SQL string, the command, which returns the desired columns and rows of a table. In SQL Server (and also in smaller dimension in Access ACCDB/MDB) a view's SELECT command will also be precompiled, an execution plan is generated and saved with the query. Whenever you call the view SQL Server don't need to check the command and compile it and create an execution plan and so on - it's all already there and so the result can immediately begin to be loaded into the result recordset. Moreover a view should only contain the really needed columns from one (or more) table(s) so that you can reduce the amount of data a lot. The result will also be cached by SQL Server so if more than one user uses the same view again and again that also saves a lot of time. It's of course similar with stored procedures. Nevertheless it IS an additional layer in both cases but it's a really desired layer because views/SPs makes the data access a lot faster (and additionally more secure as you can apply separated security to these objects).
 - in the meantime I tested your form with my SQL Server 2005 by creating a table which contains an nvarchar(4000) field and then created a stored procedure like this:
 

ALTER PROCEDURE proc_FilterOrientNotes_Test @strWhere AS nvarchar(4000)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	INSERT INTO tblTestBigString (TextValue) VALUES (@strWhere);

	SELECT 'X' AS LastName, 'Y' AS FirstName, 'Mr' AS Title
END

Select allOpen in new window

That inserts the parameter of strWhere into the test table to see if it is forwarded to SQL Server without cutting it. I used the proc name and parameter name of your form which has this as record source and uses the InputParamaters property to forward the control TextstrWhere to the procedure. I then changed your code like this:

...
    strFilter = strFilter & AdditionalFilter
    
    Me.TextstrWhere = strFilter
    Me.Requery
    ...

Select allOpen in new window

to see if the filter string (691 characters in my test) would be forwarded correctly and what a surprise: No problem. The table contained the complete string without any problem so this makes sure that there is no problem in the frontend's forwarding of the value in case of InputParameters.

(I inserted the SELECT command into this procedure so that the proc returns something for the form to display at least one result row.)

 - SQL Injection: A simple method is to add a ";" to an input which is not checked by your application. In your case the form contains for example the three "Units Experiemces" comboboxes where you missed to set the option "allow only list entries" - that means, I can enter anything I want into these fields and it is simply added to the filter string. So if I add ";DROP TABLE Orientations;" to one of these fields I can delete this table if the user has the right to delete that. Otherwise I could use "DELETE FROM Orientations" or any other stuff I am able to do with your table. You see it is really important to secure such things or otherwise you open a really big security hole. And the often heard argument "We do not expose that to the outer world so there is no risk" is a really bad argument. Humans may react not really loyal to an employer if they hear that they will get fired next month and they maybe come to bad thoughts to how they can do a little bit they have never thought of before....to only mention a simple sample. So security is a theme in ALL databases, external or intenal. ("Noooo, OUR employees would never do that...")
 - Securing a form in a frontend is not enough. If the form can execute this stored procedure with the current user's login then the same user can use the same login to execute the procedure directly for example using Excel and so any nice frontend tests are not available and "@strWhere" is open for any kind of bad things. You are forced to secure such parameters against any kind of misuse on the server, and that's really not an easy job.
 - At least you should always use the system stored procedure "sp_executesql" instead of directly executing a string using "EXEC". Ask your favorite search engine for this procedure for more information. I strongly recommend that you buy and read the two "Inside SQL Server" books from Itzik Ben-Gan for the SQL Server you are using and study them completely, you'll learn a LOT about all this stuff.
 - the stored procedure, using aliases for all tables, is now a lot shorter:
 

CREATE PROCEDURE [dbo].[proc_FilterOrientNotes_Test2] (@strWhere nvarchar(4000))
AS 
BEGIN
declare @tmp nvarchar(4000)
declare @strWhere2 nvarchar(4000)

	SET @tmp='SELECT     (CASE WHEN Last_Orient_Note_Day IS NULL OR Last_Orient_Note_Day < GETDATE() - 60
								THEN ''T'' ELSE '''' END) AS Red,
						  ORN.ID, O.EmployeeID, 
						  O.FacilityID, O.Active, ORN.Note, COALESCE (ORN.Day, O.DateEntered) 
						  AS Day, ORN.Initial, O.ID AS OrientationID, O.DateEntered, O.Initial AS OrientationInitial, 
						  O.Traveler, EDT.TovInfoLastDay AS LastDay, O.DueDate, ORN.Mailing, 
						  O.DueDateInitial, EDT.Last_Orient_Note_Day, EMP.AvailibilityPDays, 
						  EMP.AvailibilityPShifts, EMP.City, EMP.LastName, EMP.FirstName, EMP.Title, 
						  FAC.Name AS FacilityName, vwOLN.LastNoteID, EDT.TovInfoFacility1 AS LastFacility, 
						  EMP.Degree
					FROM  dbo.Orientations AS O INNER JOIN
						  dbo.Employeestbl AS EMP ON O.EmployeeID = EMP.ID INNER JOIN
						  dbo.Facilitiestbl AS FAC ON O.FacilityID = FAC.ID LEFT OUTER JOIN
						  dbo.view_OrientationLastNote AS vwOLN ON O.ID = vwOLN.OrientationID LEFT OUTER JOIN
						  dbo.EmployeesDocsTov AS EDT ON O.EmployeeID = EDT.EmployeeID LEFT OUTER JOIN
						  dbo.OrientationNotes AS ORN ON O.ID = ORN.OrientationID';

	IF @strWhere IS NOT NULL AND @strwhere <> 'ALL' and @strwhere <> 'Long'
		set @tmp = @tmp + ' WHERE ' + @strWhere ;

	IF @strWhere IS NULL
		set @tmp = @tmp + ' WHERE O.EmployeeID = 3';

	IF @strWhere = 'Long'
	begin
		set @strWhere2 = (select [value] from ProgrammingSettings where [Code] = 'FilterOrientationNotesSql');
		set @tmp = @tmp + ' WHERE ' + @strWhere2;
	end

	--set @tmp = @tmp + ' ORDER BY O.DateEntered DESC, ORN.ID desc';
	EXECUTE sp_executesql @tmp;
END

Select allOpen in new window

 - the frontend now uses the aliases so the filter string is also shorter.

These are of course only suggestions and not the only way and this also continues to be a security hole with SQL injection, so you should better use a way of passing the values and assemble the string on the server where you also should test each parameter exactly (all the time whenever you use dynamic SQL in any procedure!).

Cheers,

Christian
Adp3.zip

Hi all,
due to technical issues i'm unable to connect to the app till next week, will keep you posted.

@Anthony Perkins
Will do some testing on this next week.

@Bitsqueezer,
I read you comments/suggestions, really really awesome, sorry that will have to wait for next week to be able to fully test all & respond.

BTW, is this MSForms ListBox control compatible with access version 2k? I dont see that on the references menu option.
since some of my office users still have this version, I am forced to write all code in that format.

Have a nice weekend!
Ben

Hi Ben,

I'm not sure as I don't use A2000 anymore. But to add that to the references simply add another MS Forms Listbox to the form (you can delete it then), that will add the reference to VBA also. Otherwise you can also select the fm20.dll manually in the VBA editor.
As long as A2000 is able to add ActiveX controls it should also be compatible to MS Forms or other ActiveX controls.

Nice weekend also to you.

Cheers,

Christian

Hi Bitsqueezer,

Here are my comments..
1-you really did a great job on redesigning the search fields, will present to the users in the end of the week (when they’re back from vacation) and see their feedback.

2-first of all, I am stuck in middle testing your version of the form, as it only filters the first time, afterwards no matter what I choose, the value of the text box containing the filter does not get transferred to the server (verified that by starting a trace and always had the same value, in my case “

exec sp_executesql N'EXEC "dbo"."proc_FilterOrientNotes_Test2" @P1 ',N'@P1 nvarchar(500)',N' O.EmployeeID in (SELECT EmployeesID FROM 
EmployeesUnitstbl  WHERE unit IN (''RN'')) AND  ORN.Day >= ''01/01/2006'' AND O.DateEntered >= ''01/01/2006'''

Select allOpen in new window

“
Attaching the adp used for test.

3- although the MSForms ListBox control has I nicer look then the regular list box or combo box, it has those disadvantages below
a-      The user has to manually scroll to the value it want to select, in case there are dozens of value options to select from a specific letter, as oppose to the combo box.
b-      It requires more coding to get the value
c-      It needs to loop for all options to find out if something was selected.

4-the issue with order by performed by access instead of the server, and top 99 still requires more testing from my part, the same is with the 500 characters limitation.

5-regarding the sql injection, for the time being, if the only way it could be carried out is by having the “;” , how about checking for that character in the procedure? (Something like if instr(1,"abc;",";") > 0 then strwhere = ‘’, in T-SQL syntax of course..) Maybe we could also add all reserved words to that exclusion list like drop, delete, update, truncate etc..

6- just wondering if the same concept of using store procedure as oppose to access filters can be used in a MDB, on a form that’s used for reporting only?

7-In regards to the extra layer, I was under the impression that when you select something from a query/view, the database engine (no matter Jet or SQL) will have first to perform the first query,  and then from there requery the second data request..Therefore  I always tried to include everything what’s possible in the one query.

Again, thanks allot for your time
Ben
adp1.zip

Hi Bitsqueezer,
I added the code below in order to test the long parameter with your version and it got cut of, attached you will find what i saw on sql profiler trace.

    strFilter = AdditionalFilter(strFilter)
    strFilter = " and " & strFilter & " and " & strFilter & " " & strFilter
    Me.TextstrWhere = strFilter
    Me.Requery

Select allOpen in new window

12-30-13.txt

Hi Ben,

I have done the same with my server and duplicated the filter string x times to get a really long string and this is the result in my SQL Server profiler:

exec sp_executesql N' EXEC "dbo"."proc_FilterOrientNotes_Test" @P1  ',N'@P1 nvarchar(2000)',N' O.EmployeeID in (SELECT EmployeesID FROM EmployeesUnitstbl  WHERE unit IN (''U2'',''U5'')) OR  O.EmployeeID in (SELECT EmployeesID FROM EmployeesUnitstbl  WHERE unit IN (''U1'',''U3'',''U4'')) and O.FacilityID = 1 And O.Active = 1 And EDT.TovInfoLastDay >= ''10.01.2013'' And EDT.TovInfoLastDay <= ''10.02.14'' And O.DueDate >= ''10.03.2013'' And O.DueDate <= ''10.05.13'' And OrientationID is null And Degree = ''BSN'' O.EmployeeID in (SELECT EmployeesID FROM EmployeesUnitstbl  WHERE unit IN (''U2'',''U5'')) OR  O.EmployeeID in (SELECT EmployeesID FROM EmployeesUnitstbl  WHERE unit IN (''U1'',''U3'',''U4'')) and O.FacilityID = 1 And O.Active = 1 And EDT.TovInfoLastDay >= ''10.01.2013'' And EDT.TovInfoLastDay <= ''10.02.14'' And O.DueDate >= ''10.03.2013'' And O.DueDate <= ''10.05.13'' And OrientationID is null And Degree = ''BSN'' O.EmployeeID in (SELECT EmployeesID FROM EmployeesUnitstbl  WHERE unit IN (''U2'',''U5'')) OR  O.EmployeeID in (SELECT EmployeesID FROM EmployeesUnitstbl  WHERE unit IN (''U1'',''U3'',''U4'')) and O.FacilityID = 1 And O.Active = 1 And EDT.TovInfoLastDay >= ''10.01.2013'' And EDT.TovInfoLastDay <= ''10.02.14'' And O.DueDate >= ''10.03.2013'' And O.DueDate <= ''10.05.13'' And OrientationID is null And Degree = ''BSN'' O.EmployeeID in (SELECT EmployeesID FROM EmployeesUnitstbl  WHERE unit IN (''U2'',''U5'')) OR  O.EmployeeID in (SELECT EmployeesID FROM EmployeesUnitstbl  WHERE unit IN (''U1'',''U3'',''U4'')) and O.FacilityID = 1 And O.Active = 1 And EDT.TovInfoLastDay >= ''10.01.2013'' And EDT.TovInfoLastDay <= ''10.02.14'' And O.DueDate >= ''10.03.2013'' And O.DueDate <= ''10.05.13'' And OrientationID is null And Degree = ''BSN'' O.EmployeeID in (SELECT EmployeesID FROM EmployeesUnitstbl  WHERE unit IN (''U2'',''U5'')) OR  O.EmployeeID in (SELECT EmployeesID FROM EmployeesUnitstbl  WHERE unit IN (''U1'',''U3'',''U4'')) and O.FacilityID = 1 And O.Active = 1 And EDT.TovInfoLastDay >= ''10.01.2013'' And EDT.TovInfoLastDay <= ''10.02.14'' And O.DueDate >= ''10.03.2013'' And O.DueDate <= ''10.05.13'' And OrientationID is null And Degree = ''BSN'''

Select allOpen in new window

As you can see, Access counts the number of characters of the SQL string and changes the parameter of @P1:

...N'@P1 nvarchar(2000)'...

Select allOpen in new window

to match the length of the string. It does the same with your Access as in your case the profiler shows:

...N'@P1 nvarchar(851)'...

Select allOpen in new window

But what you can also see is that it seems to be interrupted with some characters which seems not to belong to the string:

01/¿¿¿

Select allOpen in new window

(that's the end of your profiler string)

So I would say the problem here is not the way Access or ADO sends the data, it looks a little bit like if the data gets interrupted on the transport layer, maybe a problem with the network card or driver, packet size, WLAN or anything like that, I cannot test that.
The parameter shows that also your Access wants to send 851 characters so I guess that's no problem of the database layer but a problem of the transport of data. If the data would be cutted by ADO or Access it would simply be cutted, with a closing single quote, but here it is an incomplete command with some trash data at the end and so I guess that's a problem on a lower layer.

MSForms ListBox: You can also jump to a specific entry using the leading character. See the setting "MatchEntry" of the ListBox. You can select/deselect a value using the space key. Moreover it has the advantage that you do not have 5 fields where each of it could be the same one, you can only select one value exclusively. In the end you have a lot less code than using the listbox where you simply can pick all selected values in a loop and concatenate it (which is already in the code I sent last time).

SQL Injection: Indeed that's one possible way to go with SQL Injection. But simply checking for ";" is not enough, you must make sure that this character is not part of the desired search string so you can't simply check if it is somewhere in the string.
If you search for "SQL Injection" in your favourite search engine you'll find a lot about that and also methods what to do to avoid that as best as possible (you'll see that also experts have a lot of problems to find an algorithm to make it as safe as possible because it is REALLY difficult!)

MDB: Yes, you can of course use an SP in an MDB. One method is to use a pass-through query (always read-only), another is to use ADO, build a recordset and assign it to a form or report. Interestingly a form can use DAO and ADO recordsets, so you can use either in MDB and ADP, it is not limited by the file type.

View: In case of ADP you are directly working with SQL Server so you are executing the SELECT in the view, nothing else. And all a view does is executing this (precompiled) SELECT and return the data directly from the table(s) it uses. In case of MDB you have of course always another layer in between which is the JET/ACE engine of Access which is the one which executes the (own) SQL, that means: Access SQL only. As an MDB usually works with linked tables in case of a database backend it recognizes that this is not a table from the own database engine and executes commands on the SQL Server (usually some helper stored procedures which are part of any SQL Server installations) to get the data back so the only thing you have here is the really basic Access SQL in comparison to the rich T-SQL (one of the reasons why I prefer to use ADP where you have the full T-SQL power directly on the frontend).

Unfortunately I cannot do any further tests with your form/SP as I do not have all the needed tables and demo data. But as I said above, I guess that your problem is not in the code or way of sending it to the server but somewhere in the transport layer which you can only test on your machine(s).

Cheers,

Christian

Hi Bitsqueezer,

I really enjoyed reading all your suggestions, some of them will apply here or in other parts of the application.

I guess regarding the limitation issue, I will use the save filter to table approach and accept your suggestion (id:39738790) about the multi user issue.

In order to finalize this post, i just wonder if you tested your screen with more then one type of filter without closing and reopening the form? (as you see in the beginning of my previous post id:39747193, this is not working by me, only works the first time).

with appreciation,
Ben

Hi,

yes, I've the short test sp I wrote above which writes the string to a test table, was no problem.

Happy new year..:-)

Christian

Thanks allot Bitsqueezer
Was really a pleasure dealing with you.
Looking forward to work with you on future issues that may occur..
Wish you happy new year and all the best!

Thanks, you're welcome...:-)