Connect to Access Db

[Newbie]

Hello All,

 

I'm trying to create a Vertical Gauge chart type using through the PowerGadgets UI. The data is in an Access db locate on the network location. Here are the steps:

1. Launch PowerGadgets 

2. Vertical Gauge, Next

3. User Windows PowerShell

4. in the PowerShell Script, I enter the following:

invoke-sql -connection "Provider=Microsoft.Jet.OLEDB.4.0;DataSource=\\TestLocation\Customer\Customer.mdb" -sql "select company,Sales,Budget from Orders where company=wood" | out-gauge -MainValue {$_.Sales} -MainScale_Max {$_.Budget} -refresh 0:0:1

 

when I hit test the following show in the result:

The term 'invoke-sql' is not recognized as a cmdlet, function, operable program, or script file. Verify the term and try again.

At line:1 char:11+ invoke-sql <<<< -connection "Provider=Microsoft.Jet.OLEDB.4.0;DataSource=\\TestLocation\Customer\Customer.mdb" -sql

The term 'out-gauge' is not recognized as a cmdlet, function, operable program, or script file. Verify the term and try again.

At line:2 char:73+ "select company,Sales,Budget from Orders where company=wood" | out-gauge <<<< -MainValue {$_.Sales} -MainScale_Max {$_.Budget} -refesh 0:0:1

 Can you please tell me what is wrong with the syntax?  Your help/suggestion is greatly appreciated.

 

[cosmin.onea]

Just before invoking any powergadget in powershell you need to register it.

To do that run the following and then try your command:add-pssnapin powergadgets

You can have this command in the powershell profile or try to open the power shell console from the powergadgets program group: Start-> All programs-> PowerGadgets -> Powershell with powergadgets

Hope this helps,Cosmin.

[JuanC]

Note that the command you typed is one you would type in a Powershell console, when selecting PowerShell data from the PG creator you cannot call out-chart/out-gauge. I noticed you are trying to set the max to be a column from the database, this cannot be achieved in the UI so you will have to use the following trick

• Make sure your command run and gets the right data from a powershell console (note Cosmin comments where you have to add our snapin or run the shortcut labeled "Powershell with powergadgets"
• Run your command and with powergadgets selected as the active window press "Ctrl-C"
• Create a new gadget in PG creator and select "Edit-Paste"
• If desired remove the previous gadget

At this point you should have a gadget where the data is already set and also the max in the mainscale should be bound to a column returned by your SQL. Now you should be able to customize how the gauge looks using our UI.

JuanC

 

[Newbie]

Thank you all for the input.  Here is what I did and the result:

 Start - All Programs - PowerGadgets - Windows PowerShell with PowerGadgets

a Windows PowerShell with PowerGadgets open, I copy the script and select >-,edit,paste to paste the script and hit enter

 here is the script:

 invoke-sql -connection "Provider=Microsoft.Jet.OLEDB.4.0;DataSource=\\TestLocation\Customer\Customer.mdb" -sql "select company,Sales,Budget from Orders where company=wood"

here is the result:

 Invoke-Sql : Could not find installable ISAM. At line:1 char:11

+ invoke-sql -connection "Provider=Microsoft.Jet.OLEDB.4.0;DataSource=\\TestLocation\Customer\Customer.mdb" -sql "select company,Sales,Budget from Orders where company=wood"

We just install the trial version software to see if it work for us.  So I don't really know much about the software.  Sounds like my computer missing something (ISAM)?  Any idea?

 Thank you all

[Newbie]

ok i figure out why i got the missing ISAM message.  I had to change directory to where the Access db locate to run the script.

Now I have a new problem:

when I "select * from Customer" it works fine.

 

when I "select * from Customer where company = wood" i get this message:

Invoke-Sql : No value given for one or more required parameters??? thanks

[IGSFX]

If company is a string then you need to enclose wood in single quotes like this:

select * from Customer where company = 'wood'

 

[Newbie]

Ivang, thanks I already figure that out.  I'm stuck with setting the MTDBdgtSales as the Scale Max value.  I'll ready JuanC post again.