All populated textboxes into dataset? Problem with my code I think.

[DaveJS]

Hi All.

I have just registered, I appologise that my first thread is a question but I am at my witts end with this problem. I am sure it is a simple fix and I am missing something obvious but any help would be very welcome!

I have a form which has a number of Textboxes (approx 30) bound to a recordset ('EquipData') table called "ThisEquipment".

I am trying to loop through each Textbox in the Form and, if the textbox is populated, change the dataset to its value.

At the end I want to update the original table in the database.

Each textbox is name 'txt_###' where ### is the name of the field. So I loop through all textboxes, remove the 'txt_' and populate the field of that name with the textboxes .text value.

My problem is this.. it doesnt work! If i remove the entire 'For each Textbox.. Next' loop and manually set each field in the database individually (still using the '.Name.Remove(0, 4)') it works. If I put the loop back in though it does not throw up an error, it just doesnt change the original data.

Any help (or alternative methods) would be gratefully appreciated. Thanks.

    Dim EquipData As New DataSet
    Dim EquipID As Integer

 Public Sub New(ByVal EquipID As Integer)

        ' This call is required by the designer.
        InitializeComponent()

        ' Create the adaptor
        Dim EquipAdapter As New SqlDataAdapter("Select * from Equipment where EquipID=" & EquipID & ";", ServiceDBConn) ' This item of equipment
        Dim AllCountriesAdapter As New SqlDataAdapter("Select distinct Country from Equipment;", ServiceDBConn) ' DISTINCT list of all countries
        Dim AllEquipAdapter As New SqlDataAdapter("Select distinct Equipment from Equipment;", ServiceDBConn) ' DISTINCT list of all equipment
        Dim Builder As New SqlCommandBuilder(EquipAdapter)

        ' Open Database
        ServiceDBConn.Open()

        ' Use adapters to fill Equipdata Dataset with tables
        EquipAdapter.Fill(EquipData, "ThisEquipment")
        AllCountriesAdapter.Fill(EquipData, "DistinctCountries")
        AllEquipAdapter.Fill(EquipData, "DistinctEquipment")

        ' Close database and tidy up
        ServiceDBConn.Close()
        EquipAdapter = Nothing
        AllCountriesAdapter = Nothing
        AllEquipAdapter = Nothing

        ' If more than record set to show the 1st
        Me.BindingContext(EquipData.Tables("ThisEquipment")).Position = 1

        ' Set form title
        Me.Text = EquipData.Tables("ThisEquipment").Rows(0).Item("SerNo") & " Equipment Information"

        ' Populateform
        BindData() ' All this does is set binding for each textbox

        ' Set to be subform of Main
        Me.MdiParent = Main

    End Sub

    Private Sub tsb_Ok_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsb_Ok.Click

        Dim EquipAdapter As New SqlDataAdapter("Select * from Equipment where EquipID=" & EquipID & ";", ServiceDBConn) ' This item of equipment
        Dim Builder As New SqlCommandBuilder(EquipAdapter)
        Dim NewEquipData As New DataSet

        Try

            ' For each textbox, check empty and if not, write to database
            For Each C As Control In Me.Controls
                If TypeOf C Is GroupBox Then
                    For Each C2 As Control In C.Controls.OfType(Of TextBox)()
                        If Not (C2.Text = vbNullString) Then
                            EquipData.Tables("ThisEquipment").Rows(0).Item(C2.Name.Remove(0, 4)) = C2.Text
                        End If
                    Next
                End If
            Next

            ' Open Database and update dataset
            ServiceDBConn.Open()
            EquipAdapter.Update(EquipData, "ThisEquipment")

            ' Close Database
            ServiceDBConn.Close()


        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

        ' Close form
        Me.Close()

    End Sub

 

[cjard]

What I'm going to say might sound a litle extreme, but it's intended to start you off on the correct path (you are currently headed down the wrong path)

What you've done so far - set it aside (you'll scrap it right after you finish reading the following tiutorial) and make a new project
Read the DW2 link in my signature, section "creating a simple data app"

At the point of the tutorial where it says to drag the data source to the form, ensure you set the source to Details (click the dropdown on the node in the data sources window, choose details) first

When you drag the node to the form, textboxes etc will appear


You will now be at a stage where your problem is virtually solved (a from, containing textboxes, that reads and writes database data properly) and it will have taken probably about 15 minutes if it's your first time - probably less time than you took to create the post describing your problem
-

The only other point of note, after you finish the tutorial, read the DNU link in my signature

 

[DaveJS]

Thankyou for the help.

I have read the tutorial and am trying it now (although I am currently having difficulty connecting when adding the existing database, I am using SQL server and it is showing an error saying the server was not found or not accessible).

I am intending for the database to be hosted on our network server and everybody can connect from their own machine. Will adding the database this way still work ok for that use?

We are currently using an access database which is slow and not very efficient so we are moving over to this app instead and will eventually copy the data over, only problem is I am learning ADO on the fly as you can tell!

What makes you say that my code is on the wrong path? Slow? Outdated? Just plain wrong?

Thanks again for the help, it is much appreciated.

..Dave

 

[cjard]

Thankyou for the help.

I have read the tutorial and am trying it now (although I am currently having difficulty connecting when adding the existing database, I am using SQL server and it is showing an error saying the server was not found or not accessible).

You may not have made the SQL Server accessible; open the Surface Area Configuration tool and check that the sql server and its browser service are running, that they accept local and remote connections over tcp as well as named pipes, and that it supports the authentication youre using.

The tutorial I think does cover some aspects of this.. Though you may choose to familiarise yourself with the concepts of DataSets, TableAdapters etc using something simpler like the northwind access database

Ends up though, if you can connect to your sql serve rin code, but not through the dataset designer, there's a fundamental problem with what youve typed into the designer - either the connection string or the chosen driver has some problem in its setup

I am intending for the database to be hosted on our network server and everybody can connect from their own machine. Will adding the database this way still work ok for that use?

Of course.. Indeed you may prefer to develop on it this way to start with (set it up elsewhere first) but remember the notes above about making it accessible

We are currently using an access database which is slow and not very efficient so we are moving over to this app instead and will eventually copy the data over, only problem is I am learning ADO on the fly as you can tell!

SQLServer is not a magic bullet that will solve a slow access database; if your data structure, logic, sql statements etc are badly designed and written, sqlserver won't help. If it's literally just that the app has grown to a 4 gig access db running on a file share to 300 users, then sqlserver should help

What makes you say that my code is on the wrong path? Slow? Outdated? Just plain wrong?

It looks like you'll end up with an app that has a load of SQL statements coded into button click handlers, and you'll be manually pushing data in and out of text boxes yourself.. i.e. more than half the code you'll write will be some menial data pushing exercise. Follow the tutorial and you'll get a feel for what I mean