Reusing Parameters

I have a loop that is doing multiple Stored Procedure calls (same call but
data is changing) and I get an error saying I parameter is already added.
It makes sense, but I am not sure how I move data into the parameter I have
already added.
The routine is:
objCmd.CommandText = "exec AddNewApplicantScreen
@.ApplicantID,@.PositionID,@.Version,@.Quest
ionUnique,@.Answer,@.AnswerTime"
with objCmd.Parameters
.Add("@.ApplicantID",SqlDbType.Int).value = session("ApplicantID")
.Add("@.PositionID",SqlDbType.Int).value = session("PositionID")
.Add("@.Version",SqlDbType.Int).value = 0
.Add("@.QuestionUnique",SqlDbType.Int).value = oQuestionUnique.text
.Add("@.Answer",SqlDbType.Int).value = AnswerBits
.Add("@.AnswerTime",SqlDbType.Int).value = 0
end with
objConn.Open
objCmd.ExecuteNonQuery
objConn.Close()
next
I assume I would take the "objCmd.CommandText = " and .add statements
outside of the loop and them somehow do something like
objCmd.Parameters(...) = something
Also, can I just put the objConn.Open and objConn.Close outside of the loop
and reexecute the objCmd.ExecuteNonQuery multiple times with closing and
reopening or do I need to do it as above?
Thanks,
TomHi Tom,
There are a couple of ways to to handle this...
One is that you can reset the objCmd with objCmd = New
System.Data.SqlClient.SqlCommand() as the first line in the loop...this will
necessitate you resetting the Connection object for the command.
Or Two before you start adding parameters after you invoke the
objCmd.Parameters.Clear() method. The Clear Method does exactly what it says
it clears/deletes all parameters in the Parameters Collection.
And yes you can put the Connection.Open() and Connection.Close() method
outside the loop.
Just objConn.Open() before your loop and invoke objConn.Close() after the
loop.
Hope this helps,
Chris
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:e9O%23uynAFHA.3416@.TK2MSFTNGP09.phx.gbl...
>I have a loop that is doing multiple Stored Procedure calls (same call but
>data is changing) and I get an error saying I parameter is already added.
> It makes sense, but I am not sure how I move data into the parameter I
> have already added.
> The routine is:
> objCmd.CommandText = "exec AddNewApplicantScreen
> @.ApplicantID,@.PositionID,@.Version,@.Quest
ionUnique,@.Answer,@.AnswerTime"
> with objCmd.Parameters
> .Add("@.ApplicantID",SqlDbType.Int).value = session("ApplicantID")
> .Add("@.PositionID",SqlDbType.Int).value = session("PositionID")
> .Add("@.Version",SqlDbType.Int).value = 0
> .Add("@.QuestionUnique",SqlDbType.Int).value = oQuestionUnique.text
> .Add("@.Answer",SqlDbType.Int).value = AnswerBits
> .Add("@.AnswerTime",SqlDbType.Int).value = 0
> end with
> objConn.Open
> objCmd.ExecuteNonQuery
> objConn.Close()
> next
> I assume I would take the "objCmd.CommandText = " and .add statements
> outside of the loop and them somehow do something like
> objCmd.Parameters(...) = something
> Also, can I just put the objConn.Open and objConn.Close outside of the
> loop and reexecute the objCmd.ExecuteNonQuery multiple times with closing
> and reopening or do I need to do it as above?
> Thanks,
> Tom
>|||"Chris Hayes" <cp.hayesATsbcglobal.net@.nospam.nospam> wrote in message
news:%23w8uaDoAFHA.3988@.TK2MSFTNGP11.phx.gbl...
> Hi Tom,
> There are a couple of ways to to handle this...
> One is that you can reset the objCmd with objCmd = New
> System.Data.SqlClient.SqlCommand() as the first line in the loop...this
> will necessitate you resetting the Connection object for the command.
>
Will I need to close and reopen the connection here, since I am resetting
the connection?

> Or Two before you start adding parameters after you invoke the
> objCmd.Parameters.Clear() method. The Clear Method does exactly what it
> says it clears/deletes all parameters in the Parameters Collection.
> And yes you can put the Connection.Open() and Connection.Close() method
> outside the loop.
> Just objConn.Open() before your loop and invoke objConn.Close() after the
> loop.
>
So each time I can do multiple objCmd.ExecuteNonQuery commands on the same
connection, what about DataAdapters or ExecuteReaders which return data.
Will it clear the old results for each execution or append the results?

> Hope this helps,
It does.
Thanks,
Tom
> Chris
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:e9O%23uynAFHA.3416@.TK2MSFTNGP09.phx.gbl...
>|||I've included a VB.NET code sample to illustrate.
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:erXs%23MoAFHA.2624@.TK2MSFTNGP11.phx.gbl...
> "Chris Hayes" <cp.hayesATsbcglobal.net@.nospam.nospam> wrote in message
> news:%23w8uaDoAFHA.3988@.TK2MSFTNGP11.phx.gbl...
> Will I need to close and reopen the connection here, since I am resetting
> the connection?
>
You will need to assign the connection only:
objCommand.Connection = conn
As long as you instantiated the connection object as its own object, you
will not need to reopen the connection .

> So each time I can do multiple objCmd.ExecuteNonQuery commands on the same
> connection, what about DataAdapters or ExecuteReaders which return data.
> Will it clear the old results for each execution or append the results?
>
From my experiences:
If you are using the DataAdapter to "Fill" a DataTable or a DataSet, it will
append to the DataTable or DataSet. If you are doing .ExecuteReader it will
not append as only one DataReader can be assigned at a time from the
.ExecuteReader method.
I hope this helps,
Chris
CODE SAMPLE:
Private Sub btnAppend_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnAppend.Click
Dim da As SqlClient.SqlDataAdapter
Dim conn As SqlClient.SqlConnection
Dim cmd As SqlClient.SqlCommand
Dim i As Int32
Dim ds As DataSet
ds = New DataSet
da = New SqlClient.SqlDataAdapter
conn = New SqlClient.SqlConnection("Server=(local);Initial
Catalog=Test;Integrated Security=SSPI;")
conn.Open()
For i = 1 To 5
cmd = New SqlClient.SqlCommand("procAppend", conn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@.ID", SqlDbType.Int).Value = i
da.SelectCommand = cmd
da.Fill(ds)
cmd.Dispose()
Next
conn.Close()
conn.Dispose()
datagrid2.DataSource = ds
End Sub|||"Chris Hayes" <cp.hayesATsbcglobal.net@.nospam.nospam> wrote in message
news:%23PdW25oAFHA.3616@.TK2MSFTNGP11.phx.gbl...
> I've included a VB.NET code sample to illustrate.
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:erXs%23MoAFHA.2624@.TK2MSFTNGP11.phx.gbl...
> You will need to assign the connection only:
> objCommand.Connection = conn
> As long as you instantiated the connection object as its own object, you
> will not need to reopen the connection .
When I close the the page, will it close the connection at this point if I
haven't alrea done it?

>
> From my experiences:
> If you are using the DataAdapter to "Fill" a DataTable or a DataSet, it
> will append to the DataTable or DataSet. If you are doing .ExecuteReader
> it will not append as only one DataReader can be assigned at a time from
> the .ExecuteReader method.
> I hope this helps,
> Chris
> CODE SAMPLE:
> Private Sub btnAppend_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles btnAppend.Click
> Dim da As SqlClient.SqlDataAdapter
> Dim conn As SqlClient.SqlConnection
> Dim cmd As SqlClient.SqlCommand
> Dim i As Int32
> Dim ds As DataSet
> ds = New DataSet
> da = New SqlClient.SqlDataAdapter
> conn = New SqlClient.SqlConnection("Server=(local);Initial
> Catalog=Test;Integrated Security=SSPI;")
> conn.Open()
> For i = 1 To 5
> cmd = New SqlClient.SqlCommand("procAppend", conn)
> cmd.CommandType = CommandType.StoredProcedure
> cmd.Parameters.Add("@.ID", SqlDbType.Int).Value = i
> da.SelectCommand = cmd
> da.Fill(ds)
> cmd.Dispose()
> Next
> conn.Close()
> conn.Dispose()
> datagrid2.DataSource = ds
> End Sub
Thanks,
Tom|||Hi Tom,
While the Garbage Collector is supposed to help clean up memory, I don't
fully trust it.
I always close my connections and dispose of my objects after I have used
them. My philosophy is to only instantiate an object when needed and keep it
in memory for only as long as it needed and then when it is not to Dispose
of it. Of course the Dispose method only marks an object for the Garbage
Collector to deal with, but at least it's marked and the Garbage Collector
doesn't have to figure it out.
I normally wrap my dataaccess code in a try catch finally statement and in
the finally I check my dataaccess objects, if they are instantiated (not
equal to null/nothing) then I check the connection state if it's a
connection object, if it's not closed, I close it then dispose of it.
Chris
Here's a simple example of some clean up code I use:
Try
'logic
Catch ex As Exception
'handle the error
Finally
If Not da Is Nothing Then da.Dispose()
If Not cmd Is Nothing Then cmd.Dispose()
If Not conn Is Nothing Then
If conn.State = ConnectionState.Open Then conn.Close()
conn.Dispose()
End If
da = Nothing
cmd = Nothing
conn = Nothing
End Try
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:ONRsawxAFHA.2196@.TK2MSFTNGP14.phx.gbl...
> "Chris Hayes" <cp.hayesATsbcglobal.net@.nospam.nospam> wrote in message
> news:%23PdW25oAFHA.3616@.TK2MSFTNGP11.phx.gbl...
> When I close the the page, will it close the connection at this point if I
> haven't alrea done it?
>
> Thanks,
> Tom
>