The variable name has already been declared error - SQL parameter insert

[Bmack500]

Hello, I have the following code (vb.net) below. I'm just taking an
array and pushing it into an sql (MS) database.
However, it keeps giving me the following error:
"InsertFileRecords: The variable name '@cn' has already been declared.
Variable names must be unique within a query batch or stored
procedure."

If I get rid of CN, then the next one down will give me the error. I
don't get it; I don't have the variable declared globally anywhere, or
in any calling functions. So what gives? What's wrong with my code?



Sub InsertFileRecords(ByVal contacts As String(,), ByVal strOU As
String)
Dim Sql, strDN, strError, strNick, strName, sqlGet As String
Dim rowsaffected As Integer
Dim iIndex1 As Integer
Dim sqlCMD As New SqlClient.SqlCommand
Dim contactInfo As contactStruct
sqlCMD.CommandType = CommandType.Text
sqlCMD.Connection = sqlConnection1
prgsInsert.Value = 0
prgsInsert.Maximum = UBound(contacts)
Sql = "INSERT INTO Tradoc.dbo.Incoming (cn, filecn,
mailNickName, filemailNickName, " _
& " targetAddress, fileproxy, fileDisplayName, givenName,
initials, sn, title, telephonenumber," _
& " company, department, physicalDeliveryOfficeName, l, st,
postalCode, co, ou, dn, adspath, " _
& " description)" _
& " VALUES
(@cn,@filecn,@mailNickName,@fileMailNickName,@targetAddress,@fileproxy,
" _
& " @fileDisplayName,
@givenname,@initials,@sn,@title,@telephoneNumber,@company,@department,
" _
& "
@physicalDeliveryOfficeName,@l,@st,@postalCode,@co,@ou,@dn,@adspath,@description)"
opensql(1)
sqlCMD.CommandText = Sql
For iIndex1 = 0 To UBound(contacts)
contactInfo = csv2ContactStruct(iIndex1, contacts, strOU)

Try
'sqlCMD.Parameters.Add(New SqlParameter("@objectClass",
SqlDbType.VarChar, 50)).Value = "contact"
sqlCMD.Parameters.Add(New SqlParameter("@cn",
SqlDbType.VarChar, 100)).Value = contactInfo.CN
sqlCMD.Parameters.Add(New SqlParameter("@filecn",
SqlDbType.VarChar, 100)).Value = contactInfo.fileCN
sqlCMD.Parameters.Add(New SqlParameter("@mailNickName",
SqlDbType.VarChar, 100)).Value = contactInfo.mailNickName
sqlCMD.Parameters.Add(New
SqlParameter("@fileMailNickName", SqlDbType.VarChar, 100)).Value =
contactInfo.fileMailNickName
sqlCMD.Parameters.Add(New
SqlParameter("@targetAddress", SqlDbType.VarChar, 400)).Value =
contactInfo.targetaddress
sqlCMD.Parameters.Add(New SqlParameter("@fileProxy",
SqlDbType.VarChar, 500)).Value = contactInfo.fileProxy
sqlCMD.Parameters.Add(New
SqlParameter("@filedisplayname", SqlDbType.VarChar, 400)).Value =
contactInfo.fileDisplayName
sqlCMD.Parameters.Add(New SqlParameter("@givenName",
SqlDbType.VarChar, 100)).Value = contactInfo.givenName
sqlCMD.Parameters.Add(New SqlParameter("@initials",
SqlDbType.VarChar, 50)).Value = contactInfo.initials
sqlCMD.Parameters.Add(New SqlParameter("@sn",
SqlDbType.VarChar, 100)).Value = contactInfo.sn
sqlCMD.Parameters.Add(New SqlParameter("@title",
SqlDbType.VarChar, 400)).Value = contactInfo.title
sqlCMD.Parameters.Add(New
SqlParameter("@telephoneNumber", SqlDbType.VarChar, 50)).Value =
contactInfo.telephoneNumber
sqlCMD.Parameters.Add(New SqlParameter("@company",
SqlDbType.VarChar, 400)).Value = contactInfo.company
sqlCMD.Parameters.Add(New SqlParameter("@department",
SqlDbType.VarChar, 400)).Value = contactInfo.department
sqlCMD.Parameters.Add(New
SqlParameter("@physicalDeliveryOfficeName", SqlDbType.VarChar,
400)).Value = contactInfo.physicalDeliveryOfficeName
sqlCMD.Parameters.Add(New SqlParameter("@l",
SqlDbType.VarChar, 50)).Value = contactInfo.l
sqlCMD.Parameters.Add(New SqlParameter("@st",
SqlDbType.VarChar, 50)).Value = contactInfo.st
sqlCMD.Parameters.Add(New SqlParameter("@postalcode",
SqlDbType.VarChar, 50)).Value = contactInfo.postalCode
sqlCMD.Parameters.Add(New SqlParameter("@co",
SqlDbType.VarChar, 50)).Value = contactInfo.co
sqlCMD.Parameters.Add(New SqlParameter("@ou",
SqlDbType.VarChar, 400)).Value = contactInfo.OU
sqlCMD.Parameters.Add(New SqlParameter("@dn",
SqlDbType.VarChar, 400)).Value = contactInfo.DN
sqlCMD.Parameters.Add(New SqlParameter("@adspath",
SqlDbType.VarChar, 400)).Value = contactInfo.adsPath
sqlCMD.Parameters.Add(New SqlParameter("@description",
SqlDbType.VarChar, 200)).Value = contactInfo.description

rowsaffected = sqlCMD.ExecuteNonQuery()

Catch e As Exception
logit("InsertFileRecords: " & e.Message)
Debugger.Break()
Finally
Sql = ""
prgsUpdate(UBound(contacts))
End Try
'Else
prgsUpdate(UBound(contacts))
'End If
Next 'iIndex1 = 0 To UBound(contacts)
closesql(1)
sqlCMD.Dispose()
'ds = Nothing
End Sub

 

[Marina Levit [MVP]]

You are adding the parameters once for every contact. So the first time,
that works.

The second time, the parameters are all already there - so adding them again
causes the error.

Add the parameters only once before you start updating any contacts. Then,
in every loop, just set the parameter value.

 

[Bmack500]

Like this?
@cn = sqlRDR("filecn").ToString

Or how do I just add the values?

 

[Marina Levit [MVP]]

You would set the Value property which you are already doing when you are
adding it. Except the first time you would just add them. And then you
would go through the parameter collection and just set the value of each
one.

And no, "@cn" is not something you can type in VB, and have it somehow refer
to the right object in the parameter collection you happened to have
declared.

 

[Bmack500]

So, Declare it like this:
sqlCMD.Parameters.Add(New SqlParameter("@description",
SqlDbType.VarChar, 200)).Value = contactInfo.description

opensql(1)
sqlCMD.CommandText = Sql
For iIndex1 = 0 To UBound(contacts)
contactInfo = csv2ContactStruct(iIndex1, contacts, strOU)

Try
sqlCMD.Parameters.Item("@objClass").Value = "contact"

 

[Bmack500]

So, Declare it like this:
sqlCMD.Parameters.Add(New SqlParameter("@description",
SqlDbType.VarChar, 200))

And then set the value like this?

sqlCMD.Parameters.Item("@objClass").Value = "myValue"

Is that correct?