Improve company productivity with a Business Account.Sign Up


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

Posted on 2013-01-30
Medium Priority
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 1600 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 400 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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

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 …
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…
Watch the video to know how one can repair corrupt Exchange OST file effortlessly and convert OST emails to MS Outlook PST file format by using Kernel for OST to PST converter tool. It can convert OST to MSG, MBOX, EML to access them. It can migrate…
Watch the working video to know how to import Outlook PST/OST files to Amazon WorkMail. Kernel released this tool which is very easy to use and migrate single or multiple PST and OST files to Amazon WorkMail. To know more about Kernel Import PST to …

606 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