Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2008
    Posts
    8

    Unanswered: Problem with writing a storeprocedure that should perform like an append query

    Can someone help me please? I have been dealing with this problem for 2 days now. Please Please Help me.

    I want to write a storeprocedure that perform the work of an append query that I maked in access, i want to use it in sql server so it can append the records to a specific table.

    But I do not know how to write the procedure, paste it as it is in access but it gives me error.
    So I want to make the procedure so that when I enter the info in the form in access, and when I click the button save it runs the procedure so it can update the table.So I have to associate the button with the procudure.


    My query that I made in access is as follow:

    Code:
    INSERT INTO Marks ( Student_Id, Course_Id, Grupo_Id )
    SELECT Attendance.Student_Id, Course.Course_Id, Attendance.Grupo_Id
    
    FROM Grupo_Section INNER JOIN (Course INNER JOIN ((Grupo INNER JOIN Attendance ON Grupo.Grupo_Id = Attendance.Grupo_Id) INNER JOIN Student ON Attendance.Student_Id = Student.Student_Id) ON Course.Course_Id = Grupo.Course_Id) ON (Grupo_Section.Grupo_Date = Attendance.Grupo_Date) AND (Grupo_Section.Grupo_Id = Attendance.Grupo_Id) AND (Grupo_Section.Grupo_Id = Grupo.Grupo_Id)
    
    GROUP BY Attendance.Student_Id, Course.Course_Id, Attendance.Grupo_Id, Attendance.Grupo_Id, Course.Course_Levelid, Attendance.Attend
    
    HAVING (((Course.Course_Levelid)="N1") AND ((Attendance.Attend)=-1) AND ((Count(Attendance.Attend))=1)) OR (((Course.Course_Levelid)="N2") AND ((Attendance.Attend)=-1) AND ((Count(Attendance.Attend))>=3));

    How can use this in sql server 2005?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    In SQL Server we use single quotes as text qualifiers, not doubles.
    Code:
    INSERT INTO Marks (Student_Id, Course_Id, Grupo_Id)
    SELECT Attendance.Student_Id
         , Course.Course_Id
         , Attendance.Grupo_Id
    FROM   Attendance
     INNER
      JOIN Grupo
        ON Grupo.Grupo_Id = Attendance.Grupo_Id
     INNER
      JOIN Course
        ON Course.Course_Id = Grupo.Course_Id
     INNER
      JOIN Grupo_Section
        ON Grupo_Section.Grupo_Date = Attendance.Grupo_Date
       AND Grupo_Section.Grupo_Id = Attendance.Grupo_Id
       AND Grupo_Section.Grupo_Id = Grupo.Grupo_Id
     INNER
      JOIN Student
        ON Student.Student_Id = Attendance.Student_Id
    WHERE  Course.Course_Levelid IN ('N1', 'N2')
    AND    Attendance.Attend = -1
    GROUP
        BY Attendance.Student_Id
         , Course.Course_Id
         , Attendance.Grupo_Id
         , Course.Course_Levelid
         , Attendance.Attend
    HAVING Count(Attendance.Attend) = 1
        OR Count(Attendance.Attend) >= 3
    George
    Home | Blog

  3. #3
    Join Date
    Sep 2008
    Posts
    8
    Quote Originally Posted by gvee
    In SQL Server we use single quotes as text qualifiers, not doubles.
    Code:
    INSERT INTO Marks (Student_Id, Course_Id, Grupo_Id)
    SELECT Attendance.Student_Id
         , Course.Course_Id
         , Attendance.Grupo_Id
    FROM   Attendance
     INNER
      JOIN Grupo
        ON Grupo.Grupo_Id = Attendance.Grupo_Id
     INNER
      JOIN Course
        ON Course.Course_Id = Grupo.Course_Id
     INNER
      JOIN Grupo_Section
        ON Grupo_Section.Grupo_Date = Attendance.Grupo_Date
       AND Grupo_Section.Grupo_Id = Attendance.Grupo_Id
       AND Grupo_Section.Grupo_Id = Grupo.Grupo_Id
     INNER
      JOIN Student
        ON Student.Student_Id = Attendance.Student_Id
    WHERE  Course.Course_Levelid IN ('N1', 'N2')
    AND    Attendance.Attend = -1
    GROUP
        BY Attendance.Student_Id
         , Course.Course_Id
         , Attendance.Grupo_Id
         , Course.Course_Levelid
         , Attendance.Attend
    HAVING Count(Attendance.Attend) = 1
        OR Count(Attendance.Attend) >= 3


    HI, gvee

    Thanks for your help.

    The codes were good, the query run, but there is another problem now.

    Because I wanted to do this apppend query in sql , like in access, when I run this query the table Marks has to be updated with the append query.
    The case is that when I insert info into a form in access and hit the save button, that is my front end, it should update the table Marks. So in my button property I should call the append query that is on my SQL so it can update the table, that also reflect in my access front end.

    But when I run it, every time it say ' 0 rows affected, and that's not true, because there must be records added.

    What can i do now to update the table through this query? or should I do something else ?

    Please help me.
    Thank you

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Run the query without the INSERT line; you won't get any results.
    I refactored your query a tad so that could be the culprit...

    What does this return?
    Code:
    SELECT Attendance.Student_Id
         , Course.Course_Id
         , Attendance.Grupo_Id
    FROM   Attendance
     INNER
      JOIN Grupo
        ON Grupo.Grupo_Id = Attendance.Grupo_Id
     INNER
      JOIN Course
        ON Course.Course_Id = Grupo.Course_Id
     INNER
      JOIN Grupo_Section
        ON Grupo_Section.Grupo_Date = Attendance.Grupo_Date
       AND Grupo_Section.Grupo_Id = Attendance.Grupo_Id
       AND Grupo_Section.Grupo_Id = Grupo.Grupo_Id
     INNER
      JOIN Student
        ON Student.Student_Id = Attendance.Student_Id
    GROUP
        BY Attendance.Student_Id
         , Course.Course_Id
         , Attendance.Grupo_Id
         , Course.Course_Levelid
         , Attendance.Attend
    HAVING ( Course.Course_Levelid = 'N1'
         AND Attendance.Attend = -1
         AND Count(Attendance.Attend) = 1 )
        OR ( Course.Course_Levelid = 'N2'
         AND Attendance.Attend) = -1
         AND Count(Attendance.Attend) >= 3 )
    George
    Home | Blog

Posting Permissions

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