[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 170
  • Last Modified:

Procedure or function has too many arguments specified

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
fadiel ras
Asked:
fadiel ras
  • 5
  • 3
1 Solution
 
PawełCommented:
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
 
AndyAinscowCommented:
>>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
 
fadiel rasAuthor Commented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
fadiel rasAuthor Commented:
tried removing them, but still getting the same error.
0
 
PawełCommented:
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
 
fadiel rasAuthor Commented:
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
 
PawełCommented:
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
 
fadiel rasAuthor Commented:
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
 
fadiel rasAuthor Commented:
Thanks for your assistance, it's much appreciated.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now