?
Solved

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

Posted on 2006-04-03
12
Medium Priority
?
254 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?
0
Comment
Question by:pauldonson
  • 6
  • 4
11 Comments
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 16362394
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
 

Author Comment

by:pauldonson
ID: 16362433
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 34

Expert Comment

by:Brian Crowe
ID: 16362652
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
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 16362658
You can put the code or call a separate procedure with the stored procedure from the load event.
0
 

Author Comment

by:pauldonson
ID: 16370418
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
 

Author Comment

by:pauldonson
ID: 16370526
Do I even need a data adapter? The results from the Stored Procedure will only ever be one row.
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 16370640
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
 

Author Comment

by:pauldonson
ID: 16370677
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
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 2000 total points
ID: 16372754
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
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 16372762
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Loops Section Overview
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses
Course of the Month17 days, 10 hours left to enroll

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question