Discuss "Help with handling sql insert error" in the ASP Development forum.

Rebelle · #1 July 23rd, 2008, 09:18 AM

Hi All,

I have a sql insert statement on my asp page and when I attempt to use the same Equipment # on the form I get teh following error because Equipment # is a primary key, the screen shows the ugly error message below....I would rather it have it come up with a message stating "sorry but that equipment number already exists in the database".

Code:

Microsoft OLE DB Provider for SQL Server error '80040e2f' 

Violation of PRIMARY KEY constraint 'PK_tblTestNCR'. Cannot insert duplicate key in object 'tblTestNCR'.

Thanks in advance for your help/suggestions!

mehere · #2 July 23rd, 2008, 09:39 AM

your best bet for that is to do a select statement first to see if it exists ...something like this:

Code:

strSQL = SELECT * FROM tbl_TableName WHERE equipmentno = '" & whatever_variable & "'"
set rs = dbConn.Execute(strSQL)
if rs.EOF and rs.BOF then
     --do your insert statement
else
     response.Write("Equipment # already exists in DB.")
end if

BLaaaaaaaaaarche · #3 July 23rd, 2008, 10:12 AM

My other question is why are you allowing the user to set their own value for the primary key? Why not assign a unique autonumber ID, and have that be the primary key?

Rebelle · #4 July 23rd, 2008, 10:26 AM

Thanks Mehere....trying to get it working...right now its skipping over the error but I'll keep trying to get it working...if not, I'll post my code.

Hey BLaaaaaaaaaarche,

Well, once I get this all working, I will need to some how incorporate it with something that's already existing and in the existing table there is no AutoNumID.

The equipment # that is being entered is unique and should only be entered once. I'm still not sure how I'm going to handle the process of incorporating this new form system with the existing .... the existing way, the data is loaded thru msAccess to sqldatabase and files are placed in folder manually on the server and users can only use a read-only search web interface to search all the records that have been added and also currently there is only 1 file per record...the new one will allow 2 files per record.

Wolffy · #5 July 23rd, 2008, 10:31 AM

I'll guess that Equipment # is probably like an Asset Tag or Serial Number, so the Primary Key is assigned by sticking a tag to something. (Tho there is an argument that the PK should always be assigned by the DB and thus EquipmentNumber would become a Unique Constraint [edit] This would be an especially good idea if the Equip# is not numeric[/edit]).

I'm going to disagree slightly with the SELECT..INSERT approach in a multi-user environment. It is possible that a second user could INSERT the Equipment# in the very small slice of time between the SELECT and the INSERT so it possible that the INSERT could error out still.

In .NET I would catch the exception and check the SQL return code for primary key violation. Not sure what the parallel is in classic

Rebelle · #6 August 19th, 2008, 11:47 AM

Thank you Mehere and Wolffy....

I'm trying to implement your suggestion Mehere but having some problems to get it to work for me.

this is the original code that i started with and records were added successfully...

Code:

Set rs = Server.CreateObject ("ADODB.Recordset")

 if inStr(strFile1,",") > 0 then
     arrFile = split(strFile1,",")
     blnArray = 1
 end if
  if blnArray = 1 then
     strSQL = "Insert into tblMyTable (Originator,Equipment,ManPartNo,Description,File1,File2) values ('" & strOriginator & "','" & strEQNumber & "', '" & strPartNo & "', '" & strDesc & "', '" & arrFile(0) & "','" & arrFile(1) & "')"
   else
     strSQL = "Insert into tblMyTable (Originator,Equipment,ManPartNo,Description,File1,File2) values ('" & strOriginator & "','" & strEQNumber & "', '" & strPartNo & "', '" & strDesc & "', '" & strFile1 & "','')"
  end if

rs.Open strSQL, conn,3,1
Conn.Close
Set Conn = Nothing

My problem is it won't add any records to the table....can you check to see what i'm doing wrong? this is what i have now.
when i do try to add an equipment number that i know is already in the table, i do get the message, but when i add something that is not, its not inserting it.

Code:

strSQL = "Select * from tblMyTable where Equipment = '" & strEQNumber & "'"
Set rs = Conn.Execute(strSQL)
if rs.eof and rs.bof then
 if inStr(strFile1,",") > 0 then
     arrFile = split(strFile1,",")
     blnArray = 1
 end if
     strSQL = "Insert into tblMyTable (Originator,Equipment,ManPartNo,Description,File1,File2) values ('" & strOriginator & "','" & strEQNumber & "', '" & strPartNo & "', '" & strDesc & "', '" & arrFile(0) & "','" & arrFile(1) & "')"
   else
     strSQL = "Insert into tblMyTable (Originator,Equipment,ManPartNo,Description,File1,File2) values ('" & strOriginator & "','" & strEQNumber & "', '" & strPartNo & "', '" & strDesc & "', '" & strFile1 & "','')"
  end if
else
	response.write("Equipment already exist. Your request has not been submitted.")
end if

Conn.Close
Set Conn = Nothing

mehere · #7 August 19th, 2008, 11:51 AM

looks to me as if you're missing a line. see the red line. you're also missing the execute line for the sql string. see the blue line

Code:

strSQL = "Select * from tblMyTable where Equipment = '" & strEQNumber & "'"
Set rs = Conn.Execute(strSQL)
if rs.eof and rs.bof then
 if inStr(strFile1,",") > 0 then
     arrFile = split(strFile1,",")
     blnArray = 1
 end if
 if blnArray = 1 then
     strSQL = "Insert into tblMyTable (Originator,Equipment,ManPartNo,Description,File1,File2) values ('" & strOriginator & "','" & strEQNumber & "', '" & strPartNo & "', '" & strDesc & "', '" & arrFile(0) & "','" & arrFile(1) & "')"
   else
     strSQL = "Insert into tblMyTable (Originator,Equipment,ManPartNo,Description,File1,File2) values ('" & strOriginator & "','" & strEQNumber & "', '" & strPartNo & "', '" & strDesc & "', '" & strFile1 & "','')"
  end if
  conn.execute(strSQL)  'This line was missing
else
	response.write("Equipment already exist. Your request has not been submitted.")
end if

Conn.Close
Set Conn = Nothing

Rebelle · #8 August 19th, 2008, 12:07 PM

That was it!!! Thanks!

Wolffy · #9 August 19th, 2008, 12:08 PM

Just a couple of thoughts:
Rather than do SELECT * just to see if a record is in a table, use Select ID so as to not return extra data that isn't going to be used -- in fact, try Select 1 for a giggle.

Also, there is a very very slight chance that your Insert could fail in a heavy multi-user environment. While it might be a 1:1,000,000 chance, I'd code for it. (Oh heck, always assume an SQL statement could fail -- the server could go down after all!)

Rebelle · #10 August 29th, 2008, 08:22 AM

Thanks for the info Wolffy regarding the heavy multi-user environment....I'll be looking into it to see what I find for classic asp. Right now I went ahead and used the select and I tried out the select 1...hehe.

1) I am trying to see if there is a way to incorporate the old data with the new....has anyone had to do this for any of their projects? if so, how'd you go about it?
2) The old table doesn't have a autoID...the equipment # is the primary key. Right now my test table doesn't have one either but i'm going to implement one today....because once records start getting added, i will need a way to lock the record when someone is editing (have a thread on this subject in the forum).
3) Right now I am using the one select statement in asp....since I have the old table and a new table, is it possible to check both tables prior to the insert?

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Free ASP Upload (insert issue)	Rebelle	ASP Development	3	July 31st, 2008 09:36 AM
query error	todd2006	SQL Development	1	June 24th, 2008 12:05 PM
XML Node Error	noFriends	Visual Basic Programming	4	June 3rd, 2008 08:57 AM
dynamically created dropdowns insert to sql	peebman2000	.Net Development	14	May 19th, 2008 11:00 AM

The Following User Says Thank You to mehere For This Useful Post:
Rebelle (August 19th, 2008)

Discuss "Help with handling sql insert error" in the ASP Development forum.

ASP Development - Learn coding practices and tips to get the best out of your Active Server Pages (ASP). The Classic ASP forum is for ASP/VBScript and ASP/JScript applications.