«

What would cause a DataGrid to only show the header??

The code inside my Page_Load is:
'=======================
Dim ConStr as String = "Provider=Microsoft.Jet.OLEDB.4.0;data
source=C:\\malpha.mdb"
Dim dcMalpha as OleDB.OleDbConnection(conStr)

Dim daMembers as New OleDb.OleDbDataAdapter
daMembers.SelectCommand = New OleDb.OledbCommand(strMemberSQL,dcMalpha)

Dim dsMembers as New Dataset
daMembers.Fill(dsMembers, "Membership")
DGMembers.DataSource = dsMembers.Tables("Membership")
DGMembers.DataBind()

'==============

The variable strMemberSQL is a Public Variable set from another page
(It's the only way I could figure out how to do this)

Does the datagrid require any pieces to be hooked up manualy (outside of
code?)
Because the following string is what gets passed to: strMemberSQL

SELECT
[MRN],[MemName],[DOB],[SEX],[SSN],[GROUP],[SGR],[FROM-DT],[THRU-DT]FROM
MEMBERSHIP WHERE [MemName] like 'Johnson*';

The above string was copied from a label on the same page that the datagrid
is on - and pasted into a query in Access - and it works.

I'm thinking that there might be something not so obvious that I missed for
hooking up the DataGrid.

"jonefer" wrote:

> What would cause a DataGrid to only show the header??
>
> The code inside my Page_Load is:
> '=======================
> Dim ConStr as String = "Provider=Microsoft.Jet.OLEDB.4.0;data
> source=C:\\malpha.mdb"
> Dim dcMalpha as OleDB.OleDbConnection(conStr)
>
> Dim daMembers as New OleDb.OleDbDataAdapter
> daMembers.SelectCommand = New OleDb.OledbCommand(strMemberSQL,dcMalpha)
>
> Dim dsMembers as New Dataset
> daMembers.Fill(dsMembers, "Membership")
> DGMembers.DataSource = dsMembers.Tables("Membership")
> DGMembers.DataBind()
>
> '==============
>
> The variable strMemberSQL is a Public Variable set from another page
> (It's the only way I could figure out how to do this)

I'm with Teemu Keiski on no rows being the common culprit


HttpContext.Current.Response.Write("<BR/>Rows: " &
dsMembers.Tables("Membership").Rows.Count)

*before* the databind will help

another thing that would cause this is if you have custon databinding
code (ItemDataBound) that throws an exception. This exception won't
necesarily show up anywhere

Hope this helps
-- addup --

With your suggestions, I added the row count - and - True - it really is not
coming up with a row count. I also did a Try-Catch, but it's not throwing
any exceptions. So I constructed a connection, DataAdapter and DataSet using
the wizards so that I have something that I know works

OleDbConnection1, daMembers, DataSet11 my Load event looks like this now:

Try
OleDbConnection1.Open()

me.lblSQL.Text = strMemberSQL'To prove that the SQL String is being passed
'from the Search page to the result page

daMembers.SelectCommand = New OleDb.OleDbCommand(Me.lblSQL.Text,
OleDbConnection1)

daMembers.Fill(DataSet11, "Membership")

Catch ex as Exception
lblsql.Text = ex.message

Finally
OleDbConnection1.Close()
HttpContext.Current.Response.Write("<BR/>Rows: " &
DataSet11.Tables("Membership").Rows.Count)
DGMembers.DataSource = DataSet11.Tables("Membership")
DGMembers.DataBind()
End Try

'============

Remember strMemberSQL is proven to work in Access as I take the string from
the lblStatus.text and go back to Access and paste it into a Query AS IS and
it works.
(but maybe for ADO.NET it needs to be different?? )

This is frustrating because I feel I'm awfully close.





"addup" wrote:

> I'm with Teemu Keiski on no rows being the common culprit
>
>
> HttpContext.Current.Response.Write("<BR/>Rows: " &
> dsMembers.Tables("Membership").Rows.Count)
>
> *before* the databind will help
>
> another thing that would cause this is if you have custon databinding
> code (ItemDataBound) that throws an exception. This exception won't
> necesarily show up anywhere
>
> Hope this helps
> -- addup --
>
>

> it really is not coming up with a row count.

What, exactly, does it print?
Does it print "Rows: 0" ??

if yes, then It's the query, plain and simple.
If you are *not* getting anything from the response.write then it's
something else (I would check the connection)

Try this reworked code

Try
OleDbConnection1.Open()
me.lblSQL.Text = strMemberSQL 'To prove that the SQL String is being
passed
daMembers.SelectCommand = New OleDb.OleDbCommand(Me.lblSQL.Text,
OleDbConnection1)
daMembers.Fill(DataSet11, "Membership")

HttpContext.Current.Response.Write("<BR/>DataSet11 Exists? " &
isNothing(DataSet11))
HttpContext.Current.Response.Write("<BR/>DataSet11 Tables: " &
DataSet11.Tables.Count)
HttpContext.Current.Response.Write("<BR/>Member Table? " &
isNothing(DataSet11.Tables("Membership")))
HttpContext.Current.Response.Write("<BR/>Rows: " &
DataSet11.Tables("Membership").Rows.Count)

DGMembers.DataSource = DataSet11.Tables("Membership")
DGMembers.DataBind()

Catch ex as Exception
lblsql.Text = ex.message
Finally
If Not IsNothing(OleDbConnection1) Then
If OleDbConnection1.State = ConnectionState.Open Then
OleDbConnection1.Close()
OleDbConnection1 = Nothing
End If
End Try

Ok,
perhaps you are able to access what's wrong with these numbers:
(maybe it is my usage or understanding of the names of the datasets, or
tables)

For example: I was originally naming my dataset dsMembers, but the one that
the wizard creates automatically attaches a 1 to it.

So here are the numbers:
Dataset11 Exists? False
Dataset11 Tables: 1
Member Table? False
Rows: 0

If you know a sure way for me not to mess up the naming and think about it
correctly please suggest... Thanks.



"addup" wrote:

> > it really is not coming up with a row count.
>
> What, exactly, does it print?
> Does it print "Rows: 0" ??
>
> if yes, then It's the query, plain and simple.
> If you are *not* getting anything from the response.write then it's
> something else (I would check the connection)
>
> Try this reworked code
>
> Try
> OleDbConnection1.Open()
> me.lblSQL.Text = strMemberSQL 'To prove that the SQL String is being
> passed
> daMembers.SelectCommand = New OleDb.OleDbCommand(Me.lblSQL.Text,
> OleDbConnection1)
> daMembers.Fill(DataSet11, "Membership")
>
> HttpContext.Current.Response.Write("<BR/>DataSet11 Exists? " &
> isNothing(DataSet11))
> HttpContext.Current.Response.Write("<BR/>DataSet11 Tables: " &
> DataSet11.Tables.Count)
> HttpContext.Current.Response.Write("<BR/>Member Table? " &
> isNothing(DataSet11.Tables("Membership")))
> HttpContext.Current.Response.Write("<BR/>Rows: " &
> DataSet11.Tables("Membership").Rows.Count)
>
> DGMembers.DataSource = DataSet11.Tables("Membership")
> DGMembers.DataBind()
>
> Catch ex as Exception
> lblsql.Text = ex.message
> Finally
> If Not IsNothing(OleDbConnection1) Then
> If OleDbConnection1.State = ConnectionState.Open Then
> OleDbConnection1.Close()
> OleDbConnection1 = Nothing
> End If
> End Try
>
>

The Rows: 0 indicates that everything else worked fine, It's the SQL
query that returned no rows.

If you can copy-paste the query into access, and it works, then I'm at
a loss

The ONLY thing that comes to mind - - - >
remember that what you see in the browser as me.lblSQL.Text may not be
the actual contents of me.lblSQL.Text because the browser display
ignores whitespace.

so, View|Source and copy-paste the query from there into access

I finally discovered what it is!
Something that works in Access but doesn't work in ADO.NET is the '*' for
like.

Once I changed it to a '%' everything was fine.

Thanks for all your troubleshooting.

"addup" wrote:

> The Rows: 0 indicates that everything else worked fine, It's the SQL
> query that returned no rows.
>
> If you can copy-paste the query into access, and it works, then I'm at
> a loss
>
> The ONLY thing that comes to mind - - - >
> remember that what you see in the browser as me.lblSQL.Text may not be
> the actual contents of me.lblSQL.Text because the browser display
> ignores whitespace.
>
> so, View|Source and copy-paste the query from there into access
>
>

Ah, nice to know that you found the reason.

It's not ADO.NET but Jet OLE DB provider which uses '%' as wildcard
character (it is per standard, similarly as SQL server uses it). Access UI
accepts *'s as wildcards but Jet provider does not.

--
Teemu Keiski
ASP.NET MVP, AspInsider
Finland, EU
http://blogs.aspadvice.com/joteke

"jonefer" <> wrote in message
news:810CEFA9-6F63-428A-8C82-...
>I finally discovered what it is!
> Something that works in Access but doesn't work in ADO.NET is the '*' for
> like.
>
> Once I changed it to a '%' everything was fine.
>
> Thanks for all your troubleshooting.
>
> "addup" wrote:
>
>> The Rows: 0 indicates that everything else worked fine, It's the SQL
>> query that returned no rows.
>>
>> If you can copy-paste the query into access, and it works, then I'm at
>> a loss
>>
>> The ONLY thing that comes to mind - - - >
>> remember that what you see in the browser as me.lblSQL.Text may not be
>> the actual contents of me.lblSQL.Text because the browser display
>> ignores whitespace.
>>
>> so, View|Source and copy-paste the query from there into access
>>
>>