PowerShell MySQL query syntax

I want to insert data into my MySQL database using PowerShell.

My code is working, but I have a little problem with the variables in the insert string.

How can I use a variable in the insert string?

[system.reflection.assembly]::LoadWithPartialName("MySql.Data")
$cn = New-Object -TypeName MySql.Data.MySqlClient.MySqlConnection
$cn.ConnectionString = "SERVER=localhost;DATABASE=test;UID=root;PWD=pwd"
$cn.Open()
$cm = New-Object -TypeName MySql.Data.MySqlClient.MySqlCommand


#The problem, not working
$n = 7
$sql = 'INSERT INTO db1ea4test1.besitzer (besID, Vorname, Name) VALUES ( '$n' , "Testvor" + '$n' , "Testnach" + '$n')'

# Working
#$sql = 'INSERT INTO db1ea4test1.besitzer (besID, Vorname, Name) VALUES ("6", "Testvor", "Testnach")'


$cm.Connection = $cn
$cm.CommandText = $sql
$dr = $cm.ExecuteNonQuery()

Answers


Try using double quotes for sql string, single quotes around individual variables and ensuring your concatenated variables are quoted:

$sql = "INSERT INTO db1ea4test1.besitzer (besID, Vorname, Name) VALUES ( '$n' , 'Testvor$($n)' , 'Testnach$($n)')"

Need Your Help

C# : decode cookies

c# cookies decode

i got problem with libraries , now i write a program to read cookies and some cookies are being encoded so i need to decode them and i know that i must use the library System.Web.HttpUtility.UrlEnc...

Highlight only required field validation errors

asp.net validation requiredfieldvalidator

In one of our projects we want to show the "Required field validation error" by changing the border color of the corresponding textbox. our page contains a lot of required field validator,regular

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.