473,322 Members | 1,493 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Reuse paramter list and reuse connection

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
5 1750
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
"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
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
"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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: DPfan | last post by:
What's exactly the meaning of "code reuse" in C++? Why such kind of reuse have more advantages over the counterpart in other language like in C? How is "code reuse" realized in C++? By...
0
by: anders thoresson | last post by:
Hi, I have this function I call everytime I need to make a query from within my php-scripts: function db_connect ($user, $pwd, $db, $debug = 0) { $link = @mysql_pconnect("localhost",...
3
by: danra | last post by:
Hi, I have a question which seems to me pretty basic, unfortunately I can't seem to figure it out. Let's say I have an abstract base class Vehicle and classes Car and Truck which derive from...
34
by: Kovan Akrei | last post by:
Hi, I would like to know how to reuse an object of a thread (if it is possible) in Csharp? I have the following program: using System; using System.Threading; using System.Collections; ...
0
by: integragreg | last post by:
I apologize in advance if I am posting to the wrong group, but at least one of my questions is related to Platform Invoke in C#. I am using .NET Framework 1.1, and for improved performance, I...
4
by: Craig Buchanan | last post by:
I would like to reuse a SQLDataReader that is populated with information as the datasource for multiple dropdownlists. Unfortunately, the first DDL closes the SDR and my code fails on the second...
3
by: steph | last post by:
Hi, I've got below function to dynamically define and run pass-through queries. This works OK. But I don't want to have the connection string hard-coded, instead I want to reuse the existing...
2
by: Garx | last post by:
Hi Guys, This is my first post so bear with me :) I am running Access 2003 and am still feeling my way around it. I have a form (FORM_IDL) that uses a combobox (cboIDA) which uses a query as...
7
by: RichB | last post by:
I am just trying to get to grips with C# and OOP, and one of the benefits would seem to be code reuse. However I am not sure where to draw the line. I have the following section of code: if...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.