Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Modify Datawindow.Table.Select

9 posts in DataWindow Last posting was on 2009-08-11 19:22:49.0Z
Chuck Posted on 2009-07-30 17:11:59.0Z
From: Chuck <cchoate@widomaker.com>
User-Agent: Thunderbird 2.0.0.21 (Windows/20090302)
MIME-Version: 1.0
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Modify Datawindow.Table.Select
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a71d45f$1@forums-3-dub.sybase.com>
Date: 30 Jul 2009 10:11:59 -0700
X-Trace: forums-3-dub.sybase.com 1248973919 10.22.241.152 (30 Jul 2009 10:11:59 -0700)
X-Original-Trace: 30 Jul 2009 10:11:59 -0700, vip152.sybase.com
Lines: 42
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89709
Article PK: 418896

PB10 Build 4510
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] Posted on 2009-07-30 17:36:01.0Z
From: "John Strano[Sybase]" <nichtspamjstrano@csi.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <4a71d45f$1@forums-3-dub.sybase.com>
Subject: Re: Modify Datawindow.Table.Select
Lines: 56
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a71da01$5@forums-3-dub.sybase.com>
Date: 30 Jul 2009 10:36:01 -0700
X-Trace: forums-3-dub.sybase.com 1248975361 10.22.241.152 (30 Jul 2009 10:36:01 -0700)
X-Original-Trace: 30 Jul 2009 10:36:01 -0700, vip152.sybase.com
X-Authenticated-User: TeamSybase
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89710
Article PK: 418897

>> " like :s_parm )"

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" <cchoate@widomaker.com> wrote in message
news:4a71d45f$1@forums-3-dub.sybase.com...
> PB10 Build 4510
> 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


Chuck Posted on 2009-07-31 07:08:47.0Z
Sender: 4537.4a72941a.1804289383@sybase.com
From: Chuck
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: Modify Datawindow.Table.Select
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4a72987f.487d.1681692777@sybase.com>
References: <4a71da01$5@forums-3-dub.sybase.com>
NNTP-Posting-Host: forums-3-dub.sybase.com
X-Original-NNTP-Posting-Host: forums-3-dub.sybase.com
Date: 31 Jul 2009 00:08:47 -0700
X-Trace: forums-3-dub.sybase.com 1249024127 10.22.241.188 (31 Jul 2009 00:08:47 -0700)
X-Original-Trace: 31 Jul 2009 00:08:47 -0700, forums-3-dub.sybase.com
Lines: 64
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89715
Article PK: 418903

Not sure what you are asking. On the window the user
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.

> >> " like :s_parm )"
>
> 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" <cchoate@widomaker.com> wrote in message
> news:4a71d45f$1@forums-3-dub.sybase.com...
> > PB10 Build 4510
> > 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
>
>


Scott Morris Posted on 2009-07-30 18:43:08.0Z
From: "Scott Morris" <bogus@bogus.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <4a71d45f$1@forums-3-dub.sybase.com>
Subject: Re: Modify Datawindow.Table.Select
Lines: 57
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a71e9bc$4@forums-3-dub.sybase.com>
Date: 30 Jul 2009 11:43:08 -0700
X-Trace: forums-3-dub.sybase.com 1248979388 10.22.241.152 (30 Jul 2009 11:43:08 -0700)
X-Original-Trace: 30 Jul 2009 11:43:08 -0700, vip152.sybase.com
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89711
Article PK: 418899


"Chuck" <cchoate@widomaker.com> wrote in message
news:4a71d45f$1@forums-3-dub.sybase.com...
> PB10 Build 4510
> 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 Posted on 2009-07-31 07:33:43.0Z
Sender: 4537.4a72941a.1804289383@sybase.com
From: Chuck
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: Modify Datawindow.Table.Select
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4a729e57.4cc7.1681692777@sybase.com>
References: <4a71e9bc$4@forums-3-dub.sybase.com>
NNTP-Posting-Host: forums-3-dub.sybase.com
X-Original-NNTP-Posting-Host: forums-3-dub.sybase.com
Date: 31 Jul 2009 00:33:43 -0700
X-Trace: forums-3-dub.sybase.com 1249025623 10.22.241.188 (31 Jul 2009 00:33:43 -0700)
X-Original-Trace: 31 Jul 2009 00:33:43 -0700, forums-3-dub.sybase.com
Lines: 83
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89716
Article PK: 418904

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.
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

"Chuck" <cchoate@widomaker.com> wrote in message
> news:4a71d45f$1@forums-3-dub.sybase.com...
> > PB10 Build 4510
> > 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).
>
>


Scott Morris Posted on 2009-07-31 13:53:31.0Z
From: "Scott Morris" <bogus@bogus.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <4a71e9bc$4@forums-3-dub.sybase.com> <4a729e57.4cc7.1681692777@sybase.com>
Subject: Re: Modify Datawindow.Table.Select
Lines: 48
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a72f75b$2@forums-3-dub.sybase.com>
Date: 31 Jul 2009 06:53:31 -0700
X-Trace: forums-3-dub.sybase.com 1249048411 10.22.241.152 (31 Jul 2009 06:53:31 -0700)
X-Original-Trace: 31 Jul 2009 06:53:31 -0700, vip152.sybase.com
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89720
Article PK: 418907


<Chuck> wrote in message news:4a729e57.4cc7.1681692777@sybase.com...
>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.


Chris Pollach Posted on 2009-07-31 11:13:01.0Z
From: "Chris Pollach" <cpollach@travel-net.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <4a71d45f$1@forums-3-dub.sybase.com>
Subject: Re: Modify Datawindow.Table.Select
Lines: 65
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a72d1bd$2@forums-3-dub.sybase.com>
Date: 31 Jul 2009 04:13:01 -0700
X-Trace: forums-3-dub.sybase.com 1249038781 10.22.241.152 (31 Jul 2009 04:13:01 -0700)
X-Original-Trace: 31 Jul 2009 04:13:01 -0700, vip152.sybase.com
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89718
Article PK: 418906

Hi Chuck;

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" <cchoate@widomaker.com> wrote in message
news:4a71d45f$1@forums-3-dub.sybase.com...
> PB10 Build 4510
> 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


KL Posted on 2009-08-04 21:35:10.0Z
Sender: 15b0.4a71b8a5.1804289383@sybase.com
From: KL
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: Modify Datawindow.Table.Select
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4a78a98e.75b5.1681692777@sybase.com>
References: <4a72d1bd$2@forums-3-dub.sybase.com>
NNTP-Posting-Host: forums-3-dub.sybase.com
X-Original-NNTP-Posting-Host: forums-3-dub.sybase.com
Date: 4 Aug 2009 14:35:10 -0700
X-Trace: forums-3-dub.sybase.com 1249421710 10.22.241.188 (4 Aug 2009 14:35:10 -0700)
X-Original-Trace: 4 Aug 2009 14:35:10 -0700, forums-3-dub.sybase.com
Lines: 72
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89736
Article PK: 418924

Hope Chuck got this to work.
You could:

> Hi Chuck;
>
> 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" <cchoate@widomaker.com> wrote in message
> news:4a71d45f$1@forums-3-dub.sybase.com...
> > PB10 Build 4510
> > 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
>
>


Eddy Soeparmin Posted on 2009-08-11 19:22:49.0Z
From: "Eddy Soeparmin" <SoeparminE@MDAInc.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <4a71d45f$1@forums-3-dub.sybase.com>
Subject: Re: Modify Datawindow.Table.Select
Lines: 53
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a81c509@forums-3-dub.sybase.com>
Date: 11 Aug 2009 12:22:49 -0700
X-Trace: forums-3-dub.sybase.com 1250018569 10.22.241.152 (11 Aug 2009 12:22:49 -0700)
X-Original-Trace: 11 Aug 2009 12:22:49 -0700, vip152.sybase.com
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89758
Article PK: 418945

I think you need to include single quote surrounded the your sle_1.text:

"WHERE (tbl_fy_contractor_census." + is_Column + "LIKE '" + sle_1.text +
"%')"

--
Eddy Soeparmin

"Chuck" <cchoate@widomaker.com> wrote in message
news:4a71d45f$1@forums-3-dub.sybase.com...
> PB10 Build 4510
> 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