I have a longstanding problem where Stored Procedures or complex T-SQL called from VB.NET will not populate a DataTable object, but will work fine with a DataSet. For example:
'oConn is defined elsewhere...
Dim sErr as String = ""
Dim dt As New DataTable
If Not oConn Is Nothing Then
Try
Dim sSQL as String = "select 1"
Dim oCommand As New OdbcDataAdapter(sSQL, oConn)
oCommand.Fill(dt)
Catch ex As Exception
sErr = "Database Error: " & ex.Message
Finally
sqlCloseConnection(oConn)
End Try
End If
this works fine and my dt DataTable object gets one row. However using this as the SQL:
Dim sSQL as String = "declare @.foo table(mycol integer);insert @.foo select 1;select mycol from @.foo;"
does not work. It executes with no errors, but the DataTable has no rows. Finally, if I replace the DataTable with:
Dim ds as DataSet
I can then get the data in ds.Tables(0) no problem.
So, if the results of the sql are a single result table being put at index 0 of a DataSet, why are they not being put in a single DataTable?
When a sql is a simple select statement it always works directly to a DataTable. Only when it's a SP or sql with some logic does it require the DataSet approach. This is a reporting utility so I need to standardize the code though the sql will be dynamic.
Any ideas?
Hello my friend,
I tried your code in my application and it worked for me, but I am using the SqlDataAdapter. Why are you using Odbc? Try using the Connection, Command, DataAdapter, etc classes from the System.Data.SqlClient namespace and I think it will work.
Kind regards
Scotty
