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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

749 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