asked on

How to change the parameter value and re-use a sqlCommand object

What's the appropriate way to re-use a sqlCommand and re-execute a datareader in which all I want to change is a parameter value? See code below. Note: Executing the Reader in the first iteration works fine, then it bombs on the second iteration of the loop.

cn2 = New SqlConnection(GlobalVariables.strConn)
cn2.Open()
cmd2 = New SqlCommand(m_strNameLookupSQL, cn2)
cmd2.Parameters.Add("@ItemId", SqlDbType.Int)

Do While <some condition>
cmd2.Parameters("@ItemId").Value = <some value that's different with each iteration of the loop>
rdr2 = cmd2.ExecuteReader
rdr2.Read()
Loop

Alfredo Luis Torres Serrano

Do While <some condition>
cmd2.Parameters("@ItemId").Value = <some value that's different with each iteration of the loop>
rdr2 = cmd2.ExecuteReader
rdr2.Read()
cmd2.Parameters.Clear()
cmd2.Parameters.Add("@ItemId", SqlDbType.Int)
Loop

Declan Basile

ASKER

It still bombs on the .executereader line during the second iteration of the loop ...

Do While rdr1.Read
lngRootNodeId = rdr1("Id")
cmd2.Parameters.Clear()
cmd2.Parameters.Add("@ItemId", SqlDbType.Int).Value = lngRootNodeId
rdr2 = cmd2.ExecuteReader
rdr2.Read()
Loop

Chinmay Patel

What error you are getting?

ASKER CERTIFIED SOLUTION

Alfredo Luis Torres Serrano

membership

This solution is only available to members.

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

Start Free Trial

Declan Basile

ASKER

Thank you!. I was missing the rdr2.Close. I was able to just change the value of the parameter instead of clearing and re-adding the parameter every time. The following code worked ...

cmd2 = New SqlCommand(m_strNameLookupSQL, cn2)
cmd2.Parameters.Add("@ItemId", SqlDbType.Int)

Do While rdr1.Read
lngRootNodeId = rdr1("Id")
cmd2.Parameters("@ItemId").Value = lngRootNodeId
rdr2 = cmd2.ExecuteReader
rdr2.Read()
strRootNodeName = rdr2("ItemName")
rdr2.Close()

Alfredo Luis Torres Serrano

Glad to help, Yes you have to close the reader