Fill Datagridview with data from an access

Ask questions about creating Graphical User Interfaces (GUI) in PowerShell and using WinForms controls.
Forum rules
Do not post any licensing information in this forum.

Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
Locked
User avatar
jramon.r@tv3.cat
Posts: 21
Joined: Mon Jan 19, 2015 2:32 am

Fill Datagridview with data from an access

Post by jramon.r@tv3.cat » Wed Jun 12, 2019 4:48 am

I want to fill a Datagridview with data from an Access select

We have installed the v.5.6.164.0 64 bits

This code Works ok

$QueryString = 'select IP,NomServer from TaulaLlistatIP where (Part1=10)'
#Database Connection String


$ConnectionString = 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\snnewscpa2\web\llistatIP.mdb'

$command = New-Object System.Data.OleDb.OleDbCommand ($QueryString, $ConnectionString)
$adapter = New-Object System.Data.OleDb.OleDbDataAdapter ($command)


#Load the Dataset
$dataset = New-Object System.Data.DataSet
[void]$adapter.Fill($dataset)

#Use the Result Table as the DataSource
$datagridview1.DataSource = $dataset.Tables[0]
$datagridview1.CurrentCell = $null
#$ConnectionString.close()

But if in the select the condition where is with a variable it doesn't work. The code is

$a=10
$QueryString = 'select IP,NomServer from TaulaLlistatIP where (Part1=$a)'
#Database Connection String


$ConnectionString = 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\snnewscpa2\web\llistatIP.mdb'

$command = New-Object System.Data.OleDb.OleDbCommand ($QueryString, $ConnectionString)
$adapter = New-Object System.Data.OleDb.OleDbDataAdapter ($command)


#Load the Dataset
$dataset = New-Object System.Data.DataSet
[void]$adapter.Fill($dataset)

#Use the Result Table as the DataSource
$datagridview1.DataSource = $dataset.Tables[0]
$datagridview1.CurrentCell = $null
#$ConnectionString.close()
and the error is

ERROR: Exception calling "Fill" with "1" argument(s): "Error de sintaxis en la expresión de consulta '(Part1=$a)'."
LlistatIP.psf (430, 2): ERROR: At Line: 430 char: 2
ERROR: + [void]$adapter.Fill($dataset)
ERROR: + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ERROR: + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
ERROR: + FullyQualifiedErrorId : OleDbException
ERROR:



Thanks
Last edited by jramon.r@tv3.cat on Wed Jun 12, 2019 11:32 pm, edited 1 time in total.

User avatar
jvierra
Posts: 13810
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: Fill Datagridview with data from an access

Post by jvierra » Wed Jun 12, 2019 5:48 am

You need to use double quotes.

$QueryString ="'select IP,NomServer from TaulaLlistatIP where (Part1=$a)"

User avatar
jvierra
Posts: 13810
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: Fill Datagridview with data from an access

Post by jvierra » Wed Jun 12, 2019 5:53 am

If you are not using data binding then do not use an adapter. JUst load the data table object and assign.

Code: Select all

$queryStr = "select IP,NomServer from TaulaLlistatIP where (Part1=$a)"
$connStr = 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\snnewscpa2\web\llistatIP.mdb'

$cmd = New-Object System.Data.OleDb.OleDbCommand ($queryStr, $connStr)
$rdr = $cmd.ExecuteReader()

$dt = New-Object System.Data.DataTable
$dt.Load($rdr)
$cmd.Connection.Close()

$datagridview1.DataSource = $dt

User avatar
jramon.r@tv3.cat
Posts: 21
Joined: Mon Jan 19, 2015 2:32 am

Re: Fill Datagridview with data from an access

Post by jramon.r@tv3.cat » Thu Jun 13, 2019 2:09 am

jvierra wrote:
Wed Jun 12, 2019 5:48 am
You need to use double quotes.

$QueryString ="'select IP,NomServer from TaulaLlistatIP where (Part1=$a)"
It was my problem. Thank you. With double quotes it Works ok

Locked