Database issue with updating the Table [Data]

[Start4me]

I'm trying to create a database. After creating the Table [Design], I've went to the Table [Data] to add items to the database, ran the program to check if the database items show up, they do. Then I stopped debugging, went back to the table to add more information to the same item, but after adding more information and going on to create another item, I get this error: (Photo Attachment)

In the second photo, when I hover the mouse cursor on the red exclamation point, it gives me the reason of the error, but I don't know how to fix it.

Why is this happening and how could I fix this?

[kRod]

Can you show some code where your error is happening. From the message it would seem you are trying to execute a query with the connection Closed.

[Start4me]

I haven't written a line of code yet. I'm trying to edit the Data within the Table, but it wont let me. Take a look at the second photo attachment.
It says "The data in this cell has changed. The change has not been committed to the database...", but I don't know how to commit the change to the database. Does anybody know how to?

[Start4me]

I found the solution myself! So if I want to make changes, I need to close the dbo.Table [Data], and reopen it from the Server Explorer --> Table --> (right click) Show Table Data, and from there I can edit the previous items!

[Start4me]

kRod, I have one more question. If I want to perform a search from a textbox, for phone numbers for example, since there are two different cells (one is Office_Phone, and the other is Extention_Phone) how would I import both of them into a listbox and perform a search among both of them?

[kRod]

Start4me;
Sorry for the delay in replying...
I would have either have a textbox for each type of phone or some checkboxes or radiobuttons to determine which phone you'd want to query.

[Start4me]

Well I've attached a photo of how I would like the GUI to look like. As you can see I have made only one radiobutton to search among the phone numbers. The place where I get lost is when trying to set the listbox to import two Data Sources at the same time after clicking on the listbox arrow, checking the checkbox to "Use Data Bound Items". Maybe I need to code the radiobutton for phone numbers to import both Office_Phone, and the Extention_Phone. But then, does than mean that I will have to code the textbox in order to search those items too?

[kRod]

First what are the 2 datasources? it looked from your first images of the data it was all in one table: Second what is supposed to happen when you click on a name in your listbox?

[Start4me]

I'm sorry, I meant "Display Member" instead of datasources. And the photo displayed all the data in one listbox because I've used two System.Collections.Specialized.StringCollection(s). But I want to change my approach towards the database. So when the user clicks on the name in the listbox, the information will be displayed about that person.
I've attached a picture where you can see that I was able to import only on Display Memeber: "Office_Phone", but I also need to import the "Extension_Phone" but I'm limited to only one selection. Is there a code approach to this?

[kRod]

I would load the listbox with the names and when the user clicks the name you could execute a query against either the database or a DataSet to get your data about the person.

I'm not much into the binding of controls because mine usually fail when I need to do some workarounds which inevitably happens. So I all my coding of database to controls by hand. I've always hated the way Visual Studio stores the connectionString and all the DataSource operations. That's only my opinion and I know a lot of coders on here and elsewhere will frown on that but that's just me.

Anyway You could use LINQ against your datatable to retrieve the data you want about the selected person and display it in Textboxes or in Lables or even in a multiline textbox using some formating.

[Start4me]

I'm new to working with databases, and I don't know how to

load the listbox with the names and when the user clicks the name you could execute a query against either the database or a DataSet to get your data about the person.

Would you help me with code examples?

[kRod]

You will need to decide which fields you'll be needing from your sql server datatable. then you can create the commands, connection, dataadapter, and dataset.Then you can use the field with the names for your ListBox.

Here is a MSN page on how to fill your dataset.
Click Here

SQL Server Connection Strings Click Here

Once you get that going, let me know, then we can move on to the selecting records by the selected name.

[Start4me]

What I've tried so far, is added two Queries. One for Office Phone, and the other is for the Extension Phone from the TableDataGridView. Then I've added a textbox and two checkboxes with the following code:

Private Sub txtSearch_TextChanged(sender As Object, e As EventArgs) Handles txtSearch.TextChanged
        'Search by the Office Phone Numeber
        If CheckBox1.CheckState = CheckState.Checked Then
            Me.TableTableAdapter.FillByPhoneOffice(Me.PhoneNumbersSearchDataSet1.Table, txtSearch.Text)
        End If
        'Search by the Extention Phone Numeber
        If CheckBox2.CheckState = CheckState.Checked Then
            Me.TableTableAdapter.FillByPhoneExtention(Me.PhoneNumbersSearchDataSet1.Table, txtSearch.Text)
        End If
        'If the user clears the textbox, show all the items contained in the database
        If txtSearch.Text = "" Then
            Me.TableTableAdapter.Fill(Me.PhoneNumbersSearchDataSet1.Table)
        End If
    End Sub

The problem is that when one of the checkboxes is checked, the search works fine, but since I need to perform the search for both Phone numbers, it won't work when both checkboxes are checked. I also don't need the TableDataGridView, because I want to use a listbox instead, but I don't know how to change the code, or add query to the listbox.

[kRod]

Use the following to create your queries based on the CheckBoxes Checked State

        Select Case True
            Case CheckBox1.Checked And CheckBox2.Checked
                MsgBox("Both CheckBoxes Are Checked")
            Case CheckBox1.Checked
                MsgBox("CheckBox 1 Checked")
            Case CheckBox2.Checked
                MsgBox("CheckBox 2 Checked")
            Case Else
                MsgBox("No Checkbox Is Checked")
        End Select

[kRod]

Your query for the selected person from the ListBox should be like
SELECT Field1, Field2, Field3... FROM [TableName] WHERE Last_First_Name = 'selectedName';

[Start4me]

Use the following to create your queries based on the CheckBoxes Checked State

What will the cases with the display of MsgBox do? I don't seem to need them at all. Because I've tried previously to have these lines of code in one checkbox, but the search did not perform properly:

Me.TableTableAdapter.FillByPhoneOffice(Me.PhoneNumbersSearchDataSet1.Table, txtSearch.Text)
Me.TableTableAdapter.FillByPhoneExtention(Me.PhoneNumbersSearchDataSet1.Table, txtSearch.Text)

And most importantly, how would I import the data in a listbox instead of having to use the TableDataGridView.

[kRod]

Ok the Select Case was a demo so you could create your queries by which checkbox was checked. You really need to study up on your ADO.Net. I'm not trying to be condescending but the questions your asking tell me you need to do a little research on SQL Server operations with VB.Net. Click Here

You create a command, connection, dataadapter, and datatable or dataset. Then you use the data in the datatable to populate your controls. For the Listbox you need the following query "Select Last_First_Name From [tablename];" that's your Command's Text. You would populate the ListBox using a SqlDataReader
Click Here