Thread: basic questions with odbc and visual basic.

Hello,

I have some basic questions regarding visual basic and odbc.  So far,
I've been having problems getting Visual Basic/Access to write data back
to my database via odbc.  Normally I use Delphi for this type of thing,
but some of our clients are insisting on vb 6.0 access.

The biggest problem I have is that updating data from a VB app always
fails...the problem is that at update time ADO builds a where clause
containing every field in the table, not just the primary key.
Invariably, this causes the update to not to work.  If I have some type
of integer primary key for my table is there some way of forcing the
driver row resolution to write queries in the form of

update t set [] where f = k?

instead of

update t set [] where f1 = a, f2 = b, f3 = c [...] for all the fields in
t?


Merlin

> Yes, you can, if you are willing to handle the save yourself.  I also
> prefer
> Delphi but have done a lot of VB coding. I wrote some libraries to
handle
> the back end, but the core is something like:
>
>     sConnection = "DSN=" & SomeDSN & ";UID=" & SomeUser & ";PWD=" &
> SomePasswd & ";"
>     Set cnn = New ADODB.Connection
>     cnn.Open sConnection
>
>     Set rs = New ADODB.Recordset
>     Set comm = New ADODB.Command
>     comm.CommandText = "update t set [] where f = k?"
>     comm.Execute

In this case the recordset is read only then?  I need to find some way
to allow the data aware components to work.  I did some more research
and found there supposedly is an 'update criteria' property to fix the
update problem
http://support.microsoft.com/default.aspx?scid=kb;EN-US;190727 but that
doesn't work.  Can it really be there is no way to edit data in data
aware controls in access/vb?

Merlin

I have done a fair amount with VB and PostgreSQL ODBC and I am perplexed
by what you have described as your problem. You make it sound as it VB
is "making up" the SQL statements to send.

My update sections, typically look like:

Dim cnn as connection
Dim sql_str as string

cnn.Open "my_datasource_specificatin_goes_here"
sql_str = "UPDATE t set f1=a WHERE f2=c"
cnn.Execute sql_str
cnn.Close
set cnn = Nothing

Of course, my SQL strings are usually a complex concatention of
variables and such.
This is practically a pass-through query. Logging on the client and
server should verify this.

Did you leave out some information, like you are using a bound data
control perhaps?

You mentioned Access. I could say look for the abundant information on
Access issues. If you are trying to update from data table view in
Access -- I suggest make sure you have a primary key on your server
table, turn row-versioning on in the PostgreSQL driver. Try it on a
table with OIDs and without OIDs to see if it makes a difference for
you. This only applies to data table view. Updating in code modules is a
programmer issue about using ADO.




Merlin Moncure wrote:

>Hello,
>
>I have some basic questions regarding visual basic and odbc.  So far,
>I've been having problems getting Visual Basic/Access to write data back
>to my database via odbc.  Normally I use Delphi for this type of thing,
>but some of our clients are insisting on vb 6.0 access.
>
>The biggest problem I have is that updating data from a VB app always
>fails...the problem is that at update time ADO builds a where clause
>containing every field in the table, not just the primary key.
>Invariably, this causes the update to not to work.  If I have some type
>of integer primary key for my table is there some way of forcing the
>driver row resolution to write queries in the form of
>
>update t set [] where f = k?
>
>instead of
>
>update t set [] where f1 = a, f2 = b, f3 = c [...] for all the fields in
>t?
>
>
>Merlin
>
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faqs/FAQ.html
>
>

> In this case the recordset is read only then?  I need to find some way
> to allow the data aware components to work.  I did some more research
> and found there supposedly is an 'update criteria' property to fix the
> update problem
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;190727 but that
> doesn't work.  Can it really be there is no way to edit data in data
> aware controls in access/vb?
>
> Merlin
>

I gave up trying to use data-aware controls in VB a very long time ago
(about 3 weeks after trying to get them to work AFAIR). I have used MS
SQLServer and Sybase SQL Anywhere with VB 6.

If you use readonly recordsets to fetch data and simple update/insert
commands to change data, the whole thing is much easier and more
efficient in the long run.

You probably can get it to work eventually. I did for small utilities and
such, but would not consider it for anything serious.

Cheers,
Gary.

> I have done a fair amount with VB and PostgreSQL ODBC and I am
perplexed
> by what you have described as your problem. You make it sound as it VB
> is "making up" the SQL statements to send.
[...]
> Did you leave out some information, like you are using a bound data
> control perhaps?

Yes, problem is with data-aware controls.  The query ado generates to
write the data back to the server from the recordset checks every single
field in the table to find the column which fails.

For example (skipping connection, etc.),
rs.Fields("soeme_field") = "set"
rs.Update

will 1: generate a query which will fail to update the proper record
(confirmed by logging the query and trying in psql) and 2: generate an
exception in vb which reads:

multiple step old db operation generated errors...

Merlin

I too almost never use data-aware controls. I use ADO to get the data. I
use code to populate a grid.
When a chnange event is fired, I use code to update the row in question.
Why all the work?

Data aware controls tend to be heavy -- they don't know how users are
going to use them -- so the code in them is not lean or application
specific. They do code "under the covers" for lowest common denominator
in an MS presumed world. So if you are not using Access or SQLServer--
they tend to act funny -- in fact I found they act funny when you use MS
products.  Some people use them with great success -- more RAD power to
them. I am old school and follow the axioms of transaction between
client and server being resource expensive. I tend to code to get just
the fields and records I need -- and update just the records I want. I
try not to hold open a whole multi-row recordset, because I want to
update 1 row.


With luck someone else on the mailing list has advice for optimizing
with data-aware controls.




Merlin Moncure wrote:

>>I have done a fair amount with VB and PostgreSQL ODBC and I am
>>
>>
>perplexed
>
>
>>by what you have described as your problem. You make it sound as it VB
>>is "making up" the SQL statements to send.
>>
>>
>[...]
>
>
>>Did you leave out some information, like you are using a bound data
>>control perhaps?
>>
>>
>
>Yes, problem is with data-aware controls.  The query ado generates to
>write the data back to the server from the recordset checks every single
>field in the table to find the column which fails.
>
>For example (skipping connection, etc.),
>rs.Fields("soeme_field") = "set"
>rs.Update
>
>will 1: generate a query which will fail to update the proper record
>(confirmed by logging the query and trying in psql) and 2: generate an
>exception in vb which reads:
>
>multiple step old db operation generated errors...
>
>Merlin
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>
>

> I too almost never use data-aware controls. I use ADO to get the data.
I
> use code to populate a grid.
> When a chnange event is fired, I use code to update the row in
question.
> Why all the work?
>
> Data aware controls tend to be heavy -- they don't know how users are
> going to use them -- so the code in them is not lean or application
> specific. They do code "under the covers" for lowest common
denominator
> in an MS presumed world. So if you are not using Access or SQLServer--
> they tend to act funny -- in fact I found they act funny when you use
MS
> products.  Some people use them with great success -- more RAD power
to
> them. I am old school and follow the axioms of transaction between
> client and server being resource expensive. I tend to code to get just
> the fields and records I need -- and update just the records I want. I
> try not to hold open a whole multi-row recordset, because I want to
> update 1 row.
>
>
> With luck someone else on the mailing list has advice for optimizing
> with data-aware controls.

Right.  I agree 100%...I think RAD tools make great screen designers and
get you into trouble if you take them beyond that.  In fact, I would
never even be looking at VB except I have to train a couple of VB
jockeys from another company on how to access our system so they can
make simple forms and run reports.

My initial thought was to build views on the server encapsulating the
more complex aspects of the database design for the vb guys to read and
write from.  I had hoped that it would be relatively easy to bind these
views to various Microsoft stuff...and it is, but in a slightly more
code intensive way than I had expected.  I may try giving the ole db
driver a shot and see if it provides better results.

Merlin

Ahh, I see now.
Well for reports, which are pretty much read-only as far as data
concerned you have lots of freedom.

For manipulating the data -- Access works pretty good as long as Access
can know which record(s) to update (primary keys or OIDs -- that sort of
thing).


Merlin Moncure wrote:

>>I too almost never use data-aware controls. I use ADO to get the data.
>>
>>
>I
>
>
>>use code to populate a grid.
>>When a chnange event is fired, I use code to update the row in
>>
>>
>question.
>
>
>>Why all the work?
>>
>>Data aware controls tend to be heavy -- they don't know how users are
>>going to use them -- so the code in them is not lean or application
>>specific. They do code "under the covers" for lowest common
>>
>>
>denominator
>
>
>>in an MS presumed world. So if you are not using Access or SQLServer--
>>they tend to act funny -- in fact I found they act funny when you use
>>
>>
>MS
>
>
>>products.  Some people use them with great success -- more RAD power
>>
>>
>to
>
>
>>them. I am old school and follow the axioms of transaction between
>>client and server being resource expensive. I tend to code to get just
>>the fields and records I need -- and update just the records I want. I
>>try not to hold open a whole multi-row recordset, because I want to
>>update 1 row.
>>
>>
>>With luck someone else on the mailing list has advice for optimizing
>>with data-aware controls.
>>
>>
>
>Right.  I agree 100%...I think RAD tools make great screen designers and
>get you into trouble if you take them beyond that.  In fact, I would
>never even be looking at VB except I have to train a couple of VB
>jockeys from another company on how to access our system so they can
>make simple forms and run reports.
>
>My initial thought was to build views on the server encapsulating the
>more complex aspects of the database design for the vb guys to read and
>write from.  I had hoped that it would be relatively easy to bind these
>views to various Microsoft stuff...and it is, but in a slightly more
>code intensive way than I had expected.  I may try giving the ole db
>driver a shot and see if it provides better results.
>
>Merlin
>
>
>
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 9: the planner will ignore your desire to choose an index scan if your
>      joining column's datatypes do not match
>
>

> For manipulating the data -- Access works pretty good as long as
Access
> can know which record(s) to update (primary keys or OIDs -- that sort
of
> thing).


Looking deeper it seems all my problems are really boiling down to one
problem: the where clause used to update the edited record back to the
server.  Both access and vb generate a where clause that includes every
field in the table instead of the p-key.  For various sundry reasons
such as improper handling of null values, etc. this where clause gets
busted.  This problem is aggravated by the fact that all my domains get
treated as text types, although this is correctable by using a view
which I was planning to use anyways (aside: I make heavy use of
domains).  This busted where clause trips up vb and access in different
ways.  This may be a case where I may want to explore hacking the odbc
driver to get it to do what I want, depending on who is building the
clause.  My guess is the ado driver is building it so I'm basically
hosed.  It's really queer to me that they use the p-key to pull the data
out and the entire record to write the data back...what's the point of
the p-key anyways?

The odbc driver is supposed to have a property exposed to the ado
wrapper that allows you to constrain the write-back where clause to
different things, but apparently this does not work in pgsql-odbc.

I tried the ole db driver and got nowhere, couldn't even get a basic
connection.  Hopefully I can get this corrected and work with this
driver a little bit.  I have about two weeks to figure out the best way
to proceed.  So far, I'm really unimpressed with the ado architecture,
this being my first real experience with it (as compared to ado.net,
which is a dream to work with).

Merlin

On 27 Sep 2004 at 16:42, Merlin Moncure wrote:

> > For manipulating the data -- Access works pretty good as long as
> Access
> > can know which record(s) to update (primary keys or OIDs -- that sort
> of
> > thing).
>
>
> Looking deeper it seems all my problems are really boiling down to one
> problem: the where clause used to update the edited record back to the
> server.  Both access and vb generate a where clause that includes every
> field in the table instead of the p-key.  For various sundry reasons
> such as improper handling of null values, etc. this where clause gets
> busted.  This problem is aggravated by the fact that all my domains get
> treated as text types, although this is correctable by using a view
> which I was planning to use anyways (aside: I make heavy use of
> domains).  This busted where clause trips up vb and access in different
> ways.  This may be a case where I may want to explore hacking the odbc
> driver to get it to do what I want, depending on who is building the
> clause.  My guess is the ado driver is building it so I'm basically
> hosed.  It's really queer to me that they use the p-key to pull the data
> out and the entire record to write the data back...what's the point of
> the p-key anyways?
>
> The odbc driver is supposed to have a property exposed to the ado
> wrapper that allows you to constrain the write-back where clause to
> different things, but apparently this does not work in pgsql-odbc.
>
> I tried the ole db driver and got nowhere, couldn't even get a basic
> connection.  Hopefully I can get this corrected and work with this
> driver a little bit.  I have about two weeks to figure out the best way
> to proceed.  So far, I'm really unimpressed with the ado architecture,
> this being my first real experience with it (as compared to ado.net,
> which is a dream to work with).
>

The only reason the ADO driver would build an update statement using
all columns is that it can't identify a unique key (primary key). It's
possible that the ODBC driver does not correctly supply this info.

Can you do something artificial like put an additional unique index on
the primary key?

As a last resort you might try a commercial ODBC driver for postgres
,openlink I think for one, to see if this helps. At least it may give pointers
to the real problem. ( you can get a trial copy for a while I think)

Cheers,
Gary.

The traditional hack for faking a unique key is to add a timestamp
column to the database table in question-- unique indexed - with a
default timestamp of Now().

Gary Doades wrote:

>On 27 Sep 2004 at 16:42, Merlin Moncure wrote:
>
>
>
>>>For manipulating the data -- Access works pretty good as long as
>>>
>>>
>>Access
>>
>>
>>>can know which record(s) to update (primary keys or OIDs -- that sort
>>>
>>>
>>of
>>
>>
>>>thing).
>>>
>>>
>>
>>
>>Looking deeper it seems all my problems are really boiling down to one
>>problem: the where clause used to update the edited record back to the
>>server.  Both access and vb generate a where clause that includes every
>>field in the table instead of the p-key.  For various sundry reasons
>>such as improper handling of null values, etc. this where clause gets
>>busted.  This problem is aggravated by the fact that all my domains get
>>treated as text types, although this is correctable by using a view
>>which I was planning to use anyways (aside: I make heavy use of
>>domains).  This busted where clause trips up vb and access in different
>>ways.  This may be a case where I may want to explore hacking the odbc
>>driver to get it to do what I want, depending on who is building the
>>clause.  My guess is the ado driver is building it so I'm basically
>>hosed.  It's really queer to me that they use the p-key to pull the data
>>out and the entire record to write the data back...what's the point of
>>the p-key anyways?
>>
>>The odbc driver is supposed to have a property exposed to the ado
>>wrapper that allows you to constrain the write-back where clause to
>>different things, but apparently this does not work in pgsql-odbc.
>>
>>I tried the ole db driver and got nowhere, couldn't even get a basic
>>connection.  Hopefully I can get this corrected and work with this
>>driver a little bit.  I have about two weeks to figure out the best way
>>to proceed.  So far, I'm really unimpressed with the ado architecture,
>>this being my first real experience with it (as compared to ado.net,
>>which is a dream to work with).
>>
>>
>>
>
>The only reason the ADO driver would build an update statement using
>all columns is that it can't identify a unique key (primary key). It's
>possible that the ODBC driver does not correctly supply this info.
>
>Can you do something artificial like put an additional unique index on
>the primary key?
>
>As a last resort you might try a commercial ODBC driver for postgres
>,openlink I think for one, to see if this helps. At least it may give pointers
>to the real problem. ( you can get a trial copy for a while I think)
>
>Cheers,
>Gary.
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>
>

> On 27 Sep 2004 at 16:42, Merlin Moncure wrote:
> The only reason the ADO driver would build an update statement using
> all columns is that it can't identify a unique key (primary key). It's
> possible that the ODBC driver does not correctly supply this info.
>
> Can you do something artificial like put an additional unique index on
> the primary key?

There are several unique indexes on most of my tables.  Access correctly
determines the primary key for the raw tables (and for the views, allows
me to select the correct one).  It pulls the data out by the p-key, but
rights it back using the whole row.  Same for bound recordset in vb.

Using Zeos components and Delphi (actually C++ builder), I have no
problems writing back data through the OLE DB wrapper for the ODBC
driver (itself thinly wrapped by Zeos).  In fact, this even gets the
tangential cases correct such as domains and arrays.

I can only surmise that the VB/Access runtimes are building the spurious
update statement.  Using views and constraining things to a few columns,
I can update a view through access or vb.  Can you confirm that your
update statements use the p-key in the where clause of your update
statements (only)?

Thx for help all.

Merlin

Do you have driver option "Row Versioning" set to
true?  I'm vague on the details, but I suspect that
may help.

--- Merlin Moncure <merlin.moncure@rcsonline.com>
wrote:

> > For manipulating the data -- Access works pretty
> good as long as
> Access
> > can know which record(s) to update (primary keys
> or OIDs -- that sort
> of
> > thing).
>
>
> Looking deeper it seems all my problems are really
> boiling down to one
> problem: the where clause used to update the edited
> record back to the
> server.  Both access and vb generate a where clause
> that includes every
> field in the table instead of the p-key.  For
> various sundry reasons
> such as improper handling of null values, etc. this
> where clause gets
> busted.  This problem is aggravated by the fact that
> all my domains get
> treated as text types, although this is correctable
> by using a view
> which I was planning to use anyways (aside: I make
> heavy use of
> domains).  This busted where clause trips up vb and
> access in different
> ways.  This may be a case where I may want to
> explore hacking the odbc
> driver to get it to do what I want, depending on who
> is building the
> clause.  My guess is the ado driver is building it
> so I'm basically
> hosed.  It's really queer to me that they use the
> p-key to pull the data
> out and the entire record to write the data
> back...what's the point of
> the p-key anyways?
>
> The odbc driver is supposed to have a property
> exposed to the ado
> wrapper that allows you to constrain the write-back
> where clause to
> different things, but apparently this does not work
> in pgsql-odbc.
>
> I tried the ole db driver and got nowhere, couldn't
> even get a basic
> connection.  Hopefully I can get this corrected and
> work with this
> driver a little bit.  I have about two weeks to
> figure out the best way
> to proceed.  So far, I'm really unimpressed with the
> ado architecture,
> this being my first real experience with it (as
> compared to ado.net,
> which is a dream to work with).
>
> Merlin
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 7: don't forget to increase your free space map
> settings
>




__________________________________
Do you Yahoo!?
Yahoo! Mail - 50x more storage than other providers!
http://promotions.yahoo.com/new_mail

> Do you have driver option "Row Versioning" set to
> true?  I'm vague on the details, but I suspect that
> may help.
>

That did the trick...bless you.

FWIW, I tracked down the failed updates to Access chopping timestamp
column to nearest second (note: this was not a field I was trying to
update).  The timestamp was wrapped in a domain which may have caused
the problem.  Have confirmed both successful edit of regular table in
both access and vb.

Merlin

--- Merlin Moncure <merlin.moncure@rcsonline.com>
wrote:

> > Do you have driver option "Row Versioning" set to
> > true?  I'm vague on the details, but I suspect
> that
> > may help.
> >
>
> That did the trick...bless you.

Access (can't answer for VB) handles concurrency on
updates by checking whether the data has changed since
the row was first fetched.  If you have a unique rowid
(which is what "row versioning" implies), then that is
used for comparison (the psqlodbc driver uses the ctid
value for that).  Otherwise, every field is checked,
as you were seeing.  If any of the data has changed,
the row is presumed to have been changed by another
user in the meantime, and the update will fail with an
error message saying so.

The problem with timestamps is that Access does not
handle fractional seconds, whereas PostgreSQL
timestamps do by default, so timestamp comparisons
become problematic.  None of my apps require
fractional seconds resolution, so I usually use
timestamp(0) for tables that I know will be used by an
Access application.

This is all overview; I have no idea where in the
chain this is implemented.

>
> FWIW, I tracked down the failed updates to Access
> chopping timestamp
> column to nearest second (note: this was not a field
> I was trying to
> update).  The timestamp was wrapped in a domain
> which may have caused
> the problem.  Have confirmed both successful edit of
> regular table in
> both access and vb.
>
> Merlin
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>




__________________________________
Do you Yahoo!?
Yahoo! Mail - 50x more storage than other providers!
http://promotions.yahoo.com/new_mail

> Access (can't answer for VB) handles concurrency on
> updates by checking whether the data has changed since
> the row was first fetched.  If you have a unique rowid
> (which is what "row versioning" implies), then that is
> used for comparison (the psqlodbc driver uses the ctid
> value for that).  Otherwise, every field is checked,
> as you were seeing.  If any of the data has changed,
> the row is presumed to have been changed by another
> user in the meantime, and the update will fail with an
> error message saying so.
>
> The problem with timestamps is that Access does not
> handle fractional seconds, whereas PostgreSQL
> timestamps do by default, so timestamp comparisons
> become problematic.  None of my apps require
> fractional seconds resolution, so I usually use
> timestamp(0) for tables that I know will be used by an
> Access application.

Yep. I do the same thing for tables accessed by deplhi apps.  There
could also be a problem with really large numerics (many client app
technologies use float or double internal representation) but this comes
up less often.  The row versioning tip was incredibly helpful however.

Merlin