none
Select Returned Record From Row Number RRS feed

  • Question

  • I am returning row number and name from my database.  What I am wanting now is to be able to input the rownumber and have it apply to the returned name.  How could I achieve such?

    $fullname = Read-Host "Please Enter First and Last Name"
    
    $firstname = $fullname.Split(' ')[0]
    $lastname = $fullname.Split(' ')[1]
    
    
    $QueryString = @"
        SELECT 
            ROW_NUMBER() OVER(ORDER BY FirstName+' '+ lastname) AS Row, 
            FirstName + ' ' + LastName  AS Fullname
        FROM Employees
       Where 
           LastName='$lastname' AND FirstName='$firstname'
    "@
    $server = "server01"
    $database = "table"	
    $connectionTemplate = "Data Source={0};Integrated Security=SSPI;Initial Catalog={1};"
    $connectionString = [string]::Format($connectionTemplate, $server, $database)
    $connection = New-Object System.Data.SqlClient.SqlConnection
    $connection.ConnectionString = $connectionString
    $command = New-Object System.Data.SqlClient.SqlCommand
    $command.CommandText = $QueryString
    $command.Connection = $connection
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $command
    $DataSet = New-Object System.Data.DataSet
    $SqlAdapter.Fill($DataSet)
    $Table = New-Object data.datatable
    $Table = $DataSet.tables[0]
    #$DataSet.tables[0].rows.count
    foreach ($Row in $DataSet.Tables[0].Rows)
    { 
      Write-Host "Here Is A List of employees with that name: $($Row[0])"
    }
    $Connection.Close()

    Wednesday, July 8, 2015 5:49 PM

Answers

  • The row number is derived and only applies to the returned dataset.  If you neo retrieve the actual data row you will need to include the key in the resultset. and use  that to retrieve the row.

    Display a list of row numbers, names, key values and hav euser pick one.  Use rownumber to extract the key value from the dataset.


    \_(ツ)_/

    • Marked as answer by IndigoMontoya Wednesday, July 8, 2015 7:50 PM
    Wednesday, July 8, 2015 7:42 PM