CODEWITHSUNDEEP
sqlpowershell
user1552172
user1552172

Reputation: 676

Cannot convert Value - Powershell insert into sql

I have a script only i run that takes users from AD and puts their info into a database. This script piece fails to insert email addresses I think due to the @ sign. How can i fix this?

    $_.samaccountname  | Get-QADGroupMember -type user -SizeLimit 0 -DontUseDefaultIncludedProperties -IncludedProperties samaccountname,DN | Select samaccountname,FirstName,LastName,Email,Manager,Office,Title,HomeDirectory,HomeDrive,PasswordLastSet,PasswordAge,PasswordExpires,AccountIsDisabled,AccountIsLockedOut,PasswordNeverExpires,Domain,Notes,CreationDate,ModificationDate,Type,Description,DN | foreach{
    if (!$_.samaccountname){$samaccountname = ''}   ELSE{$samaccountname = $_.samaccountname}
    if (!$_.FirstName){$FirstName = ''}             ELSE{$FirstName = $_.FirstName}
    if (!$_.LastName){$LastName = ''}               ELSE{$LastName = $_.LastName}
    if (!$_.Email){$Email = ''}                     ELSE{$Email = $_.Email}
    if (!$_.Manager){$Manager = ''}                 ELSE{$Manager = $_.Manager}
    if (!$_.Office){$Office = ''}                   ELSE{$Office = $_.Office}
    if (!$_.Title){$Title = ''}                     ELSE{$Title = $_.Title}
    if (!$_.HomeDirectory){$HomeDirectory = ''}     ELSE{$HomeDirectory = $_.HomeDirectory}
    if (!$_.HomeDrive){$HomeDrive = ''}             ELSE{$HomeDrive = $_.HomeDrive}
    if (!$_.PasswordExpires){$PasswordExpires = ''} ELSE{$PasswordExpires = $_.PasswordExpires}
    if (!$_.Notes){$Notes = ''}                     ELSE{$Notes = $_.Notes}
    if (!$_.Description){$Description = ''}         ELSE{$Description = $_.Description}

    $cmd.CommandText = "INSERT dbo.Users (AccountName,FirstName,LastName,Email,Manager,Office,Title,HomeDirectory,HomeDrive,PasswordLastSet,PasswordAge,PasswordExpires,AccountIsDisabled,AccountIsLockedOut,PasswordNeverExpires,Domain,Notes,CreationDate,ModificationDate,Type,Description) VALUES ('"+$samaccountname+"','"+$FirstName+"','"+$LastName+"','"+$Email+"','"+$Manager+"','"+$Office+"','"+$Title+"','"+$HomeDirectory+"','"+$HomeDrive+"','"+$_.PasswordLastSet+"','"+$_.PasswordAge+"','"+$PasswordExpires+"','"+$_.AccountIsDisabled+"','"+$_.AccountIsLockedOut+"','"+$_.PasswordNeverExpires+"','"+$_.Domain+"','"+$Notes,+"','"+$_.CreationDate+"','"+$_.ModificationDate+"','"+$Description+"','"+$_.DN+"')"
    $cmd.ExecuteNonQuery() | out-null

Error I am getting

"Cannot convert value "','" to type "System.Int32". Error: "Input string was not in a correct format." At C:\Users\rld0072\Desktop\Group-Users.ps1:28 char:9 + $cmd.CommandText = "INSERT dbo.Users (AccountName,FirstName,LastName,Ema ..."

Upvotes: 0

Views: 522

Answers (1)

Brian Reynolds
Brian Reynolds

Reputation: 411

There's a typo in your $cmd.CommandText line. $Notes has an extra comma after it.

Upvotes: 1

Related Questions