Fill Datagridview with data from an access

[jramon.r@tv3.cat]

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

[jvierra]

You need to use double quotes.

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

[jvierra]

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

[jramon.r@tv3.cat]

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