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