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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at

708 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now