CodeCharge Studio
search Register Login  

Web Reports

Visually create Web Reports in PHP, ASP, .NET, Java, Perl and ColdFusion.
CodeCharge.com

YesSoftware Forums -> Archive -> CodeChargeStudio.Discussion

 Session variables

Print topic Send  topic

Author Message
Richard Taylor
Posted: 11/29/2003, 8:32 AM

I've tried 8 ways from Sunday to add a WHERE clause to an SQL SELECT
statement for a grid page using a session variable.

I added a session variable in the CCLoginUser function by editting the SQL
select statement like this:

SQL = "SELECT [UserID], [Rights], ClientID FROM [User] WHERE
[LoginID]='" & Replace(Login, "'", "''") & "' AND [Password]='" &
Replace(Password, "'", "''") & "'"

and adding this to the If Result:

Session("ClientID") = RecordSet("ClientID") 'RT added

Then in the Customer_DataSource_BeforeBuildSelect() event function I'm
adding this code (here are a couple of the ways I've tried to make this
work):

Customer.DataSource.Where = " ClientID = " & (Session("ClientID")
' Customer.DataSource.Where = " ClientID = " & Cstr(Session("ClientID"))
' Customer.DataSource.Where = " ClientID = " &
CCToSQL(Session("ClientID"),ccsText)

All of these result in this error:

Form: Grid Customer
Error: Incorrect syntax near the keyword 'ORDER'. (Microsoft OLE DB Provider
for SQL Server)
SELECT CustomerID, ClientID, CompanyName, Address1, Address2, City, State,
Zip FROM Customer WHERE ClientID = ORDER BY CompanyName

As you can see, the value of the Session(ClientID) is either blank, empty,
null, or ....

Any ideas as to what I'm doing wrong?

--
Richard Taylor


DonB
Posted: 11/29/2003, 10:13 AM

Rather than editing the login function, I'd have written the query like
this:

SELECT c.CustomerID, c.ClientID, c.CompanyName, c.Address1, c.Address2,
c.City, c.State,
c.Zip FROM Customer AS c, User AS u WHERE c.ClientID = u.ClientID and
u.UserID = " & CCGetUserID() & " ORDER BY c.CompanyName

(If I inderstand the problem correctly - that you want to show only those
clients related to the logged-in user).

--
DonB

http://www.gotodon.com/ccbth


"Richard Taylor" <docmaster@csi.com> wrote in message
news:bqahnq$pbg$1@news.codecharge.com...
> I've tried 8 ways from Sunday to add a WHERE clause to an SQL SELECT
> statement for a grid page using a session variable.
>
> I added a session variable in the CCLoginUser function by editting the SQL
> select statement like this:
>
> SQL = "SELECT [UserID], [Rights], ClientID FROM [User] WHERE
> [LoginID]='" & Replace(Login, "'", "''") & "' AND [Password]='" &
> Replace(Password, "'", "''") & "'"
>
> and adding this to the If Result:
>
> Session("ClientID") = RecordSet("ClientID") 'RT added
>
> Then in the Customer_DataSource_BeforeBuildSelect() event function I'm
> adding this code (here are a couple of the ways I've tried to make this
> work):
>
> Customer.DataSource.Where = " ClientID = " & (Session("ClientID")
> ' Customer.DataSource.Where = " ClientID = " & Cstr(Session("ClientID"))
> ' Customer.DataSource.Where = " ClientID = " &
> CCToSQL(Session("ClientID"),ccsText)
>
> All of these result in this error:
>
> Form: Grid Customer
> Error: Incorrect syntax near the keyword 'ORDER'. (Microsoft OLE DB
Provider
> for SQL Server)
> SELECT CustomerID, ClientID, CompanyName, Address1, Address2, City, State,
> Zip FROM Customer WHERE ClientID = ORDER BY CompanyName
>
> As you can see, the value of the Session(ClientID) is either blank, empty,
> null, or ....
>
> Any ideas as to what I'm doing wrong?
>
> --
> Richard Taylor
>
>
>

Richard Taylor
Posted: 11/29/2003, 5:24 PM

Don,

Thanks for the input -- you do understand the problem correctly.

I would code it this way too, but I'm trying to work within the parameters
of the code generator and haven't found a place that will let me "own" the
SQL (if my ignorance is showing, please educate me -- I won't be offended).
That's why I was just trying to define the WHERE clause in the
BeforeBuildSelect method.

My real question is, why am I getting nothing from the Session variable? And
how can I make this BeforeBuildSelect method code work?

--
Richard Taylor

"DonB" <~ccbth~@gotodon.com> wrote in message
news:bqankh$vim$1@news.codecharge.com...
> Rather than editing the login function, I'd have written the query like
> this:
>
> SELECT c.CustomerID, c.ClientID, c.CompanyName, c.Address1, c.Address2,
> c.City, c.State,
> c.Zip FROM Customer AS c, User AS u WHERE c.ClientID = u.ClientID and
> u.UserID = " & CCGetUserID() & " ORDER BY c.CompanyName
>
> (If I inderstand the problem correctly - that you want to show only those
> clients related to the logged-in user).
>
> --
> DonB
>
> http://www.gotodon.com/ccbth
>
>
> "Richard Taylor" <docmaster@csi.com> wrote in message
>news:bqahnq$pbg$1@news.codecharge.com...
> > I've tried 8 ways from Sunday to add a WHERE clause to an SQL SELECT
> > statement for a grid page using a session variable.
> >
> > I added a session variable in the CCLoginUser function by editting the
SQL
> > select statement like this:
> >
> > SQL = "SELECT [UserID], [Rights], ClientID FROM [User] WHERE
> > [LoginID]='" & Replace(Login, "'", "''") & "' AND [Password]='" &
> > Replace(Password, "'", "''") & "'"
> >
> > and adding this to the If Result:
> >
> > Session("ClientID") = RecordSet("ClientID") 'RT added
> >
> > Then in the Customer_DataSource_BeforeBuildSelect() event function I'm
> > adding this code (here are a couple of the ways I've tried to make this
> > work):
> >
> > Customer.DataSource.Where = " ClientID = " & (Session("ClientID")
> > ' Customer.DataSource.Where = " ClientID = " &
Cstr(Session("ClientID"))
> > ' Customer.DataSource.Where = " ClientID = " &
> > CCToSQL(Session("ClientID"),ccsText)
> >
> > All of these result in this error:
> >
> > Form: Grid Customer
> > Error: Incorrect syntax near the keyword 'ORDER'. (Microsoft OLE DB
> Provider
> > for SQL Server)
> > SELECT CustomerID, ClientID, CompanyName, Address1, Address2, City,
State,
> > Zip FROM Customer WHERE ClientID = ORDER BY CompanyName
> >
> > As you can see, the value of the Session(ClientID) is either blank,
empty,
> > null, or ....
> >
> > Any ideas as to what I'm doing wrong?
> >
> > --
> > Richard Taylor
> >
> >
> >
>
>

DonB
Posted: 11/29/2003, 8:08 PM

You have total control (ownership) over the SQL. Just change the Data
Source property from "Table" to "SQL".

I prefer creating a query in the DBMS rather than in the CCS Data Source
property - easier to edit, test and maintain it. But you can put the SQL
directly in the Data Source property if you like. The Query Builder in CCS
works well (click the "..." to get to it), but I find it a bit clumsy.

I can't say why the session var isn't working, you used it correctly.
Evidently the variable is getting created with a blank or null value. I'd
try putting, temporarily, two lines to debug it:

Print "ClientID = " & Session("ClientID")
Response.End

And see what's there when you read it from the database. I guess it's
conceivable someplace else you have code that modifies it, but most likely
you are not reading a value to start with.

--
DonB

http://www.gotodon.com/ccbth


"Richard Taylor" <docmaster@csi.com> wrote in message
news:bqbgt4$sft$1@news.codecharge.com...
> Don,
>
> Thanks for the input -- you do understand the problem correctly.
>
> I would code it this way too, but I'm trying to work within the parameters
> of the code generator and haven't found a place that will let me "own" the
> SQL (if my ignorance is showing, please educate me -- I won't be
offended).
> That's why I was just trying to define the WHERE clause in the
> BeforeBuildSelect method.
>
> My real question is, why am I getting nothing from the Session variable?
And
> how can I make this BeforeBuildSelect method code work?
>
> --
> Richard Taylor
>
> "DonB" <~ccbth~@gotodon.com> wrote in message
>news:bqankh$vim$1@news.codecharge.com...
> > Rather than editing the login function, I'd have written the query like
> > this:
> >
> > SELECT c.CustomerID, c.ClientID, c.CompanyName, c.Address1, c.Address2,
> > c.City, c.State,
> > c.Zip FROM Customer AS c, User AS u WHERE c.ClientID = u.ClientID and
> > u.UserID = " & CCGetUserID() & " ORDER BY c.CompanyName
> >
> > (If I inderstand the problem correctly - that you want to show only
those
> > clients related to the logged-in user).
> >
> > --
> > DonB
> >
> > http://www.gotodon.com/ccbth
> >
> >
> > "Richard Taylor" <docmaster@csi.com> wrote in message
> >news:bqahnq$pbg$1@news.codecharge.com...
> > > I've tried 8 ways from Sunday to add a WHERE clause to an SQL SELECT
> > > statement for a grid page using a session variable.
> > >
> > > I added a session variable in the CCLoginUser function by editting the
> SQL
> > > select statement like this:
> > >
> > > SQL = "SELECT [UserID], [Rights], ClientID FROM [User] WHERE
> > > [LoginID]='" & Replace(Login, "'", "''") & "' AND [Password]='" &
> > > Replace(Password, "'", "''") & "'"
> > >
> > > and adding this to the If Result:
> > >
> > > Session("ClientID") = RecordSet("ClientID") 'RT added
> > >
> > > Then in the Customer_DataSource_BeforeBuildSelect() event function I'm
> > > adding this code (here are a couple of the ways I've tried to make
this
> > > work):
> > >
> > > Customer.DataSource.Where = " ClientID = " & (Session("ClientID")
> > > ' Customer.DataSource.Where = " ClientID = " &
> Cstr(Session("ClientID"))
> > > ' Customer.DataSource.Where = " ClientID = " &
> > > CCToSQL(Session("ClientID"),ccsText)
> > >
> > > All of these result in this error:
> > >
> > > Form: Grid Customer
> > > Error: Incorrect syntax near the keyword 'ORDER'. (Microsoft OLE DB
> > Provider
> > > for SQL Server)
> > > SELECT CustomerID, ClientID, CompanyName, Address1, Address2, City,
> State,
> > > Zip FROM Customer WHERE ClientID = ORDER BY CompanyName
> > >
> > > As you can see, the value of the Session(ClientID) is either blank,
> empty,
> > > null, or ....
> > >
> > > Any ideas as to what I'm doing wrong?
> > >
> > > --
> > > Richard Taylor
> > >
> > >
> > >
> >
> >
>
>

Richard Taylor
Posted: 11/30/2003, 6:19 AM

Don,

Thanks -- that helps a lot! I was unaware of changing the source property
from "Table" to "SQL" (CodeCharge is a new programming tool in my aresenal
and I'm still learnining its ins and outs). I'll try both suggestions and
I'm sure I'll figure out what I'm doing wrong.

--
Richard Taylor

"DonB" <~ccbth~@gotodon.com> wrote in message
news:bqbqgg$7r5$1@news.codecharge.com...
> You have total control (ownership) over the SQL. Just change the Data
> Source property from "Table" to "SQL".
>
> I prefer creating a query in the DBMS rather than in the CCS Data Source
> property - easier to edit, test and maintain it. But you can put the SQL
> directly in the Data Source property if you like. The Query Builder in
CCS
> works well (click the "..." to get to it), but I find it a bit clumsy.
>
> I can't say why the session var isn't working, you used it correctly.
> Evidently the variable is getting created with a blank or null value. I'd
> try putting, temporarily, two lines to debug it:
>
> Print "ClientID = " & Session("ClientID")
> Response.End
>
> And see what's there when you read it from the database. I guess it's
> conceivable someplace else you have code that modifies it, but most likely
> you are not reading a value to start with.
>
> --
> DonB
>
> http://www.gotodon.com/ccbth
>
>
> "Richard Taylor" <docmaster@csi.com> wrote in message
>news:bqbgt4$sft$1@news.codecharge.com...
> > Don,
> >
> > Thanks for the input -- you do understand the problem correctly.
> >
> > I would code it this way too, but I'm trying to work within the
parameters
> > of the code generator and haven't found a place that will let me "own"
the
> > SQL (if my ignorance is showing, please educate me -- I won't be
> offended).
> > That's why I was just trying to define the WHERE clause in the
> > BeforeBuildSelect method.
> >
> > My real question is, why am I getting nothing from the Session variable?
> And
> > how can I make this BeforeBuildSelect method code work?
> >
> > --
> > Richard Taylor
> >
> > "DonB" <~ccbth~@gotodon.com> wrote in message
> >news:bqankh$vim$1@news.codecharge.com...
> > > Rather than editing the login function, I'd have written the query
like
> > > this:
> > >
> > > SELECT c.CustomerID, c.ClientID, c.CompanyName, c.Address1,
c.Address2,
> > > c.City, c.State,
> > > c.Zip FROM Customer AS c, User AS u WHERE c.ClientID = u.ClientID and
> > > u.UserID = " & CCGetUserID() & " ORDER BY c.CompanyName
> > >
> > > (If I inderstand the problem correctly - that you want to show only
> those
> > > clients related to the logged-in user).
> > >
> > > --
> > > DonB
> > >
> > > http://www.gotodon.com/ccbth
> > >
> > >
> > > "Richard Taylor" <docmaster@csi.com> wrote in message
> > >news:bqahnq$pbg$1@news.codecharge.com...
> > > > I've tried 8 ways from Sunday to add a WHERE clause to an SQL SELECT
> > > > statement for a grid page using a session variable.
> > > >
> > > > I added a session variable in the CCLoginUser function by editting
the
> > SQL
> > > > select statement like this:
> > > >
> > > > SQL = "SELECT [UserID], [Rights], ClientID FROM [User] WHERE
> > > > [LoginID]='" & Replace(Login, "'", "''") & "' AND [Password]='" &
> > > > Replace(Password, "'", "''") & "'"
> > > >
> > > > and adding this to the If Result:
> > > >
> > > > Session("ClientID") = RecordSet("ClientID") 'RT added
> > > >
> > > > Then in the Customer_DataSource_BeforeBuildSelect() event function
I'm
> > > > adding this code (here are a couple of the ways I've tried to make
> this
> > > > work):
> > > >
> > > > Customer.DataSource.Where = " ClientID = " & (Session("ClientID")
> > > > ' Customer.DataSource.Where = " ClientID = " &
> > Cstr(Session("ClientID"))
> > > > ' Customer.DataSource.Where = " ClientID = " &
> > > > CCToSQL(Session("ClientID"),ccsText)
> > > >
> > > > All of these result in this error:
> > > >
> > > > Form: Grid Customer
> > > > Error: Incorrect syntax near the keyword 'ORDER'. (Microsoft OLE DB
> > > Provider
> > > > for SQL Server)
> > > > SELECT CustomerID, ClientID, CompanyName, Address1, Address2, City,
> > State,
> > > > Zip FROM Customer WHERE ClientID = ORDER BY CompanyName
> > > >
> > > > As you can see, the value of the Session(ClientID) is either blank,
> > empty,
> > > > null, or ....
> > > >
> > > > Any ideas as to what I'm doing wrong?
> > > >
> > > > --
> > > > Richard Taylor
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>


   


These are Community Forums for users to exchange information.
If you would like to obtain technical product help please visit http://support.yessoftware.com.

MS Access to Web

Convert MS Access to Web.
Join thousands of Web developers who build Web applications with minimal coding.

CodeCharge.com

Home   |    Search   |    Members   |    Register   |    Login


Powered by UltraApps Forum created with CodeCharge Studio
Copyright © 2003-2004 by UltraApps.com  and YesSoftware, Inc.