Populating a Windows form with results of a SQL Stored Procedure in VB 2005

Hi,

This one is driving me mad! I am quite new to VB, my expertise being with Access Data Projects.

I have a form that has a person_ID on it, I want the user to be able to click a button and a new form opens with the data for that person_ID on the new form. This data is retrieved via a stored procedure: ProcPersonFetchPersonal.

I have tried doing this using the following code:

        'Open people maintenance
        Me.Cursor = Cursors.WaitCursor

        Dim conn As SqlClient.SqlConnection = New SqlClient.SqlConnection("Data Source=" & frmLogOn.txtServer.Text.ToString & ";Integrated Security=SSPI;Initial Catalog=" & frmLogOn.cboDatabase.Text.ToString)
        Dim cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand
        Dim data As New DataSet

        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "procPersonFetchPersonal"
        cmd.Connection = conn
        cmd.Parameters.Add(New SqlClient.SqlParameter("@Person_ID", Me.lstResults.SelectedValue))

        Dim adapter As New SqlClient.SqlDataAdapter(cmd)
        adapter.Fill(data, "procPersonFetchPersonal")

        frmPeopleMaintenance.cboOfficeID.DataBindings.Add(New System.Windows.Forms.Binding("Text", adapter, "Office_ID"))
        frmPeopleMaintenance.txtPersonName.DataBindings.Add("Text", data, "Person_name")                ************This is the problem line

        frmPeopleMaintenance.MdiParent = frmMain
        frmPeopleMaintenance.Show()

        conn = Nothing
        cmd = Nothing
        data = Nothing
        adapter = Nothing

        Me.Cursor = Cursors.Default

The difficult bit is getting the data onto the form (I don't want it bound to the form if possible) , Ideally, when the form opens I want the text property of each field to be populated with the results of the stored procedure. This would allow free editing by the user and a "commit" button would save any changes with a different stored procedure.

I have experimented with using system-generated datasets but this seems to offer a too simplistic result, requiring that the person_id is input onto the form and the Fill button pressed to retrieve the data, which works but is not professional enough for the results I need.

Can anyone point me in the right direction please?
pauldonsonAsked:
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.

Brian CroweDatabase AdministratorCommented:
Just a note on your design.  OO wise you should be doing the load of the data in the child form not in the parent.  I would suggest passing personID to the child via a property and moving all of the stored prcocedure related code to the child.  Part of your problem may be that you're trying to bind a control in one class to the dataset in another.

0
pauldonsonAuthor Commented:
Thanks for the tip BriCrowe, do I need to put the code in the load or activate event? I guess I need to be careful of the order of events, i.e. the parent needs to have passed the ID field and the child received it before the stored procedure runs.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Brian CroweDatabase AdministratorCommented:
you could also opt to edit the constructor to accept an ID value in your child form.  This would take care of any order of event issues.

public class frmChild
   inherits system.windows.forms.form

private m_iID as integer

public sub new(iID as integer)
   InitializeComponent()
   m_iID  = iID
end sub


   
0
Brian CroweDatabase AdministratorCommented:
You can put the code or call a separate procedure with the stored procedure from the load event.
0
pauldonsonAuthor Commented:
OK, I have a subroutine that is based the tmpPerson_ID field for the form to open with. Still not sure how to populate the .text property of the textbox fields in the form:

    Sub subOpenPeopleMaintenance(ByVal tmpPersonID As Integer)

        'Open people maintenance
        Dim conn As SqlClient.SqlConnection = New SqlClient.SqlConnection("Data Source = " & frmLogOn.txtServer.Text.ToString & ";Integrated Security=SSPI;Initial Catalog=" & frmLogOn.cboDatabase.Text.ToString)
        Dim cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand
        Dim data As New DataSet

        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "procPersonFetchPersonal"
        cmd.Connection = conn
        cmd.Parameters.Add(New SqlClient.SqlParameter("@Person_ID", tmpPersonID))

        Dim adapter As New SqlClient.SqlDataAdapter(cmd)
        adapter.Fill(data, "procPersonFetchPersonal")

        frmPeopleMaintenance.cboOfficeID.DataBindings.Add(New System.Windows.Forms.Binding("Text", adapter, "Office_ID"))
        frmPeopleMaintenance.txtPersonName.DataBindings.Add("Text", adapter, "Person_name")

        'frmPeopleMaintenance.txtPersonName.Text= cmd.


        frmPeopleMaintenance.MdiParent = frmMain
        frmPeopleMaintenance.Show()

        conn = Nothing
        cmd = Nothing
        data = Nothing
        adapter = Nothing

    End Sub

When I call this subroutine with the following line:

Call subOpenPeopleMaintenance(Me.lstResults.SelectedValue)

I get the following error at the line "frmPeopleMaintenance.Show()":

Cannot bind to the property or column Person_name on the DataSource.
Parameter name: dataMember


Any ideas?
0
pauldonsonAuthor Commented:
Do I even need a data adapter? The results from the Stored Procedure will only ever be one row.
0
Brian CroweDatabase AdministratorCommented:
No, but if you're writing back to your database then it is often simpler to use the dataadapter but you could use the datareader to pull in the data and either store it in a table or add the data directly to your controls.  When you go to write the data back to your database you could use a command and just use an update query using the values in your controls.
0
pauldonsonAuthor Commented:
That sounds like what I want to do, what is the syntax for setting the text property of a textbox with a field from a datareader?
0
Brian CroweDatabase AdministratorCommented:
Here is a typical use of the datareader to populte a control.   I've used you code as much as I could but you

Dim conn As SqlClient.SqlConnection = New SqlClient.SqlConnection("Data Source=" & frmLogOn.txtServer.Text.ToString & ";Integrated Security=SSPI;Initial Catalog=" & frmLogOn.cboDatabase.Text.ToString)
Dim cmd As new SqlClient.SqlCommand
dim dr as sqlclient.sqldatareader

with cmd
   .connection = conn
   .CommandType = CommandType.StoredProcedure
   .CommandText = "procPersonFetchPersonal"
   .Parameters.Add(New SqlClient.SqlParameter("@Person_ID", sqldbtype.varchar, 50).value = {assign value from class member if you went that route}

try
   cmd.connection.open
   dr = cmd.executereader
   while dr.read
      txtOfficeID.text = dr("OfficeID")  'where "OfficeID" is the name of the column returned from the storedprocedure
      txtPersonName.text = dr("PersonName")
   end while
catch ex as exception
   messagebox.show(ex.message)
finally
   if cmd.connection.state = connectionstate.open
      cmd.connection.close
   end if
end try
0

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
Brian CroweDatabase AdministratorCommented:
dropped off in the middle of that second sentence there...what I was trying to say is that you will probably have to change the column/control names and the parameter type to match yours.
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.