CODEWITHSUNDEEP
.netsqlvb.netms-access
Shane LeBlanc
Shane LeBlanc

Reputation: 2633

Why is my datatable cell returning a null value?

I have an access database and a stored query. Here is my query...

SELECT MAX(CLNG(RIGHT(ProjectNum, 6))) AS LastDigits
FROM project_master_query
WHERE ((ProjectNum LIKE (IIF([@priorityDefID] = 4, "C*", "F*"))));

When I run this query, and pass in @priorityDefID of 2 for example, I get back a column name of LastDigits and one row containing a value of 1 which is correct. Here is my VB code that calls this...

'Project Class (Without adding unnecessary code)
Public Shared Function GenerateProjectNumber(ByVal priorityDefID As Integer) As String
    Dim dt As DataTable = ProjectSQL.GetLastGeneratedNumber(priorityDefID)
    Dim lastGeneratedNumber As Integer
    If dt.Rows.Count > 0 Then
        'Exception Occurs Below: DBNull cannot be cast to other types
        lastGeneratedNumber = Convert.ToInt32(dt.Rows(0).Item(0)) ' Or .Item("LastDigits"))
    End If
End Function

'ProjectSQL Class 
Public Shared Function GetLastGeneratedNumber(ByVal priorityDefID As Integer) As DataTable
    Dim parameterList As New List(Of DataParameter)
    parameterList.Add(New DataParameter("@priorityDefID", priorityDefID, ParameterDirection.Input, OleDbType.Integer))
    Return DAL.GetDataTableUsingReader("GetLastGeneratedNumber", parameterList)
End Function

Now as you can see, a row is returned but it contains a null value. When I run the query in Access and pass in the exact same value that I pass in through VB, I get the right value. When I run it through code, I get a null value. Is there anything here that stands out that I'm missing?

Upvotes: 2

Views: 210

Answers (1)

HansUp
HansUp

Reputation: 97101

I assume your .Net uses OleDb to connect to the Access db file. OleDb (same as ADO in Access) requires different wild card characters: % and _ instead of * and ? respectively.

Try it this way:

WHERE ProjectNum LIKE IIF([@priorityDefID] = 4, "C%", "F%");

Note I discarded unneeded parentheses from the WHERE clause; you may need to add some back if I discarded too many.

Also consider whether you find ALike to be a suitable alternative to Like:

WHERE ProjectNum ALike IIF([@priorityDefID] = 4, "C%", "F%");

With ALike, the Access db engine always expects the wild cards to be % and _ regardless of the context under which the query runs ... ADO, DAO, OleDb ... it will always return the same result set. So with ALike the query could work the same in an Access session as when called from .Net using OleDb.

Upvotes: 2

Related Questions