I have listed two SQL Insert statem 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!!!

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 & "')"

 

[bassguy]

HI, I would suggest that you create a Stored procedure and them connect to that passing your parms...just an idea

 

[hdougl1]

bassguy thanks for the suggestion I am aware of that choice. 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