Having an issue inserting data into Postgresql using Npgsql and VB.net

Question

Can someone please look at my code and possibly point me to why it is not allowing me to insert data into my Postgres database? I'm creating a Comic Book database for my collection.

Everytime I click my submit button to submit the data entered, the debugger throws an exception:

'An unhandled exception of type 'Npgsql.PostgresException' occurred in Npgsql.dll'

Which happens on the execution of myCommand.ExecuteNonQuery() function.

I've spent my day trying to figure this out, I am a complete noob at this. Any guidance would be awesome!

Dim myConnection As NpgsqlConnection = New NpgsqlConnection()
        Dim myCommand As NpgsqlCommand
        Dim mySQLString As String
        myConnection.ConnectionString = "Server=localhost;Port=5432;Database=ComicsDatabase;User Id=postgres;Password=xxxxxxxx;"

        mySQLString = "INSERT INTO Comics (IssueName,IssueNumber,PublicationDate,Publisher,IsVariant) VALUES (" & comicName & "," & issueNumber & "," & publicationDate & "," & publisher & "," & isVariant & ");"
        myCommand = New NpgsqlCommand(mySQLString, myConnection)
        myConnection.Open()
        myCommand.ExecuteNonQuery()
        myConnection.Close()

Answer 1

When you concatenate strings as above to form your sql command it is very easy to fall in common errors. In your code, for example, a string value should be enclosed between single quotes.

So, supposing that IssueName is a string type field, you should express the value in this way

.... VALUES ('" & comicName & "'," & ....

But this is a remedy worse than the illness. First you will have another problem if your comicName variable contains a single quote, second the concatenation of strings is the main way that leads to Sql Injection (a very dangerous code vulnerability)

The only correct way to pass a value to a database engine (...any database engine of this world) is through a parameterized query

You write the query putting parameter placeholders instead of directly the values (No string concatenation, no weird & and single quotes....)

  mySQLString = "INSERT INTO Comics 
  (IssueName,IssueNumber,PublicationDate,Publisher,IsVariant) 
  VALUES (:comicName,:issueNumber,:publicationDate,:publisher,:isVariant);"

And then you pass the value using a parameter added to the command parameters collection

  myCommand = New NpgsqlCommand(mySQLString, myConnection)
  myCommand.Parameters.Add(":comicName", NpgsqlDbType.Varchar).Value = comicName

Of course you need to add all the other parameters required by the placeholders, the important thing to keep in mind is to use the correct NpgsqlDbType for the specific column that you are trying to update.