Re: calling stored procedure with optional parameter

Great. Thanks for the help.
Will

"William (Bill) Vaughn" <billvaRemoveThis@xxxxxxxxxx> wrote in message
news:uFX%23wKWkFHA.3756@xxxxxxxxxxxxxxxxxxxxxxx
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdpronamedparameters.asp
>
> Enable the NamedParameters property and you only have to append Parameter
> objects for the parameters you want to send to the SP.
>
> --
> ____________________________________
> 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.
> __________________________________
>
> "wk6pack" <wkwan@xxxxxxxxxx> wrote in message
> news:O8m2n4VkFHA.2720@xxxxxxxxxxxxxxxxxxxxxxx
> > Hi Bill,
> >
> > here is my sp. How do I call it using named parameters? I thought I
was
> > doing it when I created the parameter?
> >
> > CREATE PROCEDURE dbo.Insert_Employee
> > (
> > @pemployeeno nvarchar(10),
> > @S_I_N nvarchar(10),
> > @pfirstname nvarchar(50),
> > @plastname nvarchar(50),
> > @pmiddlename nvarchar(50)=" ",
> > @pprimaryemail nvarchar(50)=" ",
> > @psecondaryemail nvarchar(50) = " ",
> > @status_code nvarchar(10) = " "
> > )
> > AS
> > declare @pstatusid numeric,
> > @identity int
> >
> > 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 = @@identity
> > RETURN
> > GO
> >
> > Thanks,
> > Will
> >
> > "William (Bill) Vaughn" <billvaRemoveThis@xxxxxxxxxx> wrote in message
> > news:eFqS4YVkFHA.2472@xxxxxxxxxxxxxxxxxxxxxxx
> >> How is the SP defined? Are there default values supplied for the
optional
> >> parameters?
> >>
> >> I expect you'll need to request "Named Parameters" so you can provide a
> >> sparse Parameter list.
> >>
> >> --
> >> ____________________________________
> >> 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.
> >> __________________________________
> >>
> >> "wk6pack" <wkwan@xxxxxxxxxx> wrote in message
> >> news:%235ueWCVkFHA.3692@xxxxxxxxxxxxxxxxxxxxxxx
> >> > Hi,
> >> >
> >> > Is it possible to call a stored procedure from vb.net when some of
the
> >> > parameters are not required in the stored procedure?
> >> > Say i have a stored procedure with 3 parameters. I want to call the
> >> > stored
> >> > procedure with only two of the parameters.
> >> > I seem to get an error saying I need the 3rd parameter. When I fill
in
> >> > the
> >> > 3 parameter the sql statement call from vb.net works fine.
> >> >
> >> > code:
> >> > cmd.CommandText = "InsertVSIS"
> >> > cmd.CommandType = adCmdStoredProc
> >> > cmd.Parameters.Append cmd.CreateParameter("@studentkey", adInteger,
> >> > adParamInput,,lstudentkey)
> >> >
> >> >
> >> > thanks,
> >> > Will
> >> >
> >> >
> >>
> >>
> >
> >
>
>


.

Relevant Pages

  • Re: Check to ensure server and db available
    ... and when are we to see 2.0 Bill? ... >>> Microsoft MVP ... >>> Please reply only to the newsgroup so that others can benefit. ... >>> rights. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: how to make the database in vb.net
    ... Hey Bill, now that I have your attention, do you have anything further to ... >>I have heard that there are no longer any mind readers in Louisiana. ... >>> Please reply only to the newsgroup so that others can benefit. ... >>> rights. ...
    (microsoft.public.vb.database)
  • Why =?windows-1252?Q?DC=92s_Gun_Law_Is_Unconstitutional?=
    ... Historical arguments about American bills of rights are major points of discussion in the District of Columbia vs Heller case currently before the U.S. Supreme Court. ... At issue is exactly what the Second Amendment to the U.S. Constitution means and whether it was proper for the U.S. Court of Appeals for the District of Columbia to overturn Washington D.C.’s handgun ban for violating the Second Amendment. ... One would expect such a brief to be historically accurate, address the Second Amendment in its proper Bill of Rights related context, and include the most relevant figures, statements, and actions for understanding any historical issues in the dispute. ... Other historical materials exist that directly contradict the historians in this matter as well. ...
    (talk.politics.guns)
  • Re: Open Letter to Bill OReilly:
    ... Open Letter to Bill O'Reilly: It's Bill of Rights, ... Consider your incessant rage against the American Civil Liberties Union ... Is this the correct interpretation of why you are waging a war against the Bill of Rights? ...
    (alt.politics.bush)
  • Open Letter to Bill OReilly:
    ... Open Letter to Bill O'Reilly: It's Bill of Rights, ... Consider your incessant rage against the American Civil Liberties Union ...
    (alt.politics.bush)