Re: SQL Insert - Solved

Author: Jeff Johnson

Posted: 2014-01-03 at 07:29:19

Sorry to bother you guys with this one. I should have known. I

expected that records inserted in sequence would be queried in the same

sequence. I counted the records this morning and they are all there.

Just out of sequence.

I did learn a lot by the discussion, though. Especially Frank's

improved way of building the query strings.

Thanks all.

Jeff

Jeff Johnson

SanDC, Inc.

(623)-582-0323

www.san-dc.com <http://www.san-dc.com>

www.cremationtracker.com <http://www.cremationtracker.com>

www.arelationshipmanager.com <http://www.arelationshipmanager.com>

On 1/3/2014 5:13 AM, Jeff Johnson wrote:

> That makes sense. Thank you Frank. I am also guessing it would be

> faster. What I am seeing could be the result of a timing issue. I am

> going to check this morning to see if the records that are missing are

> reproducible.

>

> Jeff

>

> Jeff Johnson

> SanDC, Inc.

> (623)-582-0323

>

> www.san-dc.com <http://www.san-dc.com>

> www.cremationtracker.com <http://www.cremationtracker.com>

> www.arelationshipmanager.com <http://www.arelationshipmanager.com>

>

>

> On 1/3/2014 4:52 AM, Frank Cazabon wrote:

>> I'm not seeing any problems there. Maybe you could add in a query

>> for the record you just inserted to see if it was inserted.

>>

>> Here's how I would do it, checking if the record inserted

>> successfully, only for testing's sake:

>>

>> * I would change your SQL to use parameters all the way through,

>> * Code like this would be faster/easier to maintain but you can

>> ignore it if you want:

>>

>> TEXT TO m.lcSQL NOSHOW TEXTMERGE

>> INSERT INTO SQLSERVERTABLE (

>> DP_WKSEQNO

>> ,DP_WKCOMPANY_PS

>> ,DP_WKPAYGROUP_PS

>> ,DP_WKPAYGROUP

>> ,DP_WKDATE

>> ,DP_WKTRIPNO

>> ,DP_WKTRIPQ

>> ,DP_WKEVENT

>> ,DP_WKSTDATE

>> ,DP_WKSTTIME

>> ,DP_WKENDDATE

>> ,DP_WKENDTIME

>> ,DP_WKMINS

>> ,DP_WKSTAT

>> ,DP_WKDRIVER

>> ,DP_WKCODE1

>> ,DP_WKCODE2

>> ,DP_WKLOC

>> ,DP_WKACCNT

>> ,DP_WKHUB

>> ,DP_WKTODMILES

>> ,DP_WKMIMARK

>> ,DP_WKWEIGHT

>> ,DP_WKQUANT

>> ,DP_WKHOOK1

>> ,DP_WKPAYCODE

>> ,DP_WKGUID

>> ,DP_WKDATETIME

>> ,DP_WKSTATUS

>> ,DP_WKPROCESSED)

>> VALUES (

>> ?m.wkseqno

>> ,?m.pscompany

>> ,?m.pspaygroup

>> ,?m.wkpaygroup

>> ,?m.wkdate

>> ,?m.wktripno

>> ,?m.wktripq

>> ,?m.wkevent

>> ,?m.wkstdate

>> ,?m.wksttime

>> ,?m.wkenddate

>> ,?m.wkendtime

>> ,?m.wkmins

>> ,?m.wkstat

>> ,?m.wkdriver

>> ,?m.wkcode1

>> ,?m.wkcode2

>> ,?m.wkloc

>> ,?m.wkaccnt

>> ,?m.wkhub

>> ,?m.wktodmiles

>> ,?m.wkmimark

>> ,?m.wkweight

>> ,?m.wkquant

>> ,?m.wkhook1

>> ,?m.wkpaycode

>> ,?m.wkguid

>> ,?m._wkdatetime,

>> ,?m.wkstatus

>> ,'N')

>> ENDTEXT

>>

>>

>> SCAN

>> SCATTER MEMVAR MEMO

>>

>> lnsuccess = SQLEXEC(lnSQLhandle, m.lcSQL)

>>

>> IF lnsuccess < 1

>>

>> =MESSAGEBOX('SQL Select failed: ' + TRANSFORM(lnsuccess) + "'"

>> + m.lcSQL+ "'")

>> lclogstring = lclogstring + lcSQL + lccrlf + lccrlf

>> EXIT

>> ELSE

>> * check if record was saved

>> lnsuccess = SQLEXEC(lnSQLhandle, "SELECT recinserted =

>> COUNT(dp_wkseqno) FROM SQLServerTable WHERE dp_wkseqno = ?m.wkseqno",

>> "c_Result")

>> IF m.lnSuccess < 1

>> * failed

>> ELSE

>> IF c_Result.RecInserted <> 1

>> * failed or multiple seqno

>> SET STEP ON

>> ENDIF

>> ENDIF

>>

>> ENDIF

>>

>> ENDSCAN

>>

>

>

>

> --- StripMime Report -- processed MIME parts ---

> multipart/alternative

> text/plain (text body -- kept)

> multipart/related

> text/html

> image/jpeg

> ---

>

[excessive quoting removed by server]

_______________________________________________

Post Messages to: ProFox@leafe.com

Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox

OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech

Searchable Archive: http://leafe.com/archives/search/profox

This message: http://leafe.com/archives/byMID/profox/52C6BB2F.3080007@san-dc.com

** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.

©2014 Jeff Johnson