Cannot convert Value - Powershell insert into sql

Question

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 ..."

9 characters into the email was the @ sign, though i get the same error when that attribute is null despite me assigning it empty string. — user1552172
– user1552172, Commented May 27, 2015 at 14:09
I am starting to think it is due to nulls, yet I am handling those with the if's. — user1552172
– user1552172, Commented May 27, 2015 at 14:37

Brian Reynolds · Accepted Answer · 2015-06-02 14:48:46Z

1

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

answered Jun 2, 2015 at 14:48

Brian Reynolds

4112 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Cannot convert Value - Powershell insert into sql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related