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

TheMSsForum.com: The Microsoft Software Forum

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
Top

Re: The variable name has already been declared error - SQL parameter insert by Marina

Marina
Thu Jun 08 14:39:50 CDT 2006

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" <brett.mack@gmail.com> wrote in message
news:1149791726.629985.107670@h76g2000cwa.googlegroups.com...
> 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
>


Top

Re: The variable name has already been declared error - SQL parameter insert by Bmack500

Bmack500
Thu Jun 08 14:59:25 CDT 2006

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

Or how do I just add the values?

Marina Levit [MVP] wrote:
> 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" <brett.mack@gmail.com> wrote in message
> news:1149791726.629985.107670@h76g2000cwa.googlegroups.com...
> > 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
> >

Top

Re: The variable name has already been declared error - SQL parameter insert by Marina

Marina
Thu Jun 08 15:41:13 CDT 2006

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" <brett.mack@gmail.com> wrote in message
news:1149796765.095411.262960@i39g2000cwa.googlegroups.com...
> Like this?
> @cn = sqlRDR("filecn").ToString
>
> Or how do I just add the values?
>
> Marina Levit [MVP] wrote:
>> 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" <brett.mack@gmail.com> wrote in message
>> news:1149791726.629985.107670@h76g2000cwa.googlegroups.com...
>> > 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
>> >
>


Top

Re: The variable name has already been declared error - SQL parameter insert by Bmack500

Bmack500
Fri Jun 09 12:57:30 CDT 2006

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"




Marina Levit [MVP] wrote:
> 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" <brett.mack@gmail.com> wrote in message
> news:1149796765.095411.262960@i39g2000cwa.googlegroups.com...
> > Like this?
> > @cn = sqlRDR("filecn").ToString
> >
> > Or how do I just add the values?
> >
> > Marina Levit [MVP] wrote:
> >> 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" <brett.mack@gmail.com> wrote in message
> >> news:1149791726.629985.107670@h76g2000cwa.googlegroups.com...
> >> > 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
> >> >
> >

Top

Re: The variable name has already been declared error - SQL parameter insert by Bmack500

Bmack500
Fri Jun 09 12:58:37 CDT 2006

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?

Marina Levit [MVP] wrote:
> 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" <brett.mack@gmail.com> wrote in message
> news:1149796765.095411.262960@i39g2000cwa.googlegroups.com...
> > Like this?
> > @cn = sqlRDR("filecn").ToString
> >
> > Or how do I just add the values?
> >
> > Marina Levit [MVP] wrote:
> >> 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" <brett.mack@gmail.com> wrote in message
> >> news:1149791726.629985.107670@h76g2000cwa.googlegroups.com...
> >> > 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
> >> >
> >

Top