Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

In Visual Studio 2010 with ASP.NET 4, I am trying to join several tables together to put the results in a gridview and details view with sqldatasource. In the sqldatasource wizard I have chosen to specify a custom SQL statement or stored procedure and then in the Query Builder to define complex queries such as JOINs, I have tried to generate a SQL statement to join the problem table with speficific columns from other tables. But when I try to test the query I get an error message which says "Cannot call methods on varchar". I am new to sql statements so please can you advise on what is wrong with the statement. Here is the generated sql statement below

 SELECT Problem.ProblemID, Problem.CustomerID, Problem.Summary, 
        Problem.DateLogged, Problem.DateUpdated, Status.Status, Priority.Priority, 
        Technician.Name, Technician.Surname, [Skill Group].[Skill Group], 
        HelpdeskOperator.Name AS Expr1,
        HelpdeskOperator.Surname AS Expr2, Problem.NoteID, Problem.ResolutionID 

 FROM Problem 
INNER JOIN Status ON Problem.StatusID = Status.Status.StatusID 

    INNER JOIN HelpdeskOperator ON 
    Problem.HelpdeskID = HelpdeskOperator.HelpdeskID AND Status.StatusID = HelpdeskOperator.StatusID 

    INNER JOIN Priority ON Problem.PriorityID = Priority.PriorityID 

    INNER JOIN [Skill Group] ON Problem.SkillGroupID = [Skill Group].SkillGroupID 

    INNER JOIN Technician ON Problem.ProblemID = Technician.ProblemID 
    AND Status.StatusID = Technician.StatusID AND
     Priority.PriorityID = Technician.PriorityID 
    AND [Skill Group].SkillGroupID = Technician.SkillGroupID

Thank you in advance

share|improve this question

1 Answer

up vote 0 down vote

Fixed your query:

SELECT p.ProblemID, p.CustomerID, p.Summary, p.DateLogged, p.DateUpdated, s.Status, pr.Priority, t.Name, t.Surname,
       sg.* , ho.Name AS Expr1, ho.Surname AS Expr2, p.NoteID, p.ResolutionID
FROM Problem p
INNER JOIN Status s ON p.StatusID = s.StatusID
INNER JOIN HelpdeskOperator ho ON p.HelpdeskID = ho.HelpdeskID AND s.StatusID = ho.StatusID
INNER JOIN Priority pr ON p.PriorityID = pr.PriorityID
INNER JOIN [Skill Group] sg ON p.SkillGroupID = sg.SkillGroupID
INNER JOIN Technician t ON p.ProblemID = t.ProblemID AND s.StatusID = t.StatusID AND pr.PriorityID = t.PriorityID
                         AND sg.SkillGroupID = t.SkillGroupID
  1. You had duplicate table identifier in your join clause Status.Status.StatusID
  2. I doubt that your Skill Group table contains column [Skill Group] so changed it to return all values from Skill Group

I just think those were the errors, if not I will need more info about your query and table structure.

EDIT:

First it did not return anything for HelpdeskOperator, look at our query:

INNER JOIN HelpdeskOperator ho ON p.HelpdeskID = ho.HelpdeskID AND s.StatusID = ho.StatusID

that meanse that here is no such HelpdeskOperator record that is assigned to our problem AND statusid, so either problem id points to noexisting helpdeskoperator or statusid of this operator is different that problem status id.

next is Skill Group

INNER JOIN [Skill Group] sg ON p.SkillGroupID = sg.SkillGroupID

again our problem point to no existing skill group

then Technican

INNER JOIN Technician t ON p.ProblemID = t.ProblemID AND s.StatusID = t.StatusID AND pr.PriorityID = t.PriorityID
                        AND sg.SkillGroupID = t.SkillGroupID

here is more work as more checks, technicas must be assigned to our problem with given status and priorityt and be in skill group, BUT our skill group is null? so to check if there is technican for our problem remove AND sg.SkillGroupID = t.SkillGroupID so you get 

INNER JOIN Technician t ON p.ProblemID = t.ProblemID AND s.StatusID = t.StatusID AND pr.PriorityID = t.PriorityID

and see if now we get any technican.

I hope this points you into right direction. You must be sure that there are matching record in every joining table.

share|improve this answer
Hi there, I tried your fix and now the error message is "The multi-part identifier "Technician.ProblemID could not be bound". Please could you help. – Jenny P 8 hours ago
Please try again, was typo in my query :) – gzaxx 8 hours ago
The tables I want to join are Problem table, Technician Table, HelpdeskOperator table, Skill Group table, Status table and Priority table each table has a ID as the foreign key in the problem table. The query is to return specific columns from the tables mentioned and join them as one result alongside values from the problem table. I wanted to test this first as ideally I wanted a dropdown list to filter the skill group table and all the results will be populated into the gridview. – Jenny P 8 hours ago
I understand, but is there something wrong with my query? Is it working? Or is returning wrong results? I need more info :) – gzaxx 8 hours ago
Hiya, the query has worked but for some reason when I run the webpage in visual studio it will not display the gridview, do you why? – Jenny P 8 hours ago
show 10 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.