Solved

Procedure or function has too many arguments specified

Posted on 2016-11-09
9
24 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 8

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
 

Author Comment

by:fadiel ras
ID: 41880590
tried removing them, but still getting the same error.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 8

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 8

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

707 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now