Using multiple parameters for SqlConnection

[sck10]

Hello,

I have a stored procedure that requires 3 parameters. How do you add
multiple parameters for a SqlConnection?

Thanks, Steven


Public strConn = "Data Source=MyServer;Initial Catalog=MyDatabase;User
Id=MyUser;Password=MyPwd;"
Dim cnnSearch As SqlConnection = New SqlConnection(strConn)
Dim cmdRegion As SqlCommand = New SqlCommand("SalesByCategory", cnnSearch)
cmdRegion.CommandType = CommandType.StoredProcedure

Dim myParm As SqlParameter = cmdRegion.Parameters.Add("@CategoryName",
SqlDbType.VarChar, 15)
myParm.Value = "NorthAmerica"

cnnSearch.Open()

Dim myReader As SqlDataReader = cmdRegion.ExecuteReader()

 

[Marina]

It looks like you already know how to add parameters, so what's the problem?
Just do what you did to add 1 parameter, but do it 3 times.

 

[Kevin Yu [MSFT]]

Thanks for Marina's quick response!

Hi Steve,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to know how to add 3
parameters to a SqlCommand object. If there is any misunderstanding, please
feel free to let me know.

Just like Marina mentioned, seems that you know how to add 1 parameter. You
can do it 3 times to add your 3 parameters, since SqlCommand.Parameters is
a collection, which can hold all the parameters required for the stored
procedure. The return value for the Add method is the reference to the
newly added parameter.

For more information, please check the following link:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfSystemDataSqlClientSqlCommandClassParametersTopic.asp

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Kevin Yu [MSFT]]

Hi Steve,

I'd like to know if this issue has been resolved yet. Is there anything
that I can help. I'm still monitoring on it. If you have any questions,
please feel free to post them in the community.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[rockgenius]

another possible way is to create an array of parameters, and then add the array, like this:

dim Insert as sqlcommand = new sqlcommand("INSERT INTO tblTable field1, field2, field3 VALUES(@param1, @param2, @param3)", yourSqlConnection)

dim param1 as sqlparameter = new sqlparameter("@param1", text1.text)
dim param2 as sqlparameter = new sqlparameter("@param2", text2.text)
dim param3 as sqlparameter = new sqlparameter("@param3", text3.text)

'create array
dim parameterArray() as sqlparameter = () {param1, param2, param3)
Insert.parameters.addrange(parameterArray)

and taDa! you have added multiple parameters.