Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Procedure or function has too many arguments specified

Posted on 2016-11-09
9
Medium Priority
?
133 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
9 Comments
 
LVL 10

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 45

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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 

Author Comment

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

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 10

Accepted Solution

by:
Paweł earned 2000 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

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…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

618 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