ADO.NET Gotchas

Using Reserved Words for Column Names

by Les Smith

Why do queries that worked in VB6, against an Access Database, not work in ADO.NET.  The problem may be the use of reserved words as column names.
Having problems with database column names that worked in VB6, but they are not working in ADO.Net?  In VB6 you could use column names such as Module, Object, Property, etc.  Using these keywords as database column names did not cause a problem in VB6 with an Access MDB, but in ADO.NET you can get some nasty results.  Note the following examples:

sql = "insert into modules (Module, Object) values('TestModule', 'TestObject')"

The sql statment shown above will return an error message of "syntax error in Insert statement, even though there is no syntax error there.  Changing the column names to ModuleName and ObjectName respectively will clear the error.
If you attempt to run the following query on the same table (with columns named with reserved words), you will get an "unexplained error" with about 10 lines of call stack information.

sql = "select module, object from modules where module = 'TestModule' "

Again, changing the column names as suggested previously will remedy the problem. You can also place "[ ]" around the column names in question and it should solve the problem.

Another gotcha in ADO.NET comes from failing to initialize a DataTable before reusing it.  When using the same datatable over and over again in a method, make sure that you always use the dt.Reset() method before requesting the datatable be filled again.  If you do not, you will get an accumulation of results constantly building up in your datatable and wonder what's going on.  The DataAdaptor does not automatically clear the datatable when you use "da.Fill(dtA)."  The following code sequence illustratest the technique:

Public Sub test()       Dim dt As New DataTable       Dim da As New SqlDataAdaptor       Dim cn As New SqlConnection       Dim cmd As New SqlCommand       Dim Sql As String       ' open your connection       ' build the sql       sql = "Select * from table where field = '" & value & "' "       sql &= "order by field"       ' fill the table       cmd.CommandText = sql       cmd.Connection = cn       da = New SqlDataAdaptor(cmd)       da.Fill(dt)       ' process the datatable results       ' now reuse the same datatable       ' build the sql       sql = "Select * from table2 where field2 = '" & value2 & "' "       sql &= "order by field"       ' fill the table       cmd.CommandText = sql       cmd.Connection = cn       da = New SqlDataAdaptor(cmd)       ' reset the datatable before reusing it       dt.Reset()       da.Fill(dt)    End Sub