SQL Select Query

• • Reply

  19 years ago

  by bransoj

  jonb

  , United States

  Joined 19 years ago

  I have started to learn SQL for some stuff I am doing at work. I tryed a number of queries that I wrote using a sample database loaded on our server and all but one have been successful but I can't find teh problem with the one that doesn't work.
  

  
  When tested in the Query Tool with Enterprise Manager it pulls back the correct 12 records but once I try it within some VB code it runs but fails to pull out any records.
  

  
  The select statement I am using is :-
  

  
  select SkillCode from Skill where DATALENGTH(Convert(varchar(4),SkillCode)) <=3 order by SkillCode ASC
  

  
  The VB Code :-
  

  
  cn.CursorDriver = rdUseOdbc
  cn.Connect = "Driver={SQL Server};Server=RMSC001;UID=sa;PWD=sa;Database=sqlclass;"
  cn.EstablishConnection
  qy.SQL = "select SkillCode from Skill where DATALENGTH(Convert(varchar(4),SkillCode)) <=3 order by SkillCode ASC"
  qy.RowsetSize = 1
  Set qy.ActiveConnection = cn
  Set Rs = qy.OpenResultset(rdOpenKeyset, rdConcurRowVer)
  

  
  The same chuck of code has worked on the other 4 so I am assuming that the select statement is incorrect in some way but the fact that it works in Enterprise Manager Query Tool but not in VB has thrown me!!!
  

  
  Can anyone spot any reason why this wouldn't work within VB code?

  Report abuse
• • Reply

  19 years ago

  by Reptile

  Reptile

  Montreal, United States

  Joined 19 years ago

  Hi there,
  

  
  Well, I didn't really work with pure SQL yet... i'm only using the MsAccess query statements.. so I guess that it differs in terms of internal function usage...
  

  
  I would like to help you, but I don't understand how this part :
  

  Code:

  
  DATALENGTH(Convert(varchar(4),SkillCode))
  

  
  

  
  I guess they are all built-in specific SQL functions (except of course the "SkillCode" at the end that is the field).... One thing is sure though, they wouldn't work in MsAccess using VBA.
  

  
  Let me know if you figured this out.
  

  
  Regards!

  Report abuse
• • Reply

  19 years ago

  by CodeWarrior76

  CodeWarrior

  Calgary, CA, United States

  Joined 19 years ago

  What verison of SQL Server are you running?
  If your running SQL Server 2000 the problem may be that all field and table names need to be correct case.  SQL 2000 seems to be a lot more picky about syntax and case sensitivity.
  

  
  Just a guess.
  

  
  Also, is that DAO you're using?  Try it in ADO maybe, and again, if you're using SQL server 2000 then the ADO version must be 2.6 or higher.
  

  
  maybe like this:
  

  
  cn.connectionstring = "Provider=SQLOLEDB.1;Password=sa;Persist Security Info=True;User ID=sa;Initial Catalog=sqlclass;Data Source=RMSC001"
  if cn.state = 0 then cn.open
  strQuery = "select SkillCode from Skill where DATALENGTH(Convert(varchar(4),SkillCode)) <=3 order by SkillCode ASC"
  rs.open strquery, cn
  

  
  I replaced your connection details into an OLEDB connection string already for you.

  Report abuse
• • Reply

  19 years ago

  by laura

  laura

  , United States

  Joined 19 years ago

  Did you see MY DATABASE???
  Also working good on Internet Side Programming!
  

  
  Throw away MS DATABASE..My base will never crash with 255 more character per ITEM!You can store Large Book in only one ITEM!!!!!
  What is SQL...Hmm...My base will show you What is a true Query!!!!!!!!!!!!!!!!!!!!!
  After All,My DATABASE use compression to store data...
  I've got some base len. 50kb, in MS DATABASE Yo've got 500KB!!!!!!
  

  
  

  Report abuse
• • Reply

  19 years ago

  by hobbes68

  hobbes68

  Milan, United States

  Joined 19 years ago

  wow laura, back to your advertisement ...
  why do't you just add a resource?
  

  Report abuse