need help in vb.net and rowstate

[aska07]

Hi All, I am developing a window form where in my form, i have 3 buttons, insert, delete and update, a SAVE menutoolstripbutton and a datagrid to display the data get from database.

now I want is for example whenever a user want to insert a new data (click the insert button), the new data will be display at datagrid but actually at the background the new data haven't inserted into the database. The data only will be inserted into database when the user click the SAVE menutoolstripbutton.

I want my application to be like this but I have no idea how I gonna do this...

Does anyone know and help me with this??

Thanks very much!!

[lolafuertes]

On your application, create a dataset showing the underlying database.
Fill it in using the data adapters.

Show the data binding the datagrid to the tables in your dataset

When inserting, updating or deleting, do it against the tables in the dataset, then refresh the datagrid.

Once the users clicks the Save button, use the data adapters to update the underlying database.

You can find a detailed HowTo in http://support.microsoft.com/kb/301248/en-us

[aska07]

thank you very much for your help, lolafuertes...but I still have some problem with the 3 buttons and the save menutoolstripbutton as well.

my dataadapter is not working, I think. Whenever I click on the save menutoolstripbutton, all the data cannot be save. Here is my save menutoolstripbutton coding.

Public Function PerformSave() As Object Implements IToolbars.PerformSave
        Try
            Dim dr As DataRow
            For Each dr In datasetEmp.Tables(0).Rows
            
                Select Case dr.RowState
                    Case DataRowState.Added

                        datasetEmp = datasetEmp.GetChanges(DataRowState.Added)
                        Dim objCommandBuilder As New SqlCommandBuilder(da)

                        da.Update(datasetEmp)
                        datasetEmp.AcceptChanges()
                        LoadData()
                     
                    Case DataRowState.Deleted
                        Dim objCommandBuilder As New SqlCommandBuilder(da)
                        datasetEmp = datasetEmp.GetChanges(DataRowState.Deleted)
                        da.Update(datasetEmp)
                        dsEmployee.AcceptChanges()
                      
                        LoadData()

                    Case DataRowState.Modified
                        Dim objCommandBuilder As New SqlCommandBuilder(da)
                        
                        da.Update(datasetEmp)
                        datasetEmp = datasetEmp.GetChanges(DataRowState.Modified)
                        datasetEmp.AcceptChanges()
                      
                    Case DataRowState.Unchanged
                        LoadData()
                    Case DataRowState.Detached
                        LoadData()
                End Select
            Next
            datasetEmp.AcceptChanges()

        Catch ex As Exception

        End Try
    End Function

and then my delete button's coding:

Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        Try
            Dim currentIndex As Integer = 0
            dr = datasetEmp.Tables(0).Rows(currentIndex)

            Dim deleteCommand As SqlCommand = New SqlCommand()
            deleteCommand.Connection = New SqlConnection(strConn)

            deleteCommand.Connection.Open()

            deleteCommand.CommandType = CommandType.StoredProcedure
            deleteCommand.CommandText = "dbo.Delete_Emp"

            Dim param As SqlParameter

            param = deleteCommand.Parameters.Add("@EmpNo", SqlDbType.Int)
            param.Direction = ParameterDirection.Input
   
            param.Value = Me.txtEmpNo.Text

            param.SourceVersion = DataRowVersion.Original 'use original for delete

            da.DeleteCommand = deleteCommand
            dr.Delete()

            Call MsgBox("Delete successfully")
            
            PerformNew()

        Catch ex As Exception

        End Try
    End Sub

my data adapter:

da = New SqlDataAdapter("dbo.Select_Employee_By_Code", strConn)

            da.SelectCommand.CommandType = CommandType.StoredProcedure

            da.SelectCommand.Parameters.Add(New SqlParameter("@Code", SqlDbType.VarChar))

            'Assign the search value to the parameter.
            da.SelectCommand.Parameters("@CompanyCode").Value = Trim(txtCode.Text)

            da.Fill(datasetEmp)
            da.Update(datasetEmp)

can you help me with this??

[lolafuertes]

IMO you need to do significant changes to your code:

First, your data adapter needs to define also the Insert, the Update and the Delete commands, or nothing will happen to the data base when you issue a da.update. The Delete command sould only be defined once, not at every button delete click event.

Second, executing a da.update just after a da.fill has no effect. Did you cleared the previous content of the table before filling it?

Third, on the button delete event is enough to execute the dr.delete for deleting the record in the DS. Also to modify the dr you can dr.beginedit, do the changes in the dr fields an then do dr.endedit. All the changes are performed into the datasetEmp in memory.

Finally, on the button save is enough to issue the da.update for the datasetEmp, table datasetEmp.Tables(0).Name to insert, update or delete all the modified, inserted or deleted records at once. Please refer to the example in the referenced article from Microsoft.

Hope this helps.

Hope this helps

[aska07]

Once again, thank you so much, lolafuertes. You are so great...
Actually I never learn vb before so just have so much mistake here...

have a nice day!!