Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cusoxty
Constraint Violating Yak Guru
271 Posts |
 Posted - 2004-06-17 : 16:38:35
|
| I am having problems consistently inserting my data into a table. On my form that contains two drop downs, an accout number is selected and a territory to transfer that account to is selected. However, when i submit the data, sometimes no record shows up and other times it will insert the record multiple times. Here is my stored procedure for inserting the account and its current territory, region, division and employee information. I'm guessing something is wrong here. CREATE PROCEDURE InsertAccounts@AccountID char(10),@Explanation nvarchar(800)ASinsert into accounttransfers(AccountID, FromTerritoryID, FromRegionID, FromDivisionID, FromEmpID, Explanation)select Accounts2.AccountID, Territories.TerritoryID, Regions.RegionID, Divisions.DivisionID, Employees.EmployeeID, @ExplanationFrom Accounts2, 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 Where (Accounts2.AccountID = @AccountID) and (Accounts2.TerritoryID = Territories.TerritoryID)GO |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-17 : 16:54:10
|
| Instead of running the stored procedure from your application, you need to first get it working by debugging it in Query Analyzer. DECLARE @AccountID char(10)DECLARE @Explanation nvarchar(800)SET @AccountID = 'SomeValue1'SET @Explanation = 'Cuz I said so'select Accounts2.AccountID, Territories.TerritoryID, Regions.RegionID, Divisions.DivisionID, Employees.EmployeeID, @ExplanationFrom Accounts2, 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 Where (Accounts2.AccountID = @AccountID) and (Accounts2.TerritoryID = Territories.TerritoryID)Run the above in Query Analyzer, modify @AccountID and @Explanation. Does it give you the correct results? Debugging the stored procedure from your application is not a good idea until it is working in Query Analyzer.Tara |
 |
|
|
cusoxty
Constraint Violating Yak Guru
271 Posts |
Posted - 2004-06-17 : 16:58:05
|
| When I run this in query analyzer, it gives me the same results that are given when running the application. For example, Account NUmber 0000000007 does not yield any values, however 0000018447 yields three records. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-17 : 16:59:30
|
| So what should the query be returning then? We can't see your system, so you have to be verbose so that we know what is going on.Tara |
|
|
|
cusoxty
Constraint Violating Yak Guru
271 Posts |
Posted - 2004-06-17 : 17:01:15
|
| Each time an account number is selected form the list, inserted into the database should be that Account Number, the TerritoryID, RegionID, DivisionID, and EmployeeID that are curretnyl associated with that account. Each time it runs, only one record should be submitted for that unique account. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-17 : 17:05:04
|
| When account number yields three records, why is it returning three records? Due to the data or due to the query? Could you show us a data example of what you mean? We aren't familiar with your application, so we need to see data.Tara |
|
|
|
|
|
|
|
|
Problems consistently inserting data