none
conversion from type DBNULL to type integer RRS feed

  • Question

  • I am reading a null column and I am getting “conversion from type DBNULL to type integer is not allowed” I know was it saying but I do not know how to solve it. Thanks 

    And this part of the code get highlighted. MaxID = dr(”MAXIMUM”)   

     Dim maxID As Integer
            Dim strAbbreviation As String = LSet(txtSchAbbreviation.Text, 2)
            Dim strCity As String = LSet(txtSchCity.Text, 1)
            Dim strState As String = LSet(cboSchState.Text, 2)
            str = "SELECT MAX(MaxNumber) AS MAXIMUM FROM students"
            Dim cmd As OleDbCommand = New OleDbCommand(str, conn)
            dr = cmd.ExecuteReader
            While dr.Read()
    
                maxID = dr("MAXIMUM")

    • Moved by CoolDadTx Tuesday, January 16, 2018 5:23 PM VB related
    Tuesday, January 16, 2018 5:17 PM

Answers

  • If you are working with a Microsoft Access database then return 0 if MAX returns Null:

    SELECT IIF(IsNull(Max(MaxNumber)), 0, Max(MaxNumber)) As Maximum
    FROM students

    If you are working with another database then let us know so we can provide the proper SQL syntax.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by alobi Tuesday, January 16, 2018 9:04 PM
    Tuesday, January 16, 2018 6:32 PM

All replies

  • Hi

    Perhaps, if you put a breakpoint on that line and examine what 'dr' contains and if it has the expected contents. In particular, check if MAXIMUM is what it should be (looks like it is a NULL,causing the exception).If you put the breakpoint as suggested, then when reached, place the cursor over the 'dr' portion of the line and select QuickWatch from the menu - there you can view the data in a more manageable format.


    Regards Les, Livingston, Scotland

    Tuesday, January 16, 2018 5:35 PM
  • Your column Maxnumber is probably empty in all rows.

    Use an executescalar that is much easier it is made for your kind of code

    Instead of SQLClient use OleDB and instead of Cstr use CInt(TextObj) and some other small changes to tailor it to your needs.

    http://www.vb-tips.com/ExecuteScalarText.ASPX.


    Success Cor

    Tuesday, January 16, 2018 5:41 PM
  • If you are working with a Microsoft Access database then return 0 if MAX returns Null:

    SELECT IIF(IsNull(Max(MaxNumber)), 0, Max(MaxNumber)) As Maximum
    FROM students

    If you are working with another database then let us know so we can provide the proper SQL syntax.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by alobi Tuesday, January 16, 2018 9:04 PM
    Tuesday, January 16, 2018 6:32 PM
  • Hello,

    Try the following pattern

    Public Sub Example()
        Dim maxID As Integer = 0
        Using cn = New OleDbConnection("Your connection string")
            Using cmd = New OleDbCommand() With
                {
                    .Connection = cn,
                    .CommandText = "SELECT MAX(MaxNumber) AS MAXIMUM FROM students"
                }
    
                cn.Open()
    
                Dim reader As OleDbDataReader = cmd.ExecuteReader
                While reader.Read
                    If Not Convert.IsDBNull(reader("MAXIMUM")) Then
                        maxID = reader.GetInt32(0)
                    End If
                End While
            End Using
        End Using
    End Sub


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, January 16, 2018 6:40 PM
    Moderator
  • It is the first entry on this table,  so it suppose to be a null, Just do not know how to handle it.
    Tuesday, January 16, 2018 7:54 PM
  • I believe I am using Oledb and SQLClient look as this line of code "
    Dim cmd As OleDbCommand = New OleDbCommand(str, conn)" It Access db 

    Tuesday, January 16, 2018 7:58 PM
  • That is what I expected. Look at my sample but also what Paul wrote it is Access and I assume he is the best of us here in that. 

    Success Cor

    Tuesday, January 16, 2018 7:59 PM
  • Thanks, I try it and use I am working with access DB, my bads for not explaining that. thank
    Tuesday, January 16, 2018 8:01 PM
  • Thanks karen,

    I will go try this code and get back to the forum

    al

    Tuesday, January 16, 2018 8:02 PM
  • I believe I am using Oledb and SQLClient look as this line of code "
    Dim cmd As OleDbCommand = New OleDbCommand(str, conn)" It Access db 

    That is version VB7.0 kind of code, in VB7.1 it was already changed

    VBTips is our website, but to avoid to much writing I changed the code for you implementing also the part of Paul. 

    This is VB15 code (VB2017)

    Imports System.Data.OleDb
    Public Class Form1
        Private Maxnumber As Integer
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Try
                Using Con As New OleDbConnection("Data Source=YourServer;Initial Catalog=Northwind;Integrated Security=True")
                    Con.Open()
                    Using com As New OleDbCommand("SELECT IIF(IsNull(Max(MaxNumber)), 0, Max(MaxNumber)) As Maximum FROM students", Con)
                        Dim tObj = com.ExecuteScalar
                        If Not tObj Is Nothing AndAlso Not tObj Is DBNull.Value Then
                            Maxnumber = CInt(tObj)
                        End If
                    End Using
                End Using
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
        End Sub
    End Class

    Not tested


    Success Cor


    Tuesday, January 16, 2018 8:39 PM
  • Thanks
    Tuesday, January 16, 2018 9:04 PM