Getting problem with Null Value

[singhSP]

Front End : VB 2008
Back End : Access 2003

Example of Database Record:
cCode | Name | Company | Contact
20091 | ABC | XYZ | 123
20101 | AB1 | XYZ1 | 456
20102 | AB1 | XY123 | 987

I am using year for making a unique code for person.
Eg: 1 person joined in year 2009 so only one entry with code 20091, 2 join in 2010 so two codes 20101, 20102

'Combo Box Used for Selecting Year Starting from 2007
Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
            cboYear.Items.Clear()
            Dim i As Single
            For i = 2007 To Year(Now)
                cboYear.Items.Add(i)
            Next
            cboYear.SelectedIndex = cboYear.MaxDropDownItems - 1
End Sub

'Calling Database on Combo Box Change
Private Sub cboYear_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboYear.SelectedIndexChanged
        cCode()
End Sub

' Calling from Combo Box Change
Private Sub cCode()
        Try
            If con.State = ConnectionState.Open Then con.Close()
            con.Open()

            com = New OleDbCommand("Select max(val(mid(cCode,5,len(cCode)))) as maxV from Contact where val(left(cCode,4)) = " & Val(cboYear.SelectedItem), con)
            reader = com.ExecuteReader(CommandBehavior.CloseConnection)


            If reader.read Then
                txt.Text = Val(cboYear.SelectedItem) & (reader("maxV") + 1)
            Else
                txt.Text = Val(cboYear.SelectedItem) & 1
            End If
            reader.Close()

        Catch ex As Exception
            If con.State = ConnectionState.Open Then
                con.Close()
            End If
        End Try
End Sub

Also tried

If reader("maxV") Is DBNull.Value Then

Above code works if any existing value is there for any given year, if 20091 is there then it show new value as 20092. But when selecting 2011 from Combo Box then it do now show any changes.

Wants it to check for the highest value with any year and then increase the value by 1, if no record is found for the year then create a new record like YEAR1.

During runtime checked that maxV is showing System.DBNull while selecting a New Year which is not in database.

Please provide some information on the matter.

[Teme64]

Could you use a second table (YearTable) to hold year and count information:

YearCode YearCount
2009     1
2010     2

Now you could SELECT MAX(YearCount) AS YearVal FROM YearTable WHERE YearCode = 2010 Also SELECT MAX(YearCount) AS YearVal FROM YearTable WHERE YearCode = 2011 would return
YearVal = DbNull. If YearVal = DbNull then you could add a row: 2011 0
I think this would be the easiest solution.

HTH

[lolafuertes]

Agreed with Teme64. IMO is the best solution.

But if you can not change the database design for any reson, if I remember well, in Access you can force to return 0 when not found using the immediate if function (IIF):

("Select iif(max(val(mid(cCode,5,len(cCode)))=Null, 0, max(val(mid(cCode,5,len(cCode))))) as maxV from Contact where val(left(cCode,4)) = " & Val(cboYear.SelectedItem), con)

Hope this helps

[singhSP]

Thank you for effects, but not getting it working.

Problem getting with Coding:

reader("maxV")

returns value System.DBNull: {System.DBNull}

How to check Null value and make it use the If Else Statements ?

[Teme64]

Datareader has IsDBNull method, use that:

If reader.IsDBNull("maxV") Then
  ' Value is null, handle it
Else
  ' Value is not null, use the value
End If

HTH

[singhSP]

Usind IsDBNull

Private Sub cCode()
        Try
            If con.State = ConnectionState.Open Then con.Close()
            con.Open()
 
            com = New OleDbCommand("Select max(val(mid(cCode,5,len(cCode)))) as maxV from Contact where val(left(cCode,4)) = " & Val(cboYear.SelectedItem), con)
            reader = com.ExecuteReader(CommandBehavior.CloseConnection)
 
 
            If reader.IsDBNull("maxV") Then
                txt.Text = Val(cboYear.SelectedItem) & (reader("maxV") + 1)
            Else
                txt.Text = Val(cboYear.SelectedItem) & 1
            End If
            reader.Close()
 
        Catch ex As Exception
            If con.State = ConnectionState.Open Then
                con.Close()
            End If
        End Try
End Sub

Result: Referenced object has a value of 'Nothing'.

It jumps from Line 10 to 17

[lolafuertes]

You must invert the IF in order to verify that the returned value is not null before using it like in the following code

If [B]Not[/B] reader.IsDBNull(0) Then
                txt.Text = Val(cboYear.SelectedItem) & (reader.GetValue(0) + 1)
            Else
                txt.Text = Val(cboYear.SelectedItem) & 1
            End If

I used to referene the returned fields by their position because is a little bit faster than resolving them by name.

Hope this helps

[singhSP]

I have gone through many option as told and even by searching on Internet

Not able to get value as I desired

I am attaching the part of program which is not getting solved, it's only working when some value for specified year is posted in database.

Please check attachment and help me out.

[singhSP]

Finally after too much Hit & Try and searching on Internet got the correct way to solve my Problem.

Here is the Code:

If reader.Read And Not reader.Item("maxV") Is System.DBNull.Value Then

Thank you all who took the pain to Help me.