Passing date parameter to stored procedure.

Question

0.00/5 (No votes)

See more:

Hi
I wrote the following code, which works when I comment out the Parameters.Add line. I keep getting an error message saying:

"Procedure vs_PM_SelectDates has no parameters and arguments were supplied"

I rewrote the line several different ways, but with little luck in getting it working. If someone could explain how to correct my problem I would be very grateful.

VB

Dim Today As DateTime = DateTime.Today
Dim SelectDates As New SqlCommand("vs_PM_SelectDates", Connection)
SelectDates.CommandType = CommandType.StoredProcedure
SelectDates.Parameters.Add("@Param1", SqlDbType.DateTime).Value = Today
PMDataAdapter.SelectCommand = SelectDates
PMDataAdapter.Fill(PMDataTable)

Posted 31-Jul-13 23:28pm

IanMac91

Add a Solution

Comments

Maciej Los 1-Aug-13 5:50am

And the body of SP is...

3 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Harshil_Raval · Answer 1 · 2013-07-31T23:34:00

Solution 1

Hi,

Try replacing,

SelectDates.Parameters.Add("@Param1", SqlDbType.DateTime).Value = Today

with

SelectDates.Parameters.AddWithValue("your parameter name", Today)

Note :- Here "your parameter name"should be parameter name in your sp ("vs_PM_SelectDates")

Hope it helps.

Posted 31-Jul-13 23:34pm

Harshil_Raval

Comments

IanMac91 1-Aug-13 5:50am

Hi. Thanks for your reply! I tried this and still get the same message. My stored procedure can be found below. Perhaps there is a problem with it?

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[vs_PM_SelectDates]

As

Begin

Declare @Param1 As DateTime

Select [Table1].[EmployeeID], [Table2].[Dispensing ID], [Table1].[DateEntered]
From [Table1]
Inner Join [Detail]
On [Table2].[ID] = [Table1].[EmployeeID]
Where [DateEntered] > @Param1
Order By EmployeeID, DateEntered

End

Harshil_Raval · Answer 2 · 2013-07-31T23:55:00

Hi IanMac91,

Problem is in your stored procedure. see updated stored procedure below.

SQL

set ANSI_NULLS ON 
set QUOTED_IDENTIFIER ON 
go 

ALTER PROCEDURE [dbo].[vs_PM_SelectDates] 
   @Param1 DateTime //Add here
As 
Begin 
Declare @Param1 As DateTime // remove this
Select [Table1].[EmployeeID], [Table2].[Dispensing ID], [Table1].[DateEntered] 
From [Table1] Inner Join [Detail] On [Table2].[ID] = [Table1].[EmployeeID] 
Where [DateEntered] > @Param1 
Order By EmployeeID, DateEntered 

End

Raja Sekhar S · Answer 3 · 2013-08-01T00:07:00

Quote:
"Procedure vs_PM_SelectDates has no parameters and arguments were supplied"

The Error itself is self explanatory.. It's States that Even Thought no Parameters are Required for a Stored Procedure, you are Passing the Parameter to the Stored Procedure...

Your Stored Procedure has to be Changed like this..

SQL

Use DataBase_Name
Go
set ANSI_NULLS ON
Go
Set QUOTED_IDENTIFIER ON
Go
ALTER PROCEDURE [dbo].[vs_PM_SelectDates]
@Param1 As DateTime
As
Begin
    Select t1.[EmployeeID], t2.[Dispensing ID], t1.[DateEntered]
    From [Table1] t1
    Inner Join [Detail] t2 On t2.[ID] = t1.[EmployeeID]
    Where [DateEntered] > @Param1
    Order By EmployeeID, DateEntered
End

Passing date parameter to stored procedure.

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Passing date parameter to stored procedure.

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us