Visual Basic.NET
Visual Basic.NET

Get value into textbox from sproc

I am executing a stored procedure in which the dataset that is returned is only 1 row/column. I want to put that value into a textbox. The textbox does not have data binding. How do I get that value into it?

Private Sub GetCostCenter()
    Dim myCommand5 As New SqlCommand("Manager_Select_CostCenter", connection)
     myCommand5.CommandType = CommandType.StoredProcedure
     Dim myDA5 As New SqlDataAdapter
     myDA5.SelectCommand = myCommand5
     Dim dsCostCenter As New DataSet
     myDA5.Fill(dsCostCenter)
     txtCostCenter.Text = dsCostCenter `???
End Sub

Open in new window

LVL 1

allelopathAsked: 2013-07-01

Who is Participating?

LVL 83

CodeCruiserCommented: 2013-07-03

>You do not need SqlDataAdapter a reader will do the job just fine and quicker

You do not need a reader either. A single value can be retrieved using ExecuteScalar method

Private Sub GetCostCenter()
    Dim myCommand5 As New SqlCommand("Manager_Select_CostCenter", connection)
     myCommand5.CommandType = CommandType.StoredProcedure
     txtCostCenter.Text = myCommand5.ExecuteScalar()
End Sub

Open in new window

LVL 87

David Johnson, CD, MVPOwnerCommented: 2013-07-01

as long as it returns a string
myDA5.Fill(dsCostCenter)
txtCostCenter.Text = dsCostCenter

or even
txtCostCenter.Text= myDA5.Fill(dsCostCenter)

LVL 36

Miguel OzSoftware EngineerCommented: 2013-07-01

You do not need SqlDataAdapter a reader will do the job just fine and quicker:

Private Sub GetCostCenter()
     Dim myCommand5 As New SqlCommand("Manager_Select_CostCenter", connection)
     myCommand5.CommandType = CommandType.StoredProcedure
     myCommand5.Parameters.Add("@costcentre", SqlDbType.VarChar)
     myCommand5.Parameters("@costcentre").Direction = ParameterDirection.Output
     Dim sqlReader As SqlDataReader = myCommand5..ExecuteReader()
     sqlReader.Close()
     txtCostCenter.Text = myCommand5.Parameters("@costcentre").Value)
End Sub

Open in new window

Asumption: Stored procedure returns a string parameter called costcentre.