"ted185" <ted***@discussions.microsoft.com> wrote in message
news:64D65EF6-8B0F-4EAE-86BE-32834EE0877F@microsoft.com...
>I am trying to come up with a generic way of generating an insert statement
> widh command builer then additional parameter to the insert command so I
> can
> get the id of new records back.     Once I get the inserrt command from
> sql
> command builder any changes I make do not get reflected when the intert
> command runs.    i have to copy the information to a new sqlcommand and
> use
> it.   If I query the commandtext it has been updated but the updated
> version
> does not run.     Any ideas why the changes to not get updated.   I am
> updating a sql 2005 server with this.    The following code works but I am
> looking for a way to make the changes without needing a second sqlcommand
>
> private sub updrec()
>
> Dim cn As New SqlConnection("Data Source=localhost;Integrated
> Security=SSPI;Initial Catalog=northwind")
>
>        Dim sql As String = "select CategoryId,CategoryName,Description
> from
> categories where CategoryID='105'"
>        Dim sqlData As New SqlDataAdapter(sql, cn)
>        Dim prm As SqlParameter
>
>        Dim sqlBld As New SqlCommandBuilder(sqlData)
>        Dim OsqlCmd As SqlCommand = sqlBld.GetInsertCommand
>
>        cn.Open()
>
>        Dim nSqlCmd As New SqlCommand
>        OsqlCmd.CommandText += " set @CategoryID =  SCOPE_IDENTITY()"
>
>        prm = New SqlParameter("@CategoryID", SqlDbType.Int, 0,
> "CategoryID")
>        prm.Direction = ParameterDirection.Output
>        OsqlCmd.Parameters.Add(prm)
>
>        nSqlCmd = OsqlCmd.Clone
>
>        Dim sqlCmd As New SqlCommand(OsqlCmd.CommandText, cn)
>        CopyParams(OsqlCmd, sqlcmd) '
>        sqlData.InsertCommand = sqlCmd
>        sqlData.UpdateCommand = sqlBld.GetUpdateCommand
>        sqlData.DeleteCommand = sqlBld.GetDeleteCommand
>
>
>        TextBox2.Text = sqlData.InsertCommand.CommandText
>
>        Dim myDs As New DataSet
>        sqlData.Fill(myDs, "Categories")
>
>        myDs.Tables(0).Rows(0).Item("Description") = "freddy"
>
>        Dim mYrow As DataRow
>        mYrow = myDs.Tables(0).NewRow
>        mYrow.Item("categoryname") = "cat1"
>        mYrow.Item("description") = "Desc1"
>        myDs.Tables(0).Rows.Add(mYrow)
>
>        mYrow = myDs.Tables(0).NewRow
>        mYrow.Item("categoryname") = "cat1"
>        mYrow.Item("description") = "Desc1"
>        myDs.Tables(0).Rows.Add(mYrow)
>
>        sqlData.Update(myDs, "Categories")
>        'myDs.GetChanges()
>
>
> end sub
> Private Sub CopyParams(ByVal oldCmd As SqlCommand, ByRef NewSqlCmd As
> SqlCommand)
>
>        Dim oParam As SqlParameter
>        Dim NPrm As SqlParameter
>        For Each oParam In oldCmd.Parameters
>            NPrm = New SqlParameter(oParam.ParameterName, oParam.SqlDbType,
> oParam.Size, oParam.SourceColumn)
>            NPrm.Direction = oParam.Direction
>            NewSqlCmd.Parameters.Add(NPrm)
>
>        Next
>
>    End Sub
>
>
> Thank You