Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2177
  • Last Modified:

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!
0
safenetworksolutions
Asked:
safenetworksolutions
  • 3
  • 3
1 Solution
 
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
 
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
Who's Defending Your Organization from Threats?

Protecting against advanced threats requires an IT dream team – a well-oiled machine of people and solutions working together to defend your organization. Download our resource kit today to learn more about the tools you need to build you IT Dream Team!

 
Olaf DoschkeSoftware DeveloperCommented:
It's been almost two weeks. Whats up?

Bye, Olaf.
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now