Must declare the scalar variable "@" - paramaterized sqlclient insert

3 Mar 2006 2:34 PM

Bmack500

Below is my code, the error occurs when it is executed. I don't quite
understand it; What am I doing wrong?

    Sub insertADRecords(ByVal oEntry As SearchResult)
        Dim iIndex1 As Integer
        Dim strSql, strDN, strProxyAdd, strProxy(), strMyString As
String, rowsAffected As Integer
        strSql = "INSERT INTO Tradoc.dbo.CurrAD (objclass, cn,
mailnickname, targetAddress, proxyAddresses, displayname,
givenName,initials,sn,title,telephoneNumber,company,department,physicalDeliveryOfficeName,l,st,postalcode,
co,adspath) VALUES (@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@))"
        Dim sqlCMD As SqlClient.SqlCommand = New SqlCommand(strSql,
sqlConnection)
        'sqlCMD.Connection() = sqlConnection
        sqlCMD.CommandType = CommandType.Text
        writeLog(strSql)

        'Dim oResult As SearchResult
        Dim item, strtemp As String

        sqlCMD.Parameters.Add(New SqlParameter("@objclass",
SqlDbType.VarChar, 50))
        sqlCMD.Parameters.Add(New SqlParameter("@cn",
SqlDbType.VarChar, 100))
        sqlCMD.Parameters.Add(New SqlParameter("@mailnickname",
SqlDbType.VarChar, 100))
        sqlCMD.Parameters.Add(New SqlParameter("@targetAddress",
SqlDbType.VarChar, 400))
        sqlCMD.Parameters.Add(New SqlParameter("@proxyAddresses",
SqlDbType.VarChar, 400))
        sqlCMD.Parameters.Add(New SqlParameter("@displayName",
SqlDbType.VarChar, 400))
        sqlCMD.Parameters.Add(New SqlParameter("@givenName",
SqlDbType.VarChar, 100))
        sqlCMD.Parameters.Add(New SqlParameter("@initials",
SqlDbType.VarChar, 50))
        sqlCMD.Parameters.Add(New SqlParameter("@sn",
SqlDbType.VarChar, 100))
        sqlCMD.Parameters.Add(New SqlParameter("@title",
SqlDbType.VarChar, 400))
        sqlCMD.Parameters.Add(New SqlParameter("@telephoneNumber",
SqlDbType.VarChar, 400))
        sqlCMD.Parameters.Add(New SqlParameter("@company",
SqlDbType.VarChar, 400))
        sqlCMD.Parameters.Add(New SqlParameter("@department",
SqlDbType.VarChar, 400))
        sqlCMD.Parameters.Add(New
SqlParameter("@physicalDeliveryOfficeName", SqlDbType.VarChar, 400))
        sqlCMD.Parameters.Add(New SqlParameter("@l", SqlDbType.VarChar,
50))
        sqlCMD.Parameters.Add(New SqlParameter("@st",
SqlDbType.VarChar, 50))
        sqlCMD.Parameters.Add(New SqlParameter("@postalcode",
SqlDbType.VarChar, 50))
        sqlCMD.Parameters.Add(New SqlParameter("@co",
SqlDbType.VarChar, 50))
        sqlCMD.Parameters.Add(New SqlParameter("@adspath",
SqlDbType.VarChar, 400))
        '***********************************************************
        sqlCMD.Parameters("@objclass").Value = "contact"
        Try
            sqlCMD.Parameters("@cn").Value =
strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("cn")(0))))
        Catch ex As Exception
            sqlCMD.Parameters("@cn").Value = ""
        End Try
        Try
            sqlCMD.Parameters("@mailnickname").Value =
strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("mailnickname")(0))))
        Catch ex As Exception
            sqlCMD.Parameters("@targetAddress").Value = ""
        End Try
        Try
            sqlCMD.Parameters("@targetAddress").Value =
strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("targetAddress")(0))))
        Catch ex As Exception
            sqlCMD.Parameters("@targetAddress").Value = ""
        End Try
        Try
            sqlCMD.Parameters("@proxyAddresses").Value() =
Convert.ToString(oEntry.Properties("proxyAddresses")(0))
        Catch ex As Exception
            sqlCMD.Parameters("@proxyAddresses").Value() = ""
        End Try
        Try
            sqlCMD.Parameters("@displayName").Value =
strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("displayName")(0))))
        Catch ex As Exception
            sqlCMD.Parameters("@displayName").Value() = ""
        End Try
        Try
            sqlCMD.Parameters("@givenName").Value =
strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("givenName")(0))))
        Catch ex As Exception
            sqlCMD.Parameters("@givenName").Value = ""
        End Try
        Try
            sqlCMD.Parameters("@initials").Value =
strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("initials")(0))))
        Catch ex As Exception
            sqlCMD.Parameters("@initials").Value = ""
        End Try
        Try
            sqlCMD.Parameters("@sn").Value =
strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("sn")(0))))
        Catch ex As Exception
            sqlCMD.Parameters("@sn").Value = ""
        End Try
        Try
            sqlCMD.Parameters("@title").Value =
strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("title")(0))))
        Catch ex As Exception
            sqlCMD.Parameters("@title").Value = ""
        End Try
        Try
            sqlCMD.Parameters("@telephoneNumber").Value =
strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("telephoneNumber")(0))))
        Catch ex As Exception
            sqlCMD.Parameters("@telephoneNumber").Value = ""
        End Try
        Try
            sqlCMD.Parameters("@company").Value =
strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("company")(0))))
        Catch ex As Exception
            sqlCMD.Parameters("@company").Value = ""
        End Try
        Try
            sqlCMD.Parameters("@department").Value =
strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("department")(0))))
        Catch ex As Exception
            sqlCMD.Parameters("@department").Value = ""
        End Try
        Try
            sqlCMD.Parameters("@physicalDeliveryOfficeName").Value =
strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("physicalDeliveryOfficeName")(0))))
        Catch ex As Exception
            sqlCMD.Parameters("@physicalDeliveryOfficeName").Value = ""
        End Try
        Try
            sqlCMD.Parameters("@l").Value =
strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("l")(0))))
        Catch ex As Exception
            sqlCMD.Parameters("@l").Value = ""
        End Try
        Try
            sqlCMD.Parameters("@st").Value =
strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("st")(0))))
        Catch ex As Exception
            sqlCMD.Parameters("@st").Value = ""
        End Try
        Try
            sqlCMD.Parameters("@postalcode").Value =
strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("postalcode")(0))))
        Catch ex As Exception
            sqlCMD.Parameters("@postalcode").Value = ""
        End Try
        Try
            sqlCMD.Parameters("@co").Value =
strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("co")(0))))
        Catch ex As Exception
            sqlCMD.Parameters("@co").Value = ""
        End Try
        sqlCMD.Parameters("@adspath").Value =
strQuoteReplace(Trim(Convert.ToString(oEntry.Properties("adspath")(0))))
        If sqlConnection.State = ConnectionState.Closed Then
            sqlConnection.Open()
        End If
        Try
'Here's where the error occurs, of course...
            rowsAffected = sqlCMD.ExecuteNonQuery()
        Catch ex As Exception
            writeLog(ex.Message)
        End Try
        sqlCMD.Dispose()

    End Sub

3 Mar 2006 3:21 PM

Sericinus hunter

Bmack500 wrote:

> Below is my code, the error occurs when it is executed. I don't quite
> understand it; What am I doing wrong?
>
>     Sub insertADRecords(ByVal oEntry As SearchResult)
>         Dim iIndex1 As Integer
>         Dim strSql, strDN, strProxyAdd, strProxy(), strMyString As
> String, rowsAffected As Integer
>         strSql = "INSERT INTO Tradoc.dbo.CurrAD (objclass, cn,
> mailnickname, targetAddress, proxyAddresses, displayname,
> givenName,initials,sn,title,telephoneNumber,company,department,physicalDeliveryOfficeName,l,st,postalcode,
> co,adspath) VALUES (@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@))"

Here, try to list all params with their names as they are farther in your code:

VALUES(@objclass, @cn, @mailnickname...)

3 Mar 2006 3:32 PM

Bmack500

Thanks! Almost there - I just need to track down a syntax error..

INSERT INTO Tradoc.dbo.CurrAD (objclass, cn, mailnickname,
targetAddress, proxyAddresses, displayname,
givenName,initials,sn,title,telephoneNumber,company,department,physicalDeliveryOfficeName,l,st,postalcode,

co,adspath) VALUES
(@objclass,@cn,@mailnickname,@targetaddress,@proxyaddresses,@displayname,@givenname,@initials,
@sn,@title,@telephonenumber,@company,@department,@physicaldeliveryofficename,@l,@st,@postalcode,@co,@adspath))

The error:
Incorrect syntax near ')'.

3 Mar 2006 3:39 PM

Bmack500

Cool, just an extra ")". Thank you Thank you Thank you!!!

3 Mar 2006 3:40 PM

Sericinus hunter

Bmack500 wrote:

> Thanks! Almost there - I just need to track down a syntax error..
>
> INSERT INTO Tradoc.dbo.CurrAD (objclass, cn, mailnickname,
> targetAddress, proxyAddresses, displayname,
> givenName,initials,sn,title,telephoneNumber,company,department,physicalDeliveryOfficeName,l,st,postalcode,
>
> co,adspath) VALUES
> (@objclass,@cn,@mailnickname,@targetaddress,@proxyaddresses,@displayname,@givenname,@initials,
> @sn,@title,@telephonenumber,@company,@department,@physicaldeliveryofficename,@l,@st,@postalcode,@co,@adspath))
>
> The error:
> Incorrect syntax near ')'.

You have an extra ')' at the end which you don't need. The syntax is:
INSERT table_name (col_list) values (value_list)