how to use store procedures 1

[aspro]

I have a little task that I have been set which requires me to retrieve data from a database via a store procedure and display the result in the grid.

my first problem is that i have never used store procedures before so maybe someone could point me in the direction of some good tuts because they seem to be hiding from me.

secondly does anyone think that the word 'grid' could be anything else other than a datagrid because that is the only grid i know.

many thanks
aspro

 

[chrissie1]

I think datagrid is wath you are looking for adn to get the data from a storedprocedure to a dataset you just put this in the commandtext of the dataadapter

"exec storedprocedurename"

perhaps you even need to set some parameters.

Storedprocedures are part of your databasesystem so for further information on that look their.

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting

 

[chiph]

tuts

???

Chip H.


____________________________________________________________________
Click here to learn Ways to help with Tsunami Relief
If you want to get the best response to a question, please read FAQ222-2244 first

 

[aspro]

tuts= tutorials
thanks for the datagrid advice!

 

[chiph]

If you have a stored procedure whose function signature looks like:

CREATE PROCEDURE GetUserName
@UserID int, @UserName nvarchar(20) OUTPUT
AS
SELECT @UserName = UserName
FROM t_Users
WHERE UserID = @UserID

You'd write code like this to call it:

Dim adoConn As New SqlConnection(connectString) ' Use real connect string

Dim adoComm As New SqlCommand("GetUserName", adoConn)
adoComm.CommandType = CommandType.StoredProcedure

Dim adoParm As SqlParameter

adoParm = adoComm.Parameters.Add("@UserID", SqlDbType.Int)
adoParm.Direction = ParameterDirection.Input
adoParm.Value = 123  ' the ID we want to get back

adoParm = adoComm.Parameters.Add("@UserName", SqlDbType.NVarChar, 20)
adoParm.Direction = ParameterDirection.Output

adoConn.Open()
adoComm.ExecuteNonQuery()
adoConn.Close()

Debug.Print "Got back: "
If adoComm.Parameters("@UserName").Value = DBNull.Value Then
  Debug.Print "<NULL>"
Else
  Debug.Print adoComm.Parameters("@UserName").Value
End If

Chip H.


____________________________________________________________________
Click here to learn Ways to help with Tsunami Relief
If you want to get the best response to a question, please read FAQ222-2244 first

 

[aspro]

Thanks Chip H. That code was very helpful. I found it hard to find the right code and put it in the right order and I wasn't sure how to call the store procedure.

Many thanks everyone
aspro

 

[chiph]

My code probably won't compile right off -- I'm a C# guy, not a VB.NET guy. Plus, there's no exception handling.

But glad it was of some help. I would suggest picking up a good book on ADO.NET. There's a lot of small tricks you can use to make your job easier.

Chip H.


____________________________________________________________________
Click here to learn Ways to help with Tsunami Relief
If you want to get the best response to a question, please read FAQ222-2244 first