How to add a new row to a datagrid Winforms VB.Net

DavidHannen
DavidHannen used Ask the Experts™
on
Experts

I have added a grid view control to a windows form. The form is not bound to a datasource.

The form and data grid are updated programattically using SQLAdapter . The following is the main bit of the code that currently gets the data into the grid view.

'==========================================================================
     objDataAdapter.SelectCommand = New SqlCommand
        objDataAdapter.SelectCommand.Connection = objCnn
        objDataAdapter.SelectCommand.CommandText = strMedicalHistorySQL & "WHERE tblMedical.MEMBER_ID = " & MemberNumber 'SQL string representing required table fields in SQL Server DB"
        objDataAdapter.SelectCommand.CommandType = CommandType.Text

        Try
            '---------------------------------
            'Create the dataset and data views
            objMemDS = New DataSet
            objDataAdapter.Fill(objMemDS, "MedicalHistory")

            '------------------
            'Fill the data grid
            grdMedicalHistory.AutoGenerateColumns = True
            grdMedicalHistory.DataSource = objMemDS
            grdMedicalHistory.DataMember = "MedicalHistory"


'==========================================================================

I want to be able to allow users to add rows to the grid view and edit existing rows but cannot get it to work. My questions are:

1) How do I update the dataset
2) How will the application 'know' whether I am adding a new record (insert) or editing an existing one (update)
3) in one of the grid views I need to implement there will be several tables included in the update.

I am completely lost after hours trying to research this by myself and could really use some help

Thanks








Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Craig WagnerSoftware Architect

Commented:
Because your grid is bound to a dataset you have to add new rows to the dataset, not the datagrid.

.NET keeps track of whether rows are changed, deleted, or new automatically.

You can update the dataset by setting InsertCommand, UpdateCommand, and DeleteCommand on the dataadapter and calling Update() method.

Author

Commented:
Thanks Craig.

Where do I call the update method from? Does an eveny fire when the user adss/updates the grid view?

Thanks
Craig WagnerSoftware Architect

Commented:
There's probably an event, but the idea behind the datagridview is that the user can make a whole bunch of changes (add/update/delete) and then save them all at once. Create a Save button or menu item and let the user click that.

Author

Commented:
OK. Sorry If I am beiung stupid here. But I am still not there.

1) Iv'e created a connection
2) I've created a SQL Adapter

The SQLAdapter is created with a set of update, select inset and delete commands (eg for select below)

3) I create a Dataset object and want to fill it using the sqlAdapter  eg
DataAdapter.Fill(Dataset, "MedicalHistory")

But I get errors because I have not passed parameter values.

I'm sure this is the last bit of the puzzle - but how do I get the parameter values into the mix?

I have added the code below for the SQLAdapter code - which is  function I found on the microsoft website. Hope you can guide me from here.

Thanks


Public Function CreateMedicalAdapter(ByVal connection As SqlConnection) As SqlDataAdapter

        Dim adapter As SqlDataAdapter = New SqlDataAdapter()

        ' Create the SelectCommand.
        Dim command As SqlCommand = New SqlCommand("SELECT * FROM tblMedical WHERE MEMBER_ID = @MEMBER_ID", connection)

        ' Add the parameters for the SelectCommand.
        command.Parameters.Add("@MEMBER_ID", SqlDbType.Int)

        adapter.SelectCommand = command

        ' Create the InsertCommand.
        command = New SqlCommand("INSERT INTO tblMedical (DATE_ADD, MEMBER_ID, MEDICAL_ISSUE, CARRIES_MEDS, CLOSED_ISSUE, NOTES) " _
                                 & "VALUES (@DATE_ADD, @MEMBER_ID, @MEDICAL_ISSUE, @CARRIES_MEDS, @CLOSED_ISSUE, @NOTES)", connection)

        ' Add the parameters for the InsertCommand.
        command.Parameters.Add("@DATE_ADD", SqlDbType.SmallDateTime, 4, "DATE_ADD")
        command.Parameters.Add("@MEMBER_ID", SqlDbType.Int, 4, "MEMBER_ID")
        command.Parameters.Add("@MEDICAL_ISSUE", SqlDbType.NVarChar, 100, "MEDICAL_ISSUE")
        command.Parameters.Add("@CARRIES_MEDS", SqlDbType.Bit, 1, "CARRIES_MEDS")
        command.Parameters.Add("@CLOSED_ISSUE", SqlDbType.Bit, 1, "CLOSED_ISSUE")
        command.Parameters.Add("@NOTES", SqlDbType.NVarChar, 510, "NOTES")

        adapter.InsertCommand = command

        ' Create the UpdateCommand.
        command = New SqlCommand("UPDATE tblMedical SET DATE_ADD = @DATE_ADD, MEDICAL_ISSUE = @MEMBER_ID, CARRIES_MEDS =@CARRIES_MEDS, CLOSED_ISSUE = @CLOSED_ISSUE, NOTES = @NOTES", connection)

        ' Add the parameters for the UpdateCommand.
        command.Parameters.Add("@DATE_ADD", SqlDbType.SmallDateTime, 4, "DATE_ADD")
        command.Parameters.Add("@MEMBER_ID", SqlDbType.Int, 4, "MEMBER_ID")
        command.Parameters.Add("@MEDICAL_ISSUE", SqlDbType.NVarChar, 100, "MEDICAL_ISSUE")
        command.Parameters.Add("@CARRIES_MEDS", SqlDbType.Bit, 1, "CARRIES_MEDS")
        command.Parameters.Add("@CLOSED_ISSUE", SqlDbType.Bit, 1, "CLOSED_ISSUE")
        command.Parameters.Add("@NOTES", SqlDbType.NVarChar, 510, "NOTES")

        Dim parameter As SqlParameter = command.Parameters.Add("@oldCustomerID", SqlDbType.NChar, 5, "CustomerID")
        parameter.SourceVersion = DataRowVersion.Original

        adapter.UpdateCommand = command

        ' Create the DeleteCommand.
        command = New SqlCommand("DELETE FROM tblMedical WHERE MEDICAL_ID = @Medical_ID", connection)

        ' Add the parameters for the DeleteCommand.
        command.Parameters.Add("@Medical_ID", SqlDbType.SmallInt, 2, "MEDICAL_ID")
        parameter.SourceVersion = DataRowVersion.Original

        adapter.DeleteCommand = command

        Return adapter
    End Function

Open in new window

any hours later I have found the simple and possibly rather obvious solution


As in the snippet below, pass the value to the parameter before filling the dataset.

Try
            '---------------------------------
            'Create the dataset and data views
            objMemDS = New DataSet
            objDataAdapter.SelectCommand.Parameters("@MEMBER_ID").Value = MemberID
            objDataAdapter.Fill(objMemDS, "MedicalHistory")

sorted

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial