Passing parameters to a stored proc

[Criki]

OK, this may be a silly question, but I'm all about saving time and space in my code.

Right now this is what my code looks like to insert a record thru a sp:
cn.Open()
cmd.Connection = cn
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "sp_Request"

With cmd
.Parameters.Add("@Request", SqlDbType.VarChar, 200)
.Parameters("@Request").Direction = ParameterDirection.Input
.Parameters("@Request").Value = getTypes()
.Parameters.Add("@Rq_No", SqlDbType.VarChar, 200)
.Parameters("@Rq_no").Direction = ParameterDirection.Input
.Parameters("@Rq_no").Value = getPermitNumber()
End With

Is there a more compact way of doing this?
I'm still feeling my way thru .net.

[pvb]

Dim cn As New SqlConnection("YourConnectionString")
Dim cmd As New SqlCommand("sp_Request")
cmd.Connection = cn
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New SqlParameter("@Request", getTypes()))
cmd.Parameters.Add(New SqlParameter("@Rq_No", getPermitNumber()))
cmd.Connection.Open()

[Edneeis]

Hey pvb did that work for you?

I saw that in the Help docs and tried it a while back but if I don't specify the datatype then it doesn't work for me.

As for shortening that, if pvb's way (which should work but doesn't for me) doesn't work then you can drop the direction because by default its input. And even though I have to specify datatype for mine to work I don't have to specify length.

[pvb]

Show the definition of the stored proc, cuz you don't need to specify the datatype.

[Edneeis]

Here is the parameter part:

Alter PROCEDURE sel_User
@LoadBy as int=0, /*determines what to use to load by*/
@UserID as int=0, /*holds the ID if used otherwise ignored*/
@Username as varchar(255), /*holds username otherwise ignored*/
@Password as varchar(255) /*holds password otherwise ignored*/
AS

I'll have to try again because I don't remember if it just didn't accept my parameter value or if it gave an error.

[pvb]

problem might be how you're setting up those params in your stored proc, the following works fine:

Private Sub testCmd()
    Dim dr As SqlDataReader
    Dim connString As String = System.Configuration.ConfigurationSettings.AppSettings.Item("DBConnString").ToString()
    Dim cn As New SqlConnection(connString)
    Dim cmd As New SqlCommand("sel_User")
    cmd.Connection = cn
    cmd.CommandType = CommandType.StoredProcedure
    cmd.Parameters.Add(New SqlParameter("@LoadBy", 5))
    cmd.Parameters.Add(New SqlParameter("@UserID", 2))
    cmd.Parameters.Add(New SqlParameter("@UserName", "some name"))
    cmd.Parameters.Add(New SqlParameter("@Password", "my password"))
    cmd.Connection.Open()
    dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
    If (dr.Read()) Then
        LoadBy.Text = dr("LoadBy").ToString() & ""
        UserId.Text = dr("UserId").ToString() & ""
        UserName.Text = dr("UserName").ToString() & ""
        Password.Text = dr("Password").ToString() & ""
    End If
End Sub

SQL:

Create Procedure sel_User
	  @LoadBy int = 0
	, @UserID int = 0
	, @Username varchar ( 255 )
	, @Password  varchar ( 255 )
As
Set Nocount On
Select 
	  @LoadBy As 'LoadBy'
	, @UserID As 'UserID'
	, @Username As 'Username'
	, @Password As 'Password'
Set Nocount Off
go

[Edneeis]

Is that normal use teh non @ name and then reassign it? I've always just used the @LoadBy or whatnot directly. Actually this isn't the sp that gave me the trouble and I started to retest and shorten things up a bit but ran out of time. Although it let me do it your way with @LoadBy so maybe it was something else in 1 particular sp??? I'll test more tomorrow.

[Musician]

In response to the original query this is how I add a parameter:-

cmd.parameters.add(new sqlparamater("@someid", sqldbtype.int)).value = someint

This makes the code nice and short, allows me to define the type and specify the value all in one line and I only need another line if I need to specify direction, which is rarely.

[Criki]

Thanks, that speeds up coding tremedously.

But now you all can tease me for not continuing to use sourcesafe or some other backup. I just lost my whole project.

Oh well, this is what turns me into a better programmer, right?

:-)