Data Mismatch Error in VB.net SQL statement

I have this code:

Protected Sub unlikebtn_Click(sender As Object, e As EventArgs) Handles unlikebtn.Click
    Dim strSQL As String
    Dim MemberDataConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; data source=" & Server.MapPath("database/Database.mdb"))
    Dim MemberCommand As New OleDbCommand(strSQL, MemberDataConn)
    MemberDataConn.Open()

    MemberCommand.Connection = MemberDataConn
    MemberCommand.CommandText = "DELETE FROM LIKES WHERE  user_id ='" & Session("user_id") & "' AND post_id = '" & Session("post_id") & "'"
    MemberCommand.ExecuteNonQuery()
    MemberDataConn.Close()


    likebtn.Visible = True
    unlikebtn.Visible = False
End Sub

When I run it, I get an error on the .ExecuteNonQuery():

System.Data.OleDb.OleDbException: ‘Data type mismatch in criteria expression.

I don’t think the problem is the data-types in the database…

Answer

It is (almost) always a bad idea to concatenate values into an SQL command. Instead, you should use SQL parameters to pass the values.

Also, instances of some classes, such as an OleDbConnection, use unmanaged resources and you have to tell the .NET Framework to dispose of those resources when you’ve finished using them. You can either call the .Dispose() method yourself or use the Using construct. The latter is tidier as it will take care of the disposal even if there was a problem.

So your code could look like:

Dim strSQL As String = "DELETE FROM LIKES WHERE user_id = ? AND post_id = ?"

Using conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; data source=" & Server.MapPath("database/Database.mdb"))
    Using sqlCmd As New OleDbCommand(strSQL, conn)

        'TODO: set the OleDbType to match the columns in the database '
        sqlCmd.Parameters.Add(New OleDbParameter With {
                              .ParameterName = "@UserId",
                              .OleDbType = OleDbType.VarWChar,
                              .Size = 32,
                              .Value = CStr(Session("user_id"))})

        sqlCmd.Parameters.Add(New OleDbParameter With {
                              .ParameterName = "@PostId",
                              .OleDbType = OleDbType.Integer,
                              .Value = CInt(Session("post_id"))})

        conn.Open()
        sqlCmd.ExecuteNonQuery()
        conn.Close()

    End Using
End Using

likebtn.Visible = True
unlikebtn.Visible = False

The ?s in the SQL command are placeholders for the parameters. The parameter names aren’t used, but it makes it easier to see what is what in the code. Because the names aren’t used, the parameters must be added in the same order as the corresponding placeholders. You will need to adjust the types of the parameters (and the sizes for strings) to match the column types in the database.

Incidentally, if the site is to run under IIS, then you could put the database in the App_Data directory as nothing in there will be served to a client, by default. Also, SQL Server is a more suitable database for a multi-user environment – you can use the Express edition for free.