Connecting to same database at the same time

tobin46 used Ask the Experts™
In a function where I'm gathering claim data per policy, I execute a stored procedure and the result set returned with the following code:
scGet_Claim_Data = New SqlCommand("Get_Claim_Data", scDBConnection)
                    With (scGet_Claim_Data)
                        .CommandType = CommandType.StoredProcedure
                        .Parameters.Add("@PolNum", SqlDbType.VarChar, 18)
                        .Parameters("@PolNum").Value = sdrBasicPolicyInfo.Item("PolNum")
                        .Parameters.Add("@userdate", SqlDbType.DateTime, 18)
                        .Parameters("@userdate").Value = dteuserdate
                        sdrLossRecordInfo = .ExecuteReader()
                    End With
I have created structures that will assemble the data returned and write it to a text file.

There is one trigger for a particular field that will require another query to be run against the same database to gather additional information.  

IF a claim meets this requirement, I want to pass, Policy Number and Claim Number to another function and a procedure that would be connecting to the same database.  Could I re-use the connection since it would still be open in the other function?  Or would I simply need to create another connection using the same connection string?

For example, the connection string used above is scDBConnection, would I just need to create scDBConnection2?  Can I even do that since there is a live connection against the database?

Trying to plan on how to accomplish this...Thanks!
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You can re-use the connection for multiple queries. The connection is available till it is closed.

Create a new SqlCommand object and associate the existing connection object with that. Use the new command object to execute your second SP

Most Valuable Expert 2012
Top Expert 2014
In such scenarios, I declare the connection, command and reader(if needed) objects at the class level. I initialize them in the form load. These can then be used in all the functions. For error handling purposes, I just add a check to see if the connection is open or not. For example

Public Class MyClass
    Dim dbcon as sqlconnection
    Dim dbcmd as sqlcommand
Private Sub Form_Load...
End Sub

Private Sub LoadData...
    If IsNothing(dbcon) or isnothing(dbcmd) then
    End Sub
End Sub

Private Sub InitializeObjs()
    dbcon = new Sqlconnection(constring)
    dbcmd = new Sqlcommand
    dbcmd.connection = dbcon
End Sub
End Class

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial