Invoke-SqlCmd variable not defined, even though it is?

PowerShell Help
1

Hello everyone, new user here, and (somewhat) new-ish to PowerShell. I have an issue that I’m hoping to get help with: I’m trying to insert a record into a database using Invoke-SqlCmd, but it’s giving me the following error:

Invoke-Sqlcmd : Variable UserId is not defined.
At C:\Scripts\ImportFileRecord.ps1:86 char:4

I’m not sure why it’s saying it’s not defined. I’ve specifically defined it below, and I’ve even just tried passing in a number in the query itself. If I remove the UserId from the query, it then fails on the next bit, UploadedBy.

I’m unsure of what’s going on here. All the data is there when I output it in the code block below.

Can someone assist me with understanding what I’m missing?


			$UserId = 025;
			$UploadedBy = "Admin";
			$FileViews = 1;
			$DateTime = Get-Date -Format "yyyy-MM-dd HH:mm:ss";
			$FileName = $file.Name;

			$FileLocation = "\\server\appFolder"
			$FileExtension = $file.Extension.Substring(1, 3);
			$FileSize = $file.Length;
			$FileHash = Get-FileHash -Path $file.FullName -Algorithm SHA256;

			# Connection string for Invoke-Sqlcmd
			$invokeConnectionString = "Data Source=server;Integrated Security=True;Initial Catalog=database"; 


			Write-Output "UserId: $($UserId)"
			Write-Output "UploadedBy: $($UploadedBy)"
			Write-Output "FileViews: $($FileViews)"
			Write-Output "DateTime: $($DateTime)"
			Write-Output "FileName: $($FileName)"
			Write-Output "FileLocation: $($FileLocation)"
			Write-Output "FileExtension: $($FileExtension)"
			Write-Output "FileSize: $($FileSize)"
			Write-Output "FileHash: $($FileHash)"
			Write-Host "QUERY: ($($UserId), $($UploadedBy), $($FileViews), '$($DateTime)', '$($FileName)', '$($FileLocation)', '$($FileExtension)', $($FileSize), '$($FileHash)')`n`n"
			
			Invoke-Sqlcmd -ConnectionString $invokeConnectionString -Query "INSERT INTO [DATABASE].[TABLE] (UserId, UploadedBy, FileViews, DateTime, FileName, FileLocation, FileExtension, FileSize, FileHash) VALUES (`$(UserId)`, '`$(UploadedBy)`', `$(FileViews)`, '`$(DateTime)`', '`$(FileName)`', '`$(FileLocation)`', '`$(FileExtension)`', `$(FileSize)`, '`$(FileHash)`')"
2

Hi, welcome to the forum :wave:

I can’t test this, but the way you’re specifying the values looks wrong to me. The backtick in PowerShell ` is the escape character so the value `$(UserId)` is being passed literally as $(UserId) the string.

If you needed to use the sub-expression operator $() which I don’t think you do in this case, then the variable name should be preceded by a $: $($UserId).

Try specifying your values as:

('$UserId', '$UploadedBy', '$FileViews', '$DateTime', '$FileName', '$FileLocation', '$FileExtension', '$FileSize', '$FileHash')

For readability, you may consider crafting your queries as a here-string:

$Query = @"
INSERT INTO [DATABASE].[TABLE] (
UserId,
UploadedBy,
FileViews,
DateTime,
FileName,
FileLocation,
FileExtension,
FileSize,
FileHash
)
VALUES (
'$UserId',
'$UploadedBy',
'$FileViews',
'$DateTime',
'$FileName',
'$FileLocation',
'$FileExtension',
'$FileSize',
'$FileHash'
)
"@

Invoke-Sqlcmd -ConnectionString $invokeConnectionString -Query $Query
3

Thank you very much for the reply! I wasn’t aware of here-strings, but after reading up on them, that’ll definitely make things a little easier when reading :wink:

It definitely helped with troubleshooting. I had to use the sub-expression operator and put all string values in single quotes.

VALUES (
'$($UserId)',
'$($UploadedBy)',
$($FileViews),
'$($DateTime)',
'$($FileName)',
'$($FileLocation)',
'$($FileExtension)',
$($FileSize),
'$($FileHash)'
)

Thank you very much for your help!