[Resolved] ListBox Database Remove Item Dilemma

[Firehawk]

I am trying to write an order entry program where an individual enter orders and order details. So far everything i have done works except one thing. I need to program in a way to select an item from a list box, then click a button and remove that detail from the order as well as from the database that it is being entered into. For example/ my program is linked to an access database. when a user enters the proper information and clicks a "add detail" button, that information is inserted into my database table OrderDetails as well as populated into a listbox so that a user can view all line items of the order. if the user selects an item from the listbox, i need to somehow extract from that entry the first item of the entry, in this case orderdetailid, insert that orderdetailid into a sql statement and also remove that entry from the listbox.
My line items displayed in the listbox resemble the following:

27 Widget1 7.00 2 14

where 27 is the orderdetail id, widget1 the description, 7.00 the price, 2 the quantity, and 14 the extended price. I need to somehow be able to select a line item from the list box, pull that orderdetailid, in this case 27, from it, then insert that returned string into my sql statement and remove the line item from the listbox. Does anyone know how to extract this information. All i seem to be able to do is find the selectedindex which merely returns the index value of the item in the listbox. Thanks in advance for any and all help.

[zaza]

Hi,

Try:

Listbox1.selecteditems(0).text

I can't remember if this is for a Listview only or not. If you're putting in multiple items of data for a single entry, you should consider using a listview anyway. You can set individual columns for each piece of data and then extract them individually.

Once you have the text, do you know how to get the data? You could use the Split method (with " " as the separator) to put them in an array, or use something like instr to find the first item etc.

Also see this

zaza

[Firehawk]

Here is my code but I keep getting an error on the line:
lbDetails.SelectedItem.Text = LineItems

saying

An unhandled exception of type 'System.MissingMemberException' occurred in microsoft.visualbasic.dll

Additional information: Public member 'Text' on type 'String' not found.

Here is my code for the button:

Private Sub btnRemoveDetail_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRemoveDetail.Click
Dim SQLStmt, LineItems As String
Dim Fields(99) As String
lbDetails.SelectedItem.Text = LineItems
'lbDetails.SelectedItem().text = LineItems
Fields = LineItems.Split()
Dim UpdateOK As Integer
Dim UpdateCnxn As New OleDbConnection( _
"Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=CM20050907.mdb")
UpdateCnxn.Open()
SQLStmt = String.Format("delete from OrderDetails where Id='{0}'", Fields(0))
Dim UpdateCommand As New OleDbCommand(SQLStmt, UpdateCnxn)
UpdateOK = UpdateCommand.ExecuteNonQuery()
lbDetails.ClearSelected()
MsgBox("Line Item Removed")
Debug.Write("SQLStmt=" & SQLStmt & vbCrLf)
End Sub

I will be honest, i know that a datagrid or listview would be better but i have never delt with or coded for either. My only experience with with a listbox. Again thanks in advance for any help.

[zaza]

Re-read my previous post.

Also, you may wish to be setting "Lineitems = ..." rather than the other way around.

zaza

[Firehawk]

I reread your post and I appreciate all your help. Unfortunately I'm not following it. I will admit I am not an experienced VB'er at all, but rather only have experience with java. Thats besides the point however.

I tried switching my equals statement and received the same error.

[Firehawk]

Ok, I played with my code some more and here is what I have come up with:

Private Sub btnRemoveDetail_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRemoveDetail.Click
Dim SQLStmt, LineItems As String
Dim Fields(99) As String
LineItems = lbDetails.SelectedIndex.ToString
'lbDetails.SelectedItem().text = LineItems
Fields = LineItems.Split()
Dim UpdateOK As Integer
Dim UpdateCnxn As New OleDbConnection( _
"Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=CM20050907.mdb")
UpdateCnxn.Open()
SQLStmt = String.Format("delete from OrderDetails where Id='{0}'", Fields(0))
Dim UpdateCommand As New OleDbCommand(SQLStmt, UpdateCnxn)
UpdateOK = UpdateCommand.ExecuteNonQuery()
lbDetails.ClearSelected()
MsgBox("Line Item Removed")
Debug.Write("SQLStmt=" & SQLStmt & vbCrLf)
End Sub

Now I am receiving an error that highlights the following line of code:
UpdateOK = UpdateCommand.ExecuteNonQuery()

Any ideas on what this error means, or how to fix it?

Thanks

[jmcilhinney]

First off, remove the "99" from the declaration of the Fields variable. The Split method creates a new array itself so you don't need to create an array yourself when you declare the variable. You are just creating an array object with room for 99 string variables that gets discarded when you yuou call Split, which creates a new array object with just the number of elements that's needed.

Secondly, if your Id field is a number you need to remove the single quotes in the SQL statement. The single quotes indicate that the value is a string.

Finally, I'd actually suggest using a ListView or DataGrid if you want to display more than one column, rather than a ListBox.