How to try and catch in Powershell with oledb

Hello,

I was wondering how I could Try and Catch or Trap an error when using oledb in Powershell. I have a problem with the following line :

$command = New-Object System.Data.OleDb.OleDbCommand $sqlcommand,$connection

For some reasons, if I change manually the $sqlcommand to my actual SQL command the rest of the script works fine. If I print the value of $sqlcommand, I get the right thing (the actual SQL command I entered manually to make it work) so I know its value is OK.

Any idea how I could try and catch this ?
Function Global:SQL\Invoke-Command ([string] $DataSource, [string] $Database, [string] $SQLCommand, [System.Management.Automation.PsCredential] $Credential) {
# // Prepare the authentication information. By default, we pick Windows authentication
$authentication = Integrated Security=SSPI;
# // If the user supplies a credential, then they want SQL authentication
if($credential)
{
$plainCred = $credential.GetNetworkCredential()
$authentication =
(uid={0};pwd={1}; -f $plainCred.Username,$plainCred.Password)
}
# // Prepare the connection string out of the information they provide
$connectionString = Provider=sqloledb;  +
Data Source=$dataSource;  +
Initial Catalog=$database;  +
$authentication; 
# // Connect to the data source and open it
$connection = New-Object System.Data.OleDb.OleDbConnection $connectionString
$command = New-Object System.Data.OleDb.OleDbCommand $sqlcommand,$connection
$connection.Open()
# // Fetch the results, and close the connection
$adapter = New-Object System.Data.OleDb.OleDbDataAdapter $command
$dataset = New-Object System.Data.DataSet
[void] $adapter.Fill($dataset)
$connection.Close()
# // Return the dataset
Return $dataset

Open in new window

tech_ci_sherbeAsked:
Who is Participating?
 
VeeNashiCommented:
try this..


trap [Exception]
      {
            write-error $("TRAPPED: " + $_.Exception.Message);
            continue;
      }

Function Global:SQL\Invoke-Command ([string] $DataSource, [string] $Database, [string] $SQLCommand, [System.Management.Automation.PsCredential] $Credential) { 
trap [Exception] 
	{
		write-error $("TRAPPED: " + $_.Exception.Message);
		continue;
	}
 
# // Prepare the authentication information. By default, we pick Windows authentication 
$authentication = Integrated Security=SSPI; 
# // If the user supplies a credential, then they want SQL authentication 
if($credential) 
{ 
$plainCred = $credential.GetNetworkCredential() 
$authentication = 
(uid={0};pwd={1}; -f $plainCred.Username,$plainCred.Password) 
} 
# // Prepare the connection string out of the information they provide 
$connectionString = Provider=sqloledb;  + 
Data Source=$dataSource;  + 
Initial Catalog=$database;  + 
$authentication;  
# // Connect to the data source and open it 
$connection = New-Object System.Data.OleDb.OleDbConnection $connectionString 
$command = New-Object System.Data.OleDb.OleDbCommand $sqlcommand,$connection 
$connection.Open() 
# // Fetch the results, and close the connection 
$adapter = New-Object System.Data.OleDb.OleDbDataAdapter $command 
$dataset = New-Object System.Data.DataSet 
[void] $adapter.Fill($dataset) 
$connection.Close() 
# // Return the dataset 
Return $dataset

Open in new window

0
 
tech_ci_sherbeAuthor Commented:
Thanks, that's exactly what I was looking for. For some odd reasons, my script started working even though I let the $sqlcommand. It still helps me troubleshoot further though. Thanks a lot I should have asked earlier instead of wasting plenty of time :P <3 expert exchange.
0
 
tech_ci_sherbeAuthor Commented:
Just wanted to mention that I solved the mystery behind the SQL command. The single quotes between the variable weren't the good one.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.