Reading values from vfoxpro in powershell

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) 
$conn.open() 
$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  
$da.fill($dt) 
$dt

Open in new window


The output for that gives:

1

                                                                                 milestoday
                                                                                 ----------
                                                                                         16

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
Write-output $obj

Open in new window


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!
safenetworksolutionsAsked:
Who is Participating?
 
Olaf DoschkeConnect With a Mentor Software DeveloperCommented:
It's been almost two weeks. Whats up?

Bye, Olaf.
0
 
Olaf DoschkeSoftware DeveloperCommented:
Take a look at the Fill method of the dataadapter: http://msdn.microsoft.com/en-us/library/377a8x4t.aspx

The Fill method fills data into the datatable ($dt in your case), the return value is the number of records returned or updated. Therfore there is nothing more in $items than the record count.

Next error: The Fill method of a dataadapter fills a dataset, not a datatable. So you'd rather need a $ds = new-object System.DataSet, not a datatable.

Bye, Olaf.
0
 
Olaf DoschkeSoftware DeveloperCommented:
Searching a bit more...

Ok, there also is a Fill(datatable) here: http://msdn.microsoft.com/en-us/library/905keexk.aspx
So your code should work and fill $dt. Still the $items is only the number of records.

$dt.Rows[0]["MilesToday"] should give you the miles today. Maybe a 1 instead of 0, but I think the .net DataRows collection is 0 based.

Bye, Olaf.
0
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
safenetworksolutionsAuthor Commented:
Olaf -

Thank you SO much for the response.  I will try your suggestion tonight when I am back at the client site.  Again, I really appreciate your input!!
0
 
safenetworksolutionsAuthor Commented:
Sorry for the delay - Had numerous setbacks as 1 routine needed exclusive access to the DB.  We've overcome those issues -

Your solution worked perfectly!

Here's the revised code:

 
$ConnString = "Provider=vfpoledb.1;Data Source=C:\dispatch\dispatch.dbc;Collating Sequence=machine;"
$Conn = new-object System.Data.OleDb.OleDbConnection($connString) 
$conn.open() 

$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  
$da.fill($dt)
$items = $dt.Rows[0]["MilesToday"]

Open in new window


Thanks so much!  I appreciate the input!
0
 
safenetworksolutionsAuthor Commented:
Thanks again!  Really appreciate the input!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.