I am trying to create an SQL statement using user-supplied data. I use code similar to this in C#:

var sql = "INSERT INTO myTable (myField1, myField2) " +
          "VALUES ('" + someVariable + "', '" + someTextBox.Text + "');";

var cmd = new SqlCommand(sql, myDbConnection);
cmd.ExecuteNonQuery();

and this in VB.NET:

Dim sql = "INSERT INTO myTable (myField1, myField2) " &
          "VALUES ('" & someVariable & "', '" & someTextBox.Text & "');"

Dim cmd As New SqlCommand(sql, myDbConnection)
cmd.ExecuteNonQuery()

However,

• this fails when the user input contains single quotes (e.g. O'Brien),
• I cannot seem to get the format right when inserting DateTime values and
• people keep telling me that I should not do this because of "SQL injection".

How do I do it "the right way"?