Vista - Multiple ExecuteReaders in same Script

Hello

I made a script, which gets me driver informations from different servers.
Now i have the problem that I have multiple SQL-DataReaders with
ExecuteReader().

This gives me these Errors:

********************************************
HP ProLiant Advanced System Management Controller
5.37.0.0
-------------------------------------
Exception calling "ExecuteReader" with "0" argument(s): "Diesem Befehl ist
bereits ein geöffneter DataReader zugeordnet
, der zuerst geschlossen werden muss."
At line:21 char:55
--> in english something like: There is already an open DataReader
associated with this command which first has to be closed

+ $DataReader2 = $cmd2.ExecuteReader <<<< ()
You cannot call a method on a null-valued expression.
At line:22 char:41
+ if($DataReader2.Read <<<< () -eq "True"){


I read on the internet that there is only one open query per connection
allowed...
So how could i rebuild my script, does anyone have any ideas?

thanks for help!
greetings

---------------------------------------------------------------
Here the script:

$sqlconn = new-object
system.data.SqlClient.SQLConnection("Server=xxxxx;Database=Driver_DB;Integrated Security=True");
$sqlconn.open()
$kitdrivers = "test1","test2","test3"

$cmd = new-object "System.Data.SqlClient.SqlCommand"("SELECT * FROM
tbl_Nodes",$sqlconn)
$cmd.CommandTimeout = 0
$rs = $cmd.ExecuteReader()
$pingnok = ""
while($rs.read() -eq "True"){
$nodename = $rs.Item("NodeName_Name")
$pinge03 = Get-WmiObject Win32_PingStatus -filter "Address='$nodename-e03'"
| select protocoladdress, statuscode
$pings03 = Get-WmiObject Win32_PingStatus -filter "Address='$nodename-s03'"
| select protocoladdress, statuscode
$pingx64 = Get-WmiObject Win32_PingStatus -filter "Address='$nodename-x64'"
| select protocoladdress, statuscode
if($pinge03.statuscode -eq "0"){
$nodename = $nodename+"-e03"
$PnP = Get-WMIObject Win32_PnPSignedDriver -computer $nodename
write-host "********************************************"
Foreach ($driver in $PnP){
$devicename = $driver.Devicename
$deviceversion = $driver.DriverVersion
if(!$devicename){continue}
foreach($drivername in $kitdrivers){
if($devicename.Startswith($drivername)){
write-host $devicename
$driver.DriverVersion
write-host "-------------------------------------"
$cmd2 = new-object "System.Data.SqlClient.SqlCommand"("SELECT * FROM
tbl_Server_Objects WHERE NodeName_Name='$server' AND
Object_Name='$devicename'",$sqlconn)
$cmd2.CommandTimeout = 0
$DataReader2 = $cmd2.ExecuteReader()
if($DataReader2.Read() -eq "True"){
$DataReader2.Close()
$sqlcmd = $sqlconn.CreateCommand()
$Sqlcmd.CommandText = "UPDATE tbl_Server_Objects SET
Object_Version='$deviceversion' WHERE NodeName_Name='$server' AND
Object_Name='$devicename'"
$sqlcmd.ExecuteNonQuery()
}else{
$DataReader2.Close()
$sqlcmd = $sqlconn.CreateCommand()
$Sqlcmd.CommandText = "INSERT tbl_Server_Objects VALUES
('$server','$devicename','$deviceversion')"
$sqlcmd.ExecuteNonQuery()
}
}
}
}
}elseif($pings03.statuscode -eq "0"){
$nodename = $nodename+"-s03"
$PnP = Get-WMIObject Win32_PnPSignedDriver -computer $nodename
write-host "********************************************"
Foreach ($driver in $PnP){
$devicename = $driver.Devicename
$deviceversion = $driver.DriverVersion
if(!$devicename){continue}
foreach($drivername in $kitdrivers){
if($devicename.Startswith($drivername)){
write-host $devicename
$driver.DriverVersion
write-host "-------------------------------------"
$cmd2 = new-object "System.Data.SqlClient.SqlCommand"("SELECT * FROM
tbl_Server_Objects WHERE NodeName_Name='$server' AND
Object_Name='$devicename'",$sqlconn)
$cmd2.CommandTimeout = 0
$DataReader2 = $cmd2.ExecuteReader()
if($DataReader2.Read() -eq "True"){
$DataReader2.Close()
$sqlcmd = $sqlconn.CreateCommand()
$Sqlcmd.CommandText = "UPDATE tbl_Server_Objects SET
Object_Version='$deviceversion' WHERE NodeName_Name='$server' AND
Object_Name='$devicename'"
$sqlcmd.ExecuteNonQuery()
}else{
$DataReader2.Close()
$sqlcmd = $sqlconn.CreateCommand()
$Sqlcmd.CommandText = "INSERT tbl_Server_Objects VALUES
('$server','$devicename','$deviceversion')"
$sqlcmd.ExecuteNonQuery()
}
}
}
}
}elseif($pingx64.statuscode -eq "0"){
$nodename = $nodename+"-x64"
$PnP = Get-WMIObject Win32_PnPSignedDriver -computer $nodename
write-host "********************************************"
Foreach ($driver in $PnP){
$devicename = $driver.Devicename
$deviceversion = $driver.DriverVersion
if(!$devicename){continue}
foreach($drivername in $kitdrivers){
if($devicename.Startswith($drivername)){
write-host $devicename
$driver.DriverVersion
write-host "-------------------------------------"
$cmd2 = new-object "System.Data.SqlClient.SqlCommand"("SELECT * FROM
tbl_Server_Objects WHERE NodeName_Name='$server' AND
Object_Name='$devicename'",$sqlconn)
$cmd2.CommandTimeout = 0
$DataReader2 = $cmd2.ExecuteReader()
if($DataReader2.Read() -eq "True"){
$DataReader2.Close()
$sqlcmd = $sqlconn.CreateCommand()
$Sqlcmd.CommandText = "UPDATE tbl_Server_Objects SET
Object_Version='$deviceversion' WHERE NodeName_Name='$server' AND
Object_Name='$devicename'"
$sqlcmd.ExecuteNonQuery()
}else{
$DataReader2.Close()
$sqlcmd = $sqlconn.CreateCommand()
$Sqlcmd.CommandText = "INSERT tbl_Server_Objects VALUES
('$server','$devicename','$deviceversion')"
$sqlcmd.ExecuteNonQuery()
}
}
}
}
}else{
write-host "********************************************"
write-host "ping NOK $nodename"
write-host "********************************************"
$pingnok = $pingnok+$nodename+","
}
}

$rs.close()
$sqlconn.close()

you can set a property in the connection string to allow more connectioins
on one datareader
add this to your coonectionstring :

MultipleActiveResultSets=True

Enjoy,
Greetings /\/\o\/\/
http://thePowerShellGuy.com

"Wilson" <Wilson@xxxxxx> wrote in message
news:5CC0A655-F21B-43A8-80B9-A1D01C059343@xxxxxx

But i need to have multiple datareaders on one(or multiple) connection string
:-S
Is this somehow also possible?


"/\\/\\o\\/\\/" wrote:

Hi Wilson,

MSDN describes:
While the SqlDataReader is being used, the associated SqlConnection is busy
serving the SqlDataReader, and no
other operations can be performed on the SqlConnection other than closing
it. This is the case until the Close
method of the SqlDataReader is called. For example, you cannot retrieve
output parameters until after you call Close.

You can create another sqlconnection object and pass it to the cmd2. Or call
SqlDataAdapter and fill a DataSet contained tbl_Nodes infomations.

Tao Ma

"Wilson" <Wilson@xxxxxx> дÈëÏûÏ¢ÐÂÎÅ:5CC0A655-F21B-43A8-80B9-A1D01C059343@xxxxxx

Hi Tao

Thanks for your information.
I changed my script by adding a second sqlconnection and now it works!

greetings


"Tao Ma" wrote: