One Insert writing 4 rows???? 1

[hdougl1]

I have listed two SQL Insert statements use in an ASP page. The first SQL inserts four rows into the database, the second only one, as they both should. The objective is to write a row to the db and obtain the identity field to write to another table as the link field. The question is; Why is the first SQL statement writing four rows to the database?
Thanks in advance!!!
Henry

strSQLInsert1 = "SET NOCOUNT ON; INSERT INTO Disposal_Header(UserID, UnitNumber, UnitName, Phone) VALUES ('" & strUserID & "' , '" & strUnitNumber & "', '" & strUnitName & "', '" & strPhone & "') SELECT @@IDENTITY AS RetVal;SET NOCOUNT OFF"

strSQLInsert2 = " INSERT INTO Disposal_Header(UserID, UnitNumber, UnitName, Phone) VALUES ('" & strUserID & "' , '" & strUnitNumber & "', '" & strUnitName & "', '" & strPhone & "')"

 

[hdougl1]

HELP Please
I have been made aware of the alternative of using a stored procedure. However I'm determined to get this query working. I have tried the query statement directly in the SQL Enterprise Manager and it works correct adding one row and returning the Identity value. Maybe it's the open statement as followed; any more suggestions would be appreciated.
rstHeader.Open strSQLInsert, objConn, 3, 3

 

[tlbroadbent]

I don't see any reason the query should write 4 records. Have you tried using Trace or Profiler to see what is actually be sent to SQL server from the client?

This is a wild stretch but I do notice there is no semi-colon between the Insert statment and the Select to get the identity value. It should'nt make a difference but it is the only abnormaility I notice.

I also suggest that the entire process would work better if encapsulated in a stored procedure. Yo may waste a lot of time trying to resolve a problem cuased by a bug in a some layer of software when a simple change like that could resolve the problem completely and quickly. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.