Data shape command text contains a syntax error at or near position 106 in the command.

[Bob]

2007 .adp front ends connected to SQL 2005.
I frequently run into this when users are drilling into data on a DataSheet
form.
They may filter on a value in one column and when they try to apply the next
filter, they
get the above error. I use stored procs for all my forms. Procs using
input params in the
Form.InputParameters property seem to be the ones with the problems. I've
tried everything
I can think of. I'd really appreciate anyone that can shed any light on
this ...
thanks in advance,
bob.

 

[Bob]

I was able to come up with a fix for this. I am sharing this in case anyone
else
bumps into this problem.

Make the form a subForm on another form.
Add a control that will contain the name of the CurrentUser.
Make the Master property of subForm = to tbCurrentUser
Make the Child property of the subForm = UserName in the Table (Used as
Recordset for the subForm)

From the Menu Form.
the button that opens the above Form & subForm
--will 1st Delete all recs in the target table where UserName =
tbCurrentUser.
--Insert Into table using the user's params and tbCurrentUser.

Now you have a resultset for the subForm that is a table as opposed to a
query.
The table is filtered via the Master/Child relationship.
--The user can now Sort and apply as many filters as desired to the
resultset.

hope this helps someone...
Bob.

 

[mark.sjolin]

Passing a parameter to a stored procedure without using the full
syntax for the stored procedure parameter can cause this problem.

For example, me.InputParameters="1" might work for a while but may
suddenly begin causing the error you described.

Instead, use me.InputParameters="@parm=1" where @parm is the name of
the stored procedure parameter being passed to the form.

 

[Bob]

Thanks Mark..
I will give this a shot. It was driving me crazy. The workaround I
described works
perfectly but does require additional steps.
Thanks again for the reply.
bob.