none
store procedure

    Question

  • i try to execute auery but it shows me 1 record 5 or more than 5 timess...

    WHERE IS IN TABLE THERE IS ONLY 1 RECORD

    query

    ALTER procedure [dbo].[dddddd]
    @userid int
    as                     
    
            
    SELECT DocumentInfo.DocID as DocumentID ,
    dbo.DocumentInfo.DocName as DocumentName,
    dbo.DocumentInfo.Uploadfile as FileUploaded,
    dbo.DocType.DocType as Document,
    dbo.Department.DepType as Department ,
    dbo.ApproveType.ApproveType AS ApproveID
    FROM dbo.DocumentInfo
    inner JOIN dbo.DocType ON dbo.DocumentInfo.DocTypeID=dbo.DocType.DocTypeID
    inner JOIN dbo.Department ON dbo.DocumentInfo.DepID=dbo.Department.DepID
    LEFT JOIN dbo.ApproveType ON dbo.ApproveType.approveid=dbo.Department.DepID  LEFT OUTER JOIN
            Approval a ON a.DocID = a.DocID
        JOIN
            ApproveType at ON at.ApproveID = ISNULL(a.Approveid, 3)  where UserID=@userid

    where is the problem?

    Thursday, October 24, 2013 8:01 AM

Answers

All replies

  • Well, when I need to guess: It's the data model. I would assume that "Approval" is a process which creates more then one row in the correspondend table.

    Yelling "WHERE IS IN TABLE THERE IS ONLY 1 RECORD" makes absolutly no sense, when you're working with a JOIN over five tables with using at least one INNER JOIN. Cause in this case, the result must be empty. It's a contradiction.

    Thursday, October 24, 2013 8:07 AM
  • so what i do?
    Thursday, October 24, 2013 8:11 AM
  • see how tables are related. If its 1 to many show some sample data for us and explain what you want
    Thursday, October 24, 2013 8:15 AM
  • ok i try this

    SELECT DISTINCT DocumentInfo.DocID as DocumentID ,
    dbo.DocumentInfo.DocName as DocumentName,
    dbo.DocumentInfo.Uploadfile as FileUploaded,
    dbo.DocType.DocType as Document,
    dbo.Department.DepType as Department ,
    dbo.ApproveType.ApproveType AS ApproveID
    FROM dbo.DocumentInfo
    inner JOIN dbo.DocType ON dbo.DocumentInfo.DocTypeID=dbo.DocType.DocTypeID
    inner JOIN dbo.Department ON dbo.DocumentInfo.DepID=dbo.Department.DepID
    LEFT JOIN dbo.ApproveType ON dbo.ApproveType.approveid=dbo.DocumentInfo.ApproveID  LEFT OUTER JOIN
            Approval a ON a.DocID = a.DocID
        JOIN
            ApproveType at ON at.ApproveID = ISNULL(a.Approveid, 3)  where UserID=@userid
    

    it works but it show me error in this line

    "

    DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'DocID'."

                        <td><%#DataBinder.Eval(Container.DataItem,"DocID") %></td>
    

    Thursday, October 24, 2013 8:23 AM
  • Cause you used the column alias DocumentID in your query?!
    Thursday, October 24, 2013 8:40 AM
  • <td><%#DataBinder.Eval(Container.DataItem," DocumentID") %></td>
    it works for me
    Thursday, October 24, 2013 10:01 AM