Adding Command Parameters

[riversr]

I'm trying to learn how to use command parameters to do my SQL commands and seemed to have hit an impasse.

I created the following code:

cmd.CommandText = "Insert into PINS (PIN, ControlNumber, BatchID, Consumed) values (@PIN, @Control, @ID, @Consumed)"

cmd.Parameters.Add("@PIN", SqlDbType.VarChar).Value = pin.number
cmd.Parameters.Add("@Control", SqlDbType.VarChar).Value = pin.control
cmd.Parameters.Add("@ID", SqlDbType.Int).Value = BatchID
cmd.Parameters.Add("@Consumed", SqlDbType.Bit).Value = False

cmd.ExecuteNonQuery()

When the ExecuteNonQuery command runs, I get the following error:

The variable name '@ID' has already been declared. Variable names must be unique within a query batch or stored procedure.
Must declare the scalar variable "@DateStamp".

My problem is that this variable name is not used anywhere else in my code, so it's not duplicate use of the name. What am I doing wrong?

 

[Anti-Rich]

i have a feeling its because you have specified the names of the fields in the first set of brackets. take those out, and try running it again. im not entirely sure though, so i could be wrong.


regards
adam

 

[riversr]

Figured it out!

It was because I had the cmd.parameter.add statements inside a For loop. I am moving the contents of an array to a DB, so I put it in a loop to step through the array. The problem is that you can't redefine the parameter again until you clear them, so all I had to do is add this command to my loop:

cmd.parameter.clear

There may be a better way, but this works.

 

[Anti-Rich]

could you post the entire sub? this might make it easier for us to get a clearer picture of things

 

[cjard]

add this command to my loop:

Um, there's soemthing missing in your understanding of parameterized SQLS - you set them up ONCE and then every time you want to re-run them, you just change the values..

There shouldnt be any loop.. There should be a block of code, somewhere that performs a one time setup of the command. You have one query per purpose..

Typically, you put the common used SQLs in a parameterized mode at design time. The few that you should create at runtime are the seldom runs and dynamically built...

Dont re-use one comamnd for something else:

cmd.CommandText = "SELECT a FROM a WHERE z = @z"
cmd.Parameters.Add("z" ...).Value = "z"
cmd.ExecuteQuery()

'run it again
cmd.Parameters("z").Value = "zzzzzzzzzzz"
cmd.ExecuteQuery()


DONT USE IT FOR SOEMTHING ELSE:
cmd.CommandText = "SELECT b FROM b WHERE y = @y"
cmd.Parameters.Add("y" ...).Value = "y" 'the parameters collection still contains z!


-

As an analogy, you dont have just one Form, and programmatically lay out all the controls on it at runtime, then if the user wants to open another screen, Clear() the controls off the form and add another bunch.. You have 2 separate forms, created at design time..


Youre using dot net 2. You should really be doing DB access the way Microsoft intended. Read the DW2 link in my signature

 

[riversr]

The Loop!

The reason for the loop was that I was moving data from an array into the database. I didn't realize that I was trying to re-define the parameter name by doing that. I fixed it by doing what you said.

In researching this problem I learned that I can instantiate a SQLParameters collection and fill it with parameters and their values. My question: Assuming I have done this does anyone know how to then associate that collection to a SQLCommand. It's normally done by adding parameters like this:

cmd.parameters.add

But if I instantiate the collection independently of the cmd, then how do I hook it to the command?

Thanks,

 

[cjard]

mySqlCommand.Parameters = mySeparatelyMadeParametersCOllection

?

 

[cjard]

actually, i tell a lie = the params property is reead only..

youd do this:

Dim ary as New DbParameterCollection()
mySeparatelyMadeParamCollection.CopyTo(ary, 0)
dbCommand.Parameters.AddRange(ary)

bold = use whatever type of collection suitable for your app



i.e. its so much messing around that you shouldnt bother; just get a reference to the existing colelction and build it..


Maybe you missed an important point I made:
Initialise it once
Set the values
Run the query
Set the values
Run the query
Set the values
Run the query
Set the values
Run the query
Set the values
Run the query
Set the values
Run the query

There should be no need at all for this separate parameters collection youre talking about. You should set the parameters at the time you make the command

Later when you come to repeatedly reuse it, set the values, dont add the parameters.
You DONT need to add a parameter just to set its value

the Add() command returns the parameter it just created. THis is why it works to say Add(..).Value

There is nothing stoipping you saying:
cmd.Parameters("paramName").Value = "value"
cmd.ExecuteQuery()
cmd.Parameters("paramName").Value = "another value"
cmd.ExecuteQuery()
cmd.Parameters("paramName").Value = "another another value"
cmd.ExecuteQuery()
cmd.Parameters("paramName").Value = "another another another value"
cmd.ExecuteQuery()

See what I mean?

 

[riversr]

Got it working

Thanks for all the feedback. I certainly have a better understanding of how it works now.