We help IT Professionals succeed at work.

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

pauldonson
pauldonson asked
on
Medium Priority
281 Views
Last Modified: 2010-04-23
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?
Comment
Watch Question

CERTIFIED EXPERT

Commented:
Brian CroweDatabase Engineer
CERTIFIED EXPERT
Top Expert 2005

Commented:
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.

Author

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.
Brian CroweDatabase Engineer
CERTIFIED EXPERT
Top Expert 2005

Commented:
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


   
Brian CroweDatabase Engineer
CERTIFIED EXPERT
Top Expert 2005

Commented:
You can put the code or call a separate procedure with the stored procedure from the load event.

Author

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?

Author

Commented:
Do I even need a data adapter? The results from the Stored Procedure will only ever be one row.
Brian CroweDatabase Engineer
CERTIFIED EXPERT
Top Expert 2005

Commented:
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.

Author

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?
Database Engineer
CERTIFIED EXPERT
Top Expert 2005
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Brian CroweDatabase Engineer
CERTIFIED EXPERT
Top Expert 2005

Commented:
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.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.