Query writing Error in VB 1

[oops4me]

Hello,
I have written following query in Vb
dim str11 as string

str11="select case a.Company_id when 0 then '' Else: a.Comp_name end , case b.Desig_id when 0 then '' Else: b.Desig_name end , case c.Industry_id when 0 then '' Else: c.Ind_name end , case d.Function_id when 0 then '' Else: d.Function_name end from Company_Mst a,Designation_Mst b,Industry_Mst c, Function_Mst d Where a.Company_id = " & rs4.Fields("Company_id") & " And b.Desig_id =" & rs4.Fields("Desig_id") & " And c.Industry_id =" & rs4.Fields("Indusrty_id") & " And d.Function_id =" & rs4.Fields("Function_id")

after this i ve written
set rs2=con.execute(str11)

but it is giving me error at str11 where i am assigning query to string str11
as "Item can not found in collection corresponding to requested name or ordinal "
still i ve not executed the query.

i ve written following to check query contents

msgbox(str11="select case a.Company_id when 0 then '' Else: a.Comp_name end , case b.Desig_id when 0 then '' Else: b.Desig_name end , case c.Industry_id when 0 then '' Else: c.Ind_name end , case d.Function_id when 0 then '' Else: d.Function_name end from Company_Mst a,Designation_Mst b,Industry_Mst c, Function_Mst d Where a.Company_id = " & rs4.Fields("Company_id") & " And b.Desig_id =" & rs4.Fields("Desig_id") & " And c.Industry_id =" & rs4.Fields("Indusrty_id") & " And d.Function_id =" & rs4.Fields("Function_id"))

then also it is giving me same error.
can any body tell me what is the problem & it's solution. If i run this query at
sql server it is working fine.
please help me.
Edit/Delete Message

 

[HarleyQuinn]

It might be a typo but is

rs4.Fields("Indusrty_id")

meant to be

rs4.Fields("Industry_id")

as this may casue your problem.

Hope this helps

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[oops4me]

thanx for ur help.

 

[HarleyQuinn]

You're welcome, thanks for the star

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[BobRodes]

<as this may casue [sic!] your problem.

Struck me funny.

 

[HarleyQuinn]

A typo in a post about a typo

I noticed when I posted it but I was hopimg no-one else would...

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[HarleyQuinn]

And if you look closely I've made another in that reply

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[BobRodes]

And not just any old typo, but the exact same kind as in the code.

<And if you look closely I've made another in that reply

Hopimg your wishes are met,

Bob