Discussion:
DBGrid Not Showing ODBC bound data
(too old to reply)
DerekT
2008-09-16 08:48:43 UTC
Permalink
Hi Folks,

I have been asked to work on an old program in VB6 and convert its database
from Access to SQL Server. It uses a Data control and a data bound grid.

I have changed the Data control to access the SQL Server data source via
ODBC and I can confirm that it works by stepping through the data and
showing it in text boxes. Problem is that the DBGrid is now not showing the
bound data.

I have tried setting its data source to Data1, I have tried Data1.Recordset
but nothing I do will make it show data. However when Date used to be using
Jet on an Access database the grid showed data just fine.

Any ideas?

Many thanks,
Derek
Ralph
2008-09-17 13:56:05 UTC
Permalink
Post by DerekT
Hi Folks,
I have been asked to work on an old program in VB6 and convert its database
from Access to SQL Server. It uses a Data control and a data bound grid.
I have changed the Data control to access the SQL Server data source via
ODBC and I can confirm that it works by stepping through the data and
showing it in text boxes. Problem is that the DBGrid is now not showing the
bound data.
I have tried setting its data source to Data1, I have tried
Data1.Recordset
Post by DerekT
but nothing I do will make it show data. However when Date used to be using
Jet on an Access database the grid showed data just fine.
Was this program originally written using VB5? Did you convert to VB6? What
SP?
Are you using DAO and an ODBC driver, or ADO and an OLE DB for ODBC
provider?

Temporarily remove any design-time layout properties, such as column widths,
etc., the 'essentials' like DataSource and Database can remain. If this
works you can add the properties in code - after the data is fetched. This
was an old bug I thought was fixed.

-ralph
DerekT
2008-09-17 15:25:53 UTC
Permalink
Hi Ralph,

Very possibly used to be a VB5 prog but I dont want that to cloud the issue.
So I have created a brand new prog in VB6 with a form that has a data
control and a data bound grid (dbGrid)

The data control I assume uses DAO. This populates OK as I can interrogate
it and see data. The dbGrid is a virgin grid with no design time stuff on it
apart from the datamode and datasource

Here is the code

Data1.DatabaseName = "HelpDesk"
SQL = "select RequestID from Log"
Data1.RecordSource = SQL
Data1.Refresh
DBGrid1.Refresh

Grid properties are

DataMode = bound
Datasource = Data1

Still will not show any data in the grid, yet swith the Data control to
access the same data from JET instead of odbc and it works.

Cant figure out what is wrong surely if the Data Control has data then the
bound grid should show it?

Many thanks,
Derek
Post by DerekT
Post by DerekT
Hi Folks,
I have been asked to work on an old program in VB6 and convert its
database
Post by DerekT
from Access to SQL Server. It uses a Data control and a data bound grid.
I have changed the Data control to access the SQL Server data source via
ODBC and I can confirm that it works by stepping through the data and
showing it in text boxes. Problem is that the DBGrid is now not showing
the
Post by DerekT
bound data.
I have tried setting its data source to Data1, I have tried
Data1.Recordset
Post by DerekT
but nothing I do will make it show data. However when Date used to be
using
Post by DerekT
Jet on an Access database the grid showed data just fine.
Was this program originally written using VB5? Did you convert to VB6? What
SP?
Are you using DAO and an ODBC driver, or ADO and an OLE DB for ODBC
provider?
Temporarily remove any design-time layout properties, such as column widths,
etc., the 'essentials' like DataSource and Database can remain. If this
works you can add the properties in code - after the data is fetched. This
was an old bug I thought was fixed.
-ralph
Ralph
2008-09-17 15:53:56 UTC
Permalink
Post by DerekT
Hi Ralph,
Very possibly used to be a VB5 prog but I dont want that to cloud the issue.
So I have created a brand new prog in VB6 with a form that has a data
control and a data bound grid (dbGrid)
The data control I assume uses DAO. This populates OK as I can interrogate
it and see data. The dbGrid is a virgin grid with no design time stuff on it
apart from the datamode and datasource
Here is the code
Data1.DatabaseName = "HelpDesk"
SQL = "select RequestID from Log"
Data1.RecordSource = SQL
Data1.Refresh
DBGrid1.Refresh
Grid properties are
DataMode = bound
Datasource = Data1
Still will not show any data in the grid, yet swith the Data control to
access the same data from JET instead of odbc and it works.
Cant figure out what is wrong surely if the Data Control has data then the
bound grid should show it?
The DBGrid and Data Control have always had odd bugs. But as you noted they
always seem get alone with Jet just fine. Probably because they were all
developed at the same time. <g>

That's why I asked to you to be more specific about the tools and versions
you were using. I have always had to go in an massage a VB4/5 app containing
those controls when converting to VB6 or to another store, either for the
trouble you mentioned or other oddities. Usually to provide a work-around
for the work-around the previous programmer did. So don't feel alone. <g>
And don't expect the conversion to be seemless. One of the reasons I usually
replace DBGrid/Data Control at the first opportunity.

You might try placing a rebind just before the refresh ...
Post by DerekT
Data1.RecordSource = SQL
Data1.Refresh
DBGrid1.rebind
Post by DerekT
DBGrid1.Refresh
You might also place a DoEvents() between the retrieval and presention. Not
forever, just to test to see if the problem isn't one of
one-asking-before-the-other-is-ready.

-ralph
DerekT
2008-09-18 12:50:52 UTC
Permalink
No luck I'm afraid Ralph,

It just will not show any data when using ODBC against a SQL Server
database. Tried everything suggested and more.
Post by DerekT
Post by DerekT
Hi Ralph,
Very possibly used to be a VB5 prog but I dont want that to cloud the
issue.
Post by DerekT
So I have created a brand new prog in VB6 with a form that has a data
control and a data bound grid (dbGrid)
The data control I assume uses DAO. This populates OK as I can interrogate
it and see data. The dbGrid is a virgin grid with no design time stuff on
it
Post by DerekT
apart from the datamode and datasource
Here is the code
Data1.DatabaseName = "HelpDesk"
SQL = "select RequestID from Log"
Data1.RecordSource = SQL
Data1.Refresh
DBGrid1.Refresh
Grid properties are
DataMode = bound
Datasource = Data1
Still will not show any data in the grid, yet swith the Data control to
access the same data from JET instead of odbc and it works.
Cant figure out what is wrong surely if the Data Control has data then the
bound grid should show it?
The DBGrid and Data Control have always had odd bugs. But as you noted they
always seem get alone with Jet just fine. Probably because they were all
developed at the same time. <g>
That's why I asked to you to be more specific about the tools and versions
you were using. I have always had to go in an massage a VB4/5 app containing
those controls when converting to VB6 or to another store, either for the
trouble you mentioned or other oddities. Usually to provide a work-around
for the work-around the previous programmer did. So don't feel alone. <g>
And don't expect the conversion to be seemless. One of the reasons I usually
replace DBGrid/Data Control at the first opportunity.
You might try placing a rebind just before the refresh ...
Post by DerekT
Data1.RecordSource = SQL
Data1.Refresh
DBGrid1.rebind
Post by DerekT
DBGrid1.Refresh
You might also place a DoEvents() between the retrieval and presention. Not
forever, just to test to see if the problem isn't one of
one-asking-before-the-other-is-ready.
-ralph
Ralph
2008-09-18 13:50:36 UTC
Permalink
Post by DerekT
No luck I'm afraid Ralph,
It just will not show any data when using ODBC against a SQL Server
database. Tried everything suggested and more.
It is always a PITA, but in my exerience there is ALWAYS a solution. The
solution may range from a subtle re-arrangement of the code to a wholesale
swap-out of the offending control, but there will be a solution, and the
latter is likely easier to do than you might think.

What follows is more exotica ... Just a list of things I would check/do if I
were sitting there.

First, is the 'program' using DAO or ADO? What follows assumes you are using
DAO. If you are using ADO, then we need to go in another direction.
Check the references. Make a note of the the exact library being used, eg,
DAO 3.51, DAO 2.5/3.51, ADO 2.5, ...
Open the Reference list and see if you have "Microsoft DAO 3.6 Object
Library" available. Don't select it yet. Keep what you have for the time
being.
If it is not available then download the appropriate Jet Package for your
development machine.
http://support.microsoft.com/kb/239114
(If you are using Vista then there is no package available - it should
already be there.)

[What version of MSAccess was the original project using? Something 2000 or
better, or pre-MSAccess 2000?]

[What's the exact connection string or code you are using to connect to the
database, the specific driver or provider you are using?]

[Do you have both VB5 and VB6 installed on your machine? Have you updated
VB6 to the latest SP?]

Create a simple test VB6 project with a Form that includes just a
DataControl and a DBGrid.
Reference "Microsoft DAO 3.6 Object Library".
Save it. Open up the VBP file and note the Ref# line for both controls.
Open up the original VBP file and compare them. Does VB6 bring in a
different GUID?
If it does copy the lines from the new project and replace the references in
the original. (AFTER saving a copy of the original project in a safe place
of course. <g>)
Then open the original project. If it loads without error, then go to
references and select "Microsoft DAO 3.6 Object Library".
Run the program.

- holler back
DerekT
2008-09-18 14:10:42 UTC
Permalink
I've got the answer Ralph

The problem was with the conversion from Access to SQLServer. Text fields
converted to a data type of nVarchar. I went through the tables and
converted them manually to type Varchar and the data is now showing in the
grid.

I don't know what type of data nVarChar is but the DBGrid doesn't like it.

Anyway I hope this solution is useful to others who may experience this
problem.

Best Regards,
Derek
Post by Ralph
Post by DerekT
No luck I'm afraid Ralph,
It just will not show any data when using ODBC against a SQL Server
database. Tried everything suggested and more.
It is always a PITA, but in my exerience there is ALWAYS a solution. The
solution may range from a subtle re-arrangement of the code to a wholesale
swap-out of the offending control, but there will be a solution, and the
latter is likely easier to do than you might think.
What follows is more exotica ... Just a list of things I would check/do if I
were sitting there.
First, is the 'program' using DAO or ADO? What follows assumes you are using
DAO. If you are using ADO, then we need to go in another direction.
Check the references. Make a note of the the exact library being used, eg,
DAO 3.51, DAO 2.5/3.51, ADO 2.5, ...
Open the Reference list and see if you have "Microsoft DAO 3.6 Object
Library" available. Don't select it yet. Keep what you have for the time
being.
If it is not available then download the appropriate Jet Package for your
development machine.
http://support.microsoft.com/kb/239114
(If you are using Vista then there is no package available - it should
already be there.)
[What version of MSAccess was the original project using? Something 2000 or
better, or pre-MSAccess 2000?]
[What's the exact connection string or code you are using to connect to the
database, the specific driver or provider you are using?]
[Do you have both VB5 and VB6 installed on your machine? Have you updated
VB6 to the latest SP?]
Create a simple test VB6 project with a Form that includes just a
DataControl and a DBGrid.
Reference "Microsoft DAO 3.6 Object Library".
Save it. Open up the VBP file and note the Ref# line for both controls.
Open up the original VBP file and compare them. Does VB6 bring in a
different GUID?
If it does copy the lines from the new project and replace the references in
the original. (AFTER saving a copy of the original project in a safe place
of course. <g>)
Then open the original project. If it loads without error, then go to
references and select "Microsoft DAO 3.6 Object Library".
Run the program.
- holler back
Ralph
2008-09-18 14:45:28 UTC
Permalink
Post by DerekT
I've got the answer Ralph
The problem was with the conversion from Access to SQLServer. Text fields
converted to a data type of nVarchar. I went through the tables and
converted them manually to type Varchar and the data is now showing in the
grid.
I don't know what type of data nVarChar is but the DBGrid doesn't like it.
Anyway I hope this solution is useful to others who may experience this
problem.
LOL

Thanks for reporting back.

Makes me feel kind of silly. I knew there could be problems with a
difference in datatypes, but since you said the DataControl worked, I just
assumed it was something farther down the line. (There's that curse of the
word assume. ha)

Glad you got it working and hope I didn't steer you too far off course.

-ralph
DerekT
2008-09-18 15:34:35 UTC
Permalink
Not at all, your help was very useful, made me break it down systematically
to get the answer.

Many thanks.
Post by Ralph
Post by DerekT
I've got the answer Ralph
The problem was with the conversion from Access to SQLServer. Text fields
converted to a data type of nVarchar. I went through the tables and
converted them manually to type Varchar and the data is now showing in the
grid.
I don't know what type of data nVarChar is but the DBGrid doesn't like it.
Anyway I hope this solution is useful to others who may experience this
problem.
LOL
Thanks for reporting back.
Makes me feel kind of silly. I knew there could be problems with a
difference in datatypes, but since you said the DataControl worked, I just
assumed it was something farther down the line. (There's that curse of the
word assume. ha)
Glad you got it working and hope I didn't steer you too far off course.
-ralph
DerekT
2008-09-18 13:01:59 UTC
Permalink
Ralph,

Another bit of info, instead of using an ODBC DSN to the new SQL Server
database, I tried an ODBC DSN to the old Access database and this works.

So OBDC ---> Access is OK
ODBC ----> SQL Server - Fails

What would the difference be in the returned recordset that could cause
this?

Best Regards,
Derek
Post by DerekT
Post by DerekT
Hi Ralph,
Very possibly used to be a VB5 prog but I dont want that to cloud the
issue.
Post by DerekT
So I have created a brand new prog in VB6 with a form that has a data
control and a data bound grid (dbGrid)
The data control I assume uses DAO. This populates OK as I can interrogate
it and see data. The dbGrid is a virgin grid with no design time stuff on
it
Post by DerekT
apart from the datamode and datasource
Here is the code
Data1.DatabaseName = "HelpDesk"
SQL = "select RequestID from Log"
Data1.RecordSource = SQL
Data1.Refresh
DBGrid1.Refresh
Grid properties are
DataMode = bound
Datasource = Data1
Still will not show any data in the grid, yet swith the Data control to
access the same data from JET instead of odbc and it works.
Cant figure out what is wrong surely if the Data Control has data then the
bound grid should show it?
The DBGrid and Data Control have always had odd bugs. But as you noted they
always seem get alone with Jet just fine. Probably because they were all
developed at the same time. <g>
That's why I asked to you to be more specific about the tools and versions
you were using. I have always had to go in an massage a VB4/5 app containing
those controls when converting to VB6 or to another store, either for the
trouble you mentioned or other oddities. Usually to provide a work-around
for the work-around the previous programmer did. So don't feel alone. <g>
And don't expect the conversion to be seemless. One of the reasons I usually
replace DBGrid/Data Control at the first opportunity.
You might try placing a rebind just before the refresh ...
Post by DerekT
Data1.RecordSource = SQL
Data1.Refresh
DBGrid1.rebind
Post by DerekT
DBGrid1.Refresh
You might also place a DoEvents() between the retrieval and presention. Not
forever, just to test to see if the problem isn't one of
one-asking-before-the-other-is-ready.
-ralph
Ralph
2008-09-18 14:38:19 UTC
Permalink
Post by DerekT
Ralph,
Another bit of info, instead of using an ODBC DSN to the new SQL Server
database, I tried an ODBC DSN to the old Access database and this works.
So OBDC ---> Access is OK
ODBC ----> SQL Server - Fails
What would the difference be in the returned recordset that could cause
this?
Well, I could bore with you a lot of guesses, but honestly, as my son would
say it in a text message, "idk". Frankly I was surprised when you reported
the DataControl had no trouble while the DBGrid did. Had the DataControl
also failed my advice in my previous message would have been slightly
different.

Appreciate that ODBC is not a 'thing' in its own right - it is merely a
protocol. It defines the behavior of a thing that sits between an
Application and the database engine. It defines an API for both sides...

Data Access Library
<client interface API>
ODBC Driver Manager
<database interface API>
Vendor's ODBC Driver
Vendor's Data Engine

ODBC has changed through the years. The most major one was the introduction
of Level 2 APIs. The original ODBC protocol define "Core" and "Level 1"
APIs. With the introduction of Level 2, Level 1 was "deprecated". Some
vendors decided that meant "no longer supported". Others interpreted that to
mean Level 2 was "additional".

The end result, is while ODBC Drivers are meant to be like plug'ns - mix and
match often doesn't work. You often have to have a complete matched set -
the program Data Access Library, the ODBC Driver, and the Vendor's ODBC
Drivers all have to be on the same page. With bound controls - you have to
include their versioning in the mix.

At this point we need to find what that mix is, without going to extremes (a
la rewriting the whole damn thing with ADO and another Grid. ha). But take
heart. There will be a solution.

DSNs are merely a simple way to give a single "id" to a particular
connection scheme. It is not surprising at all that a specific ODBC Driver
that works with MSAccess fails when connected to another database engine.

-ralph
Ralph
2008-09-17 16:09:52 UTC
Permalink
"DerekT" <***@bull.co.uk> wrote in message news:%***@TK2MSFTNGP02.phx.gbl...

Forgot to mention that .ClearFields is another method to play with.

It relates back to DBGrid having trouble with layout changes. If the
structure of the data changes (number of columns) with refresh DBGrid can
become confused. This can also happen with "no columns" going to "some
columns".

When you find the cure, please report back so others will know what might
work.

-ralph
ais
2008-11-13 13:34:01 UTC
Permalink
hey i am not sure whether this is relevant but the db grid is not in my
components do u know where i could get it since i have a project to do and
desperately need it
thanks a lot
-ais
Ralph
2008-11-13 15:49:04 UTC
Permalink
Post by ais
hey i am not sure whether this is relevant but the db grid is not in my
components do u know where i could get it since i have a project to do and
desperately need it
thanks a lot
-ais
If you are using VB6 you should have the following components available when
you select Project->Components->Controls tab.

Microsoft Data Bound Grid Control 5.0 (SP3)
dbgrid32.ocx
Microsoft DataGrid Control 6.0 (SP5) (OLEDB)
msdatgrd.ocx
Microsoft Grid Control
Grid32.ocx
Microsoft Hierarchical FlexGrid Control 6.0 (SP4) (OLEDB)
mshflxgd.ocx

The first two are what are commonly called a "DBGrid".

-ralph
ais
2008-11-14 07:13:05 UTC
Permalink
the main problem is that the first component on ur list which is the data
bound grid control is not available and i do not have the cd to re-install
since i had borrowed it from a friend so do u know where i can find it
online?
Or else are you aware whether i can use the data grid instead since i have
to make a search from where the user selects the data from a db combo box and
the records containing the data appear in the grid
thanks a lot
-ais
Ralph
2008-11-14 21:19:26 UTC
Permalink
Post by ais
the main problem is that the first component on ur list which is the data
bound grid control is not available and i do not have the cd to re-install
since i had borrowed it from a friend so do u know where i can find it
online?
Or else are you aware whether i can use the data grid instead since i have
to make a search from where the user selects the data from a db combo box and
the records containing the data appear in the grid
thanks a lot
-ais
That seems odd, as I thought they were part of the basic install. But its
been years since I messed about with VB versions and what came with what.
<g>

I'm not aware of any "official" download location for these controls. But
you can often obtain common dlls online from various websites - just be sure
to have your FireWall up and running. ha

If you are not using DAO but ADO then I would recommend using the DataGrid
anyway. While you're at it you might check out the Data Repeater and Data
ComboBox. However, if you are not using a fully installed edition - those
components may not be available either.

-ralph

Loading...