Datareader returning null value incorrectly

[JulesBos]

All, I wonder if you can help me, I have the following code that is returning unexpected results and I have no idea why:

Dim searchYear As String
    If InStr(3, wholeYear) = "0" Then
        searchYear = Right(wholeYear, 1)
    Else
        searchYear = Right(wholeYear, 2)
    End If

    'now we need to get the last number for the search year and increment by 1
    DBCommand.CommandText = "Select Max(CSDNo) as MaxofCSDNo from CSDs WHERE CSDNo Like Cstr(" + searchYear + ")&'*'"
    Connection.Open()
    Dim getNewCSDNoReader As OleDb.OleDbDataReader = DBCommand.ExecuteReader(CommandBehavior.CloseConnection)
    While getNewCSDNoReader.Read
        Dim getNewNo As String
        If Not getNewCSDNoReader.GetValue(0) Is DBNull.Value Then
            getNewNo = CStr(getNewCSDNoReader.GetValue(0) + 1)
        Else
            getNewNo = searchYear & "001"
        End If
    End While
    Connection.Close()

The DBcommand commandtext results are:

"Select Max(CSDNo) as MaxofCSDNo from CSDs WHERE CSDNo Like Cstr(7)&'*'"

which I have tested in the back end database (Access) and it works with a resulting record of 7002.

However the value of getNewCSDNoReader.GetValue(0) is DBNull.Value and I just don't get why?

Any ideas?

Thanks in advance.

 

[ca8msm]

Why don't you do all that processing in the SQL Statement? It will be much better to return the correct value to start with.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[JulesBos]

Sorry I'm confused, what do you mean?

 

[ca8msm]

All you are doing is incrementing the database value by one (if it exists) otherwise you are setting a default. You can do all of this in SQL and I'd say that's where it probably should be done. If you are using Access as your database, a combination of the IIF and IsNull methods should work for you.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[JulesBos]

OK I get what you mean. That's fine, however I still need to work out the current year first. The numbering system goes as follows:

Year = 2007
Number = 7001, 7002, 7003 etc until end of year
Year changes to 2008
Numbers = 8001, 8002, 8003 etc until end of year

That means I still have the problem of selecting the max number from a table where year is like ..... so I will still have the original problem regardless, just some of the processing will be done in a different place.

 

[ca8msm]

All of the processing can be done in the SQL statement. If you have trouble implementing the functions I suggested, you could try asking in the forum for whichever database you are using.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[JulesBos]

Thanks I do appreciate you response, and I'm not disagreeing, however I still don't see that this will help - my SQL function is still going to need some similar syntax e.g. Cstr(7)&'*' - which is what seems to be returning a null value......, so whether I process in code or SQL I have exactly the same problem.

 

[JulesBos]

Just thought I'd let you know that I've resolved the problem by changing the like and wildcard operators to the following:

Dim searchYear As String
    Dim searchYearStart As String
    Dim searchYearEnd As String
    If InStr(3, wholeYear) = "0" Then
        searchYear = Right(wholeYear, 1)
    Else
        searchYear = Right(wholeYear, 2)
    End If
    searchYearStart = searchYear & "000"
    searchYearEnd = searchYear & "999"

    'now we need to get the last number for the search year and increment by 1
    getNewCSDNoCommand = New System.Data.OleDb.OleDbCommand
    getNewCSDNoCommand.Connection = Connection
    getNewCSDNoCommand.CommandText = "Select Max(CSDNo) as MaxofCSDNo from CSDs WHERE CSDNo Between " + searchYearStart + " AND " + searchYearEnd
    Connection.Open()
    Dim getNewCSDNoReader As OleDb.OleDbDataReader = getNewCSDNoCommand.ExecuteReader(CommandBehavior.CloseConnection)
    While getNewCSDNoReader.Read
        If Not getNewCSDNoReader.GetValue(0) Is DBNull.Value Then
            GetNewNo = CStr(getNewCSDNoReader.GetValue(0) + 1)
        Else
            GetNewNo = searchYear & "001"
        End If
    End While

And it works fine now.