Populating a Windows form with results of a SQL Stored Procedure in VB 2005
Posted on 2006-04-03
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)
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
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?