Passing Optional Parameter to Stored Procedure in VS VB.net

  • Thread starter Thread starter wk6pack
  • Start date Start date
W

wk6pack

Hi,
I'm trying to pass only the required parameters to a stored procedure using
the following code. I keep getting the following error now.

Running dbo."InsertEmployee" ( @pemployeeno = "888888", @S_I_N =
"123456789", @pfirstname = "Sam", @plastname = "Wise", @pmiddlename =
<DEFAULT>, @pprimaryemail = "(e-mail address removed)", @psecondaryemail =
<DEFAULT>, @status_code = "A" ).

---> Error converting data type varchar to numeric. <------

Dim SIMSSqlcommand As New SqlCommand
SIMSSqlcommand.Connection = connection
SIMSSqlcommand.CommandText = "InsertEmployee"
SIMSSqlcommand.CommandType = CommandType.StoredProcedure
SIMSSqlcommand.namedparameter = True
SIMSSqlcommand.Parameters.Add("@CustomerID", "PAULK")
SIMSSqlcommand.Parameters.Add("@CompanyName", "Pauly's Bar")

STORED PROCEDURE
CREATE PROCEDURE dbo.InsertEmployee
/*
(
@parameter1 datatype = default value,
@parameter2 datatype OUTPUT
)
*/
(
@pemployeeno char(10),
@S_I_N nvarchar(10),
@pfirstname nvarchar(50),
@plastname nvarchar(50),
@pmiddlename nvarchar(50)=" ",
@pprimaryemail nvarchar(50)=" ",
@psecondaryemail nvarchar(50) = " ",
@status_code char(10) = " "
)
AS
declare @pstatusid numeric
/* SET NOCOUNT ON */
select @pstatusid = statusid
from status
where statuscode = @status_code;
insert into Employee
(
Employeeno,
S_I_N,
firstname,
lastname,
middlename,
primaryemail,
secondaryemail,
statusid
)
values
(
@pemployeeno,
@S_I_N,
@pfirstname,
@plastname,
@pmiddlename,
@pprimaryemail,
@psecondaryemail,
@status_code
)
select @@identity
RETURN
GO

thanks,
Will
 
ADO.NET does not have a NamedParameter property, method or function on the
SqlCommand class. The SqlClient provider defaults to NamedParameters and the
OleDb provider does not support it at all. I'm not sure why that compiled...
(if it did).

I don't know what this is:

Running dbo."InsertEmployee" ( @pemployeeno = "888888", @S_I_N =
"123456789", @pfirstname = "Sam", @plastname = "Wise", @pmiddlename =
<DEFAULT>, @pprimaryemail = "(e-mail address removed)", @psecondaryemail =
<DEFAULT>, @status_code = "A" ).
Click to expand...
It does not look like you're framing the strings correctly.

I would also avoid use of the char type. It's hard to manage if you aren't
very careful and know the side-effects.

Also don't use @@Identity--use SCOPE_IDENTITY() instead.

You also need to fix your name in the Express mail account--unless your name
is "news.microsoft.com".

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
I was running the stored procedure from within VS in the server explorer
window.

From the stored procedure, if I set defaults it seems to work.

I'll try to figure out what the news.microsoft.com in the mail account.

thanks,
Will
 
Back
Top