VB.Net - Referencing DataTable Fields in Code and Query Strings

Posted on 2013-01-30
Last Modified: 2013-01-31
Good Day Experts!

I am looking for some direction in how to reference data in a DataTable.  

For Each sqlRSGetLineItems In sqlRStbl.Rows


When I just want to condition on a field, do I just need? --> sqlRSGetLineItems("SCAC")
How do I know if I need .Value or .ToString at the end?

When I just want to use a value from a field, why not just? --> '" & sqlRSGetLineItems("SCAC") & "'
What are the rules with using .Value or .ToString at the end?
What about needing to use CDec? -->  '" & CDec(sqlRSGetLineItems("SCAC").ToString) & "'
I mean the data in the DataTable I am retrieving is decimal so why do I have to do the CDec?

Hopefully you can lend some insight and help me understand what needs to be used when and why.

Question by:Jimbo99999
  • 2
  • 2
LVL 40

Accepted Solution

Jacques Bourgeois (James Burger) earned 400 total points
ID: 38838502
Even if the table in the database has specific types of columns, the DataTable was made so that it can receive anything. So the values returned are set as Object data types.

The Object data type is nice in some situations, because it can accept any value. So it was well suited to receive data from different databases. But it needs to be converted to a type pertinent for the application you intend before using it.

There a not really "rules", it depends on what you need to do. But typically, you could start with the following 2 approaches.

If you want to display the value, on the screen on in a report, its needs to be shown as Text. The easiest way to do that is with ToString, because it will work with most types of values from a database, and you do not have to care about the real type of the object.

However, if you need to use is as its type, either to perform calculations with a numeric field, either to manipulate other types such as dates, you need to convert to the proper type.  This is mainly a question of performance. If you did not implicitely convert the object, the system would need to perform an analysis everytime you would want to use a value to make sure that it is OK for the context where you want to use it. And you do not use ToString in such a case, only the conversion: CDec(sqlRSGetLineItems("SCAC")).
LVL 83

Assisted Solution

CodeCruiser earned 100 total points
ID: 38839137
I rarely have to use anything other than


itself unless I need an Integer or other similar value.

Author Comment

ID: 38839946

So, if I had the following would it be right?

Dim curTotalInvPaid As Decimal
curTotalInvPaid = sqlRSGetLineItems("AMOUNT PAID")

If the SQL table field is decimal would the DataTable automatically "take on" the decimal type? So then if I define a variable as decimal type I can do the above?

LVL 83

Expert Comment

ID: 38840049
Yes as VB will implicitly convert the value. But it is error prone as you will get an exception if the value returned from reader can not be converted to a decimal (if its null, blank, a string etc).
LVL 40
ID: 38840238
"VB will implicitly convert the value"

This depend on a compilation setting.

By default, Option Strict is Off in the Compile tab of the project's properties window. This will implicitly convert the value. This also one of the main reason why many programmers say that VB is inferior to C#.

If you set Option Strict On, VB behave mostly like C#. I think that this is On in Jimbo's project. Working that way will force you to explicitely call a conversion when assigning a type of variable to another when there is a danger that the conversion will bring problems. It will also prevents a lot of runtime errors. Personnally, I would rather be warned of a potential problem while coding that having to debug later.

Here are a couple of samples where Option Strict shows its value.

Dim x As Byte = 20
Dim y As Integer = 30

y = x  'No flag, because a Byte can always fit in an Integer
x = y  'Option Strict triggers an error because there is a danger that the value will be two big

Having a compilation error telling you to implicitely convert with CByte could seem annoying here because y can fit in x. But if forces you to review your code an make sure that that x = y makes sense. If y ever gets a value of 2000 from somewhere, the compiler would not generate any error, but there would be an exception while the application is running. The user would be pissed of, and the programmer would have to go back to the code, find the problem, correct it and redeploy the application.

Here is another one:

Dim x As Boolean
Dim y As Integer = 10

x = y
y = x

This time, Option Strict will flag the first assignment. Option Strict Off will not, and you might end up with a problem somewhere, because the implicit conversion converts 10 to False (this is what happens when you put a non-zero value into a Boolean), and the second assignment would convert the False to -1. You lost a 10 that might seem to just go from one variable into another and back at first sight. Once again Option Strict On sends you a warning "Are you sure? Do you know what you are doing? Shouldn't x be an Integer?"

By letting implicit conversion do its job, you save a couple of seconds from time to time by having to type CInt or CDate. By asking the compiler to help you in identifying potential problems, you often save hours in debugging.

For me, setting Option Strict On is what makes the difference between a professional programmer and an amateur. And if it was On by default, the bashing against VB would stop and there would be more programmers programming in VB.

Featured Post

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit If you want to manage em…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question