Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2006-07-03
12
Medium Priority
?
974 Views
Last Modified: 2012-06-27
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
Comment
Question by:huzefaq
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
12 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17032452
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
 

Author Comment

by:huzefaq
ID: 17032767
aneeshattingal

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

Thanks
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17034652
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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 

Author Comment

by:huzefaq
ID: 17043127
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
 

Expert Comment

by:royalcyber
ID: 17044067
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17044661
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
 

Author Comment

by:huzefaq
ID: 17046131
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17048331
>' And Customer.customer =' +  @chapter_id    +

' And Customer.customer =' +  ISNULL(@chapter_id,Customer.customer )    +
0
 

Author Comment

by:huzefaq
ID: 17054636
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17056221
Can you post the complete sp +
the exact ' Print(@sql) '  output as u give in the previous cases for both the cases..
0
 

Author Comment

by:huzefaq
ID: 17074024
-------------------------------------
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
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 17074359
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question