asked on

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?

Mikal613

http://www.vbdotnetheaven.com/Code/Jun2003/2102.asp

https://www.experts-exchange.com/questions/21415723/Executing-SQL-Server-stored-procedure-using-VB-NET.html

Brian Crowe

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.

pauldonson

ASKER

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 Crowe

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 Crowe

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

pauldonson

ASKER

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?

pauldonson

ASKER

Do I even need a data adapter? The results from the Stored Procedure will only ever be one row.

Brian Crowe

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.

pauldonson

ASKER

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?

ASKER CERTIFIED SOLUTION

Brian Crowe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Brian Crowe

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.