• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1006
  • Last Modified:

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer] Invalid Column Name

I have a stored procedre which searches in a table where customer_id is (user enters)
Now this query works fine if the customer_id is present in the table
but if no customer_id is present than it gives an error

Now what I need is if there is no customer_id as it is given by the user than it retun a null resultset

Any help will be greatly appreciated

Error:
------------------------------------------------------------------------------------------------
EXEC ccim_getChapterMembers 'HALBA000','','','','customer'
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Inv
alid column name 'HALBA000'.

Stored Procedure :
------------------------------------------------------------------------------------------
declare @sql varchar(4000)

set @sql = 'SELECT ' +
'ChapterMemberRelationship.relationship_ID,' +
'ChapterMemberRelationship.modified_by,' +
'ChapterMemberRelationship.date_modified,' +
'customer.customer, ' +
'customer.first_name, ' +
'customer.last_name, ' +
'customer.member_class, ' +
'customer.customer_status ' +
'from customer, ChapterMemberRelationship ' +
'where Customer.customer = ChapterMemberRelationship.member' +
' And Customer.customer =' +  @chapter_id    +
ISNULL(@member_id_clause,'') +
ISNULL(@first_name_clause,'') +
ISNULL(@last_name_clause,'')  +
' ORDER BY  ' + @order_by

EXEC(@sql)
GO
0
huzefaq
Asked:
huzefaq
  • 6
  • 5
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
set @sql = 'SELECT ' +
'ChapterMemberRelationship.relationship_ID,' +
'ChapterMemberRelationship.modified_by,' +
'ChapterMemberRelationship.date_modified,' +
'customer.customer, ' +
'customer.first_name, ' +
'customer.last_name, ' +
'customer.member_class, ' +
'customer.customer_status ' +
'from customer, ChapterMemberRelationship ' +
'where Customer.customer = ChapterMemberRelationship.member' +
' And Customer.customer =''' +  @chapter_id    +
ISNULL(@member_id_clause,'') +
ISNULL(@first_name_clause,'') +
ISNULL(@last_name_clause,'')  +''''
' ORDER BY  ' + @order_by
0
 
huzefaqAuthor Commented:
aneeshattingal

I tried your query but it says incorrect syntax near @order_by

Thanks
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Can you post the exact procedure ... I doubt the problem is with this
ISNULL(@member_id_clause,'') +
ISNULL(@first_name_clause,'') +
ISNULL(@last_name_clause,'')  
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
huzefaqAuthor Commented:
below is the code of how I create the different clauses and how I call the stored procedre

-------------------------------------------------------------------------------------------------------------------------------------------------------------------

 /* if there was some sort of member id given, create the where clause for it */
       if(member_id != null && member_id.trim().length() > 0) { member_id_clause = " AND customer.customer like '%" + member_id + "%'"; }

       /* if there was some sort of first name given, create the where clause for it */
       if(first_name != null && first_name.trim().length() > 0) { first_name_clause = " AND customer.first_name like '%" + first_name.replaceAll("'", "''") + "%'"; }

       /* if there was some sort of last name given, create the where clause for it */
        if(last_name != null && last_name.trim().length() > 0) { last_name_clause = " AND customer.last_name like '%" + last_name.replaceAll("'", "''") + "%'"; }
        /* if none was given, default to oder by customer                */
        if(!order_by.equals("customer") && !order_by.equals("first_name") && !order_by.equals("last_name")) {
            order_by = "customer ";
        }


        try {
            String sql = "EXEC ccim_getChapterMembers " + StringUtil.prepForDB(chapter_id) + "," +
                                                        StringUtil.prepForDB(member_id_clause) +  "," +
                                                        StringUtil.prepForDB(first_name_clause) + "," +
                                                        StringUtil.prepForDB(last_name_clause) + "," +
                                                        StringUtil.prepForDB(order_by);
0
 
royalcyberCommented:
I tried using If statement that if there are no records for a parcticular chapter_id, don't even execute the sql. but as I generate no recordset I get this error
Is there a way to get rid of this error and do guys think that I am on the right direction

Thanks

----------------------------------------------------------------------------------------------------
EXEC ccim_getChapterMembers 'HALBA000','','','','customer'
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]No ResultSet s
et was produced.

------------------------------------------------------------------------------------------------------
IF  EXISTS(SELECT 'True'
           FROM ChapterMemberRelationship
           WHERE ChapterMemberRelationship.chapter  = @chapter_id)
BEGIN

declare @sql varchar(4000)

set @sql = 'SELECT ' +
'ChapterMemberRelationship.relationship_ID,' +
'ChapterMemberRelationship.modified_by,' +
'ChapterMemberRelationship.date_modified,' +
'customer.customer, ' +
'customer.first_name, ' +
'customer.last_name, ' +
'customer.member_class, ' +
'customer.customer_status ' +
'from customer, ChapterMemberRelationship ' +
'where Customer.customer = ChapterMemberRelationship.member' +
' And ChapterMemberRelationship.chapter =' +  @chapter_id    +
ISNULL(@member_id_clause,'') +
ISNULL(@first_name_clause,'') +
ISNULL(@last_name_clause,'')  +
' ORDER BY  ' + @order_by

EXEC(@sql)
End

Else
Begin
return null
END
GO
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Instead of  'EXEC(@sql)'

try

print @sql

and send me the output



' And ChapterMemberRelationship.chapter =' +  @chapter_id    +
ISNULL(@member_id_clause,'') +
ISNULL(@first_name_clause,'') +
ISNULL(@last_name_clause,'')  +

the above part is confusing... Is 'ChapterMemberRelationship.chapter' is the concatenated value of @chapter_id    , @member_id_clause,@first_name_clause,@last_name_clause
0
 
huzefaqAuthor Commented:
aneesh... below is the query output

Now the problem is that when I pass the chapter_ID which is not in the table
for eg ChapterMemberRelationship.chapter ="HALBA000"
it gives error Invalid column name

but if the chapter ID is in the table, it gives the desired result

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SELECT ChapterMemberRelationship.relationship_ID,ChapterMemberRelationship.modified_by,ChapterMemberRelationship.date_modified,customer.customer, customer.first_name, customer.last_name, customer.member_class, customer.customer_status from customer, ChapterMemberRelationship where Customer.customer = ChapterMemberRelationship.member And ChapterMemberRelationship.chapter ="HALBA000"


0
 
Aneesh RetnakaranDatabase AdministratorCommented:
>' And Customer.customer =' +  @chapter_id    +

' And Customer.customer =' +  ISNULL(@chapter_id,Customer.customer )    +
0
 
huzefaqAuthor Commented:
but aneesh this will check if chapter_id is null, if it is than it will call customer.customer

I already check if customer_id is null in the java code
Inside the query it will never be null.

Now the problem is that all the chapter_id are not in the  ChapterMemberRelationship table. so if it is not than it gives error
Invalid column name. Now I don't understand why it gives invalid column name ?

Thanks for your help

0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Can you post the complete sp +
the exact ' Print(@sql) '  output as u give in the previous cases for both the cases..
0
 
huzefaqAuthor Commented:
-------------------------------------
declare @sql varchar(4000),
@chapter_id varchar(8),
@member_id_clause varchar(100),
@first_name_clause varchar(100),
@last_name_clause varchar(100),
@order_by varchar(100)

set @chapter_id = 'HALBA000'

set @member_id_clause = null

set @first_name_clause = null

set @last_name_clause = null

set @order_by = 'customer'

set @sql = 'SELECT ' +
'ChapterMemberRelationship.relationship_ID,' +
'ChapterMemberRelationship.modified_by,' +
'ChapterMemberRelationship.date_modified,' +
'customer.customer, ' +
'customer.first_name, ' +
'customer.last_name, ' +
'customer.member_class, ' +
'customer.customer_status ' +
'from customer, ChapterMemberRelationship ' +
'where Customer.customer = ChapterMemberRelationship.member' +
' And ChapterMemberRelationship.chapter ='' +  @chapter_id  + ''' +
ISNULL(@member_id_clause,'') +
ISNULL(@first_name_clause,'') +
ISNULL(@last_name_clause,'')  +
' ORDER BY  ' + @order_by

print (@sql)
------------------------------------------------------------------------------------

when I print it I get

------------------------------------------------------------------------------------

SELECT ChapterMemberRelationship.relationship_ID,ChapterMemberRelationship.modified_by,ChapterMemberRelationship.date_modified,customer.customer, customer.first_name, customer.last_name, customer.member_class, customer.customer_status from customer, ChapterMemberRelationship where Customer.customer = ChapterMemberRelationship.member And ChapterMemberRelationship.chapter =' +  @chapter_id  + ' ORDER BY  customer

Notice ' +  @chapter_id  + '  this is not right

what I need is

SELECT ChapterMemberRelationship.relationship_ID,ChapterMemberRelationship.modified_by,ChapterMemberRelationship.date_modified,customer.customer, customer.first_name, customer.last_name, customer.member_class, customer.customer_status from customer, ChapterMemberRelationship where Customer.customer = ChapterMemberRelationship.member And ChapterMemberRelationship.chapter ='HALBA000' ORDER BY  customer

Also i was able to get "HALBA000" but if there are double quotes the sql server thows exception 'Invalid column name'


Any help will be greatly appreciated

0
 
Aneesh RetnakaranDatabase AdministratorCommented:
declare @sql varchar(4000),
@chapter_id varchar(8),
@member_id_clause varchar(100),
@first_name_clause varchar(100),
@last_name_clause varchar(100),
@order_by varchar(100)

set @chapter_id = 'HALBA000'

set @member_id_clause = null

set @first_name_clause = null

set @last_name_clause = null

set @order_by = 'customer'

set @sql = 'SELECT ' +
'ChapterMemberRelationship.relationship_ID,' +
'ChapterMemberRelationship.modified_by,' +
'ChapterMemberRelationship.date_modified,' +
'customer.customer, ' +
'customer.first_name, ' +
'customer.last_name, ' +
'customer.member_class, ' +
'customer.customer_status ' +
'from customer, ChapterMemberRelationship ' +
'where Customer.customer = ChapterMemberRelationship.member' +
' And ChapterMemberRelationship.chapter =''' +  @chapter_id  + '''' +
ISNULL(@member_id_clause,'') +
ISNULL(@first_name_clause,'') +
ISNULL(@last_name_clause,'')  +
' ORDER BY  ' + @order_by

print (@sql)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now