Fill Datagrid with results from stored procedure.

I'm not sure how to program so that I make a call to the stored procedure, passing two parameters and it brings be make the results to a datagrid.

Here is what I have so far.  The error I get is  'DataBind' is not a member of 'System.Windows.Forms.DataGrid'.  There may be more but so far this is the only error I have.  After the error I'm not sure how to fill the da.

Thanks

 Private Function Search()
        Dim frmloading As New formloading
        ' frmloading.Show()
        Dim checkControl As RadioButton
        Dim ds As DataSet
        Dim da As System.Data.SqlClient.SqlDataAdapter
        Dim dr As DataRow
        Dim dc As DataColumn
        Dim IsChecked, IsCheckedName
        Dim fname, lname, ssn
        Dim seperator
        Dim lenght

        '  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 objConn As New SqlClient.SqlConnection("workstation id=JCleary;packet size=4096;integrated security=SSPI;data source=JCleary;persist security info=False;initial catalog=healthdistrict")
        Dim objCmd As New SqlCommand
        Dim objDR As SqlDataReader

        Try
            objConn.Open()
            objCmd.CommandType = CommandType.StoredProcedure
            objCmd.Parameters.Add(New SqlParameter("@lname", lname))
            objCmd.Parameters.Add(New SqlParameter("@ssn", ssn))
            objDR = objCmd.ExecuteReader()
            DGDuplicates.DataSource = objDR
            DGDuplicates.DataBind()
            objDR.Close()
        Finally
            objCmd.Dispose()
            objConn.Dispose()
        End Try


        ' 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.

Bob LearnedCommented:
objCmd.CommandText = "Stored procedure name goes here"

Bob

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:
Thank you.  I have added that but still have the error with the data binding.

Bob LearnedCommented:
DataBind is only for ASP.NET DataGrid.

Delete that line.

Bob
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

running32Author Commented:
When I make that change I get Connection Property not Initialized for   objDR = objCmd.ExecuteReader()
Bob LearnedCommented:
objCmd.Connection = objConn

Bob
running32Author Commented:
Thank you very much for you help.  Could you help me with the final part of the puzzel would be be how do I now get this into the datagrid.
Bob LearnedCommented:
What code do you have now?

Bob
running32Author Commented:
Below is what I have so far.  I'm not sure how to get the result from into a dataset.  Thank you
   

     objConn.Open()
        objCmd.CommandType = CommandType.StoredProcedure
        objCmd.Parameters.Add(New SqlParameter("@lname", lname))
        objCmd.Parameters.Add(New SqlParameter("@ssn", ssn))
        objCmd.CommandText = "AddNew"
        objCmd.Connection = objConn
        objDR = objCmd.ExecuteReader()
        'objDR.Close()

        ' fill a table using a select statement
        ds = New DataSet("SBTestData")
        '****************************
        da = DGDuplicates.DataSource
        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
Bob LearnedCommented:
This looks like you have mixed up a few things, so I will try to simplify:

  objConn.Open()
  objCmd.CommandType = CommandType.StoredProcedure
  objCmd.Parameters.Add(New SqlParameter("@lname", lname))
  objCmd.Parameters.Add(New SqlParameter("@ssn", ssn))
  objCmd.CommandText = "AddNew"
  objCmd.Connection = objConn
  objDR = objCmd.ExecuteReader()

  DGDuplicates.DataSource = objDR

Bob
running32Author Commented:
I did try DGDuplicates.DataSource = objDR before my final posting and got the error below.

Additional information: Complex DataBinding accepts as a data source either an IList or an IListSource
Bob LearnedCommented:
Try this:

Imports System.Data.SqlClient

 Private Sub Search()

        'Add New Record to Household table and Patient Table by calling Stored Procedure
        Dim lname As String = txtlname.Text
        Dim ssn As String = txtSSN.ClipText.SubString(txtSSN.ClipText.Length - 5, 4)

        Dim connectString As String = "workstation id=JCleary;packet size=4096;integrated security=SSPI;data source=JCleary;persist security info=False;initial catalog=healthdistrict")

        Dim da As New SqlDataAdapter("Stored procedure name", connectString)

        da.SelectCommand.Parameters.Add("@lname, lname)
        da.SelectCommand.Parameters.Add("@ssn, ssn)

        Dim ds As New DataSet

        da.Fill(ds, "SBTestData")

        DGDuplicates.Datasource = ds
        DGDuplicates.Datamember = "SBTestData"
 
    End Sub


Bob
running32Author Commented:
Thank you for working with me on this.  With these changes I get the error on da.Fill(ds, "SBTestData").  The usual Microsoft error that and I have no idea what it is.

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll

Additional information: System error.
Bob LearnedCommented:
That usually means that you have something incorrect in the connection string.

There is a way to create and test a connection.  Create a temporary form, and drag/drop an SQLDataAdapter to the form, and you will get a DataAdapter creation wizard.  This wizard uses a dialog for creating connections that will help you get the appropriate form, and it has a <Test Connection> button.

Bob
running32Author Commented:
I tested and the connection is fine.  Could it be my sp?

Thanks

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



GO
Bob LearnedCommented:
You can test Stored Procedures from the Server Explorer.  Does this stored procedure test as expected?

Bob
running32Author Commented:
Yes, sp is running just fine when I pass the parameters in sql QA.

Bob LearnedCommented:
How are lname and ssn defined in the SQL Server table?

Bob
running32Author Commented:
nvarchar(50)  = lname
nvarchar(15) = ssn
Bob LearnedCommented:
Try this instead:

     da.SelectCommand.Parameters.Add("@lname", SqlDbType.NVarChar, 50).Value = lname
     da.SelectCommand.Parameters.Add("@ssn", SqlDbType.NVarChar, 15).Value = ssn

Bob
running32Author Commented:
I'm not sure what it is.  I still get the error.  
running32Author Commented:
Thank you for your help you got me closer than I was.
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.