Retruning values from sp

I have a sp and I need to return back the values to my vb.net page.   Can someone tell me how I can do this.  Thanks

************************  SQL SP

CREATE PROCEDURE dbo.AddNew
(
 @ssn varchar(4),
 @lname varchar(25)
)
AS


SET NOCOUNT ON

SET @lname = '%' + @lname + '%'

Select     strPatientid as 'Patient ID',
     strFname as 'First Name',
     strlname as 'Last Name',
     dtmDOB as DOB,
     strSSNum as SSN,
     CASE
          WHEN strlname like @lname AND Right(strssnum, 4) = @ssn THEN 100
          WHEN strlname like @lname THEN 10
          ELSE 1
     END As Score
from     tblpatient
where     strlname like @lname
     Or Right(strssnum, 4) = @ssn
ORDER BY Score DESC








***********************  VB FORM

Call Connection1.Open()
        'Add New Record to Household table and Patient Table by calling Stored Procedure
        lname = txtlname.Text
        ssn = txtSSN.ClipText
        ssn = VB.Right(ssn, 4)

        Dim com1 As New SqlCommand("AddNew", Connection1)
        com1.CommandType = CommandType.StoredProcedure
        com1.Parameters.Add(New SqlParameter("@lname", SqlDbType.VarChar)).Value = lname
        com1.Parameters.Add(New SqlParameter("@ssn ", SqlDbType.VarChar)).Value = ssn

        ' fill a table using a select statement
        ds = New DataSet("SBTestData")
        '****************************
        da = com1.ExecuteScalar()
        da.Fill(ds)
        Dim intNumRecs As Integer = ds.Tables(0).Rows.Count
        If intNumRecs > 0 Then
            da.Fill(ds, "tblPatient")
            DGDuplicates.DataSource = ds
            DGDuplicates.DataMember = "tblPatient"
            frmloading.Hide()
        End If
        Connection1.Close()
    End Function
running32Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

StylezHouseCommented:
You might wanna use output parameters.

See http://dotnetjunkies.com/WebLog/dinakar/articles/74220.aspx
running32Author Commented:
I'm not using asp.net is vb.net the same?
StylezHouseCommented:
Yep, the SqlClient objects are the same.
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

running32Author Commented:
I'm so new to vb.net.  I'm trying to fill a datagrid could you show me an example ofthat.

Thanks
StylezHouseCommented:
Dim objConn as New SqlConnection(ConnectionString)
Dim objCmd as new SqlCommand(SqlString, objConn)
Dim objDR as SqlDataReader


Try
     objConn.Open
     objDR = objCmd.ExecuteReader()
     DataGrid1.DataSource = objDR
     DataGrid1.DataBind()
     objDR.Close
Finally
     objCmd.Dispose
     objConn.Dispose
End Try

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
running32Author Commented:
Thanks, sorry I think I worded that incorrectly.  I am trying to fill a datagride with the results of the query after I pass the parameters.  I was wondering is there was an example of passing the parameters and then filling the datagrid.

Thanks
StylezHouseCommented:
....

Ok, thats 3 questions now... and this is shown in the link I gave you.... but here...

Dim objConn as New SqlConnection(ConnectionString)
Dim objCmd as new SqlCommand(SqlString, objConn)
Dim objDR as SqlDataReader


Try
     objConn.Open
     objCmd.CommandType = CommandType.StoredProcedure
     objCmd.Parameters.Add(New SqlParameter("@ParamName", ParamValue)
     objDR = objCmd.ExecuteReader()
     DataGrid1.DataSource = objDR
     DataGrid1.DataBind()
     objDR.Close
Finally
     objCmd.Dispose
     objConn.Dispose
End Try
running32Author Commented:
Thank you
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.