none
Get User Input From PowerShell Window RRS feed

  • Question

  • I have a powershell window which will prompt the user to input a first and last name, then a query is run, and all users in a table with that same first and last name are returned.  What I am wanting to happen from here is each returned user be assigned a number 1, 2, 3, 4 etc and the user be able to input a number and further processing occur.  This is my current syntax, how would this be edited to allow such?

    $fullname = Read-Host "Please Enter First and Last Name"
    
    $firstname = $fullname.Split(' ')[0]
    $lastname = $fullname.Split(' ')[1]
    
    
    $QueryString = "Select firstname + ' ' + lastname from employees where firstname='$firstname' and lastname='$lastname'"
    
    $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 3:47 PM

Answers

  • Use this:

    $query=@"
        SELECT
            ROW_NUMBER() OVER(ORDER BY FirstName+' '+ lastname) AS Row,
            FirstName + ' ' + LastName  AS Fullname
        FROM Employees
       Where
           LastName='$lastname' AND FirstName='$firstname'
    "@


    \_(ツ)_/

    • Marked as answer by IndigoMontoya Wednesday, July 8, 2015 5:20 PM
    Wednesday, July 8, 2015 4:15 PM

All replies

  • Use this:

    $query=@"
        SELECT
            ROW_NUMBER() OVER(ORDER BY FirstName+' '+ lastname) AS Row,
            FirstName + ' ' + LastName  AS Fullname
        FROM Employees
       Where
           LastName='$lastname' AND FirstName='$firstname'
    "@


    \_(ツ)_/

    • Marked as answer by IndigoMontoya Wednesday, July 8, 2015 5:20 PM
    Wednesday, July 8, 2015 4:15 PM
  • That assigns a row number to the name as I was requesting.  What if I wanted to use that row number for further processing in my script such as my next line of syntax would then be

    Write-Host "Please enter the number of the employee to update"

    How could the rownumber then be used?

    Wednesday, July 8, 2015 5:36 PM
  • I showed you how to add row numbers.  You need a unique ID to reselect an exact record.  That is a new question.


    \_(ツ)_/

    Wednesday, July 8, 2015 5:43 PM