Hello,

I want to update a database. the code i have below gets all the values from a table called roles, and populates a dataset.

I then have a datatable that i want to add some new rows too. I then add this rows to the data table and add the data table to the dataset.

I then want to update the database by calling the dataAdapter update command. The code below as a problem with the update.

I have been told that the insertcommand would be better, but not sure, as i have never used that below. Can anyone give any advice on this.

Many thanks in advance,

Steve


using VB 2005 and SQL Server 2005
Code:
 Dim i As Integer
        Dim dt As New DataTable()
        Dim dr As DataRow
        Dim userID As Integer
        Dim ds As New DataSet()
        Dim da As New SqlDataAdapter()
        Dim cmd As New SqlCommand()
        Dim cnn As New SqlConnection
        Dim cb As New SqlCommandBuilder()

        dt.Columns.Add("UserID")
        dt.Columns.Add("GroupID")
        Try
            cnn.ConnectionString = "server=.; database=serviceMaster; integrated security = true"
            cnn.Open()
            cmd.Connection = cnn
            cb.DataAdapter = da
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "SELECT * FROM Roles"

            da.SelectCommand = cmd
            da.Fill(ds)
            dt = ds.Tables(0)

        Catch ex As Exception

        End Try

        userID = 24
        Dim chklstIndexes As CheckedListBox.CheckedIndexCollection = Me.chkLstSecurityRoles.CheckedIndices

        For i = 0 To Me.chkLstSecurityRoles.CheckedItems.Count - 1
            dr = dt.NewRow()
            dr("userID") = userID
            dr("GroupID") = chklstIndexes.Item(i)
            dt.Rows.Add(dr)
        Next

        'Add to the dataset and use the dataAdapter to update the database
        Try
            ds.Tables.Add(dt)
            cb.GetUpdateCommand()
            da.Update(ds)
        Catch ex As Exception

        End Try