Sign in to follow this  
Followers 0
ca143508

Excel SQL query

6 posts in this topic

Hi Guys,

I hope you can get me going here. I can get the basic SELECT * FROM query to populate a listview. I want to take it a step furthur and have it run something similar to the following....

$objRecordSet.Open ('Select [Field1], [Field2], [Field3], [Field4], [Field5], [Field6] FROM ' & $s_Tablename & ' WHERE ((([Field5])>="2009/05/07") AND (([Field6])<="2009/05/22"))', $objConnection, 3, 3, 0x0001)

I get the following error:

==> The requested action with this object has failed.:

and this indicates something wrong at the end of the string.

Any advice would be greatly appreciated.

Cheers

Mike.

Share this post


Link to post
Share on other sites



How are the 2 date fields formatted in Excel? If they are formatted as Dates rather than stings you may need to convert the values to strings or the string values "2009/05/07" to dates before doing the comparisons.


"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the universe trying to build bigger and better idiots. So far, the universe is winning."- Rick Cook

Share this post


Link to post
Share on other sites

At the moment both columns are formatted as straight text. You can see a copy of the function below...

Basically when they change the $Date1 control it will call this function and replace the necessary date values. For this post, I have just placed some static dates.

Const $adOpenStatic = 3
Const $adLockOptimistic = 3
Const $adCmdText = 0x0001
Global $s_Filename = FileGetShortName("File.xls") 
Global $s_Tablename = "[Sheet1$]" 

; Source XLS data
$objConnection = ObjCreate("ADODB.Connection")
$objRecordSet = ObjCreate("ADODB.Recordset")
$objConnection.Open ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $s_Filename & "; Extended Properties=""Excel 8.0;HDR=Yes;"";")
$objRecordSet.Open ('Select [Field1], [Field2], [Field3], [Field4], [Field5], [Field6] FROM ' & $s_Tablename & ' WHERE ((([Field5])>="2009/05/07") AND (([Field6])<="2009/05/22"))', $objConnection, 3, 3, 0x0001)
    $TotalDuration = 0
    Do 
        $Duration = StringTrimLeft(_DateAdd( 's', $objRecordSet.Fields(2).value, '1970/01/01 00:00:00'), 10)
        $TotalDuration = $TotalDuration + $objRecordSet.Fields(2).value
     
        GUICtrlCreateListViewItem ($objRecordSet.Fields(0).value & '|' & $Duration & '|' & $objRecordSet.Fields(2).value & '|' & $objRecordSet.Fields(3).value & '|' & $objRecordSet.Fields(4).value & '|' & $objRecordSet.Fields(5).value, $ListView1); 
$objRecordSet.MoveNext()
Until $objRecordSet.EOF()

$objConnection.Close
$objConnection = ""
$objRecordSet = ""

Share this post


Link to post
Share on other sites

Guys,

After having a bit more of a play today, it seems as though it is not just limited to the date fields. It whenever I stipulate a WHERE clause. Regardless of the column I query it goes down. Any other ideas???

Cheers

Share this post


Link to post
Share on other sites

Try some very simple where clause and say what happened. Like:

Select * from table where value=1

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0