Modify Datawindow.Table.Select
Chuck
XP SP 3
Access 2007
I have a datawindow without a where clause. I am adding the where
clause at runtime.
is_select = dw_main.Describe("DataWindow.Table.Select")
is_where_clause = " WHERE ( tbl_fy_contractor_census." + is_column + &
" like " + sle_1.text + " )" // do not have to quotes around col names
mod_where = "DataWindow.Table.Select='" &
+ is_select + is_where_clause + "'"
rtn = dw_main.Modify(mod_where)
This script fails at runtime with a incorrect syntax error. I have put
a where clause in the datawindow from the painter itself captured it at
runtime then tried to add the exact same where clause and it still
fails. For a work around I have had to put a where clause in the
datawindow with a parameter (:s_parm) in the painter and at run time
search the for the where clause and replace it but I still have to use
the parameter. Has anyone encountered this before? The script that
works follows:
is_select = &
dw_main.Describe("DataWindow.Table.Select")
is_where_clause = " WHERE ( tbl_fy_contractor_census." + is_column + &
" like :s_parm )" // do not have to quotes around col names
ll_pos = pos(is_original_select, "WHERE")
if ll_pos > 0 then
// add where clause
mod_where = "DataWindow.Table.Select='" &
+ left(is_select, ll_pos - 1) + is_where_clause + "'"
rtn = dw_main.Modify(mod_where)
IF rc = "" THEN
dw_main.Retrieve( )
ELSE
MessageBox("Status", "Modify Failed" + rc)
END IF
end if
John Strano[Sybase]
Is this late enough in the process to get the user's value
to concatenate here?
--
John Strano - Sybase Technology Evangelist
blog: http://powerbuilder.johnstrano.com/
"Chuck" <cch...@widomaker.com> wrote in message
news:4a71d45f$1...@forums-3-dub.sybase.com...
Scott Morris
> XP SP 3
> Access 2007
>
> I have a datawindow without a where clause. I am adding the where clause
> at runtime.
>
> is_select = dw_main.Describe("DataWindow.Table.Select")
> is_where_clause = " WHERE ( tbl_fy_contractor_census." + is_column + &
> " like " + sle_1.text + " )" // do not have to quotes around col names
> mod_where = "DataWindow.Table.Select='" &
> + is_select + is_where_clause + "'"
> rtn = dw_main.Modify(mod_where)
>
> This script fails at runtime with a incorrect syntax error.
Ok - fails where? At the modify (don't think so but keeping an open mind)?
If it fails during the retrieve, what is the actual query that is attempted
by PB (trace your connection). It would also help to know exactly what your
source query in the datawindow looks like (just execute the first describe
immediately after the -presumably successful- modify).
> I have put a where clause in the datawindow from the painter itself
> captured it at runtime then tried to add the exact same where clause and
> it still fails.
Ok - show us the actual query - all the important bits (you can skip the
column list) - and your coded simulation. BTW - as always, define "fails".
Always include the complete error message.
> For a work around I have had to put a where clause in the datawindow with
> a parameter (:s_parm) in the painter and at run time search the for the
> where clause and replace it but I still have to use the parameter. Has
> anyone encountered this before? The script that works follows:
I have doubts about your logic - specifically the lack of quote marks around
your argument. The reason it works with the argument approach is that PB is
doing the argument insertion for you and is smart enough to handle the
datatype of the argument correctly when executing the actual retrieval
(which includes the use of parameter binding if applicable). One thing to
consider - if you allow the use of columns that are not all the same
datatype, then you will need to ensure that the syntax you use for updating
the query is correct for the datatype of the column and that you also
correctly format the string constant that contains your actual argument.
I suspect you fear the embedding of quote marks in a modify string - which
is a PITA but you might as well learn how to do it. As a temporary
workaround, you can try using setsqlselect instead of the last modify. This
is generally easier since it avoids one level of string nesting (often all
you really need to avoid) but at a cost. BTW - I'm assuming you are
referring to character-based columns and that Access generally follows the
same rules as most other dbms (specifically that string constants must be
delimited with either single or double quotation marks).
Chuck
selects what they want to search by then inputs the data
into a sle. I build the where clause and add it to the
table.select. It fails at the modified statement everytime
with "Line 1 Column 1295: incorrect syntax". I have never
had this problem with oracle.
Chuck
your personal comments to yourself. Do not assume that the
person asking the question does not know how to write code.
I will refrain myself from putting my personal comments in
here. I believe I was pretty specific that the error was a
incorrect syntax error not a dbms error. To be more
specific "Line 1 Column 1295 incorrect syntax". Do not have
doubts about my logic. Please do not suspect I fear the
embedding of quote marks in a modify string. I was writing
this question to see if any other people have had this
problem with Access. I have never had this problem with
Oracle.
Thank you
Chris Pollach
May I suggest that you capture the completed SQL statement in the DW's
"SQLPreview" event. Then copy this to the DB Painter or your Access
environment to run the SQL there. Hopefully, in these alternate environments
(tools), when the SQL fails it may give you some more information as the
exact problem.
I would also like to suggest using PB's SQL trace facility as well. That
would capture the DML: statement and show you the return codes that the DW
is seeing in detail.
HTH
--
Regards ... Chris
ISUG - NA RUG Director
http://chrispollach.pbdjmagazine.com
"Chuck" <cch...@widomaker.com> wrote in message
news:4a71d45f$1...@forums-3-dub.sybase.com...
Scott Morris
>I really appreciate you answering but you really should keep
> your personal comments to yourself. Do not assume that the
> person asking the question does not know how to write code.
There were no personal comments, and I don't make assumptions one way or
another. Asking questions is how one solves a given problem. It also
indicates a lack of either information or understanding. If that is
troubling to you, then ignore it.
> I will refrain myself from putting my personal comments in
> here. I believe I was pretty specific that the error was a
> incorrect syntax error not a dbms error. To be more
> specific "Line 1 Column 1295 incorrect syntax".
Was that so hard? Now on which line did it occur? The modify, I assume.
So what is the text at or about column 1295 in the modify string?
> Do not have
> doubts about my logic.
Shouldn't the fact that it is failing mean something?
> Please do not suspect I fear the
> embedding of quote marks in a modify string.
Well you've avoided it so far - and I fear it because it is always a pain to
get the syntax exactly right. Your code comment indicates that you have
made a determination that you don't need quote marks - but it refers to
column names and not the constant against which the column is compared.
That sounds a bit odd to me but I don't use Oracle - maybe it has some odd
rules? Perhaps you can tell us why you don't need quote marks around what
appears to be a string constant. I have to say "appears to be" because I
still don't know the datatype of the column, the content of sle_1.text, the
content of the actual modify string.
> I was writing
> this question to see if any other people have had this
> problem with Access. I have never had this problem with
> Oracle.
So far, your responders have all had more questions. If the response had
been "no", what then? FWIW, I use a similar approach to this with sql
server a lot - and I have to format the constant value based on the datatype
of compared column. That's my experience - and it contradicts what you said
with respect to Oracle. Good luck, and I'm out.
KL
You could:
Eddy Soeparmin
"WHERE (tbl_fy_contractor_census." + is_Column + "LIKE '" + sle_1.text +
"%')"
--
Eddy Soeparmin
"Chuck" <cch...@widomaker.com> wrote in message
news:4a71d45f$1...@forums-3-dub.sybase.com...