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 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
"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
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
"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
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 >
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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",...
|
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...
|
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;
...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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...
|
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...
| |