Insert into colum gives error HELP plse

Insert into colum gives error HELP plse

Post by Norman Gree » Sun, 22 Dec 2002 10:22:37



Hi
I have A sql db that has a transactions table,I have a exe
that writes to this table and uses the same transaction ID
more than once in the primary key column, this works fine
but dont have the sorce code to this prog.

So I wrote my own using vb6 and when I try to insert into
the field I get the following error.
Violation of PRIMARY KEY constraint 'aaaaaTransaction_PK'.
Cannot insert duplicate key in object 'TransactionItems'.-
2147217873.

I can manualy add it direct in sqlmngr or running
MsQry32,without any error, but if I use code it
wont take it.

heres abit of the code I use to give idea where I might be
going wrong.

Dim CN As New ADODB.Connection
Dim RS As New ADODB.Recordset
Set lvw = Till_Module.ListView1
    CN = ReadWriteINI("GET", "DKPOS SETTINGS", "CN")
SQL2 = "INSERT INTO TransactionItems
(TransactionID,ItemID, ItemQTY) " _
& "VALUES ('" & uniqueID & "','" & li.SubItems(7) & "','"
& li.SubItems(2) & "')"

For j = 1 To Till_Module.ListView1.ListItems.Count
Set li = Till_Module.ListView1.ListItems.Item(j)
CN.Execute (SQL2)

There has to be a way for this to work please help me I am
desperate
Many Thanx
Norman

 
 
 

Insert into colum gives error HELP plse

Post by chris » Sun, 22 Dec 2002 14:14:00


Are you saying that your compiled exe is adding the same
value in the PK column for > 1 record?

And that "I can manualy add it direct in sqlmngr or
running MsQry32,without any error"... ?

Primary Key columns DO NOT except duplicate key values,
regardless of how you try to add it.

If it's a problem w/ your ado code, and your query is
passing a unique value to the PK column - and failing
because of PK constraint error, you need to double check
your values.

In your code snippet, what are you doing w/ the lvw and
CN? Check out my ado conn object on my website...

ChrisB
MCDBA MCSE
www.MyDBA2000.com

Quote:>-----Original Message-----
>Hi
>I have A sql db that has a transactions table,I have a
exe
>that writes to this table and uses the same transaction
ID
>more than once in the primary key column, this works fine
>but dont have the sorce code to this prog.

>So I wrote my own using vb6 and when I try to insert into
>the field I get the following error.
>Violation of PRIMARY KEY

constraint 'aaaaaTransaction_PK'.
Quote:>Cannot insert duplicate key in object 'TransactionItems'.-
>2147217873.

>I can manualy add it direct in sqlmngr or running
>MsQry32,without any error, but if I use code it
>wont take it.

>heres abit of the code I use to give idea where I might
be
>going wrong.

>Dim CN As New ADODB.Connection
>Dim RS As New ADODB.Recordset
>Set lvw = Till_Module.ListView1
>    CN = ReadWriteINI("GET", "DKPOS SETTINGS", "CN")
>SQL2 = "INSERT INTO TransactionItems
>(TransactionID,ItemID, ItemQTY) " _
>& "VALUES ('" & uniqueID & "','" & li.SubItems(7) & "','"
>& li.SubItems(2) & "')"

>For j = 1 To Till_Module.ListView1.ListItems.Count
>Set li = Till_Module.ListView1.ListItems.Item(j)
>CN.Execute (SQL2)

>There has to be a way for this to work please help me I
am
>desperate
>Many Thanx
>Norman

>.


 
 
 

Insert into colum gives error HELP plse

Post by Norma » Sun, 22 Dec 2002 16:55:33


The exe does add duplicates in the primary key column the
problem is I dont Know how as I dont have sorce code for it
so it appears that this can be done except how and what
can be done to achieve this. any ideas will be graetlkey
appreciated.

Quote:>-----Original Message-----

>Are you saying that your compiled exe is adding the same
>value in the PK column for > 1 record?

>And that "I can manualy add it direct in sqlmngr or
>running MsQry32,without any error"... ?

>Primary Key columns DO NOT except duplicate key values,
>regardless of how you try to add it.

>If it's a problem w/ your ado code, and your query is
>passing a unique value to the PK column - and failing
>because of PK constraint error, you need to double check
>your values.

>In your code snippet, what are you doing w/ the lvw and
>CN? Check out my ado conn object on my website...

>ChrisB
>MCDBA MCSE
>www.MyDBA2000.com

>>-----Original Message-----
>>Hi
>>I have A sql db that has a transactions table,I have a
>exe
>>that writes to this table and uses the same transaction
>ID
>>more than once in the primary key column, this works
fine
>>but dont have the sorce code to this prog.

>>So I wrote my own using vb6 and when I try to insert
into
>>the field I get the following error.
>>Violation of PRIMARY KEY
>constraint 'aaaaaTransaction_PK'.
>>Cannot insert duplicate key in

object 'TransactionItems'.-

- Show quoted text -

Quote:>>2147217873.

>>I can manualy add it direct in sqlmngr or running
>>MsQry32,without any error, but if I use code it
>>wont take it.

>>heres abit of the code I use to give idea where I might
>be
>>going wrong.

>>Dim CN As New ADODB.Connection
>>Dim RS As New ADODB.Recordset
>>Set lvw = Till_Module.ListView1
>>    CN = ReadWriteINI("GET", "DKPOS SETTINGS", "CN")
>>SQL2 = "INSERT INTO TransactionItems
>>(TransactionID,ItemID, ItemQTY) " _
>>& "VALUES ('" & uniqueID & "','" & li.SubItems(7)
& "','"
>>& li.SubItems(2) & "')"

>>For j = 1 To Till_Module.ListView1.ListItems.Count
>>Set li = Till_Module.ListView1.ListItems.Item(j)
>>CN.Execute (SQL2)

>>There has to be a way for this to work please help me I
>am
>>desperate
>>Many Thanx
>>Norman

>>.

>.

 
 
 

Insert into colum gives error HELP plse

Post by Bob Barrow » Sun, 22 Dec 2002 23:20:48


I suggest you use SQL Profiler to determine what that exe is doing. It may
be dropping the constraint before adding the dup record (although this seems
unlikely since you are able to manually add the records via  MsQry32 - you
should turn on profiler to see what happens when you insert the dup records
via this method also).

If that doesn't get you anywhere, could you please post the table creation
script (edit out the irrelevant columns please - we don't need to wade
through all 50+ lines of a script to see how constraints are being created).
Also provide us with some "Insert ...Values" statements. This will help us
reproduce the problem and see what's happening.

HTH,
Bob Barrows

Quote:> The exe does add duplicates in the primary key column the
> problem is I dont Know how as I dont have sorce code for it
> so it appears that this can be done except how and what
> can be done to achieve this. any ideas will be graetlkey
> appreciated.

> >-----Original Message-----

> >Are you saying that your compiled exe is adding the same
> >value in the PK column for > 1 record?

> >And that "I can manualy add it direct in sqlmngr or
> >running MsQry32,without any error"... ?

> >Primary Key columns DO NOT except duplicate key values,
> >regardless of how you try to add it.

> >If it's a problem w/ your ado code, and your query is
> >passing a unique value to the PK column - and failing
> >because of PK constraint error, you need to double check
> >your values.

> >In your code snippet, what are you doing w/ the lvw and
> >CN? Check out my ado conn object on my website...

> >ChrisB
> >MCDBA MCSE
> >www.MyDBA2000.com

> >>-----Original Message-----
> >>Hi
> >>I have A sql db that has a transactions table,I have a
> >exe
> >>that writes to this table and uses the same transaction
> >ID
> >>more than once in the primary key column, this works
> fine
> >>but dont have the sorce code to this prog.

> >>So I wrote my own using vb6 and when I try to insert
> into
> >>the field I get the following error.
> >>Violation of PRIMARY KEY
> >constraint 'aaaaaTransaction_PK'.
> >>Cannot insert duplicate key in
> object 'TransactionItems'.-
> >>2147217873.

> >>I can manualy add it direct in sqlmngr or running
> >>MsQry32,without any error, but if I use code it
> >>wont take it.

> >>heres abit of the code I use to give idea where I might
> >be
> >>going wrong.

> >>Dim CN As New ADODB.Connection
> >>Dim RS As New ADODB.Recordset
> >>Set lvw = Till_Module.ListView1
> >>    CN = ReadWriteINI("GET", "DKPOS SETTINGS", "CN")
> >>SQL2 = "INSERT INTO TransactionItems
> >>(TransactionID,ItemID, ItemQTY) " _
> >>& "VALUES ('" & uniqueID & "','" & li.SubItems(7)
> & "','"
> >>& li.SubItems(2) & "')"

> >>For j = 1 To Till_Module.ListView1.ListItems.Count
> >>Set li = Till_Module.ListView1.ListItems.Item(j)
> >>CN.Execute (SQL2)

> >>There has to be a way for this to work please help me I
> >am
> >>desperate
> >>Many Thanx
> >>Norman

> >>.

> >.

 
 
 

Insert into colum gives error HELP plse

Post by Norma » Mon, 23 Dec 2002 17:09:05


Thanx Bob I have seen my error I had to close the
connection before sending the id again and looping around
the for next cycle,this has allowed it to parse the same
id again thanks for the help all
Quote:>-----Original Message-----
>I suggest you use SQL Profiler to determine what that exe
is doing. It may
>be dropping the constraint before adding the dup record

(although this seems
Quote:>unlikely since you are able to manually add the records
via  MsQry32 - you
>should turn on profiler to see what happens when you

insert the dup records
Quote:>via this method also).

>If that doesn't get you anywhere, could you please post
the table creation
>script (edit out the irrelevant columns please - we don't
need to wade
>through all 50+ lines of a script to see how constraints

are being created).
>Also provide us with some "Insert ...Values" statements.
This will help us
>reproduce the problem and see what's happening.

>HTH,
>Bob Barrows


>> The exe does add duplicates in the primary key column
the
>> problem is I dont Know how as I dont have sorce code
for it
>> so it appears that this can be done except how and what
>> can be done to achieve this. any ideas will be graetlkey
>> appreciated.

>> >-----Original Message-----

>> >Are you saying that your compiled exe is adding the
same
>> >value in the PK column for > 1 record?

>> >And that "I can manualy add it direct in sqlmngr or
>> >running MsQry32,without any error"... ?

>> >Primary Key columns DO NOT except duplicate key values,
>> >regardless of how you try to add it.

>> >If it's a problem w/ your ado code, and your query is
>> >passing a unique value to the PK column - and failing
>> >because of PK constraint error, you need to double
check
>> >your values.

>> >In your code snippet, what are you doing w/ the lvw and
>> >CN? Check out my ado conn object on my website...

>> >ChrisB
>> >MCDBA MCSE
>> >www.MyDBA2000.com

>> >>-----Original Message-----
>> >>Hi
>> >>I have A sql db that has a transactions table,I have a
>> >exe
>> >>that writes to this table and uses the same
transaction
>> >ID
>> >>more than once in the primary key column, this works
>> fine
>> >>but dont have the sorce code to this prog.

>> >>So I wrote my own using vb6 and when I try to insert
>> into
>> >>the field I get the following error.
>> >>Violation of PRIMARY KEY
>> >constraint 'aaaaaTransaction_PK'.
>> >>Cannot insert duplicate key in
>> object 'TransactionItems'.-
>> >>2147217873.

>> >>I can manualy add it direct in sqlmngr or running
>> >>MsQry32,without any error, but if I use code it
>> >>wont take it.

>> >>heres abit of the code I use to give idea where I
might
>> >be
>> >>going wrong.

>> >>Dim CN As New ADODB.Connection
>> >>Dim RS As New ADODB.Recordset
>> >>Set lvw = Till_Module.ListView1
>> >>    CN = ReadWriteINI("GET", "DKPOS SETTINGS", "CN")
>> >>SQL2 = "INSERT INTO TransactionItems
>> >>(TransactionID,ItemID, ItemQTY) " _
>> >>& "VALUES ('" & uniqueID & "','" & li.SubItems(7)
>> & "','"
>> >>& li.SubItems(2) & "')"

>> >>For j = 1 To Till_Module.ListView1.ListItems.Count
>> >>Set li = Till_Module.ListView1.ListItems.Item(j)
>> >>CN.Execute (SQL2)

>> >>There has to be a way for this to work please help me
I
>> >am
>> >>desperate
>> >>Many Thanx
>> >>Norman

>> >>.

>> >.

>.

 
 
 

1. Plse help Error 429 License info not found

Hi all

I just reinstalled my VB5 proffessional edition
I had this  below error msg whenever i try to paste down
a DBGRID activeX component on a new form.

License information for this component not found. You do not
have an apprppriate license to use this functionality in the
design environment (Error 429)

anyone out there had any idea what causes this?

Thanks in advance
Simon Chan

2. dBASE '95 Call for Papers

3. Help! update gives insert error

4. Cannot remove database file even after EMPTYFILE

5. ADO Stored Procedure Help Plse

6. help problem with "POST"

7. Giving Error While Doing BULK INSERT

8. how to change temporary file location?

9. INSERT INTO gives errors

10. Insert into ms Access with ADO gives Error Message

11. JDBC driver gives error with batched insert

12. INsert permissions given, but cannot insert Data

13. Distinct giving Read-Only error help!