What is the most efficient way of adding, modifying and deleting data

[ashishraj14]

I have a DataGrid that display records from SQL Server table. User click on Add button to open Data Entry form and press OK when finished. New record should be added to the database and DataGrid should display and highlight the added record. Similarly, when user modifies a particular record, the changes should be propagated to database and DataGrid. When delete button is pressed highlighted record should be deleted.

I have come across different options such as passing DataRow from one form to another, passing DataSet by reference etc, but still confused as to which is the neatest way of doing this?

Please help

 

[Bruce1965]

From what I understand about datagrids is that they are like a window to a table on your database. Making changes within the datagrid (ie adding, deleting etc)will ultimately change the data within your database table.

Can I suggest that you allow changes to the datagrid and add a button to your form (call it 'Update' or summat) and use the code -

 Try
            sqlda1.Update(ds1)
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

behind it

(this assumes that you already have an sqlconnection, adapter and dataset configured)

See how you get on.

Paul

 

[ashishraj14]

thanks bruce

I can't allow in-line editing to the DataGrid. The user has to highlight a row and open a form with where records will be shown in textboxes and once user has finished and press ok the changes should reflect in database and datagrid. Similarly in case of new record simple press Add button which opens the form with text boxes provide values in textboxes and press OK to save new record and a row for the new record should be added to the datagrid and it should be highlighted. hope this makes sense.

 

[Bruce1965]

It sounds like you want to do something I have just coded.


Pseudo code -

Load a table into a listview
Update several text boxes with the selection made from the listview
Click a button (Add,Remove,Update or whatever)
Clicking the button then uses the datagrid to 'post' changes back to the table
Re-build listview from table


Here's a snippet of code which does part of the routine

 Try ' update the datagrid with the data off the form
                    btnUpdate.Enabled = False
                    sqlcnn.Open()
                    Dim dr As DataRow = ds.Tables("tblAuthorisedOverrides").NewRow()
                    Dim d1 As String = Format(Date.Today, "yyyyMMdd")
                    Dim t1 As String = Format(Date.Now, "HHmmssss")
                    Dim r1 As String = Int(Rnd() * 9999)
                    Dim key As String
                    key = d1 & t1 & r1
                    dr.Item("intRecordKey") = key
                    dr.Item("strEmpID") = txtEmployeeID.Text
                    dr.Item("dtedate") = txtOverTimeDate.Text
                    dr.Item("strSurname") = txtEmployeeSurname.Text
                    dr.Item("strForename") = txtEmployeeForename.Text
                    dr.Item("strOvertimeClaimed") = txtTimeClaimed.Text
                    dr.Item("strOverrideCode") = txtOverrideCode.Text
                    dr.Item("strAuthorisedBy") = txtstrUserID.Text
                    dr.Item("strOverrideType") = txtOvertimeType.Text
                    ds.Tables("tblAuthorisedOverrides").Rows.Add(dr)


                Catch ex As Exception
                    MessageBox.Show(ex.Message)
                End Try

                sqlda.Update(ds)
                sqlcnn.Close()

Hope this helps

Paul

 

[ashishraj14]

I am unsure of the architecture. How would I pass the highlighted record in DataGrid to the form where user can modify the values and then press OK to propagate the changes to the database and the datagrid? When user opens a form for adding new records how it would be saved and shown in the datagrid? Should I create different form for add & edit? How would be the highlighted record deleted from the datagrid and database

Thanks

 

[Bruce1965]

Right then ....

To Update Data

Don't use the datagrid for record selection (but still have it on our form, but hidden).

Create a listview and populate it with your records.

Create several textboxes that will represent each field from your table.

On user selection of a record from the listview, populate these textboxes with the relevant data.

Use the information in these textboxes to interactively query and build the datagrid. (doing it this way will ensure the datagrid only has the record(s) you actually want).

Make your changes within the textboxes and run the code similar to that of my last post to affect the 'update' process.


To Delete a Record

Similar to that above, but create a 'Delete' button and enter code something like -

Dim sqlcmndDelete As New SqlCommand

                sqlcmndDelete.Connection = sqlcnn
                sqlcmndDelete.CommandText = _
                "DELETE FROM youtable WHERE UniqueRecordKey = '" & UniqueRecordKey.Text & "'"
                sqlcmndDelete.CommandType = CommandType.Text
                sqlcnn.Open()
                sqlcmndDelete.ExecuteNonQuery()

                sqlda.Update(ds)
                sqlcnn.Close()

To Add a Record

Enter information in your textboxes, create an 'Add' button and enter code something like -

sqlcmndAuthorise.Connection = sqlcnn
                        sqlcmndAuthorise.CommandText = _
                        "insert into yourtable (field1,field2,field3,field4) " & _
                        "values ('" & txtfield1.text& "','" & txtfield2.Text & "','" & txtfield3.Text & "','" & txtfield4.Text & "')"

                        sqlcmndAuthorise.CommandType = CommandType.Text
                        sqlcnn.Open()
                        sqlcmndAuthorise.ExecuteNonQuery()
                        sqlcnn.Close()

I don't want to write all the code for you, but I hope this gives you a few leaders.