By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,089 Members | 1,952 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,089 IT Pros & Developers. It's quick & easy.

Reuse paramter list and reuse connection

P: n/a
I can't seem to find where to reset the parameter list.

Dim objCmd as New SqlCommand(CommandText,objConn)
with objCmd.Parameters
.Add("@StateCode",SqlDbType.Char,2).value = ByState.SelectedValue
.Add("@City",SqlDbType.char).value = ByCity.SelectedValue
end with
objConn.Open()

ZipCode.DataSource=objCmd.ExecuteReader
ZipCode.DataTextField= "ZipCode"
ZipCode.DataValueField="ZipCode"
ZipCode.databind()

Now I want to use the same SqlCommand object and the same data connection to
do another select.

How do I clear the old parameters to allow me to readd the new ones and do I
need to do an objConn.close and another objConn.Open to do this?

Thanks,

Tom
Nov 19 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
To clear the parameters, use this syntax:
objCmd.Parameters.Clear()

Yes, you should always close the connection as soon as you can after
executing a query, and don't open a connection until just before you execute
a query. The built-in ADO.NET connection pooling makes this very efficient
in almost all circumstances.

--
I hope this helps,
Steve C. Orr, MCSD, MVP
http://SteveOrr.net

"tshad" <ts**********@ftsolutions.com> wrote in message
news:e7**************@TK2MSFTNGP14.phx.gbl...
I can't seem to find where to reset the parameter list.

Dim objCmd as New SqlCommand(CommandText,objConn)
with objCmd.Parameters
.Add("@StateCode",SqlDbType.Char,2).value = ByState.SelectedValue
.Add("@City",SqlDbType.char).value = ByCity.SelectedValue
end with
objConn.Open()

ZipCode.DataSource=objCmd.ExecuteReader
ZipCode.DataTextField= "ZipCode"
ZipCode.DataValueField="ZipCode"
ZipCode.databind()

Now I want to use the same SqlCommand object and the same data connection
to do another select.

How do I clear the old parameters to allow me to readd the new ones and do
I need to do an objConn.close and another objConn.Open to do this?

Thanks,

Tom

Nov 19 '05 #2

P: n/a
"Steve C. Orr [MVP, MCSD]" <St***@Orr.net> wrote in message
news:uq*************@TK2MSFTNGP09.phx.gbl...
To clear the parameters, use this syntax:
objCmd.Parameters.Clear()
That was what I was looking for here.

Yes, you should always close the connection as soon as you can after
executing a query, and don't open a connection until just before you
execute a query. The built-in ADO.NET connection pooling makes this very
efficient in almost all circumstances.
What if I am doing a 3 or 4 selects in a row to fill dropdowns or a
datagrid - one right after another?

If I am doing a datareader, I have to wait until I am done reading it before
I can close it, so wouldn't be better to just do something like:

************************************************** *****************************************
Dim objCmd as New SqlCommand(CommandText,objConn)
with objCmd.Parameters
.Add("@StateCode",SqlDbType.Char,2).value = ByState.SelectedValue
.Add("@City",SqlDbType.char).value = ByCity.SelectedValue
end with
objConn.Open()

ZipCode.DataSource=objCmd.ExecuteReader
ZipCode.DataTextField= "ZipCode"
ZipCode.DataValueField="ZipCode"
ZipCode.databind()

objCmd.Parameters.Clear()

objCmd.CommandText = "Select Counties,CountryCode from Countries where
ClientCode = @ClientCode"
with objCmd.Parameters
.Add("@ClientCode",SqlDbType.VarChar,10).value = ClientCode.Text
end with
objConn.Open()

Countries.DataSource=objCmd.ExecuteReader
Countries.databind()

' I assume I would not need to do an objCmd.Parameters.Clear() here, since
I am using the same paramter and adding another (although my assumption may
be incorrect).

objCmd.CommandText = "Select CarrierCodes, CarrierNames from Carrierswhere
ClientCode = @ClientCode and State = @StateCode"
with objCmd.Parameters
.Add("@StateCode",SqlDbType.Char,2).value = StateCode.Text
end with
objConn.Open()

Carriers.DataSource=objCmd.ExecuteReader
Carriers.databind()
************************************************** ***********************************'

Or should I close and open the connection between each select?

Thanks,

Tom


--
I hope this helps,
Steve C. Orr, MCSD, MVP
http://SteveOrr.net

"tshad" <ts**********@ftsolutions.com> wrote in message
news:e7**************@TK2MSFTNGP14.phx.gbl...
I can't seem to find where to reset the parameter list.

Dim objCmd as New SqlCommand(CommandText,objConn)
with objCmd.Parameters
.Add("@StateCode",SqlDbType.Char,2).value = ByState.SelectedValue
.Add("@City",SqlDbType.char).value = ByCity.SelectedValue
end with
objConn.Open()

ZipCode.DataSource=objCmd.ExecuteReader
ZipCode.DataTextField= "ZipCode"
ZipCode.DataValueField="ZipCode"
ZipCode.databind()

Now I want to use the same SqlCommand object and the same data connection
to do another select.

How do I clear the old parameters to allow me to readd the new ones and
do I need to do an objConn.close and another objConn.Open to do this?

Thanks,

Tom


Nov 19 '05 #3

P: n/a
Well, if your code works then it works. Why fix what ain't broken?
Then it just becomes a matter of what is most efficient and how much effort
you are willing to put in to squeeze out every last bit of performance.
Technically, the performance would be best if you rolled all these queries
into a single stored procedure that returns multiple result sets.

--
I hope this helps,
Steve C. Orr, MCSD, MVP
http://SteveOrr.net
"tshad" <ts**********@ftsolutions.com> wrote in message
news:ed**************@TK2MSFTNGP10.phx.gbl...
"Steve C. Orr [MVP, MCSD]" <St***@Orr.net> wrote in message
news:uq*************@TK2MSFTNGP09.phx.gbl...
To clear the parameters, use this syntax:
objCmd.Parameters.Clear()


That was what I was looking for here.

Yes, you should always close the connection as soon as you can after
executing a query, and don't open a connection until just before you
execute a query. The built-in ADO.NET connection pooling makes this very
efficient in almost all circumstances.


What if I am doing a 3 or 4 selects in a row to fill dropdowns or a
datagrid - one right after another?

If I am doing a datareader, I have to wait until I am done reading it
before I can close it, so wouldn't be better to just do something like:

************************************************** *****************************************
Dim objCmd as New SqlCommand(CommandText,objConn)
with objCmd.Parameters
.Add("@StateCode",SqlDbType.Char,2).value = ByState.SelectedValue
.Add("@City",SqlDbType.char).value = ByCity.SelectedValue
end with
objConn.Open()

ZipCode.DataSource=objCmd.ExecuteReader
ZipCode.DataTextField= "ZipCode"
ZipCode.DataValueField="ZipCode"
ZipCode.databind()

objCmd.Parameters.Clear()

objCmd.CommandText = "Select Counties,CountryCode from Countries where
ClientCode = @ClientCode"
with objCmd.Parameters
.Add("@ClientCode",SqlDbType.VarChar,10).value = ClientCode.Text
end with
objConn.Open()

Countries.DataSource=objCmd.ExecuteReader
Countries.databind()

' I assume I would not need to do an objCmd.Parameters.Clear() here,
since I am using the same paramter and adding another (although my
assumption may be incorrect).

objCmd.CommandText = "Select CarrierCodes, CarrierNames from Carrierswhere
ClientCode = @ClientCode and State = @StateCode"
with objCmd.Parameters
.Add("@StateCode",SqlDbType.Char,2).value = StateCode.Text
end with
objConn.Open()

Carriers.DataSource=objCmd.ExecuteReader
Carriers.databind()
************************************************** ***********************************'

Or should I close and open the connection between each select?

Thanks,

Tom


--
I hope this helps,
Steve C. Orr, MCSD, MVP
http://SteveOrr.net

"tshad" <ts**********@ftsolutions.com> wrote in message
news:e7**************@TK2MSFTNGP14.phx.gbl...
I can't seem to find where to reset the parameter list.

Dim objCmd as New SqlCommand(CommandText,objConn)
with objCmd.Parameters
.Add("@StateCode",SqlDbType.Char,2).value = ByState.SelectedValue
.Add("@City",SqlDbType.char).value = ByCity.SelectedValue
end with
objConn.Open()

ZipCode.DataSource=objCmd.ExecuteReader
ZipCode.DataTextField= "ZipCode"
ZipCode.DataValueField="ZipCode"
ZipCode.databind()

Now I want to use the same SqlCommand object and the same data
connection to do another select.

How do I clear the old parameters to allow me to readd the new ones and
do I need to do an objConn.close and another objConn.Open to do this?

Thanks,

Tom



Nov 19 '05 #4

P: n/a
"Steve C. Orr [MVP, MCSD]" <St***@Orr.net> wrote in message
news:uK**************@TK2MSFTNGP10.phx.gbl...
Well, if your code works then it works. Why fix what ain't broken?
Then it just becomes a matter of what is most efficient and how much
effort you are willing to put in to squeeze out every last bit of
performance.
Technically, the performance would be best if you rolled all these queries
into a single stored procedure that returns multiple result sets.
I don't know if this works (I assume it does). I am just trying to find
other and better ways to do it.

As far as the multiple results sets go, I am not sure yet how to handle the
result sets when they get back.

Tom
--
I hope this helps,
Steve C. Orr, MCSD, MVP
http://SteveOrr.net
"tshad" <ts**********@ftsolutions.com> wrote in message
news:ed**************@TK2MSFTNGP10.phx.gbl...
"Steve C. Orr [MVP, MCSD]" <St***@Orr.net> wrote in message
news:uq*************@TK2MSFTNGP09.phx.gbl...
To clear the parameters, use this syntax:
objCmd.Parameters.Clear()


That was what I was looking for here.

Yes, you should always close the connection as soon as you can after
executing a query, and don't open a connection until just before you
execute a query. The built-in ADO.NET connection pooling makes this
very efficient in almost all circumstances.


What if I am doing a 3 or 4 selects in a row to fill dropdowns or a
datagrid - one right after another?

If I am doing a datareader, I have to wait until I am done reading it
before I can close it, so wouldn't be better to just do something like:

************************************************** *****************************************
Dim objCmd as New SqlCommand(CommandText,objConn)
with objCmd.Parameters
.Add("@StateCode",SqlDbType.Char,2).value = ByState.SelectedValue
.Add("@City",SqlDbType.char).value = ByCity.SelectedValue
end with
objConn.Open()

ZipCode.DataSource=objCmd.ExecuteReader
ZipCode.DataTextField= "ZipCode"
ZipCode.DataValueField="ZipCode"
ZipCode.databind()

objCmd.Parameters.Clear()

objCmd.CommandText = "Select Counties,CountryCode from Countries where
ClientCode = @ClientCode"
with objCmd.Parameters
.Add("@ClientCode",SqlDbType.VarChar,10).value = ClientCode.Text
end with
objConn.Open()

Countries.DataSource=objCmd.ExecuteReader
Countries.databind()

' I assume I would not need to do an objCmd.Parameters.Clear() here,
since I am using the same paramter and adding another (although my
assumption may be incorrect).

objCmd.CommandText = "Select CarrierCodes, CarrierNames from
Carrierswhere ClientCode = @ClientCode and State = @StateCode"
with objCmd.Parameters
.Add("@StateCode",SqlDbType.Char,2).value = StateCode.Text
end with
objConn.Open()

Carriers.DataSource=objCmd.ExecuteReader
Carriers.databind()
************************************************** ***********************************'

Or should I close and open the connection between each select?

Thanks,

Tom


--
I hope this helps,
Steve C. Orr, MCSD, MVP
http://SteveOrr.net

"tshad" <ts**********@ftsolutions.com> wrote in message
news:e7**************@TK2MSFTNGP14.phx.gbl...
I can't seem to find where to reset the parameter list.

Dim objCmd as New SqlCommand(CommandText,objConn)
with objCmd.Parameters
.Add("@StateCode",SqlDbType.Char,2).value = ByState.SelectedValue
.Add("@City",SqlDbType.char).value = ByCity.SelectedValue
end with
objConn.Open()

ZipCode.DataSource=objCmd.ExecuteReader
ZipCode.DataTextField= "ZipCode"
ZipCode.DataValueField="ZipCode"
ZipCode.databind()

Now I want to use the same SqlCommand object and the same data
connection to do another select.

How do I clear the old parameters to allow me to readd the new ones and
do I need to do an objConn.close and another objConn.Open to do this?

Thanks,

Tom



Nov 19 '05 #5

P: n/a
You can use the DataReader.NextResult method.
Here's more info:
http://msdn.microsoft.com/msdnmag/is...T/default.aspx

--
I hope this helps,
Steve C. Orr, MCSD, MVP
http://SteveOrr.net
"tshad" <ts**********@ftsolutions.com> wrote in message
news:Oj**************@TK2MSFTNGP14.phx.gbl...
"Steve C. Orr [MVP, MCSD]" <St***@Orr.net> wrote in message
news:uK**************@TK2MSFTNGP10.phx.gbl...
Well, if your code works then it works. Why fix what ain't broken?
Then it just becomes a matter of what is most efficient and how much
effort you are willing to put in to squeeze out every last bit of
performance.
Technically, the performance would be best if you rolled all these
queries into a single stored procedure that returns multiple result sets.


I don't know if this works (I assume it does). I am just trying to find
other and better ways to do it.

As far as the multiple results sets go, I am not sure yet how to handle
the result sets when they get back.

Tom

--
I hope this helps,
Steve C. Orr, MCSD, MVP
http://SteveOrr.net
"tshad" <ts**********@ftsolutions.com> wrote in message
news:ed**************@TK2MSFTNGP10.phx.gbl...
"Steve C. Orr [MVP, MCSD]" <St***@Orr.net> wrote in message
news:uq*************@TK2MSFTNGP09.phx.gbl...
To clear the parameters, use this syntax:
objCmd.Parameters.Clear()

That was what I was looking for here.
Yes, you should always close the connection as soon as you can after
executing a query, and don't open a connection until just before you
execute a query. The built-in ADO.NET connection pooling makes this
very efficient in almost all circumstances.

What if I am doing a 3 or 4 selects in a row to fill dropdowns or a
datagrid - one right after another?

If I am doing a datareader, I have to wait until I am done reading it
before I can close it, so wouldn't be better to just do something like:

************************************************** *****************************************
Dim objCmd as New SqlCommand(CommandText,objConn)
with objCmd.Parameters
.Add("@StateCode",SqlDbType.Char,2).value = ByState.SelectedValue
.Add("@City",SqlDbType.char).value = ByCity.SelectedValue
end with
objConn.Open()

ZipCode.DataSource=objCmd.ExecuteReader
ZipCode.DataTextField= "ZipCode"
ZipCode.DataValueField="ZipCode"
ZipCode.databind()

objCmd.Parameters.Clear()

objCmd.CommandText = "Select Counties,CountryCode from Countries where
ClientCode = @ClientCode"
with objCmd.Parameters
.Add("@ClientCode",SqlDbType.VarChar,10).value = ClientCode.Text
end with
objConn.Open()

Countries.DataSource=objCmd.ExecuteReader
Countries.databind()

' I assume I would not need to do an objCmd.Parameters.Clear() here,
since I am using the same paramter and adding another (although my
assumption may be incorrect).

objCmd.CommandText = "Select CarrierCodes, CarrierNames from
Carrierswhere ClientCode = @ClientCode and State = @StateCode"
with objCmd.Parameters
.Add("@StateCode",SqlDbType.Char,2).value = StateCode.Text
end with
objConn.Open()

Carriers.DataSource=objCmd.ExecuteReader
Carriers.databind()
************************************************** ***********************************'

Or should I close and open the connection between each select?

Thanks,

Tom

--
I hope this helps,
Steve C. Orr, MCSD, MVP
http://SteveOrr.net

"tshad" <ts**********@ftsolutions.com> wrote in message
news:e7**************@TK2MSFTNGP14.phx.gbl...
>I can't seem to find where to reset the parameter list.
>
> Dim objCmd as New SqlCommand(CommandText,objConn)
> with objCmd.Parameters
> .Add("@StateCode",SqlDbType.Char,2).value = ByState.SelectedValue
> .Add("@City",SqlDbType.char).value = ByCity.SelectedValue
> end with
> objConn.Open()
>
> ZipCode.DataSource=objCmd.ExecuteReader
> ZipCode.DataTextField= "ZipCode"
> ZipCode.DataValueField="ZipCode"
> ZipCode.databind()
>
> Now I want to use the same SqlCommand object and the same data
> connection to do another select.
>
> How do I clear the old parameters to allow me to readd the new ones
> and do I need to do an objConn.close and another objConn.Open to do
> this?
>
> Thanks,
>
> Tom
>



Nov 19 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.