Re: Must declare the scalar variable "@" - paramaterized sqlclient insert - HELP!!

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Access uses ? as an anonymous placeholder.

Sql Server uses named parameters i.e. IMO you should use @objclass etc in
your SQL statement instead of just @...

--
Patrice

"Bmack500" <brett.mack@xxxxxxxxx> a écrit dans le message de
news:1141398077.767482.53400@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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,physicalDeliv
eryOfficeName,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("physicalDeliveryOff
iceName")(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



.

Relevant Pages

  • Re: Net.WebRequest/WebResponse ?s
    ... if the exception is a "WebExceptionStatus.ProtocolError" the exception variable will contain a pointer to an HTTPWebResponse object that can be used to get at the values I needed! ... Dim ErrResult As String = New ... Public Function GetResponsePWP(ByVal ReqURL As String, ByVal ReqTimeout As Integer, ByVal ReqType As String, ByVal PostData As String, ByVal UserName As String, ByVal Password As String) As String ... Dim nmsStatusCode As String = "" ...
    (microsoft.public.dotnet.languages.vb)
  • DES ECB Encryption cannot decrypt
    ... If I have the value to encrypt = ... Dim globalexception As CryptographicException ... Public Function encrypt(ByRef textPIN As String, ByRef textKey As String, ... Catch exception As CryptographicUnexpectedOperationException ...
    (microsoft.public.dotnet.security)
  • Re: PathTooLongException auswerten
    ... daher auch der erwartete Fehler. ... welchem es zu der Exception kam, hast Du dann ja in Deiner ... Dim NL as String = ControlChars.CrLf ...
    (microsoft.public.de.german.entwickler.dotnet.vb)
  • Re: Conversion from string "0" to type Integer is not valid
    ... exception is thrown regardless of the context of the conversion, ... Explicitly converting the string as you ... indicated below will still throw the exception on my machine. ... Dim i as Integer ...
    (microsoft.public.dotnet.framework)
  • Re: DropDownList has SelectedValue which in invalid
    ... resulting in the invalid SelectedValue error. ... Dim ddl1 As DropDownList = CType, ... From your description you want to avoid getting the exception: ... Microsoft Online Support ...
    (microsoft.public.dotnet.framework.aspnet)