Retrieve value from database and use in another sub???

[Guest]

Hi can someone tell me how I return a value from the following sub and then
use that value in another sub!

I have an SQL statement which is a little like this:

Select officeID, offName FROM tblOffice where officeID = OffName

I basically want to pull to different ID's from differnet tables and then
insert those ID's into another table! I already have one ID but need to
return the OfficeID and then get that value to use in an insert statement
into another sub!

Thanks for any help!


... CODE

Sub GetOfficeID_for_Insert(ByVal e As DataGridCommandEventArgs)

Dim office As String
Dim txtOffice As TextBox

txtOffice = e.Item.FindControl("DDLaddOffice")
office = txtOffice.Text

'Create the appropriate SQL statement
Dim Myconn As New
SqlConnection(ConfigurationSettings.AppSettings("strConn"))
Dim cmd As New SqlCommand("SelectOfficeID", Myconn)
cmd.CommandType = CommandType.StoredProcedure

Myconn.Open()
' Gets the OfficeID from tblOffice according to which office the
user chose
Dim objOffName As SqlParameter
objOffName = cmd.Parameters.Add("@offName", SqlDbType.NVarChar)
objOffName.Direction = ParameterDirection.Input
objOffName.Value = office

Dim myReader As SqlDataReader = cmd.ExecuteReader()
myReader.Read()

myReader.Close()

Myconn.Close()
End Sub

 

[Guest]

Tim,

You could change your sub to a function that returns a dataset. Then you
would have the values at hand to insert into the other table. I have posted
some sample code below for you to look at. Please keep in mind that if you
decide to use this code to add exception handling to it.

Hope this helps.

---------------------------------

Public Function GetOfficeID_for_Insert() As DataSet
Dim cn As New SqlConnection(strConn)
Dim da As New SqlDataAdapter
Dim ds As New DataSet
Dim cmd As New SqlCommand("SelectOfficeID", cn)
cmd.CommandType = CommandType.StoredProcedure
Dim param As New SqlParameter("@offName", 5)
param.Direction = ParameterDirection.Input
cmd.Parameters.Add(param)
da.SelectCommand = cmd
da.Fill(ds)
Return ds
End Function