I'm not a programmer by trade, so please don't rough me up too badly! I'm needing to pull values from a visual fox pro database (from a .dbc container and .dbf table files). I've managed to pull together some code from other sources around the web, and I'm very close. I'm certain this is a knowledge gap with my "thrown to the wolves" education I'm going through now.
Essentially the problem is that I can display the data I want in powershell, and I can grab the number of items (although, I'm certain this is due to my inexperience with the syntax). For example, if my sql calls for all deliveries with a date of today, I can pull the count of those records. The problem is when I want to sum the miles for each drop off. I can run a sql / vfoxpro query against the db and get a table view, however, I cannot for the life of me extract the VALUE of the table data, only the record count.
Here's what I've got:
$ConnString = "Provider=vfpoledb.1;Data Source=C:\dispatch\dispatch.dbc;Collating Sequence=machine;"
$Conn = new-object System.Data.OleDb.OleDbConnection($connString)
$sql = "select ((sum(endmile)) - (sum(pickupmileage))) as MilesToday from dispatch where date = DTOT(date())"
$obj = new-object System.object
#Total Miles Today Query
$cmd = new-object System.Data.OleDb.OleDbCommand($sql,$Conn)
$da = new-object System.Data.OleDb.OleDbDataAdapter($cmd)
$dt = new-object System.Data.dataTable
The output for that gives:
What I really want to do is to pass the value '16' via:
$items = $da.fill($dt)
add-member -inputobject $obj -membertype NoteProperty -Name TotalMiles -value $items
Unmodified, I get the number of records. This code works well for calling the number of deliveries with "peaches" for example, but I can't pass any of the data, just the row counts by my code. What am I missing?
Thanks for any help you can provide!