Solved

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

Posted on 2006-07-03
12
961 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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 

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 500 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

630 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