title: Powershell - Calling Stored Procedure - Works with System.Data.SqlClient.SqlConnection but not with System.Data.Odbc.OdbcConnection
body: <p>I have a strange problem. I have a powershell script that I've been trying to get to work with an ODBC connection after having been designed to work with a direct SQLConnection. </p>
  
  <p>When $odbc is false, this code runs fine, when it is $true I get the following error:</p>
  
  <pre><code>Exception calling "Fill" with "1" argument(s): "ERROR [42000] [Microsoft][SQL Server Native Client 10.0][SQL Server]Procedure or function 'External_DeficiencyListAll' expects parameter '@startTime', which was not supplied." At C:\Users\afreeberg\Documents\Hyland\Trinitas\Deficiency Polling Powershell\Deficiency_Polling.ps1:317 char:19 +     $dataAdapter.Fill &lt;&lt;&lt;&lt; ($results[0]) | Out-Null     + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException     + FullyQualifiedErrorId : DotNetMethodException
  </code></pre>
  
  <p>Here is the code:</p>
  
  <pre><code>if ($odbc)
  {
      LogWrite("Using ODBC Connection")
      $odbcConnection = New-Object System.Data.Odbc.OdbcConnection
      $odbcConnection.ConnectionString = $odbcString
      $odbcCommand = $odbcConnection.CreateCommand()
      $odbcCommand.CommandType = [System.Data.CommandType]::StoredProcedure
      $odbcCommand.CommandText = "[dbo].[External_DeficiencyListAll]"
      $odbcCommand.Parameters.AddWithValue("@startTime", $startTime) | Out-Null
      $outParameter = New-Object System.Data.Odbc.OdbcParameter
      $outParameter.ParameterName = "@nowTime"
      $outParameter.Direction = [System.Data.ParameterDirection]::Output
      $outParameter.DbType = [System.Data.DbType]::DateTime
      $odbcCommand.Parameters.Add($outParameter) | Out-Null
      $dataAdapter = New-Object System.Data.ODBC.OdbcDataAdapter $odbcCommand
  
  }
  else
  {
      LogWrite ("Using SQL Connection")
      $sqlConnection = New-Object System.Data.sqlClient.SqlConnection
      $sqlConnection.ConnectionString = $connString
      $sqlCommand = $sqlConnection.CreateCommand()
      $sqlCommand.CommandType = [System.Data.CommandType]::StoredProcedure
      $sqlCommand.CommandText = "[dbo].[External_DeficiencyListAll]"
      $sqlCommand.Parameters.AddWithValue("@startTime", $startTime) | Out-Null
      $outParameter = New-Object System.Data.SqlClient.SqlParameter
      $outParameter.ParameterName = "@nowTime"
      $outParameter.Direction = [System.Data.ParameterDirection]::Output
      $outParameter.DbType = [System.Data.DbType]::DateTime
      $sqlCommand.Parameters.Add($outParameter) | Out-Null
      $dataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $sqlCommand
  }
  
  LogWrite ("Querying for deficiencies updated since: " + $startTime)
  #Initialize Dataset object to hold results from stored procedure query
  $results = @()
  $results += @(New-Object System.Data.Dataset)
  
  
  #Execute query using $dataAdapter and filling $results
  $dataAdapter.Fill($results[0]) | Out-Null
  
  $results += @($sqlCommand.Parameters["@nowTime"].Value)
  
  #return $results
  $results
  </code></pre>
  
  <p>Important parameters:</p>
  
  <pre><code>$odbcString = "DSN=defPolLTest" Corresponds to an ODBC connection set up to point to the SQL database referred to in $connString
  
  $connString = 'Server=ONE-017125\SQLEXPRESS;Database=DeficiencyPolling;User Id=dpolling;Password=password;'
  
  $startTime corresponds to the last run time of the script, stored in the registry. Is a DateTime value.
  </code></pre>
  
  <p>So in the case of using a SQLConnection, the parameter is recognized, but it does not when using an OdbcConnection...</p>