Getting the Records using a Stored Procedure

[Andy Nymbus]

Okay, I have written the code to create the parameters for an SP, but how do I run it then loop through the records.

 

I want to do some processing before passing a value back ...

 

I'm using Professional ASP.Net from Wrox and vb.net by Sybex. Both books should be avaoided!!!

 

 

Here is what I have so far:

 

Dim ConStr As String = "provider=SQLOLEDB.1;data source=127.0.0.1;intial catalog=SME;uid=sa;pwd="

 

' Create Instance of Connection and Command Object

Dim myConnection As New SqlConnection(ConStr)

Dim myCommand As New SqlCommand("spInsertUser", myConnection)

Dim objDataReader As SqlDataReader

 

myCommand.CommandType = CommandType.StoredProcedure

 

Dim objParam As SqlParameter

 

objParam = myCommand.Parameters.Add("@name", SqlDbType.NVarChar, 100)

objParam.Direction = ParameterDirection.Input

objParam.Value = name

[mocella]

If you want to loop through the records returned from that stored procedure, you can use a DataReader to do that.

 

Just add this:

Dim myReader As SqlDataReader = myCommand .ExecuteReader()

Do While myReader.Read()

'**ADD SOME CODE TO READ THE VALUES, IE:

messagebox.show(myReader.GetInt32(0))

Loop

myReader.Close()

 

 

 

Check out this MSDN topic: "SqlDataReader class, samples" for more info.