Solved

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

Posted on 2006-07-03
12
906 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
  • 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
 

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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now