f



Java.sql.SQlException

Hi,

I am trying to get an SQL statement to check that the users ID and the
ID row in a table match. There maybe up to a hundred users using the
database.

I have two tables in SQl Server express - Users and Property1 both
with a
SurveyID column ie Users.SurveyID and Prop1.SurveyID

The SQL statement is:
String querySQL=("Select * from Prop1_WallFinish where SurveyID =
\"Users.SurveyID\"" );

I am getting a java.sql.SQlException: Invalid column name.

Could anyone point out what is wrong with the statement???

Thanks

Clive

0
cliveswan (32)
10/31/2007 10:07:17 AM
comp.lang.java.databases 3049 articles. 0 followers. samyaksulabh (16) is leader. Post Follow

5 Replies
708 Views

Similar Articles

[PageSpeed] 11

Clive_ wrote:
> I am trying to get an SQL statement to check that the users ID and the
> ID row in a table match. There maybe up to a hundred users using the
> database.
> 
> I have two tables in SQl Server express - Users and Property1 both
> with a
> SurveyID column ie Users.SurveyID and Prop1.SurveyID
> 
> The SQL statement is:
> String querySQL=("Select * from Prop1_WallFinish where SurveyID =
> \"Users.SurveyID\"" );
> 
> I am getting a java.sql.SQlException: Invalid column name.
> 
> Could anyone point out what is wrong with the statement???

It looks to me like you have two problems.

First, your select statement is trying to select from a table named 
"Prop1_WallFinish" rather than from either of the tables you mention, 
Users and Property1.  Perhaps you actually mean

     select * from Property1 ....

Second, your "where" clause looks fishy.  Assuming that you really mean 
to select rows from the table named Property1, then your query appears to be

   select * from Property1 where SurveyID = "Users.SurveyID"

which is testing an ID column, which is presumably a number, against a 
literal string "Users.SurveyID".

Are you trying to match rows in the Property1 table which have the same 
SurveyID column as rows in the Users table?  That's called a join.  It's 
a very common type of query, but the syntax is different than what 
you're trying to use.

May I suggest that you read the documentation for your database system 
and pay close attention to the chapter which explains joins.  It will 
tell you how to query across two tables.  Use your database system's 
command-line query tool to talk directly to your database server and try 
out various different queries before you take the leap to programming 
them in Java.  One hurdle at a time ... :-)

David Harper
Cambridge, England
0
David
10/31/2007 11:27:28 AM
On 31 Oct, 11:27, David Harper <devn...@obliquity.u-net.com> wrote:
> Clive_ wrote:
> > I am trying to get an SQL statement to check that the users ID and the
> > ID row in a table match. There maybe up to a hundred users using the
> > database.
>
> > I have two tables in SQl Server express - Users and Property1 both
> > with a
> > SurveyID column ie Users.SurveyID and Prop1.SurveyID
>
> > The SQL statement is:
> > String querySQL=("Select * from Prop1_WallFinish where SurveyID =
> > \"Users.SurveyID\"" );
>
> > I am getting a java.sql.SQlException: Invalid column name.
>
> > Could anyone point out what is wrong with the statement???
>
> It looks to me like you have two problems.
>
> First, your select statement is trying to select from a table named
> "Prop1_WallFinish" rather than from either of the tables you mention,
> Users and Property1.  Perhaps you actually mean
>
>      select * from Property1 ....
>
> Second, your "where" clause looks fishy.  Assuming that you really mean
> to select rows from the table named Property1, then your query appears to be
>
>    select * from Property1 where SurveyID = "Users.SurveyID"
>
> which is testing an ID column, which is presumably a number, against a
> literal string "Users.SurveyID".
>
> Are you trying to match rows in the Property1 table which have the same
> SurveyID column as rows in the Users table?  That's called a join.  It's
> a very common type of query, but the syntax is different than what
> you're trying to use.
>
> May I suggest that you read the documentation for your database system
> and pay close attention to the chapter which explains joins.  It will
> tell you how to query across two tables.  Use your database system's
> command-line query tool to talk directly to your database server and try
> out various different queries before you take the leap to programming
> them in Java.  One hurdle at a time ... :-)
>
> David Harper
> Cambridge, England- Hide quoted text -
>
> - Show quoted text -

Hi David,

Thanks for email.

I do not want to create a inner or outer join. I want to make sure
that the UserID match in both tables. To ensure the user enters data
into the correct row.

clive

0
Clive_
10/31/2007 12:00:47 PM
David Harper, 31.10.2007 12:27:
>   select * from Property1 where SurveyID = "Users.SurveyID"
> 
> which is testing an ID column, which is presumably a number, against a 
> literal string "Users.SurveyID".

Unless SQL Server is completely ignoring the SQL standard 
"Users.SurveyID" identifies a column name (because of the double 
quotes). Character literals have to be enclosed in single quotes

Thomas
0
Thomas
10/31/2007 12:37:20 PM
Clive_ wrote:
> I do not want to create a inner or outer join. I want to make sure
> that the UserID match in both tables. To ensure the user enters data
> into the correct row.

In SQL, "Users.SurveyID" is not the same as "Users"."SurveyID".  The latter 
means the "SurveyID" column of the "Users" table where case matters.  Most 
RDBMSes prefer a case (lower for Postgres), and only enforce case when you 
enclose the identifier in quotes.  However, you must enclose /each/ identifier 
separately in quotes, not the whole expression.

You didn't answer David's comment about using a table name in the query that 
was not part of your explanation:

> I have two tables in SQl Server express - Users and Property1 both
> with a
> SurveyID column ie Users.SurveyID and Prop1.SurveyID
....
> String querySQL=("Select * from Prop1_WallFinish where SurveyID =
> \"Users.SurveyID\"" );

To go by just your description, you want something like:
  SELECT * FROM Prop1 WHERE Prop1.SurveyID = ?
and use Users.SurveyID to fill the parameter.

-- 
Lew
0
Lew
10/31/2007 12:40:01 PM
On 31 Oct, 12:40, Lew <l...@lewscanon.com> wrote:
> Clive_ wrote:
> > I do not want to create a inner or outer join. I want to make sure
> > that the UserID match in both tables. To ensure the user enters data
> > into the correct row.
>
> In SQL, "Users.SurveyID" is not the same as "Users"."SurveyID".  The latter
> means the "SurveyID" column of the "Users" table where case matters.  Most
> RDBMSes prefer a case (lower for Postgres), and only enforce case when you
> enclose the identifier in quotes.  However, you must enclose /each/ identifier
> separately in quotes, not the whole expression.
>
> You didn't answer David's comment about using a table name in the query that
> was not part of your explanation:
>
>
>
> > I have two tables in SQl Server express - Users and Property1 both
> > with a
> > SurveyID column ie Users.SurveyID and Prop1.SurveyID
> ...
> > String querySQL=("Select * from Prop1_WallFinish where SurveyID =
> > \"Users.SurveyID\"" );
>
> To go by just your description, you want something like:
>   SELECT * FROM Prop1 WHERE Prop1.SurveyID = ?
> and use Users.SurveyID to fill the parameter.
>
> --
> Lew

Hi Lew,

Will try this.

Thanks for advice.

Cheers

Clive


0
Clive_
11/2/2007 10:57:20 AM
Reply: