Needs to show database information in listbox via SELECT statement over more tables
-
Hello all, I have three tables: Tablename 1: Agent Collums: Agentnumber (PK) Agentname Tablename 2: Agent_Group Collums: Agentnumber (FK, connection with Agentnumber of Agent table) Groupnumber (FK, connection with Groupnumber of group table) Tablename 3: Group Collumns: Groupnumber (PK) Groupname Now i need to show the names of the agents which belong to (for example) groupnumber 300. This is the SELCECT statement i made: SELECT Agent.Agentname FROM (Agent_Group INNER JOIN Agent ON Agent_Group.Agentnumber = Agent.Agentnumber) WHERE (Agent_Groep.Groupsnumber = ?) The "?" is a variable, called groupNumber. Wen run the statement in the querybuilder i get some results bun when running the application i get the error message: "An unhandled exception of type 'System.Data.ConstraintException' occurred in system.data.dll" Maybee som extra info (in dutch): Additional information: Beperkingen konden niet worden ingeschakeld. Een of meer rijen bevatten waarden die in strijd zijn met de beperkingen met betrekking tot niet-null waarden, unieke waarden of afwijkende sleutels. The following statement works fine but then i see only the agentnumbers who belong to group 300. SELECT Agentnummer, Groepsnummer FROM Agent_Groep WHERE (Groepsnummer = ?) Somebody any idea? Thanks
-
Hello all, I have three tables: Tablename 1: Agent Collums: Agentnumber (PK) Agentname Tablename 2: Agent_Group Collums: Agentnumber (FK, connection with Agentnumber of Agent table) Groupnumber (FK, connection with Groupnumber of group table) Tablename 3: Group Collumns: Groupnumber (PK) Groupname Now i need to show the names of the agents which belong to (for example) groupnumber 300. This is the SELCECT statement i made: SELECT Agent.Agentname FROM (Agent_Group INNER JOIN Agent ON Agent_Group.Agentnumber = Agent.Agentnumber) WHERE (Agent_Groep.Groupsnumber = ?) The "?" is a variable, called groupNumber. Wen run the statement in the querybuilder i get some results bun when running the application i get the error message: "An unhandled exception of type 'System.Data.ConstraintException' occurred in system.data.dll" Maybee som extra info (in dutch): Additional information: Beperkingen konden niet worden ingeschakeld. Een of meer rijen bevatten waarden die in strijd zijn met de beperkingen met betrekking tot niet-null waarden, unieke waarden of afwijkende sleutels. The following statement works fine but then i see only the agentnumbers who belong to group 300. SELECT Agentnummer, Groepsnummer FROM Agent_Groep WHERE (Groepsnummer = ?) Somebody any idea? Thanks
This is a question for the SQL Forum. The Babelfish translation is a bit rough, but, it would appear that you have a key constraint violation in your INNER JOIN. You shouldn't be allowing NULL's on the keys, primary or foreign, in either table. Also, is the data constistent? Do ALL of the FK values in the Agent_Group table actually exist in the PK's of the other two tables?
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007