Re: Query parameters - Tricky Stored Procedure Question

Tech-Archive recommends: Fix windows errors by optimizing your registry

Hi Boo,

Thanks for your help on this. I didn't actually mean for you to write
the entire code but I do appreciate you taking the time to do so as it
has answered my question. I was wondering if you had to specify the
CommandType as CommandType.Text, which you have now confirmed.

This is pretty nifty, thanks!!!

Ric


GhostInAK wrote:
Hello ric_deez,

I thought I had. However, if by explain you mean write you the entire code..
*sigh* fine.. but only because I don't hate you, yet. Thias is off the
top of my head...

Dim tConnection As SqlConnection = New SqlConnection(connection_string_here)
Dim tCommand As SqlCommand = New SqlCommand
Dim tAdapter As SqlDataAdapter = New SqlDataAdapter(tCommand)
Dim tTable as DataTable = New DataTable

With tCommand
.Connection = tConnection
.CommandType = CommandType.Text
.ComandText = "SELECT field1 FROM table1 WHERE (field1 = @value1)"
' Note: Here you would pass in your constructed ad-hoc query
.Parameters.AddWithValue("@value1", value1) ' See, the parameter
name corresponds to the parameter name in the query.. spiffy huh..
End With

tConnection.Open
tAdapter.Fill(tTable)
tConnection.Close
' And now you got a shiney new DataTable to show your friends

tAdapter = Nothing
tCommand = Nothing
tConnection = Nothing

-Boo

Hi there Boo,

Can you please explain how to use the sqlParameter with ad-hoc
queries?

Dont forget that ad-hoc queries can still take advantage of the
SqlParameter
SELECT field1 FROM table1 WHERE (field1 = @field1) --
tCommand.Parameters.AddWithValue("@field1",
value1) )
Thanks!

Ric

GhostInAK wrote:

Hello ric_deez,

This is one case in which an inline ad-hoc query would be preferable
I do
believe. Done right it is extremely maintainable. TSQL, while up to
the
task, is not designed for such a query and would indeed be the less
maintainable
solution.
Put a layer of abstraction between the UI and the query. This
abstraction layer would take as inputs the name of the field to
search as well as the value to search for. It would then give you a
hashtable or some similar construct which you could then loop over to
construct your query.

Dont forget that ad-hoc queries can still take advantage of the
SqlParameter
object (eg. SELECT field1 FROM table1 WHERE (field1 = @field1) --
tCommand.Parameters.AddWithValue("@field1",
value1) )
Enjoy,
-Boo
Hi there, I would like to create a simple search form to allow users
to search for a job number based on a number of parameters. I think
I understand how to use parameteres associated with Stored
Procedures with a data reader to add various parameters.

However, if I have a stored procedure such as

CREATE usp_SelectfromJobNumbers (@par1 datatype, @par2 datatype,
@par3 datatype)

AS

SELECT FROM Jobs WHERE
PAR1 = @par1 AND
PAR2 = @par2 AND
PAR3 = @par3
I cannot just pass the one parameter to the procedure. The
question
is then: how is it possible to create a stored procedure which can
accept any number of parameters an then smartly modify the query in
the store procedure with CASE statements or similar?
I don't really want to do this as in-line code as it easily becomes
unmaintainable and it is hard to add additional conditions. I was
thinking of passing a string array into the Stored Procedure and
having the stored procedure parse through the array, determine the
parameters and do the grunt of the work. The problem is that I
haven't got a lot of experience with these advanced stored
procedures using CASE statements and the like...

Any ideas would be greatly appreciated.

Regards,

Ric


.

Relevant Pages

  • Re: Query parameters - Tricky Stored Procedure Question
    ... Dim tConnection As SqlConnection = New SqlConnection ... Here you would pass in your constructed ad-hoc query ... This is one case in which an inline ad-hoc query would be preferable ... how is it possible to create a stored procedure which can ...
    (microsoft.public.dotnet.languages.vb)
  • Re: noise words, @@ERROR, and stop and resume indexing
    ... > Noiseword varcharNot Null ... > the data from the noise file to the noise_words table. ... >> A clause of the query contained only ignored words. ... >> into query analyzer before starting the stored procedure. ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Call SQL Stored procedure in Access 2002
    ... compile it stops on Dim db as DAO.Database and says dao.database ... > Create a new, empty query, and then change it to a SQL pass-through query ... >> move the processing to a stored procedure because I am getting ODBC ...
    (microsoft.public.access.formscoding)
  • RE: ODBC query in VB code Need HELP
    ... I am trying to get a stored procedure to run on info I send it I do not need ... I played around and got the code below to work using a pass-through query, ... Dim strpass As String ...
    (microsoft.public.access.formscoding)
  • Re: slowing/halting stored procedure from ado.net
    ... calling the same stored procedure from SQL> Management ... > Studio goes without any slowdown, ... > There was/is no big job running on the sql server, ... >> Erratic performance of any query is usually due to something changing. ...
    (microsoft.public.dotnet.framework.adonet)