how to call and use store procedures in vb.net

[seep]

hi..
i m working with vb.net 1st time. i wants to work with stored procedures. i have created stored procedures successfully but have no idea about how to code in vb.net to use these store procedures for add, edit etc data in database. sql server 2000 i m using for databse.
kindly help me out as soon.

[Plater]

The stored procedure is created in the SQL server yes?
It will have a name.
Create an SQLCommand object.
Make it's SQL string be JUST the name of the stored procedure.
Set it's CommandType to that of a stored procedure
Then, for any parameters you have, you can add them in.

For example: (this code is c#, but should be similar for vb.net)

Int64 UserID=3;//looking for user #3
SqlCommand sc = new SqlCommand("GetUserInfo", dbcon);// "GetUserInfo" is my stored procedure
sc.CommandType = CommandType.StoredProcedure;//set the command type
sc.Parameters.Add("@UserID", SqlDbType.BigInt );//my procedure has a parameter called "@UserID" of type "BIGINT"
sc.Parameters["@UserID"].Value = UserID;//set the value
 

And then execute as normal.

[seep]

thanks plater. but right now i find another error while executing a store procedure. the error is:

Error: Procedure 'phealthMISDepa rtmentAdd' expects parameter '@p_depart_id_o ut', which was not supplied.

here is the SP that i have created:

-- Creates a new record in the [dbo].[Department] table.
ALTER PROCEDURE phealthMISDepar tmentAdd
@p_Department_n ame text,
@p_depart_id_ou t numeric output
AS
BEGIN
INSERT
INTO [dbo].[Department]
(
[Department_name]
)
VALUES
(
@p_Department_n ame
)

SET @p_depart_id_ou t = SCOPE_IDENTITY( )

END



here is the code for adding data from procedure::


Dim sql As String = "phealthMISDepa rtmentAdd"
Dim strConnection As String = "Data Source=JBWKN1S; Initial Catalog=HealthM IS;Integrated Security=True"
Dim conn As New SqlConnection(s trConnection)

conn.Open()

Dim cmd As New SqlCommand(sql, conn)
cmd.CommandType = CommandType.Sto redProcedure

cmd.Parameters. Add("@p_Departm ent_name", SqlDbType.VarCh ar, 50).Value = Department_name .Text
cmd.CommandText = "dbo.phealthMIS DepartmentAdd"



Try
cmd.ExecuteNonQ uery()
Catch ex As Exception
Response.Write( "Error: " & ex.Message & "<br/>")
End Try
cmd.Connection. Close()

[Plater]

Ahh yes, in code you have to add another parameter to your command object. and then set the direction to "out"

for example:

sc.Parameters.Add("@RETURN_VALUE", SqlDbType.Int);
sc.Parameters["@RETURN_VALUE"].Direction = ParameterDirection.ReturnValue;
 
sc.ExecuteNonQuery();
retval = Int.Parse(sc.Parameters["@RETURN_VALUE"].Value.ToString());
 

[seep]

thanks alot plater.
i got it really thanks:-)