My Anna

Indias Biggest Engineering Education Community Enriching Students Minds with Latest Placement Papers, Study Materials, Fresher Jobs and Free E Books

A Unit of Aim Clear Technologies Ltd.
Search Study Materials, Jobs, Placement Papers, Interview Tips, Training Videos & More

Advertise with us

Board index > Departments > Computer Science > Visual Basic,Dot Net
Who is online



Post new topic Reply to topic  Page 1 of 1
  [ 1 post ] 
Print view   |Previous topic   |Next topic

sreenath
Super Moderator
Super Moderator
User avatar

Joined:
Wed Aug 26, 2009 10:01 pm
Beans: 1,227
In Bank: 14665
College: ESEC
Degree: M.Sc
Department: CSE
State: TN
Fav Quote: no pain no gain
 
#2
Sat Oct 03, 2009 2:51 pm
Post subject: ADO.NET Gotchas Post
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
_________________
sreenath@myanna.inImage

Profile E-mail
Offline

Display posts from previous:  Sort by  
Post new topic Reply to topic  Page 1 of 1
  [ 1 post ] 
Bookmark & Share
Board index > Departments > Computer Science > Visual Basic,Dot Net

Who is online

Users browsing this forum: No registered users and 0 guests

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
cron
Powered by phpBB Group.

phpBB SEO