Results 1 to 2 of 2

Thread: Form Problems

  1. #1
    Join Date
    Jun 2004
    Posts
    25

    Unanswered: Form Problems

    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.
    -------------------------
    Code:
    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

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    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))

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •