[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 222
  • 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łSharePoint DeveloperCommented:
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
 
AndyAinscowFreelance programmer / ConsultantCommented:
>>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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
fadiel rasAuthor Commented:
tried removing them, but still getting the same error.
0
 
PawełSharePoint DeveloperCommented:
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łSharePoint DeveloperCommented:
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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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