Solved

Reading values from vfoxpro in powershell

Posted on 2011-03-13
6
1,972 Views
Last Modified: 2012-05-11
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!
0
Comment
Question by:safenetworksolutions
  • 3
  • 3
6 Comments
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 35125971
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
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 35126027
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
 

Author Comment

by:safenetworksolutions
ID: 35159045
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 29

Accepted Solution

by:
Olaf Doschke earned 500 total points
ID: 35238656
It's been almost two weeks. Whats up?

Bye, Olaf.
0
 

Author Comment

by:safenetworksolutions
ID: 35352538
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
 

Author Closing Comment

by:safenetworksolutions
ID: 35352541
Thanks again!  Really appreciate the input!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I thought I'd write this up for anyone who has a request to create an anonymous whistle-blower-type submission form created using SharePoint 2010 (this would probably work the same for 2013). It's not 100% fool-proof but it's as close as you can get…
Create and license users in Office 365 in bulk based on a CSV file. A step-by-step guide with PowerShell script examples.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question