Updating Database from a Datagridview

[smitty68503]

No mapping exists from object type System.Windows.Forms.DataGridViewRow to a known managed provider native type.

 Using conn As New SqlConnection(My.Settings.Smitty)
            Using cmd As New SqlCommand()
                With cmd
                    .Connection = conn
                    .CommandText = "Update Repairs set status = @status WHERE RepairNumber = @repairNumber"
                    .Parameters.AddWithValue("@status", "Awaiting Part Installation")
                    .Parameters.AddWithValue("@repairNumber", RepairInventoryDataGridView.Rows(4))
                End With
                conn.Open()
                cmd.ExecuteNonQuery()
            End Using
        End Using

        MessageBox.Show("Inventory Successfully Updated")

I have a datagridview that shows items I ordered. I want to check the items into inventory and change the status to "Awaiting part installation" for repairs that the part has arrived. I am hoping to do this through the datagrid and it updates from a particular row. I use the above code and get a Mapping error. I have tried several different ways and keep getting errors. I appreciate any help I can get.

[Begginnerdev]

Is the datagridview databound?

Do you have a project data source?

[G_Waddell]

Hi

RepairInventoryDataGridView.Rows(4)

Will give you a collection of cells on row 4 of the datagrid. Your Update statement is looking for a single parameter in this case the Repairnumber.

Even if your grid only consists of a single column, I still think you will have to quantify exactly what you are looking for e.g. RepairInventoryDataGridView.Rows(4).Cells(0).value

It would probably help to put in place some code to check it is a valid value i.e. not empty or null before putting it into your SQL Parameter.

[smitty68503]

Thanks for your help. I am going to get to that part when I figure out this part. Since it could be multiple rows that are affected, I did try to use something like this:

Try
            Dim I As Integer
            For I = 0 To DataGridView1.Rows.Count - 1
                Dim ordernumber As String =repairinventorydatagridview.Rows(I).Cells(0).Value


                Dim conn As New SqlConnection(My.Settings.Smitty)


                conn.Open()
                Dim query As String = "Update Status Where Repairnumber = @repairnumber"
                Dim cmd As New SqlCommand(query, conn)

                cmd.Parameters.AddWithValue("@ordernumber", ordernumber)
                cmd.ExecuteScalar()
                cmd.Dispose()

My goal was to create a count of how many rows in the data grid were changed and need to be updated then perform the update. I have tried something like this and end up with all kinds of errors. I have done a lot of reading on the update command and haven't had that epiphany yet. I will take note of what you showed me so far. Thanks

[smitty68503]

Begginnerdev: The Datagridview is databound and it does have a project data source.

[G_Waddell]

hmmm do you wish to update the rows at the press of a button etc? or just have it done on edit?

The approach you are using indicates you wish a user to press a button to update...

You could try something like this:

sub UpdateStatus()
dim DGVRow as datagridviewRow
dim Query As string = "UPDATE Status WHERE Repairnumber = @repairnumber"
Dim Conn as new sqlconnection(my.Settings.Smitty)
dim Cmd as sqlCommand
dim iUpdated as integer
for each DGVRow in repairinventorydatagridview.selectedRows
    if conn.state <> ConnectionState.Open then
        conn.open
    end if
    cmd= new sqlcommand
    cmd.Commandtype = CommandTypeText
    cmd.text = Query
    cmd.Connection = conn
    'Is this right? Your code give a different parameter name but the string is looking for @repairnumber
    cmd.paramaeters.addWithValue("@repairnumber", ordernumber) 
    if cmd.ExecuteNonQuery > 0 then
        'count the rows your updating 
        iUpdated +=1
    end if
next    

msgbox (iUpdated &" Rows updated.")

If not, you should look at specifying the Update command for the SQLDataAdapter you use to populate your Datagridview with.

[smitty68503]

I have a buttont that updates all of the changes in the datagridview all at once. My goal was to have what was updated compared to the repair number. I normally just use the update command that comes with setting up a Datagridview/dataset.

I will try this. Thanks for your help