Hi!
I am trying to get supplier address by searching on companyName of the suppliers table of northwind database.The company name is filled in a list box the code for which is not shown here.

My following query runs in SQL query analyzer and it also shows the query properly when i use reposne.write.However the query returns 0 results when i execute it in the ASP page.

please tell me whats going wrong.

thanks


dim cname,MyQuery

'replace all quotes in company name with double qutes
cname= Replace(Request.Form("CompanyName"), "'", "''")


MyQuery = " Select CompanyName, Address from NorthWind.dbo.Suppliers where CompanyName like '" & cname &"'"

Response.Write(MyQuery)

set rs=server.CreateObject("ADODB.Recordset")
conn.Open

set rs=conn.Execute(MyQuery)
if(rs.EOF) then
           Response.write "<br><b>nuthin"
else
     Response.Write "<br>my address is " &rs("address")
end if

are you getting any errors? does "nuthin" is displaying as part of your code
also, hard code company name in your query and see if you are getting any results.. let me know

hi

thanks for your reply.

yes I am getting output as 'nuthing'.I hard coded query with company name='exotic liquids' and it gave me proper output of rs("address")
only when i use a variable its giving me poutput as 'nothing'

When you response.write the query what do you get? Paste this into query analyser and see if it runs.

Your code will only match on exact name, if you want "begins with" then add a percentage sign:
MyQuery = " Select CompanyName, Address from NorthWind.dbo.Suppliers where CompanyName like '" & cname & "%'"

I would use a stored procedure. It's safer and you don't need to worry about replacing quotes etc.




--

Joe (Microsoft MVP - XML)

@ joefawcett

Your solution solved my problem! Thanx

webkasper