Re: Weird Update Behaviour

From: "Henshall, Stuart - WCP" <SHenshall(at)westcountrypublications(dot)co(dot)uk>
To: "'cgibbs(at)westmarkproducts(dot)com'" <cgibbs(at)westmarkproducts(dot)com>, pgsql-odbc(at)postgresql(dot)org
Subject: Re: Weird Update Behaviour
Date: 2002-03-18 11:41:28
Message-ID: E2870D8CE1CCD311BAF50008C71EDE8E01F7482F@MAIL_EXCHANGE
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-odbc

I assume quote_number is your primary key. It also looks to be a text field
which I know MS Access & DAO (and I guess ADO is similar) translates to memo
field unless you have unchecked Text As LongVarChar in the PostgreSQL ODBC
driver options. When you update a recordset it updates on either primary key
and xmin if it can (if row versioning is enabled) or on every non-memo
field. I suspect that what is happening is that since your primary key
appears to be a memo field it isn't using it to tell which row to update
just the other none memo fields.
Hope this helps,
- Stuart

> -----Original Message-----
> From: Corey W. Gibbs [mailto:cgibbs(at)westmarkproducts(dot)com]
> Sent: 16 March 2002 00:08
> To: pgsql-odbc(at)postgresql(dot)org
> Subject: Weird Update Behaviour
>
>
> Good Day Everyone,
>
> Here's something weird
> So, i'm using VB and ODBC and I'm getting something weird weird weird
> happening. When ever a user updates this text field, it gets
> put all over
> the same column.
>
> So if low_bid_notes.Text = "Talk about frustrating" when the With
> rs2.Update would execute, "Talk about frustrating" would
> appear all over
> that column, not just on the row where quote_number =
> data_estimating.recordset!quote_number
>
> but on anywhere from 15 to 50 other rows.
>
> rs2.Open "SELECT
> low_bid_notes,low_bid_spread,low_bid_spread_percent,"
> & _
>
>
> "addl_bid_notes,rebid,rebid_info,sales_person,date_complete,no
> _tabs_a
> vail,only_bidder,negotiated,no_tabs," & _
>
>
> "dealer_bid,accept_date,date_modified,plan_deposit,plan_return
> _date,l
> ow_bid_percent_sell FROM schedule WHERE
> quote_number='" & data_estimating.Recordset!quote_number
> & "'", cn,
> adOpenKeyset, adLockPessimistic
>
> cn is yer standard connection string that's worked time and
> time again.
>
> With rs2
> .Fields("no_tabs").Value = chk_no_tabs
> .Fields("negotiated").Value = chk_negotiated
> .Fields("only_bidder").Value = chk_only_bid
> .Fields("dealer_bid").Value = chk_dealer_bid
>
> .Fields("low_bid_percent_sell").Value =
> Format(txt_low_bid_percent_sell.Text, "general number")
> .Fields("low_bid_percent").Value =
> Format(txt_low_bid_percent.Text,
> "general number")
> .Fields("low_bid_notes").Value = txt_low_bid_notes.Text
> .Fields("low_bid_spread").Value = Format(txt_low_bid_spread.Text,
> "general number")
> .Fields("low_bid_spread_percent").Value =
> Format(txt_low_bid_spread_percent.Text, "general number")
> .Fields("date_modified") = Now()
>
> .Update
> .Close
>
>
> that's the code that's broken, here's what i did to fix it:
>
> cn.Execute "update schedule SET low_bid_notes='" &
> txt_low_bid_notes.Text &
> "'" & _
> " ,low_bid_percent_sell ='" & Format(txt_low_bid_percent_sell.Text,
> "general number") & "'" & _
> " WHERE quote_number = '" & txt_quote_id(0).Text & "'"
>
>
> instead of using the ADO recordset, i just did an execute
> command on the cn
> connection. when done like that, it works just fine.
>
> So, my questions are:
> What could cause this? Is it an ADO or ODBC thing? Am i
> forgetting to
> close or set a recordset to nothing somewhere? Thanks in
> advance and have
> a good weekend.
> corey
>
>

Browse pgsql-odbc by date

  From Date Subject
Next Message Henshall, Stuart - WCP 2002-03-18 11:44:49 Re: replacing multi-table-queries-bound-forms by view-bound forms
Previous Message Hiroshi Inoue 2002-03-18 04:48:19 Re: QUESTION: OID and fake index do i "need" to use it?