Form Problems

Results 1 to 2 of 2

Thread: Form Problems

  1. #1
    Join Date
    Dec 1969

    Default 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. <BR>-------------------------<BR>
    <BR>Sub InsertData()<BR><BR><BR><BR>Dim sql as string<BR><BR>dim sql1 = "insertaccounttransfermike &#039;AccountID&#039;&#039;" & AccNumber.SelectedItem.Value & "&#039;"<BR>dim sql2 = "insertaccounttransfermikefrom &#039;FromTerritory&#039;&#039;" & FromName.SelectedItem.Value & "&#039;"<BR>dim sql3 = "insertaccounttransfermiketo &#039;ToTerritory&#039;&#039;" & ToName.SelectedItem.Value & "&#039;"<BR><BR>&#039; Create Connection Object, Command Object, Connect to the database<BR>Dim conn as New SQLConnection(connstr)<BR><BR>Dim cmd1 as New SQLCommand(sql1,conn)<BR>Dim cmd2 as New SQLCommand(sql2,conn)<BR>Dim cmd3 as New SQLCommand(sql3,conn)<BR><BR><BR>cmd1.ExecuteNonQuery()<BR>cmd2.ExecuteNonQuery()<BR>cmd3.ExecuteNonQuery()<BR>conn.Close()<BR><BR>&#039; go to the datagrid query page<BR>Response.redirect(return_page)<BR><BR>end sub<BR>
    <BR>--------------------------<BR>CREATE PROCEDURE InsertAccountTransferMikefrom<BR><BR>@FromTerritor y nvarchar(50)<BR><BR>AS<BR><BR>Insert INTO AccountTransfersTestMike<BR><BR>(FromTerritoryID, FromRegionID, FromDivisionID, FromEmpID)<BR><BR>SELECT Territories.TerritoryID, Regions.RegionID, Divisions.DivisionID, Employees.EmployeeID<BR>FROM EndoscopySqlUser.Territories INNER JOIN<BR> EndoscopySqlUser.Regions ON EndoscopySqlUser.Territories.RegionName = EndoscopySqlUser.Regions.Region INNER JOIN<BR> EndoscopySqlUser.Employees ON EndoscopySqlUser.Territories.TerritoryID = EndoscopySqlUser.Employees.TerritoryID INNER JOIN<BR> EndoscopySqlUser.Divisions ON EndoscopySqlUser.Regions.Division = EndoscopySqlUser.Divisions.DivisionID, accounts<BR>WHERE ( (EndoscopySqlUser.Employees.DateLeft IS NULL) AND (EndoscopySqlUser.Territories.TerritoryName=@FromT erritory))<BR>GO

  2. #2
    Join Date
    Dec 1969

    Default Well, go run *just* the SELECT...

    ...part of that SP and see what you get.<BR><BR>SELECT Territories.TerritoryID, Regions.RegionID, Divisions.DivisionID, Employees.EmployeeID<BR>FROM EndoscopySqlUser.Territories <BR>INNER JOIN EndoscopySqlUser.Regions <BR>ON EndoscopySqlUser.Territories.RegionName = EndoscopySqlUser.Regions.Region <BR>INNER JOIN EndoscopySqlUser.Employees <BR>ON EndoscopySqlUser.Territories.TerritoryID = EndoscopySqlUser.Employees.TerritoryID <BR>INNER JOIN EndoscopySqlUser.Divisions <BR>ON EndoscopySqlUser.Regions.Division = EndoscopySqlUser.Divisions.DivisionID, <BR>[hl="yellow"]accounts[/hl]<BR>WHERE EndoscopySqlUser.Employees.DateLeft IS NULL <BR>AND EndoscopySqlUser.Territories.TerritoryName=@FromTe rritory<BR><BR>But I think the error is pretty clear: You just did an INNER JOIN to the [hl="yellow"]accounts[/hl] table (that&#039;s what the comma is doing, the equivalent of an inner join). And yet you have no expression that limits *which* records in the ACCOUNTS table you will get. So you get *ALL* the records. Every single one that is in the ACCOUNTS table.<BR><BR>And so then the INSERT adds one record for every record in the ACCOUNTS table.<BR><BR>Dare I ask why you have ACCOUNTS in there???<BR><BR><BR>

Posting Permissions

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