SelectedValue DBNUll Error

[Rick_Fla]

I avoided asking, but looks like I must. I have a list box that gets populate VIA access database using a DataSet all bound and pretty. I have the ability for a user to add and delete from that list. I can add and delete, for the most part, just fine. Here is the problem.

 

Say I has someone add an entry to the listbox, then decide they want to delete it. using the code below I attempt to delete the entry, but I get a DBNULL error on the SelectedValue part. it works fine if I delete something that has not been recently added, or if I close the form and reload. The Code for the Add and Delete routines I have.

 

Private Sub btnUpdate_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpdate.Click

       'Check Textbox
       If Me.txtProjectName.Text = "" Or Me.txtProjectName.Text Is Nothing Then
           SetError(True)
           Me.txtProjectName.Focus()
           Exit Sub
       Else ' Clear Error
           SetError(False)
       End If

       'Declare variables
       Dim objConnection As OleDbConnection
       Dim objCommand As OleDbCommand
       Dim strSQL As String

       'Build and Open Connection
       objConnection = New OleDbConnection(strConnectionString)
       objConnection.Open()

       'Build Command
       strSQL = "INSERT INTO tblProjects (ProjectName) VALUES ('" & txtProjectName.Text & "')"
       objCommand = New OleDbCommand
       With objCommand
           .CommandType = CommandType.Text
           .CommandText = strSQL
           .Connection = objConnection
       End With

       Try
           'Insert New Row
           Dim NewRow As DataRow

           NewRow = Me.dsProjects.Tables("Projects").NewRow()
           NewRow.Item("ProjectName") = Me.txtProjectName.Text
           Me.dsProjects.Tables("Projects").Rows.Add(NewRow)

           'Update DataSet
           objAdapter.InsertCommand = objCommand
           objAdapter.Update(Me.dsProjects, "Projects")

           Me.dsProjects.AcceptChanges()

           'Close and Dispose
           objConnection.Close()
           objConnection.Dispose()
           objCommand.Dispose()


       Catch ex As Exception
           MessageBox.Show(ex.Message, "Application Error", MessageBoxButtons.OK, MessageBoxIcon.Error)

       End Try

       SetButtons(True)

 

Now the Delete Function

 

Private Sub btnDelete_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnDelete.Click

       'Make sure user wanted to make deletion
       If MessageBox.Show("Are you sure you want to delete this project?", "Delete?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = DialogResult.No Then
           Exit Sub
       End If

       'Declare variables
       Dim objConnection As OleDbConnection
       Dim objCommand As OleDbCommand
       Dim strSQL As String

       'Build and Open Connection
       objConnection = New OleDbConnection(strConnectionString)
       objConnection.Open()

       'Build Command
       strSQL = "DELETE FROM tblProjects WHERE projectID = " & CType(Me.lstProjectList.SelectedValue, Integer) & ""
       objCommand = New OleDbCommand
       With objCommand
           .CommandType = CommandType.Text
           .CommandText = strSQL
           .Connection = objConnection
       End With

       Try

           'Delete the selected Project
           For Each dr As DataRow In Me.dsProjects.Tables("Projects").Rows
               If CType(dr.Item("projectID"), Integer) = CType(Me.lstProjectList.SelectedValue, Integer) Then
                   dr.Delete()
                   Exit For
               End If
           Next

           'Update the DataSet
           objAdapter.DeleteCommand = objCommand
           objAdapter.Update(Me.dsProjects, "Projects")
           Me.dsProjects.AcceptChanges()

           'Close and Dispose
           objConnection.Close()
           objConnection.Dispose()
           objCommand.Dispose()


       Catch ex As Exception
           MessageBox.Show(ex.Message, "Application Error", MessageBoxButtons.OK, MessageBoxIcon.Error)

       End Try

       SetButtons(True)

   End Sub

 

 

Like I said, delete works fine as long as it is not a recently added item to the list.

[Nerseus]

It looks like you're assuming the SelectedItem is an Integer? Is that right? That would imply your listbox shows just numbers? Normally you'd load a listbox with a name/value object - the name is displayed while the number is used as the key.

 

What line does your code blow up on? When you do the Database stuff (which happens first) or farther down when you remove the value from the DataTable?

 

By the way, you can use DataTable's Select method to get a set of DataRows that match a filter and delete from that. When you get the other part working, let me know and I'll get you a sample for the Select (if you want).

 

-ner

[Rick_Fla]

The code is blowing up here:

 

strSQL = "DELETE FROM tblProjects WHERE projectID = " & CType(Me.lstProjectList.SelectedValue, Integer) & ""

 

The SelectedValue should return the key correct? or am I way off? That's how I was doing the delete on the other items, just does not like on newly created items in the list.

 

The Datatables method would be a lot better code wise than the loop?

[Nerseus]

How are you adding items to the listbox? Are you binding or just adding items manually?

 

-ner

Edited July 27, 2004 by Nerseus

[Rick_Fla]

Binding them:

 

Private Sub PopulateProjectList()

       'Declare variables
       Dim objConnection As OleDbConnection
       Dim objcommand As New OleDbCommand
       Dim strSQL As String

       'Build SQL
       strSQL = "SELECT * FROM tblProjects"

       Try

           'Build Connection
           objConnection = New OleDbConnection(strConnectionString)

           'Build Command
           With objcommand
               .CommandType = CommandType.Text
               .CommandText = strSQL
               .Connection = objConnection
           End With

           'Build Adapter
           objAdapter.SelectCommand = objcommand

           'Fill DataSet
           objAdapter.Fill(dsProjects, "Projects")

           'Populate List
           With lstProjectList
               .DataSource = dsProjects.Tables("Projects")
               .DisplayMember = dsProjects.Tables("Projects").Columns(1).ToString
               .ValueMember = dsProjects.Tables("Projects").Columns(0).ToString
           End With

           'Close and dispose
           objAdapter.Dispose()
           objcommand.Dispose()
           objConnection.Close()
           objConnection.Dispose()

       Catch ex As Exception
           MessageBox.Show(ex.Message, "Application Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
       End Try

   End Sub

[Nerseus]

Are you sure Column 0 and 1 are what you want and they're not reversed? You can specify a column name if you want (I can't see them because you use "SELECT *...").

 

I'll test this out in just a bit.

 

-ner

[Rick_Fla]

Yes, there is only two columns in this table right now. projectID and ProjectName. I can switch to names and retry.

 

 

*update*

 

Ok, tested with Column names and still get a invaild convert from DBNull to Interger on the line I listed above.