Where and how to declare SQLConnection value in a module

I have a module which will hold many diffrent functions to populate diffrent dropdown boxes. My problem is how and where do i define
stuff like this

SqlConnection1.ConnectionString = "workstation id=""APONNATHXP-HP"";packet size=4096;user id=sa;data source=""SQL-HUMDM" & _
            """;persist security info=False;initial catalog=icc"

            sqlGetDropDownValues.CommandText = "[tblLookup_SEL]"
            sqlGetDropDownValues.CommandType = System.Data.CommandType.StoredProcedure
            sqlGetDropDownValues.Connection = SqlConnection1
            sqlGetDropDownValues.Parameters.Add(New System.Data.SqlClient.SqlParameter("@RETURN_VALUE", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, False, CType(0, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
            sqlGetDropDownValues.Parameters.Add(New System.Data.SqlClient.SqlParameter("@lookupid", System.Data.SqlDbType.Char, 20, "lookupid"))

if i put it within the function or sub it executes the code over and over and add's the same SqlParameter to the system. That causes me to have a invalid amount of arguments and fail the sub / function. is there a part in a code module where i can declare this just like in a form
and if so how...
Who is Participating?
soumya_paridaConnect With a Mentor Commented:
You can do this, in each function declare its own array pf parameters like
Dim params(2) As System.Data.SqlClient.SqlParameter
params(0) = new System.Data.SqlClient.SqlParameter
'Set the values for al the parameters.

Then assign it to the parameters of  the command.
sqlGetDropDownValues.Parameters  = params

So everytime you  call a function the parameters collection would be reassigned and would lose the previous values.
Your question is not so clear to me, but you could remove the parameters first

use :


for all your parameters before you add the parameters...

Good luck,

AlexPonnathAuthor Commented:
i am looking more for an reusable code so that it does not create a global parm, like it does in a module.
i want the scope to be for this single execution of the code..
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.