VB.Net ignores isDBNull condition

Question

I'm programming a dog adoption form. It retrieves data from a Access DB then the user can adopt up to three dogs, each one of them specified in 3 different fields. I'm doing it this way because I previously tried to do it with arrays, with no luck.

The issue comes here (highlighted in bold):

Try
        Dim conexion As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\PerrosDB.mdb;")
        conexion.Open()

        Dim cmd As New OleDb.OleDbCommand
        cmd.Connection = conexion
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "select adopcion1, adopcion2, adopcion3 from usuarios where codigo_usuario = " & FormPrincipal.codigo_usuario & ""
        Dim dr As OleDb.OleDbDataReader
        dr = cmd.ExecuteReader

        While dr.Read()

            **If dr.IsDBNull(1) Then
                posicionAdopcion = 1
            ElseIf dr.IsDBNull(2) Then
                posicionAdopcion = 2
            ElseIf dr.IsDBNull(3) Then
                posicionAdopcion = 3
            Else
                MsgBox("Lo sentimos, solo puedes hacer un máximo de 3 adopciones")
                Exit Sub**
            End If

        End While

        dr.Close()

        conexion.Close()
    Catch ex As Exception
        MsgBox(ex.Message & "Saliendo de la aplicación.")
        Me.Close()
    End Try

and

 Try

        Dim conexion As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\PerrosDB.mdb;")
        conexion.Open()

        Dim cmd As New OleDb.OleDbCommand
        cmd.Connection = conexion
        cmd.CommandType = CommandType.Text

        **If (posicionAdopcion = 1) Then
            cmd.CommandText = "UPDATE USUARIOS SET ADOPCION1 = '" & nombrePerro & "' WHERE codigo_usuario = " & FormPrincipal.codigo_usuario & ""
        ElseIf (posicionAdopcion = 2) Then
            cmd.CommandText = "UPDATE USUARIOS SET ADOPCION2 = '" & nombrePerro & "' WHERE codigo_usuario = " & FormPrincipal.codigo_usuario & ""
        ElseIf (posicionAdopcion = 3) Then
            cmd.CommandText = "UPDATE USUARIOS SET ADOPCION3 = '" & nombrePerro & "' WHERE codigo_usuario = " & FormPrincipal.codigo_usuario & ""
        End If**

        cmd.ExecuteNonQuery()
        conexion.Close()

    Catch ex As Exception
        MsgBox(ex.Message & "Saliendo de la aplicación...")
        Me.Close()

    End Try

What I'm trying to do is to check if the adoption fields (adopcion1, adopcion2, adopcion3) are empty, if they are, place the name of the dog there. If they are not, check for the next free slot. If none available, print the corresponding error message. But what the program does is to overwrite the adopcion1 (first field) no matter what.

I have checked this thread, I may be having a similar issue misunderstanding isDBNull usage, but so far I'm trying to do what it's stated there with no result.

What I'm doing wrong?

I dont see `IsDbNull` anywhere. Creating SQL from bits of string is very bad , you should use SQL parameters instead. Also, if they can do 3 dogs at once, `ElseIf` seems wrong - they arent mutually exclusive. — Ňɏssa Pøngjǣrdenlarp, Feb 03 '16 at 16:14
@Plutonix just updated the isDBNull issue. Can you elaborate on SQL parameters? Thanks. I'll also check that ElseIf, you're right. — xabi_sides, Feb 03 '16 at 16:18
You also appear to be using default form instances. If `FormPrincipal` is the class name for the form, then `FormPrincipal.codigo_usuario` may not reference the current/open form, but a new one which is blank. There are oodles of Q&A here on parameters: such as http://stackoverflow.com/a/29187199/1070452 — Ňɏssa Pøngjǣrdenlarp, Feb 03 '16 at 16:23
FormPrincipal.codigo_usuario refers to the code of the current user, retrieved from the DB, so it does have a value. I'll check the link provided, many thanks @Plutonix. — xabi_sides, Feb 03 '16 at 17:12
No, `FormPrincipal` appears to be a default form reference. Your problem may be that the query returns nothing because you are not using the form *instance* you think you are. — Ňɏssa Pøngjǣrdenlarp, Feb 03 '16 at 17:13

score 0 · Accepted Answer · answered Feb 03 '16 at 17:40

I got it, as I expected it was a silly mistake: I was retrieving the first data field from 1, and not from 0. Thus skipping it entirely:

If dr.isDBNull(0) Then
  posicionAdopcion = 1

But yes, the code seems clunky, didn't know about SQL parameters, going to check them ASAP.

Thanks for the help!

VB.Net ignores isDBNull condition

1 Answers1