Datagrid only shows the header

17 Dec 2005 2:50 AM

jonefer

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)

18 Dec 2005 6:20 PM

Teemu Keiski

If the query doesn't return any results and you bind that to the DataGrid,
it would show only the header. (and footer if set to). So, check what you
are querying from the database that it indeed produces a resultset.

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

Show quoteHide quote

"jonefer" <jone***@discussions.microsoft.com> wrote in message
news:32E9F698-56EF-471E-8A54-C69A0717613D@microsoft.com...
> 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)

18 Dec 2005 7:30 PM

jonefer

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.

Show quoteHide quote

"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)

19 Dec 2005 5:38 PM

addup

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

HttpContext.Current.Response.Write(" 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 --

19 Dec 2005 9:17 PM

jonefer

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(" 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.

Show quoteHide quote

"addup" wrote:

> I'm with Teemu Keiski on no rows being the common culprit
>
>
> HttpContext.Current.Response.Write(" 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 --
>
>

19 Dec 2005 9:59 PM

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(" DataSet11 Exists? " &
isNothing(DataSet11))
HttpContext.Current.Response.Write(" DataSet11 Tables: " &
DataSet11.Tables.Count)
HttpContext.Current.Response.Write(" Member Table? " &
isNothing(DataSet11.Tables("Membership")))
HttpContext.Current.Response.Write(" 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

20 Dec 2005 1:07 AM

jonefer

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.

Show quoteHide quote

"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(" DataSet11 Exists? " &
> isNothing(DataSet11))
> HttpContext.Current.Response.Write(" DataSet11 Tables: " &
> DataSet11.Tables.Count)
> HttpContext.Current.Response.Write(" Member Table? " &
> isNothing(DataSet11.Tables("Membership")))
> HttpContext.Current.Response.Write(" 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
>
>

20 Dec 2005 5:35 PM

addup

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

20 Dec 2005 6:45 PM

jonefer

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.

Show quoteHide quote

"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
>
>

20 Dec 2005 8:27 PM

Teemu Keiski

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

Show quoteHide quote

"jonefer" <jone***@discussions.microsoft.com> wrote in message
news:810CEFA9-6F63-428A-8C82-59A50594C2A6@microsoft.com...
>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
>>
>>