WinBatch Tech Support Home

Database Search

If you can't find the information using the categories below, post a question over in our WinBatch Tech Support Forum.

TechHome

WinBatch
OLE COM ADO CDO ADSI LDAP
ADO DAO
plus
ADSI LDAP CDO
plus
Samples from Users

Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.

Wildcard for SQL LIKE Using ADO

Question:

I'm writing a small little time tracking tool. I've got most of it done except for the historical data viewing.

I'm writing out my SQL statement to a file so that I can copy/paste it into a query and test it. It works fine when running as a query in MS Access 2000, but as soon as I try executing the query in Winbatch, it returns an empty recordset.

I'm using Winbatch 2003K. Anyone have any ideas??

Here is a copy of one of my SQL statements: 

SELECT TIME.TIME_IN, TIME.TIME_OUT FROM [TIME] WHERE TIME.TIME_IN LIKE '2004:18:02*' AND TIME.PROJECT_ID=1;
And here is the code that executies that SQL statement: 
#DefineSubroutine PopulateTIME(szPROJECT_NAME,szDatabaseName)
  ; -------------------------------
  ; Variables
  ; -------------------------------  
  oConn  = ObjectOpen("ADODB.Connection")
  rsDB = ObjectOpen("ADODB.RecordSet")
  iIndex = 29
  iPROJECT_ID = RetrievePROJECT_ID(szPROJECT_NAME, szDatabaseName)
    
  ; -------------------------------
  ; Connect to our Database
  ; -------------------------------  
  oConn.Open("DBQ=%szDatabaseName%;Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;");
  
  ; -------------------------------
  ; Build SQL Statement
  ; -------------------------------
  szSQL = "SELECT TIME.TIME_IN, TIME.TIME_OUT FROM [TIME] WHERE TIME.TIME_IN Like '2004:18:02*' AND TIME.PROJECT_ID=%iPROJECT_ID%;"
  
  File = FileOpen("C:\debug.txt","append")
  FileWrite(File,StrCat("PopulateTime() - ",szSQL))
  FileClose(File)
  
  ; -------------------------------
  ; Execute SQL Statement
  ; -------------------------------
  rsDB = oConn.Execute(szSQL)  
  
  
  While ! rsDB.EOF()
    fldTIME_IN = rsDB.Fields("TIME_IN")
    fldTIME_OUT = rsDB.Fields("TIME_OUT")
    Message(fldTIME_IN.Value,fldTIME_OUT.Value)
    DialogControlSet(ConfigDlgHandle, iIndex, 3, fldTIME_IN.Value)
    DialogControlSet(ConfigDlgHandle, iIndex+8, 3, fldTIME_OUT.Value)
    rsDB.MoveNext()
    iIndex = iIndex+1  
  EndWhile
  
  ; -------------------------------
  ; Cleanup
  ; -------------------------------
  oConn.Close()
  ObjectClose(oConn)
  ObjectClose(rsDB)
  
#EndSubroutine

Answer:

What are the exact Column headings in the 'Time' table? Are they TIME.TIME_IN or simply just TIME_IN? Apparently the wildcard for ADO is %, regardless of what database you're running against. Maybe try using a percent sign for the wildcard instead of *. Note: need two percent signs in Winbatch string. My guess is the sql statement should maybe look like this: 
 
szSQL = "SELECT TIME_IN, TIME_OUT FROM TIME WHERE TIME_IN LIKE '2004:18:02%%' AND PROJECT_ID=%iPROJECT_ID%;"
The following article confirms that ADO requires the percent sign instead of the asterisk.

---Quote----

Rather than using the asterisk (*) and question mark (?) symbols as wildcards, ADO requires that you use the percent sign (%) to match multiple characters and the underscore (_) to match a single character.

---EndQuote----

http://msdn.microsoft.com/library/en-us/dnima01/html/ima0601.asp?frame=true 

Article ID:   W16574

File Created: 2005:02:18:12:21:30
Last Updated: 2005:02:18:12:21:30