0

I have a VB function which calls a stored procedure to get 2 kinds of reports (actives users/inactive users). The stored procedure returns the data (columns) based on the report we choose. If we choose to get active users report, the sproc doesn't return an Inactive_Date for the users, but it will return the same field, Inactive_Date field for Inactive users report. I'm getting an error when I choose to get active users report, because the sproc doesn't return Inactive_Date field, but my VB.NET code is same for both active/inactive users reports. Here are some of the ways I tried to resolve, but no luck.

If IsDBNull(dr("Inactive_Date")) Then
    Result.Inactive_Date = Nothing
Else
    Result.Inactive_Date = SafeStr(dr("Inactive_Date"))
End If

and

If IsDBNull(dr("Inactive_Date")) Then
    Result.Inactive_Date = DateTime.Now
Else
    Result.Inactive_Date = SafeStr(dr("Inactive_Date"))
End If
ZygD
  • 22,092
  • 39
  • 79
  • 102
KnowledgeToShare
  • 1

2 Answers2

0

You're getting the exception because you're basically trying to access a dictionary entry that doesn't exist. You have a few options.

  1. Have your stored procedure for Active Users return Inactive_Date as null. This would allow you to use code you have posted in VB with no changes.
  2. Create a function that checks if the datareader has that column before you attempt to access it, as in this answer: Check for column name in a SqlDataReader object - this would be akin to checking if the dictionary has the key first
  3. Write two separate functions in your VB code - one for the active report and one for the inactive report. Or, add a parameter to your function to check if this is a request for an active user, and only try to access the "Inactive_Date" field if that parameter is set to true.

If it were up to me, I'd update the stored procedure (option 1). That would probably be the least amount of work, shouldn't hurt anything semantically (unless you have queries that SELECT * from the results of that procedure), and requires no other changes to your codebase. The only other problem I can see is confusion that might arise from having a function in your VB code that's used for both active and inactive users.

The second option will involve iterating through all the fields, and that just feels wrong to me (seems like it's asking for performance issues if used incorrectly). It's the least invasive though if you can't change the stored proc.

The third option probably creates more problems elsewhere in your code base. That said, you probably shouldn't have one function do two very different things - I'd avoid the parameterized version if you end up going down this route, and go for two separate functions.

Community
  • 1
  • 1
Dan Field
  • 20,885
  • 5
  • 55
  • 71
0

From your code and the fact that your variable is named dr, I am going to assume that you are using a DataReader.

The DataReader class exposes the column names as indexed values.

Dim columnNames = Enumerable.Range(0, dr.FieldCount).Select(dr.GetName).ToList()

Will call the method dr.GetName(index) for all columns, returning a list of column names you can check using columnNames.Contains("yourColumnName")

There is also dr.GetOrdinal, but that will throw an exception if the column doesn't exist.

jmoreno
  • 12,752
  • 4
  • 60
  • 91