Can anyone explain why I get this e

[TerraSamba]

Can anyone explain why I get this error while copying sqlcommand.parameters?

Some background:

I'm working on a program with a lot of tables in several sql server 2000 databases. Because the creation of the data adapters are all more or less the same, since I work with stored procedures, I automate the creation of these data adapters.

Each data adapter contains a select-, insert-, delete- and updatecommand. For the creation of these commands (sqlcommand objects) I use separate routines.

For the creation of the parameters of the sqlcommands I use a separate routine, this routine uses data explaining how the table is constructed (F.I. tablename, wich fields, dbtypes, sizes, defaultvalue etc, ... this part works fine).

With the data of this file the parameters are constructed. So far so good. I add the parameters to a sqlparametercollection of a fresh sql command and return this sqlparametercollection as result. (see *1)

The sql parameter collection is received by a function that returns the insert command to a function that returns the data adapter as a result.

Since:
*1) You cannot instantiate a sqlparametercollection class. Thus I create a fresh command, add the parameters to the commands collection and return this collection.
*2) You cannot state: cmd1.sqlparamcol=cmd2.sqlparamcol, because the cmd1.sqlparamcol is read-only. No problem you think, you just add the parameters van col2 to the collection of col1.

The problem:
When adding the first parameter of col2 to the collection of col1 I get this error:

-why can't I 'copy' the parameters?
-work around?

(I'm going to try an array of sqlparamater obects now, but very curious about the why part)

------------------------------------------------
problem code:
------------------------------------------------

Friend Function Ret_InsCmd(ByVal pDataType As DataTypes) As SqlClient.SqlCommand

            'deze routine geeft een sql command terug om een record in een tabel toe te voegen
            'alle velden in de tabel moeten van waarden worden voorzien. Deze routine haalt de
            'velden uit de tabel op uit de dataset met alle tabellen. Alle velden worden als
            'input parameter gebruikt op 2 uitzonderingen na: id en lastupdate
            'id: komt in iedere tabel voor en is een output parameter, de waarde doet er dus niet toe
            'lastupdate: komt in iedere tabel voor en wordt door de SP geset, zijn waarde maakt dus niet uit

            Dim Cmd As New SqlClient.SqlCommand()
            Dim Singlename As String
            Dim paramcol As SqlClient.SqlParameterCollection
            Dim param As SqlClient.SqlParameter
            Const cCmdTxt As String = "_SP_INS_"

            Try
                Singlename = mMetaData.Ret_MetaValue(modDT_Main.MetaTypes.singlename, pDataType)
                paramcol = ReturnTablefieldAsSQLParams(pDataType)

                With Cmd
                    .CommandText = cCmdTxt & Singlename
                    .CommandType = CommandType.StoredProcedure

'<--------- problem
                    For Each param In paramcol
                        .Parameters.Add(param)
                    Next
'----------->
                End With

                Ret_InsCmd = Cmd

            Catch e As Exception
                'tonen we error messages?
                If CType(ConfigurationSettings.AppSettings.Item(&quot;ShowErrMsg&quot;), System.Boolean) Then
                    MsgBox(e.Message)
                End If
            Finally
                Cmd = Nothing
                Singlename = Nothing
                paramcol = Nothing
                param = Nothing
            End Try

        End Function    '/ReturnInsCmd

Thanx 4 replies

The gap between theory and practice is not as wide in theory as it is in practice.

 

[TerraSamba]

The array - way does not work either

This works:

For Each param In paramcol
.Parameters.Add(param.ParameterName, _
param.DbType, _
param.Size, param.Sourcecolumn)

Next

But now I'm not specifying parameter direction ... But, it works.

But has anyone got an explanation of why ... ? The gap between theory and practice is not as wide in theory as it is in practice.