Solved

Procedure or function has too many arguments specified

Posted on 2016-11-09
9
47 Views
Last Modified: 2016-11-09
Hi there
I can see many threads dealing with this issue. mostly the author had simply counted incorrectly (hopefully not me as well)
I have a StoredProc updating a SQL table via vb.net datagrid view.
I have x7 paramatres, which I believe I'm passing inside VB, yet I'm getting the error: "Procedure or function has too many arguments specified"
I've been at this for hours now with zero luck...I hope someone can help soon:)
My SQL Code:

CREATE PROCEDURE [dbo].[spc_Appointments]
  (@AppointmentID INT,
  @PatientID INT,
  @SurgeryID INT,
  @SurgeryName NVARCHAR(50),
  @Comments NVARCHAR(100),
  @AuditUser NVARCHAR(50))                     
AS
  BEGIN               
   
   SET NOCOUNT ON;

   IF (@AppointmentID = 0)
         BEGIN

             INSERT INTO dbo.tblAppointments
                       (PatientID
                       ,SurgeryID
                       ,SurgeryName
                       ,Comments
                       ,AuditDate
                       ,AuditUser)
             VALUES
                       (   @PatientID,
                           @SurgeryID,
                           @SurgeryName,
                           @Comments,
                           GETDATE(),
                           @AuditUser)
         END
    ELSE
       BEGIN
                    UPDATE tblAppointments
                    SET    
			PatientID = @PatientID,
                        SurgeryID = @SurgeryID,
                        SurgeryName = @SurgeryName,
                        Comments = @Comments,
                        AuditDate = GETDATE(),
                        AuditUser = @AuditUser
                    WHERE AppointmentID = @AppointmentID; 
             END      
END
---------------------------------------------------------------------------
SET ANSI_NULLS ON

Open in new window


My VB Code:

Private Sub Save_Data()
    Dim strSQL As String
    Try
        If dgvMedical.RowCount > 0 Then
            For Each dr In dgvMedical.Rows()
                Dim IntAppointmentID As Int64
                Dim IntSurgeryID As Int64

                Dim strUserName As String = "PilotUser"

                If dr.Cells("RowModified").Value.ToString() <> "N" Then

                    If dr.Cells("SurgeryName").Value.ToString() = Nothing Then
                        MsgBox("Please complete field: Medical Field Name before saving the record", MsgBoxStyle.Information, "Medical Appointment - Medical Field Name Required")
                        lblSuccessful.Text = "Medical Field Name Required"
                        blnUpdate = False
                        Exit Sub
                    End If
                    If dr.Cells("Comments").Value.ToString() = Nothing Then
                        MsgBox("Please complete field: Comments before saving the record", MsgBoxStyle.Information, "Medical Appointment - Comments Required")
                        lblSuccessful.Text = "Comments Required"
                        blnUpdate = False
                        Exit Sub
                    End If
                    If dr.Cells("RowModified").Value.ToString() = "I" Then
                        If CheckCoffeeCupReservation(Trim(Replace(dr.Cells("SurgeryName").Value.ToString.ToUpper, "'", ""))) = "Y" Then
                            MsgBox("Unable to commit record Contact already exists within table", MsgBoxStyle.Information, "Medical Appointment - MedicalField Name Already Exists")
                            lblSuccessful.Text = "Unable to commit record Contact already exists within table. Medical Field Name: " & Trim(Replace(dr.Cells("SurgeryName").Value.ToString.ToUpper, "'", ""))
                            blnUpdate = False
                            Exit Sub
                        End If
                    End If
                    If dr.Cells("AppointmentID").Value.ToString() = Nothing Then
                        IntAppointmentID = 0
                    Else
                        IntAppointmentID = dr.Cells("AppointmentID").Value.ToString()
                    End If

                    If dr.Cells("SurgeryID").Value.ToString() = Nothing Then
                        IntSurgeryID = 0
                    Else
                        IntSurgeryID = dr.Cells("SurgeryID").Value.ToString()
                    End If

                    strSQL = "spc_Appointments"

                    ClearParm()
                    AddParm("@AppointmentID", IntAppointmentID, DbType.Int64)
                    AddParm("@PatientID", 1, DbType.Int64)
                    AddParm("@SurgeryID", IntSurgeryID, DbType.Int64)

                    If IsDBNull(dr.Cells("SurgeryName").Value) Then
                        AddParm("@SurgeryName", DBNull.Value.ToString, DbType.String)
                    Else
                        AddParm("@SurgeryName", Trim(Replace(dr.Cells("SurgeryName").Value.ToString.ToUpper, "'", "")), DbType.String)
                    End If
                    If IsDBNull(dr.Cells("Comments").Value) Then
                        AddParm("@Comments", DBNull.Value.ToString, DbType.String)
                    Else
                        AddParm("@Comments", Trim(Replace(dr.Cells("Comments").Value.ToString.ToUpper, "'", "")), DbType.String)
                    End If
                    AddParm("@AuditDate", Today.Date, DbType.Date)
                    AddParm("@AuditUser", strUserName, DbType.String)
                    ExecuteSP(strSQL)
                End If
            Next
        End If

        dgvMedical.DataSource = Nothing
        dgvMedical.Columns.Clear()

    Catch ex As Exception
        blnUpdate = False
        MsgBox("Records Failed to Update", MsgBoxStyle.Critical, "Medical Appointment")
    End Try
End Sub

Open in new window

0
Comment
Question by:fadiel ras
  • 5
  • 3
9 Comments
 
LVL 9

Expert Comment

by:Paweł
ID: 41880546
try removing
 AddParm("@AuditDate", Today.Date, DbType.Date)
at line 62

your sotred procedure doesn't take in a @AuditDate parameter; it sets it to the current datetime at the DB level
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 41880564
>>I have x7 paramatres
Correct you call AddParam seven times
But:
CREATE PROCEDURE [dbo].[spc_Appointments]
  (@AppointmentID INT,     One
  @PatientID INT,         two
  @SurgeryID INT,       three
  @SurgeryName NVARCHAR(50),        four
  @Comments NVARCHAR(100),       five
  @AuditUser NVARCHAR(50))                   six  
AS
0
 

Author Comment

by:fadiel ras
ID: 41880577
Thanks gents, I have another form that works 100% with the same rationale
>>CREATE PROCEDURE [dbo].[spc_Appointments]
without adding the audit date in the create, but then passing GetDate()
as well as
>>@AuditDate parameter; it sets it to the current datetime at the DB level
works perfectly on the opher project
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

Author Comment

by:fadiel ras
ID: 41880590
tried removing them, but still getting the same error.
0
 
LVL 9

Expert Comment

by:Paweł
ID: 41880644
by removing the @AuditDate  this code block should be fine, are you sure you're SqlCommand  doesn't get a parameter from some where else? also could you post the code for your AddParm method
0
 

Author Comment

by:fadiel ras
ID: 41880657
thanks. but I cant remove the Audit date. I also posted the full SProc, so no other paramtres.
here is the AddParm code:

 
  Public Shared Sub AddParm(ByVal strName As String, ByVal strValue As String, ByVal strType As DbType)

        Dim sqlParm = New SqlParameter()

        sqlParm.Value = strValue
        sqlParm.DbType = strType
        sqlParm.ParameterName = strName

        cmSelect.Parameters.Add(sqlParm)

    End Sub

Open in new window

0
 
LVL 9

Accepted Solution

by:
Paweł earned 500 total points
ID: 41880663
you should be able to delete audit date param

if you look at the stored procedure

      UPDATE tblAppointments
                    SET    
                  PatientID = @PatientID,
                        SurgeryID = @SurgeryID,
                        SurgeryName = @SurgeryName,
                        Comments = @Comments,
                        AuditDate = GETDATE(),
                        AuditUser = @AuditUser
                    WHERE AppointmentID = @AppointmentID;

notice that you're stored procedure sets the audit date column to the current date at that level, and it doesn't have a parameter for the audit date.
0
 

Author Comment

by:fadiel ras
ID: 41880693
why didn't I listen to you the first time around:(
more so, why did I not post my question this morning already.... thanks a mil all works 100%. records submitted
0
 

Author Closing Comment

by:fadiel ras
ID: 41880695
Thanks for your assistance, it's much appreciated.
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question