InsertCommand.ExecuteNonQuery() and violation of primaryKey

Hey,

I have a page that inserts into a customers table in the DataBase a new customer account using this function:

PublicFunction InsertCustomers(ByRef sessionid,ByVal email,ByVal pass,OptionalByVal fname ="",OptionalByVal lname ="",OptionalByVal company ="",OptionalByVal pobox ="",OptionalByVal add1 ="",OptionalByVal add2 ="",OptionalByVal city ="",OptionalByVal state ="",OptionalByVal postalcode ="",OptionalByVal country = 0,OptionalByVal tel ="")Dim resultAsNew DataSetDim tempidAsIntegerDim connAsNew SqlConnection(ConfigurationSettings.AppSettings("Conn"))Dim AdcustAsNew SqlDataAdapter

Adcust.InsertCommand =

New SqlCommand

Adcust.SelectCommand =

New SqlCommand

Adcust.InsertCommand.Connection = conn

Adcust.SelectCommand.Connection = conn

sessionExists(email, sessionid, 1)

conn.Open()

If fname =""Then

Adcust.InsertCommand.CommandText =

"Insert Into neelwafu.customers(email,password,sessionid) Values('" & email &"','" & pass &"','" & sessionid &"')"ElseDim strsqlAsString

strsql =

"Insert Into neelwafu.customers"

strsql = strsql &

"(sessionid,email,password,fname,lname,company,pobox,address,address2,city,state,postalcode,countrycode,tel) values("

strsql = strsql &

"'" & sessionid &"','" & email &"','" & pass &"','" & fname &"','" & lname &"','" & company &"','" & pobox &"','" & add1 &"','" & add2 &"','" & city &"','" & state &"','" & postalcode &"', " & country &",'" & tel &"')"

Adcust.InsertCommand.CommandText = strsql

EndIf

Adcust.InsertCommand.ExecuteNonQuery()

Adcust.SelectCommand.CommandText =

"Select Max(id) from neelwafu.Customers"

tempid =

CInt(Adcust.SelectCommand.ExecuteScalar())

conn.Close()

Return tempidEndFunction

------------------------------------------------------------

Now, I am getting an error:

Violation of PRIMARY KEY constraint 'PK_customers_1'. Cannot insert duplicate key in object 'customers'. The statement has been terminated.

------------------------------------------------------------

The customers table has as a primary key the 'email'....

so plz can I know why am I getting this error ??

Thank you in advance

Hiba

Hi,

it basically says you are trying to insert a new record with email X and email X is already present in the data base. And since the email is a primary key (unique id of a single record) it is not acceptable to have two records with the same mail.

You could check whether the email is not already present by a simple sql query.

Cheers,

Yani

|||

Hey,

The problem is I am not inserting a row with the same primary key !

When I use the sql 2005 to insert a new row like the following :

Insert Into customers(email,password,sessionid) Values('hiba@.hotmail.com'

, '0000' , 10)

It is executed normally but when that i want to insert the row from the web page using theInsertCustomers function, I got an error on theAdcust.InsertCommand.ExecuteNonQuery()!

Any idea ?

Thank you

Hiba

|||

Well,

if this is the case there is something wrong with your sql statement.

I would suggest to add a debug logging before executing the query:

Debug.WriteLine(strsql);

Start debugging the application, and when you get the error see in the Output window of your Visual Studio the exact sql query that fails.

If you cannot find the problem this way, paste your logged query here, so we could have a look at it.

Cheers,

Yani

|||

Hey,

The problem was solved :), there was a mistake in the order of passing the parameters the function insertcustomers where the sessionid was the first parameter where supposedly the email (PK) should be placed so every time i am inserting the same sessionid that's y i was getting that error :S

Anyways thank you :)

Hiba

sql

Insert Question

I need to insert a last and first name field taken from a full name
field on the same table. If I have 25 rows of customers in this table,
what would be the best way to do this? I can split the full name into 2
fields with the syntax below, but I need help with the syntax for
inserting these to fields on the existing rows. Help appreciated.
Thanks.

SELECT Left([FullName],InStr(1,[FullName]," ")-1),
Right(Trim([FullName]),Len(Trim([FullName]))-InStr(1,[FullName]," "))

I have fields in my table like:
CustomerNumber (populated)
CustomerName (populated)
FullName (populated)
Fname
Lname
SalesPersonCode (populated)

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Frank Py (fpy@.proactnet.com) writes:
> I need to insert a last and first name field taken from a full name
> field on the same table. If I have 25 rows of customers in this table,
> what would be the best way to do this? I can split the full name into 2
> fields with the syntax below, but I need help with the syntax for
> inserting these to fields on the existing rows. Help appreciated.
> Thanks.
> SELECT Left([FullName],InStr(1,[FullName]," ")-1),
> Right(Trim([FullName]),Len(Trim([FullName]))-InStr(1,[FullName]," "))
> I have fields in my table like:
> CustomerNumber (populated)
> CustomerName (populated)
> FullName (populated)
> Fname
> Lname
> SalesPersonCode (populated)

UPDATE tbl
SET Fname = Left([FullName], InStr(1,[FullName]," ")-1),
Lname = Right(Trim([FullName]),Len(Trim([FullName])) -
InStr(1,[FullName]," "))

Not that this will work on SQL Server, since there is no InStr or Trim
functions on SQL Server. But if you are using Access and just don't know
which newsgroup to post to, this should do alright, since this is
standard SQL, save the functions.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks, I see what you mean. This worked well with Access, but how would
I convert this to something SQL could use? You mentioned saving
functions. Help appreciated. Thanks.

UPDATE tbl
SET Fname = Left([FullName], InStr(1,[FullName]," ")-1),
Lname = Right(Trim([FullName]),Len(Trim([FullName])) -
InStr(1,[FullName]," "))
-------
Result:
'InStr' is not a recognized function name.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Frank Py (fpy@.proactnet.com) writes:
> Thanks, I see what you mean. This worked well with Access, but how would
> I convert this to something SQL could use? You mentioned saving
> functions. Help appreciated. Thanks.
> UPDATE tbl
> SET Fname = Left([FullName], InStr(1,[FullName]," ")-1),
> Lname = Right(Trim([FullName]),Len(Trim([FullName])) -
> InStr(1,[FullName]," "))
> -------
> Result:
> 'InStr' is not a recognized function name.

Look in Books Online, the T-SQL Reference. Find the Functions topic, and
then the string functions topic. I cannot translate the above to T-SQL,
as I don't what the Access functions do.

And since using Books Online is a far quicker way to get answers to
simple questions than asking a newsgroup, I figured I should get you
started.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

insert query results to "where in ()" query

Hello

I want to print 'delete from customers where id in (' + select id from persons+ ')'

It failes. How do I auto generate it to get the wanted resaults?

Thanks

Avi

I am not sure what you are trying here to achive.

If you want to print the string..

Print 'Delete from Customers Where Id in (Select Id from Persons)'

If you want to execte the query

Exec ('Delete From Customers Where Id in (Select Id from Persons)')

Ooops.. Its not clear.. Tell me more.. What you want to do..

|||

There is a delete command I need to do but I need to capture the current set of id. the same delete statment will not act the same a week ago.

I wan't to pring the query into a text file for futore rollback. the resault should be ' delete from X where id in (num1,num2,num3)'

|||

You are something looking for LOG Based deletion.

When you delete x rows you want to store it somewhere. Then later if you need you can revert back the changes..

Approach 1:

You can achive this using triggers.

Code Snippet

Create Table mydata (

[Id] int ,

[Name] Varchar(100)

);

Go

Create Table mylogdata (

[Id] int ,

[Name] Varchar(100) ,

[DeletedDatetime] datetime

);

Go

Create Trigger trg_MydataDeleteLoger

on Mydata For delete

as

Begin

Insert Into mylogdata

Select *,getdate() from Deleted;

End

Go

Insert Into mydata Values('1','Record1');

Insert Into mydata Values('2','Record2');

Insert Into mydata Values('3','Record3');

Insert Into mydata Values('4','Record4');

Insert Into mydata Values('5','Record5');

Go

Delete From Mydata Where Id<3

Select * From mydata

Select * From mylogdata

Approach 2:

Instead of deleting the rows use the IsDelete flag.

Code Snippet

Create Table mydata (

[Id] int ,

[Name] Varchar(100) ,

--attach the flag columns

IsDeleted bit Default 0,

DeletedDatetime datetime default null

);

|||

Isn't there a way to do it witha simple sql scritp?

|||

Reverting back your data after sometimes is not a simple task.

Your table may hold n numbers columns. Delete command is simply remove all the data.

So you should backup all the columns data before deleteing the row. Somewhere you have to keep that backup data

for future revert-back.

Trigger is one of the common way to achive this.. If you ask me, putting a IsDeleted flag is good practice too.

|||

All I want is to print the wanted string. isn't that possible? regardless of the cause or target. just print it.

Do I need to use cast or convert ?

Thanks

Avi

|||If

I want to print 'delete from customers where id in (' + select id from persons+ ')'

Then, the problem is handling the single quotes. To have a single quote in a PRINT string, use two single quotes. For example, the statement:

PRINT 'DELETE FROM Customers WHERE ID IN ('' + SELECT ID FROM Persons+ '')'

prints out:

DELETE FROM Customers WHERE ID IN (' + SELECT ID FROM Persons+ ')

|||

I would like to write

PRINT 'DELETE FROM Customers WHERE ID IN ('' + SELECT ID FROM Persons+ '')'

in the query analyzer and then press F5.

What I want to see in the resultpan is :

'DELETE FROM Customers WHERE ID IN (10,20,30)'

Can this be done ?

|||

No, a PRINT statement ONLY prints exactly what you tell it to print. It will not execute the statement [ SELECT ID FROM Persons ] and put the results in the PRINT output.

You could, however, 'build' the line you want printed as a variable [varchar()], and then print that variable. You would have to use a technique such as the one below to gather the output from the SELECT statement into a comma delimited list.

Code Snippet

SET NOCOUNT ON

DECLARE @.MyTable table
( RowID int IDENTITY,
CustomerName varchar(20)
)

DECLARE @.MyList varchar(1000)

INSERT INTO @.MyTable VALUES( 'Smith' )
INSERT INTO @.MyTable VALUES( 'Williams' )
INSERT INTO @.MyTable VALUES( 'O''Reilly' )
INSERT INTO @.MyTable VALUES( 'Jones' )
INSERT INTO @.MyTable VALUES( 'Johnson' )
INSERT INTO @.MyTable VALUES( 'Marvin' )

SELECT DISTINCT
@.MyList = substring( ( SELECT ', ' + cast( RowID as varchar(10)) as [text()]
FROM @.MyTable t2
WHERE t2.CustomerName <> t1.CustomerName
FOR XML path(''), elements
), 3, 1000
)
FROM @.MyTable t1

DECLARE @.MyPrintStatement varchar(2000)

SET @.MyPrintStatement = '''DELETE FROM Customers WHERE ID IN (' + @.MyList + ')'''

PRINT @.MyPrintStatement

'DELETE FROM Customers WHERE ID IN (2, 3, 4, 5, 6)'

insert or update data records

I'd like to do the following thing with a data flow task

Get all the records from a source (for example customers from a textfile, flat file source) Then check for each record if the customer already exists in a table, for example with a customerID. If not, insert the record in the table (ole db destination), else copy the customer thats already in the table to another table (history table) and update the record with the customer from the textfile.

Is this possible?, and what kind of data flow transformation do I need?

Use a lookup to check if the customer exists. Use error output from the lookup to insert new customers, OLE-DB Destination. Use the normal output from the lookup to feed an OLE-DB Command to do the move and update. That may be easier if wrapped in a stored procedure.