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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Internet Business Fax to Email Made Easy - With  eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

895 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