SqlDataAdapter returning empty rowsets that shouldn't be?

[downwitch]

Hi,

I'm having trouble getting data in ADO.Net recordsets. (VS 2005, SQL
Server 2005). All my rowsets come back empty--whether sourced from
queries or stored procs--despite ODBC connections with the same user
credentials (two different UID/PWDs tried) returning existing data
without a problem (i.e. it's not a SQL security problem).

With code like

strSQL = "SELECT * FROM SomeTable"
Dim da As New SqlDataAdapter(strSQL, gcnn)
Dim ds As New DataSet("MyDS")
da.FillSchema(ds, SchemaType.Source)
Dim dr As DataRow
If ds.Tables(0).Rows.Count > 0 Then
For Each dr In ds.Tables(0).Rows
'do something
Next dr
End If

I am getting no data, but there is definitely data there. No errors,
just empty datasets. Is there some better way to go about this? Should
I use a straight ODBC connection instead of trying to use ADO's SQL
adapter?

Hope someone can help me see the error of my ways. I'm a SQL pro but
something of a vb.net newb, and google isn't turning up anything of
use.

 

[Guest]

downwitch,

Try using the dataadapter's Fill method instead of FillSchema.

Kerry Moorman

 

[Bails]

Hi,

I'm having trouble getting data in ADO.Net recordsets. (VS 2005, SQL
Server 2005). All my rowsets come back empty--whether sourced from
queries or stored procs--despite ODBC connections with the same user
credentials (two different UID/PWDs tried) returning existing data
without a problem (i.e. it's not a SQL security problem).

With code like

strSQL = "SELECT * FROM SomeTable"
Dim da As New SqlDataAdapter(strSQL, gcnn)
Dim ds As New DataSet("MyDS")
da.FillSchema(ds, SchemaType.Source)
Dim dr As DataRow
If ds.Tables(0).Rows.Count > 0 Then
For Each dr In ds.Tables(0).Rows
'do something
Next dr
End If

I am getting no data, but there is definitely data there. No errors,
just empty datasets. Is there some better way to go about this? Should
I use a straight ODBC connection instead of trying to use ADO's SQL
adapter?

Hope someone can help me see the error of my ways. I'm a SQL pro but
something of a vb.net newb, and google isn't turning up anything of
use.

If you are viewing it in build mode (ie within the development
environment) you will get this as there is a bug in VB that saves the
table in 2 different places. As a beginner myself I dont understand
it, but if you want to see for yourself build your project and run it.
Then you will find its saving your data OK.

To avoid this simply enter some dummy data in the Database Explorer -
expand the Tables node and select the table node, and then on the Data
menu, choose Show Table Data.

I hope this helps. I know i took me forever to find a solution.

 

[downwitch]

This was it, thanks. There are some inaccurate code snippets out
there, I suppose.