[cusoxty]

Can anyone help me with this problem? I have a form with three drop down lists. From these lists, the user selects an accountID, the territory that ID is from, and the territory that ID is transfering to. Upon submission, that account number is inserted into the table, along with the TerritoryID, RegionID, DivisionID, and EmpID associated with both of the territories selected. However, upon running this, the accountID and ToTerritory info are inserted once, but the FromTerritory data is inserted tens of thousands of times. Although they are not entered into the same record, which i eventually want to happen, i do want to figure out why so many duplicate entries for the FromTerritory are entered. Here is my code and the stored procedure to enter the FromTerritory. I would appreciate any help.
-------------------------

Sub InsertData()



Dim sql as string

dim sql1 = "insertaccounttransfermike 'AccountID''" & AccNumber.SelectedItem.Value & "'"
dim sql2 = "insertaccounttransfermikefrom 'FromTerritory''" & FromName.SelectedItem.Value & "'"
dim sql3 = "insertaccounttransfermiketo 'ToTerritory''" & ToName.SelectedItem.Value & "'"

' Create Connection Object, Command Object, Connect to the database
Dim conn as New SQLConnection(connstr)

Dim cmd1 as New SQLCommand(sql1,conn)
Dim cmd2 as New SQLCommand(sql2,conn)
Dim cmd3 as New SQLCommand(sql3,conn)

conn.open()
cmd1.ExecuteNonQuery()
cmd2.ExecuteNonQuery()
cmd3.ExecuteNonQuery()
conn.Close()

' go to the datagrid query page
Response.redirect(return_page)

end sub

--------------------------
CREATE PROCEDURE InsertAccountTransferMikefrom

@FromTerritory nvarchar(50)

AS

Insert INTO AccountTransfersTestMike

(FromTerritoryID, FromRegionID, FromDivisionID, FromEmpID)

SELECT Territories.TerritoryID, Regions.RegionID, Divisions.DivisionID, Employees.EmployeeID
FROM EndoscopySqlUser.Territories INNER JOIN
EndoscopySqlUser.Regions ON EndoscopySqlUser.Territories.RegionName = EndoscopySqlUser.Regions.Region INNER JOIN
EndoscopySqlUser.Employees ON EndoscopySqlUser.Territories.TerritoryID = EndoscopySqlUser.Employees.TerritoryID INNER JOIN
EndoscopySqlUser.Divisions ON EndoscopySqlUser.Regions.Division = EndoscopySqlUser.Divisions.DivisionID, accounts
WHERE ( (EndoscopySqlUser.Employees.DateLeft IS NULL) AND (EndoscopySqlUser.Territories.TerritoryName=@FromT erritory))
GO

[rokslide]

The select part of your stored procedure is selecting multiple records so every time you are inserting using this procedure you are going to insert more records....

Try just running this part of your stored proc and see how many records you are going to insert....

SELECT Territories.TerritoryID, Regions.RegionID, Divisions.DivisionID, Employees.EmployeeID
FROM EndoscopySqlUser.Territories INNER JOIN
EndoscopySqlUser.Regions ON EndoscopySqlUser.Territories.RegionName = EndoscopySqlUser.Regions.Region INNER JOIN
EndoscopySqlUser.Employees ON EndoscopySqlUser.Territories.TerritoryID = EndoscopySqlUser.Employees.TerritoryID INNER JOIN
EndoscopySqlUser.Divisions ON EndoscopySqlUser.Regions.Division = EndoscopySqlUser.Divisions.DivisionID, accounts
WHERE ( (EndoscopySqlUser.Employees.DateLeft IS NULL) AND (EndoscopySqlUser.Territories.TerritoryName=@FromT erritory))