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 value 1 into customer table column stand (was "Very Basic Sql")

Hello,

Bit of basic sql here for you:

I have a table called "customer" and there is a field in customer called stand.

Stand is also a seperate table that is joined to customer.

However i would like to add the value of "1" into STAND on the CUSTOMER table and i was wondering if someone could tell me the sql to do this. So basically where there is nothing insert a value of 1 into stand on customer.

Cheersinsert
into customer (stand)
values (1)|||how do i tell it to only insert that into blank values?|||ah, you're probably thinking of UPDATE, not INSERT
update customer
set stand = '1'
where stand = ' '|||Thanks, i will give that a try|||why has someone gone and changed the title of my post?|||Moderator changed unapropriate title ("Very basic sql") into something more meaningful ("insert value 1 into customer table column stand"). As you can see, he indicated the original thread name.

Read more about How to ask questions the smart way (http://catb.org/esr/faqs/smart-questions.html), especially "Use meaningful, specific subject header" chapter.|||Ok thanks for the information

insert statement

Hi

I am having a few problems writing an insert statement.

I have a customer table with lookup values inserted in some of the columns which reference another table

customer table

name address1 status

test high street 0

lookup table

code description

0 deleted

1 customer

--

I need to insert data from the customer table into another but not us the lookup table, the statement i have written is:

insert into export (name,address1,status)

select name, address1, ( select descritption from lookup inner join customer on lookup.code = customer.status) from customer

The problem is the subquery is showing all rows from the customer table which is giving me a an error due to inserting multi values which i would expect, the question is how can get round this? row by row inserts if possble but sure how to do this.

Hi

Try

select c.name, c.address1, lu.descritption

from customer c

inner join lookup lu

on lu.code = c.status

|||Thanks for your help worked a treat

Insert Rows into four tables at one time

Hello,

I have 4 tables having Customer, Customer_personal_info, Customer_Financial_info, Customer_Other_info

In this Customer table had a primary key CustomerID , related with every other table with fkey.

I want to insert data into four tables using one form having TABs .

I created class and storedProcedures to insert row for each table.

How to execute all four classes using beginTrans-commitTrans-Rollback-EndTrans.

Thanking you,

Hi

I am not sure if you have created one stored procedure or four stored procedures (your post mentions "stored procedures")..

If you want to go the stored procedure way, It is ideal to create one stored procedure on which you can use a SQL level BEGIN TRAN and ROLLBACK. These are keywords written inside the SQL stored procedure.

Another way is to execute the queries or stored procedures usingSQLCommand's ExecuteNonQuery method, but before execution create aSQLTransaction object and associate that with the SQLconnection. If the transaction (Insert statements in SQL or SP) works fine, thencommit the transaction.

HTH

VJ

Insert row in Query Analyzer

Hi there !

I have a table called customer which got
id = int
name = varchar
address = varchar
email= varchar

can you please write the syntax that insert the below data in the table using query Analyzer

id = 1
name = fadil
Address = London
email = fadil1977@.hotmail.com

Thank you for your time and help.INSERT INTO Customer
VALUES (1, 'fadil', 'London', 'fadil1977@.hotmail.com')