VB.net update SQL command

Question

For some reason I cannot get my record to update. Here goes:

I have a Windows form, that will upon loading display the current status of an aircraft. If that status changes I can select the value from a combobox (cboServ). When I click the Exit button from that form the status should update, so upon loading the form again the new value appears. However it doesn't.

This is what I have currently as the code:

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim updateQuery As String = "UPDATE dbo.aircraft SET dbo.aircraft.condition = @OE_status WHERE aircraft.avNum = 'ab201'"

    Dim updateCmd As New SqlCommand(updateQuery, con)
    updateCmd.Parameters.Add("@OE_status", cboServ.SelectedValue)
    MessageBox.Show("it worked")
    con.Close()
    Me.Close()
    Form1.Show()
End Sub

The error I get back is - which I don't fully understand:

System.InvalidCastException: 'Conversion from type 'DataRowView' to type 'String' is not valid.'

This comes from the line reading cboServ.SelectedValue.

Answer 1

There is more than one problem with the code as shown.

First, you should use Option Strict On to make sure that the types of all the variables used match up properly.

You can isolate the problem more easily by separating the things being done in the button click handler out into other methods. For example, we could put the database interaction into its own method:

Sub UpdateAircraftCondition(avNum As String, condition As String)
    Dim connStr = "YOUR CONNECTION STRING HERE"
    Dim updateQuery As String = "UPDATE dbo.aircraft SET dbo.aircraft.condition = @OE_status WHERE aircraft.avNum = @AvNum"

    Using conn As New SqlConnection(connStr)
        Using cmd As New SqlCommand(updateQuery, conn)
            cmd.Parameters.Add(New SqlParameter With {.ParameterName = "@AvNum", .SqlDbType = SqlDbType.NVarChar, .Size = 16, .Value = avNum})
            cmd.Parameters.Add(New SqlParameter With {.ParameterName = "@OE_Status", .SqlDbType = SqlDbType.NVarChar, .Size = 16, .Value = condition})
            conn.Open()
            cmd.ExecuteNonQuery()
            conn.Close()

        End Using
    End Using

End Sub

(You will need to make the Size (for string data) and SqlDbType match up to the columns in the database.)

Notice how the connection only exists where it is needed - the Using construct takes care of disposing of unmanaged resources (like the SqlConnection) to keep memory tidied up and not leaking.

Instead of putting the actual connection string in everywhere, which makes it a lot of hassle to change it, it is normal to put it in some settings file, for example by using My.Settings.

According to the error message, cboServ.SelectedValue is a DataRowView, not a String. Which makes me wonder if cboServ is not a ComboBox. But we can get a useful error message by trying the following code for the button click event handler:

Private Sub bnUpdateCondition_Click(sender As Object, e As EventArgs) Handles bnUpdateCondition.Click

    If cboServ.SelectedIndex >= 0 Then
        Dim aircraftCondition = cboServ.SelectedItem.ToString()
        Dim avNum = "ab201"
        UpdateAircraftCondition(avNum, aircraftCondition)

        ' more code may go here

    End If

End Sub

Please note that I tried to give the button a meaningful name: doing that will make it much easier for you in the future than trying to figure out what "Button337" is for.

Answer 2

Change the row:

updateCmd.Parameters.Add("@OE_status", cboServ.SelectedValue)

to:

updateCmd.Parameters.AddWithValue("@OE_status", cboServ.SelectedValue.ToString())

or you can use a explicit cast:

var dataRowView = cboServ.SelectedValue as DataRowView;
var row = dataRowView.row As MyDataTableDefinition;
updateCmd.Parameters.AddWithValue("@OE_status", row[“OE_status”].ToString())