Once is not enough

[Carl Yos]

I have a form that uses a sproc from a SQL server 7.0
database to populate a datagrid using the
DataAdapter.fill method Although the sproc runs reliably
using the Query Analyzer or Access 2002, it always
returns an exception using ADO.net. The return is not
large, 1000 rows by 57 columns. The 7.0 Query analyzer
uses ODBC and Access 2002 uses ADO without the net.
Using a try/catch block I can force the application to
run and it returns 1000 records complete in every detail
but the exception remains.
In an effort to discover what was returning with the
data, I created a second form in which I create a
destination table with all 57 rows defined and use a
DataReader to bring in the data one row at a time. It
seems that ADO.net is returning an extra blank row.
I have found I must create the table twice even though
this produces a "table already exits" exception. If I do
not run the sub twice I cannot list the columns and the
datareader does not populate table.
Any suggestions on either of these items would be most
helpful.
I ahve included the code fragments.
Thanks in advance,
Carl Yos

Imports System.Data.SqlClient
Public Class frmDataSetExpanded
Inherits System.Windows.Forms.Form
Dim TestConn As New SqlConnection("Initial
Catalog=TestWorkOrder2KSQL; " _
& "Data
Source=suntec-nrd-devs; " _
& "Integrated
Security=SSPI")
Dim myDataSet As New DataSet()
Dim myDataTable As New DataTable("TestOrders")


#Region " Windows Form Designer generated code "

Private Sub CreateTable()
Dim bolNoEx As Boolean = True
Try
myDataTable.Columns.Add("RecNo", Type.GetType
("System.Int32"))

\\56 additonal fields\\

myDataTable = myDataSet.Tables.Add
("TestOrders")
Catch Ex As Exception
txtError.Text = Ex.ToString
Console.WriteLine(Ex.ToString)
bolNoEx = False
End Try
If bolNoEx Then
txtError.Text = "Table Created"
End If
End Sub

Private Sub PopulateTable()
On Error GoTo ErrorHandler
Dim cmdOrder As New SqlCommand
("sproc_ViewCombinedAllOrdersRowCount1000", TestConn)
Dim drOrd As DataRow
Dim myReader As SqlDataReader
Dim intRecNo As Int32
TestConn.Open()
myReader = cmdOrder.ExecuteReader
While myReader.Read
txtError.Text = "Reading Record - " & intRecNo
txtError.Refresh()
'load the data table
drOrd = myDataTable.NewRow
drOrd("RecNo") = intRecNo
myDataTable.Rows.Add(drOrd)

\\56 additional fields\\

myDataTable.Rows.Add(drOrd)
intRecNo += 1
End While
EndSub:
TestConn.Close()
DGresult.DataSource = myDataTable
Exit Sub
ErrorHandler:
If Err.Number = 5 Then 'data is Null
Resume Next
Else
txtError.Text = Err.Number &
ControlChars.NewLine & Err.Description & _
ControlChars.NewLine & Erl()
Resume EndSub
End If

End Sub

Private Sub btnGetData_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
btnGetData.Click
PopulateTable()
End Sub
Private Sub ListColumns()
Dim strList As String
Dim cols As DataColumnCollection
Dim col As DataColumn
cols = myDataTable.Columns
For Each col In cols
strList &= col.ColumnName &
ControlChars.NewLine
Next
txtError.Text = strList
txtError.Refresh()
End Sub

Private Sub btnList_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
btnList.Click
ListColumns()
End Sub
End Class

 

[William \(Bill\) Vaughn]

Perhaps I'm missing something, but why is it that you're creating a
DataTable yourself? Why not just execute the SP and have ADO.NET construct
the DataTable for you? The Fill method does just that. All you need to pass
it is an instantiated DataSet object. Everything else is done for you.


--
____________________________________
Bill Vaughn
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Carl Yos]

Mr Vaughn,
Thank you for your immediate reply.
My first construct did use the fill method and I would prefer to do so
but running the sproc produced the 1000 records complete in every detail
I expected and a phantom exception.
"Message: A severe error occurred on the current command. The results,
if any, should be discarded."
The error number reported is 0 and the severity level 10. According the
SQL server 7.0 this should be a message and not an exception. The sproc
runs in the 7.0 Query analyzer and in Access 2002, ODBC and ADO. Other
sprocs run without exception. My research indicates that ADO.net
returns rows first and then any messages.
I decided to construct the table in code and then use a datareader to
populate the table in the hope of trapping the phantom exception. (All
I got was an extra blank row, by the way.)
It was then that I noticed that I had to run the CreateTable routine
twice for VB to recognize the table. This did not seem right and
prompted my email.
Regards,
Carl Yos