"Steve C. Orr [MVP, MCSD]" <> wrote in message
news:...
> 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" <> wrote in message
> news:...
>>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
>>
>
>
Similar Threads
