ISTech Support Forum

ISTech Support Forum
http://www.istechforum.com/YaBB.pl
Crystal Reports, ODBC & Access >> Access General Issues >> APPEND QUERY inserts multiple lines into DB
http://www.istechforum.com/YaBB.pl?num=1304514603

Message started by HB on 05/04/11 at 06:10:02

Title: APPEND QUERY inserts multiple lines into DB
Post by HB on 05/04/11 at 06:10:02

I am trying to insert an item into an existing S/O using an append query. (this is done because we are giving a free item to customers provided they meet certain qty condistions - which I establish in a seperate query)
The append query has only the following entries:

Field1: EXPR1:"12345" > Append to BKAR_INVL_INVM (12345 represents the S/O # that the item is added to).
Field2: EXPR2:"S650" > Append to BKAR_INVL_PCODE (S650 represents the product ID)
Field3: EXPR3:"1" > Append to BKAR_INVL_PQTY

The problem is that when running the query, it inserts 40+ line items instead of only one.
Where do I go wrong :-?

Title: Re: APPEND QUERY inserts multiple lines into DB
Post by Kelloggs on 05/04/11 at 07:40:56

There is another way to do that
Public Sub my_insert()
Dim var_sql as string
var_sql = "INSERT INTO MY_TABLE (FIELD_1, FIELD_2) VALUES (1,2)"
conn_dba(var_sql)
End Sub

Public Function conn_dba(var_sql As String)
On Error GoTo Err_conn_dba

Dim cn As ADODB.Connection
Dim cmd As ADODB.Command

Set cn = New ADODB.Connection
Set cmd = New ADODB.Command

cn.ConnectionString = "DSN=DBA"
cn.Open

Set cmd.ActiveConnection = cn
cmd.CommandText = var_sql
cmd.Execute

cn.Close
Set cmd = Nothing
Set cn = Nothing

Exit_conn_dba:
Exit Function
Err_conn_dba:
MsgBox Err.Description
Resume Exit_conn_dba
End Function

By the way these are the fields that you should include (I think):

BKAR_INVL_INVNM,
BKAR_INVL_CNTR,
BKAR_INVL_ESD,
BKAR_INVL_PCODE,
BKAR_INVL_PDESC,
BKAR_INVL_PQTY,
BKAR_INVL_PPRCE,
BKAR_INVL_PEXT,
BKAR_INVL_TXBLE,
BKAR_INVL_LOC,
BKAR_INVL_UM_LN_1,
BKAR_INVL_UM_LN_2,
BKAR_INVL_COOP,
BKAR_INVL_OOQTY,
BKAR_INVL_SCCOG

:-?

Kelloggs

Title: Re: APPEND QUERY inserts multiple lines into DB
Post by Kelloggs on 05/04/11 at 08:08:29

If you want to do it the traditional way: Append Query.
Create a "Temp" table with exactly the same fields.

This would be the SQL for you append query:

INSERT INTO BKARINVL ( BKAR_INVL_INVNM, BKAR_INVL_CNTR, BKAR_INVL_ESD, BKAR_INVL_PCODE, BKAR_INVL_PDESC, BKAR_INVL_PQTY, BKAR_INVL_PPRCE, BKAR_INVL_PEXT, BKAR_INVL_TXBLE, BKAR_INVL_LOC, BKAR_INVL_UM_LN_1, BKAR_INVL_UM_LN_2, BKAR_INVL_COOP, BKAR_INVL_OOQTY, BKAR_INVL_SCCOG )
SELECT BKAR_INVL_INVNM, BKAR_INVL_CNTR, BKAR_INVL_ESD, BKAR_INVL_PCODE, BKAR_INVL_PDESC, BKAR_INVL_PQTY, BKAR_INVL_PPRCE, BKAR_INVL_PEXT, BKAR_INVL_TXBLE, BKAR_INVL_LOC, BKAR_INVL_UM_LN_1, BKAR_INVL_UM_LN_2, BKAR_INVL_COOP, BKAR_INVL_OOQTY, BKAR_INVL_SCCOG
FROM Temp;

:P

Kelloggs

Title: Re: APPEND QUERY inserts multiple lines into DB
Post by Lynn_Pantic on 05/04/11 at 08:19:54

Also keep in mind that updating an order this way rather than using the program the way it was designed will not update the item stock status and if the item being added to the order was not a giveaway item, the open order balance and other fields would also not be updated.

Title: Re: APPEND QUERY inserts multiple lines into DB
Post by HB on 05/04/11 at 08:21:47

Thanks a lot for your help. I will try your 2nd suggestion with the temp file

Title: Re: APPEND QUERY inserts multiple lines into DB
Post by Kelloggs on 05/04/11 at 08:59:54

Lynn_Pantic wrote:

yes, but if you "resaved" the order before been release and later invoice. It should work fine right?

:-[

Kelloggs

Title: Re: APPEND QUERY inserts multiple lines into DB
Post by HB on 05/04/11 at 10:44:52

tried the temp file solution and then append to BKARINVL - still getting 40+ line item additions with the same item number.

Title: Re: APPEND QUERY inserts multiple lines into DB
Post by Kelloggs on 05/04/11 at 12:19:07

I did test it twice. It works OK.
But before going any further. I hope you are testing all this on your play company right??
Do not try it on your live data

You must be doing something different or you table needs some serious reindexing (BACKUP, BACKUP, BACKUP)

did you try the first method?

:-?
Kelloggs

Title: Re: APPEND QUERY inserts multiple lines into DB
Post by HB on 05/05/11 at 06:28:03

I am still trying to make it work (on a test company :)). I am also considering alternatives because of the indications Lynn brought up.

Title: Re: APPEND QUERY inserts multiple lines into DB
Post by Kelloggs on 05/05/11 at 07:55:34

It should work.

Let us know if we can help.

8-)

Kelloggs

PD: dont forget that for text and date fields you need to use : ....,'Text','05/02/2011',......