CodeCharge Studio
search Register Login  

Web Reporting

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

YesSoftware Forums -> CodeCharge Studio -> ASP

 SQL Help to Pull UserLogin and Null Values

Print topic Send  topic

Author Message
rbaggins

Posts: 7
Posted: 09/12/2009, 12:39 PM

This should work but I am stumped.

Here is my SQL in the Query Builder:
SELECT *
FROM master_census_srce
WHERE SigFee_id = {SigFee_id}
AND User_id = '{UserLogin}'
AND ( (User_id IS NULL) )

I am passing the user login to a form successfully. What I am trying to accomplish is this: When a user selects either a record they have written (their user login is in the record) or if the userlogin field is a null they could edit/submit that record as well.

If a another user selects the record they can only view the record.

If I remove the "AND ( (User_id IS NULL) )" from the query it works for matching the userlogin just fine, but I cannot seem to get the null records with the populated matching UserLogin.

Any help would be appreciated !
View profile  Send private message
ReneS

Posts: 225
Posted: 09/13/2009, 4:00 AM

Hi,

First of all, i think, User_id cannot be UserLogin AND NULL at the same time. So you should use OR. Second, I am not sure if the syntax is right for User_id IS NULL, but try different syntaxes because i am not a programmer....

Try Something like this:

SELECT *
FROM master_census_srce
WHERE SigFee_id = {SigFee_id}
AND (User_id = '{UserLogin}' OR User_id= "NULL")

Rene

View profile  Send private message
Joachim Uersfeld
Posted: 09/13/2009, 7:15 AM

In MS SQL Server:

SELECT * FROM master_census_srce WHERE SigFee_id = {SigFee_id}
AND ((User_id = '{UserLogin})' OR (User_id IS NULL))

ReneS wrote:
> AND (User_id = '{UserLogin}' OR User_id= "NULL")
rbaggins

Posts: 7
Posted: 09/14/2009, 11:01 AM

Thanks for your help.

Here is what I tried based on your input:
SELECT *
FROM master_census_srce
WHERE SigFee_id = {SigFee_id}
AND ( User_id = '{UserLogin}' OR User_id= "NULL" )

Which Results in:
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'NULL'. (Microsoft OLE DB Provider for ODBC Drivers)

I am using the visual query builder in the properties tab. - in the Data Source for the form.

Any help would be really appreciated -

-Rob
View profile  Send private message
ReneS

Posts: 225
Posted: 09/14/2009, 1:20 PM

Hi,

Try Joachims solution. Switch in visual query builder to sql

Rene
View profile  Send private message
rbaggins

Posts: 7
Posted: 09/14/2009, 2:20 PM

Thanks for all your input and hanging in there with me.

Still getting an error message in the form:
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'NULL'. (Microsoft OLE DB Provider for ODBC Drivers)

Using this code:
SELECT *
FROM master_census_srce WHERE SigFee_id = {SigFee_id} AND ( User_id = '{UserLogin}' OR User_id= "NULL" )

Switched from table to SQL - same result I have enclosed a screen shot of the visual query builder/sql option

View profile  Send private message
Joachim Uersfeld
Posted: 09/14/2009, 10:55 PM

This SQL is wrong: ... OR User_id= "NULL"
This SQL works: ... OR User_id IS NULL

rbaggins wrote:
> Thanks for all your input and hanging in there with me.
> Still getting an error message in the form:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name
> 'NULL'. (Microsoft OLE DB Provider for ODBC Drivers)
> Using this code:
> SELECT *
> FROM master_census_srce WHERE SigFee_id = {SigFee_id} AND ( User_id =
> '{UserLogin}' OR User_id= "NULL" )
ReneS

Posts: 225
Posted: 09/15/2009, 1:49 AM

Hi,

As I said in my previous post:

Try JOACHIM solution. (thanks Joachim!)


Rene

View profile  Send private message
rbaggins

Posts: 7
Posted: 09/16/2009, 11:36 AM

Thanks everyone for your input. I have the sql working.
SELECT *
FROM master_census_srce
WHERE SigFee_id = {SigFee_id}
AND User_id = '{UserLogin}'
OR ( (User_id IS NULL) )

However the insert capability in the form becomes disabled for all the records. I want the insert capability in the form when the above condition is met.

When I publish the application I get this warning from CodeCharge:

"Warning:Update is allowed for Record master_census_srce. Unable to build Update based on the current Datasource settings (joins, SP or custom SQL). Change the Allow Update property to disable Update or specify Custom Update."

I have tried using the custom update but with no success. Any Ideas?

Thanks much.

-Rob
View profile  Send private message

Add new topic Subscribe to topic   


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

Web Database

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.