Page 1 of 2 12 LastLast
Results 1 to 40 of 43

Thread: Trouble populating then updating a text box

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Apr 2016
    Posts
    97

    Trouble populating then updating a text box

    I have 3 forms that share 2 common fields. On each page I will input an amount (money) in various fields and I am needing it to update the DB then refill the common text boxes with the updated amount.

    Code (a partial) of what I am trying to do and maybe someone can see where I am going off the beaten path.

    Code:
        Private Sub TruckRepairCost_TextChanged(sender As Object, e As EventArgs) Handles tbTruckRepairCost.TextChanged
            cmd.Parameters.Clear()
            cmd.CommandType = CommandType.Text
            cmd.CommandText = ("UPDATE Maintenance_Table 
                                   SET TRUCK_REPAIR_COST = TRUCK_REPAIR_COST + @upd
                                 WHERE PETS_LOAD_NUMBER = @ln")
    
            cmd.Parameters.AddWithValue("@upd", tbTruckRepairCost.Text)
            cmd.Parameters.AddWithValue("@ln", tbPetLoadNumb.Text)
    
            cmd.CommandType = CommandType.Text
            cmd.Connection = New SqlConnection With {
                   .ConnectionString = My.MySettings.Default.PETS_DatabaseConnectionString
             }
            cmd.Connection.Open()
            cmd.ExecuteNonQuery()
            cmd.Connection.Close()
    
            Calc()
        End Sub
    
        Private Sub TrkMaintWarranty()
            Dim a, b As Decimal
    
            Try
                b = CDec(IIf(tbTruckRepairCost.Text.Trim = "", 0D, tbTruckRepairCost.Text.Trim))
    
                If cbTrkMaintWarranty.Text = "No" Or cbTrkMaintWarranty.Text = "N / A" Then
                    tbTruckRepairCost.Text = b.ToString("C2")
                ElseIf cbTrkMaintWarranty.Text = "Yes" Then
                    a = b
                    tbTruckRepairCost.Text = b.ToString("C2")
    
                    Try
                        Select Case True
                            Case a > 0D
                                cmd.Parameters.Clear()
                                cmd.CommandType = CommandType.Text
                                cmd.CommandText = ("UPDATE Maintenance_Table 
                                                       SET WARRANTY_TOTAL = WARRANTY_TOTAL + @upd
                                                     WHERE PETS_LOAD_NUMBER = @ln")
    
                                cmd.Parameters.AddWithValue("@upd", a)
                                cmd.Parameters.AddWithValue("@ln", tbPetLoadNumb.Text)
    
                                cmd.CommandType = CommandType.Text
                                cmd.Connection = New SqlConnection With {
                                       .ConnectionString = My.MySettings.Default.PETS_DatabaseConnectionString
                                 }
                                cmd.Connection.Open()
                                cmd.ExecuteNonQuery()
                                cmd.Connection.Close()
    
                                Calc()
                            Case Else
                                Exit Sub
                        End Select
    
                    Catch ex As Exception
                        MessageBox.Show(ex.Message)
                    End Try
                End If
    
            Catch ex As Exception
                'If a calculation error occurs, show Error message box
                Dim frm As New MeMsgCalcError(ex, "'Truck Repair Warranty' Calculation Error." & vbCrLf & "Lines 189-233")
                frm.Show()
            End Try        
        End Sub
    
        Private Sub Calc()
            If tbPetLoadNumb.Text.Length = 0 Then
                Exit Sub
            End If
    
            Dim cmd As New SqlCommand
            Dim reader As SqlDataReader
    
            Try
                cmd.Parameters.Clear()
                cmd.CommandText = "SELECT SUM(IsNull(APU_PM_COST, 0)
                                            + IsNull(APU_REPAIR_COST, 0)
                                            + IsNull(BODY_REPAIR_COST, 0)
                                            + IsNull(TOW_COST, 0)
                                            + IsNull(TRAILER_REPAIR_COST, 0)
                                            + IsNull(TRUCK_REPAIR_COST, 0)
                                            + IsNull(TRUCK_PM_COST, 0)
                                            - IsNull(WARRANTY_TOTAL, 0))
                                     FROM Maintenance_Table
                                    WHERE PETS_LOAD_NUMBER = @ln
    
                                    UNION
                              
                                   SELECT IsNull(WARRANTY_TOTAL, 0)
                                     FROM Maintenance_Table
                                    WHERE PETS_LOAD_NUMBER = @ln"
    
                cmd.Parameters.AddWithValue("@ln", tbPetLoadNumb.Text)
    
                cmd.CommandType = CommandType.Text
                cmd.Connection = New SqlConnection With {
                    .ConnectionString = My.MySettings.Default.PETS_DatabaseConnectionString
                }
                cmd.Connection.Open()
    
                Try
                    reader = cmd.ExecuteReader()
                    Dim Cost As Decimal
                    Dim Warranty As Decimal
    
                    With reader
                        .Read()
    
                        Cost = .GetDecimal(0)
                        Warranty = .GetDecimal(0)
    
                        tbInvoiceTotal.Text = CStr(Cost)
                        tbWarrantyTotal.Text = CStr(Warranty)
    
                        Return
    
                    End With
    
                    reader.Close()
    
                Catch ex As Exception
                    'If a calculation error occurs, show Error message box
                    Dim frm As New MeMsgCalcError(ex, "'Invoice and Warranty Total' Calculation Error." & vbCrLf & "Lines 127-178")
                    frm.Show()
                End Try
    
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
            cmd.Connection.Close()
        End Sub

  2. #2
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,196

    Re: Trouble populating then updating a text box

    What's the problem???

    You need to explain your issue. What's happening, what did you expect to happen, is there an error, what is the error, where is the error.

  3. #3
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Trouble populating then updating a text box

    Quote Originally Posted by wes4dbt View Post
    What's the problem???
    You mean besies the fact tht he's using the TextChanged event, which means that as soon as the user types in somethibg, say "1" in to the text box, it updates the database adding it to the existing value (even though what they were on their way to typing was 1000, but that's for later)... it opens the connectionm updates the database, closes it, calls another function, opens the db again, does another query, gets a value, then does a return in the middle, leaving things in an unknown state....
    Other than all that? Nothing that I can see.
    Unless you mean the SQL Adding... where the first time the user tries to type in 1, it adds 1 to the field then it'll add 10, then 100 then 1000 when it should have only added 1000...
    nope, not seeing any problems at all.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  4. #4

    Thread Starter
    Lively Member
    Join Date
    Apr 2016
    Posts
    97

    Re: Trouble populating then updating a text box

    Ok, excuse me for trying to learn something only to get nothing but snide comments. I will look else wheres for people willing to teach rather than smash someone's request to learn or advise them where they went wrong. Thanks for nothing.

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Apr 2016
    Posts
    97

    Re: Trouble populating then updating a text box

    Quote Originally Posted by wes4dbt View Post
    What's the problem???

    You need to explain your issue. What's happening, what did you expect to happen, is there an error, what is the error, where is the error.
    I get no errors per se.

    1. The SQL updates the field, but never adds it to the field I need it added to.
    2. As techgnome, so rudely made note of, textchanged event is not working as I hoped it would so I need to understand how or what the correct command I should be using, once I put a value into the textbox, will write the value in it's proper column in the SQL (which it does do that), adds the additional sum to the overall total and updates the overall total textbox.

  6. #6

    Thread Starter
    Lively Member
    Join Date
    Apr 2016
    Posts
    97

    Re: Trouble populating then updating a text box

    Quote Originally Posted by techgnome View Post
    You mean besies the fact tht he's using the TextChanged event, which means that as soon as the user types in somethibg, say "1" in to the text box, it updates the database adding it to the existing value (even though what they were on their way to typing was 1000, but that's for later)... it opens the connectionm updates the database, closes it, calls another function, opens the db again, does another query, gets a value, then does a return in the middle, leaving things in an unknown state....
    Other than all that? Nothing that I can see.
    Unless you mean the SQL Adding... where the first time the user tries to type in 1, it adds 1 to the field then it'll add 10, then 100 then 1000 when it should have only added 1000...
    nope, not seeing any problems at all.

    -tg
    Ok, excuse me for trying to learn something only to get nothing but snide comments. I will look else wheres for people willing to teach rather than smash someone's request to learn or advise them where they went wrong. Thanks for nothing.

  7. #7
    PowerPoster
    Join Date
    Nov 2017
    Posts
    3,116

    Re: Trouble populating then updating a text box

    Technically, the snide comment wasn't directed at you, it was directed at wes4dbt. Now, you were caught in the crossfire, because the snide comment was basically implying that had wes4dbt taken even the slightest moment of time to examine the code, they would have noticed massive problems with it, because the problems they are a-plentiful.

    Good luck.

  8. #8
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Trouble populating then updating a text box

    It's hard to point out specific flaws with code that goes about something in completely the wrong way so I suggest that you take a step back and do what everyone should but very few do when starting out, which is work out the logic first, before writing any code. When people try to write code without understanding what that code is supposed to do - not just the end result but the steps to get there - then they often end up with a complete mess. I haven't looked at your code closely but tg's description of it is a good indication that it's a tangled mess.

    Start by putting your keyboard away and pick up a pen and paper. Treat the problem as a manual process and write down the steps you would need to perform to complete it. Break each step down into the smallest parts you can. Formalise those steps such that you could hand them to an idiot with no knowledge of your project and they could follow them to get the right result and you have yourself an algorithm. Only then should you start writing code. You can write your code without considering the original problem but purely with the aim of implementing the algorithm. If the algorithm actually works and you implement it faithfully then your code will work. If the code doesn't work, you have something concrete to compare it to to see where the aberrant behaviour is and exactly how it differs from your expectation. Even if you can't solve the problem yourself, you can answer all the questions posed in post #2.

    If you have a sensible, working algorithm and implement it faithfully then you won't end up with silly outcomes like saving to the database after every character the user types. You'll likely still encounter some issues - we all do - but there will be fewer of them and you'll have a much better idea of what and where they are. That is at least partly because you should implement the algorithm one step at a time and test to make sure that the code does what it is supposed to up to that point. If something goes wrong part-way through, you know exactly what step things went awry on and the issue is not obfuscated by any implementation of later steps.

  9. #9
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,196

    Re: Trouble populating then updating a text box

    Quote Originally Posted by OptionBase1 View Post
    Technically, the snide comment wasn't directed at you, it was directed at wes4dbt. Now, you were caught in the crossfire, because the snide comment was basically implying that had wes4dbt taken even the slightest moment of time to examine the code, they would have noticed massive problems with it, because the problems they are a-plentiful.

    Good luck.
    Your wrong, I did take the slightest moment of time. But decided I wasn't in the mood to guess at what he was actually trying to accomplish. The OP initial post was not clear, I didn't understand it and it didn't even ask a question. I thought the best help I could offer was to explain the correct way to ask a question. If anyone has a problem with that, well too bad.

  10. #10
    PowerPoster
    Join Date
    Nov 2017
    Posts
    3,116

    Re: Trouble populating then updating a text box

    I was just trying to lighten the mood of the thread a bit, not attack you.

  11. #11
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,196

    Re: Trouble populating then updating a text box

    Quote Originally Posted by OptionBase1 View Post
    I was just trying to lighten the mood of the thread a bit, not attack you.
    Good to hear, I just get so emotional this time of year. lol

    As for the OP problem, tg may have been harsh but he did point out the most obvious flaw was using the TextChange event. Hope the OP noticed that.

  12. #12

    Thread Starter
    Lively Member
    Join Date
    Apr 2016
    Posts
    97

    Re: Trouble populating then updating a text box

    Quote Originally Posted by wes4dbt View Post
    Good to hear, I just get so emotional this time of year. lol

    As for the OP problem, tg may have been harsh but he did point out the most obvious flaw was using the TextChange event. Hope the OP noticed that.
    I did notice that, and so mentioned it in a previous post... My reason for posting here initially was to learn exactly how to work around this. I am not a pro in this VB arena... I am looking for the correct function or a suggestion of the proper procedure to reach my end goal which I have explained prior and will do so again if needed. I'm not asking anyone to write the code, I learn nothing if one does... Give me a sniff down the proper path...

    1. Suggest something more appropriate than TextChange.
    2. How can I write a value to the database, and automatically update to the 2 fields automatically to show the updated value as it happens... preferably not having to open and close an SQL connection multiple times.

    SQL I am not having issues with... it is the VB side of things where I struggle.

  13. #13
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: Trouble populating then updating a text box

    I'm not sure that any of us are pros in thee VB arena. Some of us ARE paid to code in the language, so we are technically pros, but most of us started out as hobbyists or self-taught, and have kind of evolved over time. Even after a few decades, I'm not sure I'd say I was a pro.

    TextChanged is an obvious thing to reach for, but it's almost always wrong. It just happens too much. It happens when you want it to, but it also happens when you don't want it to...and sometimes it doesn't happen when you do want it to. It's rarely the right choice, and that's kind of annoying because it sure SEEMS like it could be right.

    The problem may be that there isn't a good choice. When a person is entering something into a textbox, the problem is figuring out when they are done. Are they done after the first character? Well, they MAY be, but they may not. Are they done if they have stopped typing for a second or two? Well, probably, but perhaps they got called away, or are looking something up, and how would you count that pause anyways (it can be done, so don't answer that)? So, figuring out when a person is done entering something is hard to do. The most common way to do it is to have a SUBMIT, OK, or some other button. The real purpose of the button is to make the user take some action to indicate that they are done entering data. Another option is to try to handle lost focus, or perhaps validating, but both of these can be problematic. Thus there is no clear alternative, but TextChanged isn't right.

    Don't worry about opening and closing the SQL connection. That's remarkably cheap. Working with the DB isn't necessarily cheap, but opening and closing the DB is trivial in comparison, so don't worry about that part.

    I haven't read the original code enough to be certain, but it seems like you want to update a few fields, then get a sum back. It seems like you already have most of the sum before the update, so you might do the update AND add to some variable in code, rather than repeating the DB query. However, the way you are doing it would avoid certain types of code bugs. Also, I kind of feel like you showed us only a part of the code, which was the right thing to do, but knowing the whole thing, perhaps there's a better way. Perhaps all the values that go into the sum are all available in code. That would be common, but that would also allow for a much different design if you wanted it.

    So, I'd start out with what JMC has stated (many times, not just here): Lay out the steps in writing. I generally have a design document while working on a program. It's not a true design document, as it is basically just a semi-structured scratch pad of notes as I work through how I feel things should work. Since I often end up revising it, by the time the program is written, the document contains pretty much all of what it actually does along with a fair amount of what I thought it should do, but later decided it shouldn't do.
    My usual boring signature: Nothing

  14. #14
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: Trouble populating then updating a text box

    Quote Originally Posted by wes4dbt View Post
    Good to hear, I just get so emotional this time of year. lol
    Are you making a list? Checking it twice? Trying to decide who's naughty or nice?

    Or is it just THIS particular, miserable, year?
    My usual boring signature: Nothing

  15. #15

    Thread Starter
    Lively Member
    Join Date
    Apr 2016
    Posts
    97

    Re: Trouble populating then updating a text box

    Quote Originally Posted by Shaggy Hiker View Post
    I haven't read the original code enough to be certain, but it seems like you want to update a few fields, then get a sum back. It seems like you already have most of the sum before the update, so you might do the update AND add to some variable in code, rather than repeating the DB query. However, the way you are doing it would avoid certain types of code bugs. Also, I kind of feel like you showed us only a part of the code, which was the right thing to do, but knowing the whole thing, perhaps there's a better way. Perhaps all the values that go into the sum are all available in code. That would be common, but that would also allow for a much different design if you wanted it.
    I did sort of a 'mapping' as it were and is as follows:

    GOAL: After inputting a value in a text box that value writes to the DB
    Once value is written, the value needs to be added to another column (INVOICE_TOTAL) in the DB then updated on the form.
    If a specific checkbox is checked (actually a DropDown box with Yes, No, N/A as options), the input value also needs to write to a second column in the DB (WARRANTY_TOTAL) then updated on the form.
    Use as few open close SQL connections as possible.

    EXAMPLE: User inputs 1200.00 in the TruckRepairCost textbox. Both the TruckRepairCost and InvoiceTotal textboxes then should reflect the 1200.00.
    User then inputs 300.00 into the TrailerRepairCost textbox.
    InvoiceTotal textbox now reads 1500.00, TruckRepairCost textbox shows 1200.00 and TrailerRepairCost textbox shows 300.00
    User selects 'Yes' in the warranty repair DropDown box for the trailer repair. WarrantyTotal textbox now should show 300.00, InvoiceTotal should now read 1200.00, TruckRepairCost shows 1200.00 and TrailerRepairCost shows 300.00

    I had considered LostFocus as well, but again, not entirely sure if this would work as intended.

  16. #16
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: Trouble populating then updating a text box

    Quote Originally Posted by K3JAE View Post
    I have 3 forms that share 2 common fields. On each page I will input an amount (money) in various fields and I am needing it to update the DB then refill the common text boxes with the updated amount.

    Code (a partial) of what I am trying to do and maybe someone can see where I am going off the beaten path.

    did you see the comment in Post#3 ?

    the TextChanged should not be used for a Update
    did you try and place the Update Command in a Button and Execute?
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  17. #17

    Thread Starter
    Lively Member
    Join Date
    Apr 2016
    Posts
    97

    Re: Trouble populating then updating a text box

    Quote Originally Posted by ChrisE View Post
    did you see the comment in Post#3 ?

    the TextChanged should not be used for a Update
    did you try and place the Update Command in a Button and Execute?
    Yes saw Post 3 and addressed it.

    Have not tried a button simply for lack of real estate on the form. I may try to re-work the page design for that but not sure I'm gonna be able to free up enough space for same.

  18. #18
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: Trouble populating then updating a text box

    Okay, that layout looks reasonable, to me. I would be inclined to split it into two parts, but I'm not saying that it is necessarily right to do so.

    What I'd be doing is holding the sum as it currently stands, in a variable at form level. When the user enters something, that gets added to that variable as one step, and gets updated to the database as a second step. This would be somewhat more efficient, but the way you laid it out would be somewhat safer. After all, you are querying what is in the DB. If anything goes wrong with writing to the DB, then what you are showing is what is really in there. With the approach I was suggesting, there could be a difference between what is in the DB and what is in the form level sum variable, because either the update didn't write, or there was a bug in the code.

    Either way, I would say that the best option is to require the user to press a button. At least, I would say that's the best option if they will be only entering ONE number at a time. If they might enter a series of numbers, one after the other, then I would use a grid tied to a datatable, but if it is just one number, then I'd require a button.
    My usual boring signature: Nothing

  19. #19
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,196

    Re: Trouble populating then updating a text box

    Quote Originally Posted by Shaggy Hiker View Post
    Are you making a list? Checking it twice? Trying to decide who's naughty or nice?

    Or is it just THIS particular, miserable, year?
    Actually I lied, it's January that I get emotional. That's when I get my credit card bills from Christmas.

  20. #20

    Thread Starter
    Lively Member
    Join Date
    Apr 2016
    Posts
    97

    Re: Trouble populating then updating a text box

    Quote Originally Posted by Shaggy Hiker View Post
    Okay, that layout looks reasonable, to me. I would be inclined to split it into two parts, but I'm not saying that it is necessarily right to do so.

    What I'd be doing is holding the sum as it currently stands, in a variable at form level. When the user enters something, that gets added to that variable as one step, and gets updated to the database as a second step. This would be somewhat more efficient, but the way you laid it out would be somewhat safer. After all, you are querying what is in the DB. If anything goes wrong with writing to the DB, then what you are showing is what is really in there. With the approach I was suggesting, there could be a difference between what is in the DB and what is in the form level sum variable, because either the update didn't write, or there was a bug in the code.

    Either way, I would say that the best option is to require the user to press a button. At least, I would say that's the best option if they will be only entering ONE number at a time. If they might enter a series of numbers, one after the other, then I would use a grid tied to a datatable, but if it is just one number, then I'd require a button.
    OK, so in a nutshell, you are also leaning towards using a button as well on the form to commit and run the SQL after all information is entered on that page. Am I on track here? Entry would be a series of numbers per textbox (ex 100.00, 1000.00 etc). To be honest I am not sure how to 'hold the sum in a variable form' so unless you can guide me in that direction I may have to squeeze a 'enter' button on the screen somewhere.

  21. #21

    Thread Starter
    Lively Member
    Join Date
    Apr 2016
    Posts
    97

    Re: Trouble populating then updating a text box

    Quote Originally Posted by wes4dbt View Post
    Actually I lied, it's January that I get emotional. That's when I get my credit card bills from Christmas.
    Now you gone and done it.. reminding me of the outflow of cash after the new year... Thanks.... Just crashed my Christmas spirit for the remainder of the year!!

  22. #22

    Thread Starter
    Lively Member
    Join Date
    Apr 2016
    Posts
    97

    Re: Trouble populating then updating a text box

    Just as a quick follow-up and request for corrections as required. I condensed my code just a tad.

    While still sitting as a private sub LostFocus, I will attempt to put this in a button click event but for now, does this seem reasonable and is there a way to do this all within one connection as opposed to two?

    Code:
        Private Sub TruckRepairCost_LostFocus(sender As Object, e As EventArgs) Handles tbTruckRepairCost.LostFocus
            ' Write value of Truck Repair to DB (unsure yet if will need to add to existing number. May need: (TRUCK_REPAIR_COST = TRUCK_REPAIR_COST + @upd))
            Try
                cmd.Parameters.Clear()
                cmd.CommandType = CommandType.Text
                cmd.CommandText = ("UPDATE Maintenance_Table 
                                       SET TRUCK_REPAIR_COST = @upd
                                     WHERE PETS_LOAD_NUMBER = @ln")
    
                cmd.Parameters.AddWithValue("@upd", tbTruckRepairCost.Text)
                cmd.Parameters.AddWithValue("@ln", tbPetLoadNumb.Text)
    
                cmd.CommandType = CommandType.Text
                cmd.Connection = New SqlConnection With {
                       .ConnectionString = My.MySettings.Default.PETS_DatabaseConnectionString
                 }
                cmd.Connection.Open()
                cmd.ExecuteNonQuery()
                cmd.Connection.Close()
    
            Catch ex As Exception
                'If a calculation error occurs, show Error message box
                Dim frm As New MeMsgCalcError(ex, "'Truck Repair Cost' DB Write Error." & vbCrLf & "Lines 126-148")
                frm.Show()
            End Try
    
            Dim a, b As Decimal
            ' If warranty checkbox is checked, also write the value of tbTruckRepairCost.Text to the Warranty Column of the DB
            Try
                b = CDec(IIf(tbTruckRepairCost.Text.Trim = "", 0D, tbTruckRepairCost.Text.Trim))
    
                If cbTrkMaintWarranty.Text = "No" Or cbTrkMaintWarranty.Text = "N / A" Then
                    tbTruckRepairCost.Text = b.ToString("C2")
                ElseIf cbTrkMaintWarranty.Text = "Yes" Then
                    a = b
                    tbTruckRepairCost.Text = b.ToString("C2")
    
                    Try
                        Select Case True
                            Case a > 0D
                                cmd.Parameters.Clear()
                                cmd.CommandType = CommandType.Text
                                cmd.CommandText = ("UPDATE Maintenance_Table 
                                                       SET WARRANTY_TOTAL = WARRANTY_TOTAL + @upd
                                                     WHERE PETS_LOAD_NUMBER = @ln")
    
                                cmd.Parameters.AddWithValue("@upd", a)
                                cmd.Parameters.AddWithValue("@ln", tbPetLoadNumb.Text)
    
                                cmd.CommandType = CommandType.Text
                                cmd.Connection = New SqlConnection With {
                                       .ConnectionString = My.MySettings.Default.PETS_DatabaseConnectionString
                                 }
                                cmd.Connection.Open()
                                cmd.ExecuteNonQuery()
                                cmd.Connection.Close()
    
                            Case Else
                                Exit Sub
                        End Select
    
                    Catch ex As Exception
                        MessageBox.Show(ex.Message)
                    End Try
                End If
    
            Catch ex As Exception
                'If a calculation error occurs, show Error message box
                Dim frm As New MeMsgCalcError(ex, "'Truck Repair Warranty' DB write Error." & vbCrLf & "Lines 161-189")
                frm.Show()
            End Try
    
            Calc()
        End Sub

  23. #23
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,196

    Re: Trouble populating then updating a text box

    Sure you can. Noticed your using two Try/Catch structures, do you really want to continue to the second Try/Catch if there is an error in the first?

    How about,

    Code:
        Private Sub TruckRepairCost_LostFocus(sender As Object, e As EventArgs) Handles tbTruckRepairCost.LostFocus
            ' Write value of Truck Repair to DB (unsure yet if will need to add to existing number. May need: (TRUCK_REPAIR_COST = TRUCK_REPAIR_COST + @upd))
            Try
                cmd.Connection = New SqlConnection With {
                       .ConnectionString = My.MySettings.Default.PETS_DatabaseConnectionString
                cmd.Connection.Open()
            Catch ex as Exception
                MessageBox.Show (ex.ToString)
                Exit Sub
            End Try
            Try
                cmd.Parameters.Clear()
                cmd.CommandType = CommandType.Text
                cmd.CommandText = ("UPDATE Maintenance_Table 
                                       SET TRUCK_REPAIR_COST = @upd
                                     WHERE PETS_LOAD_NUMBER = @ln")
    
                cmd.Parameters.AddWithValue("@upd", tbTruckRepairCost.Text)
                cmd.Parameters.AddWithValue("@ln", tbPetLoadNumb.Text)
    
                cmd.CommandType = CommandType.Text
    
                 }
                
                cmd.ExecuteNonQuery()
               
    
            Catch ex As Exception
                'If a calculation error occurs, show Error message box
                Dim frm As New MeMsgCalcError(ex, "'Truck Repair Cost' DB Write Error." & vbCrLf & "Lines 126-148")
                frm.Show()
            End Try
    
            Dim a, b As Decimal
            ' If warranty checkbox is checked, also write the value of tbTruckRepairCost.Text to the Warranty Column of the DB
            Try
                b = CDec(IIf(tbTruckRepairCost.Text.Trim = "", 0D, tbTruckRepairCost.Text.Trim))
    
                If cbTrkMaintWarranty.Text = "No" Or cbTrkMaintWarranty.Text = "N / A" Then
                    tbTruckRepairCost.Text = b.ToString("C2")
                ElseIf cbTrkMaintWarranty.Text = "Yes" Then
                    a = b
                    tbTruckRepairCost.Text = b.ToString("C2")
    
                    Try
                        Select Case True
                            Case a > 0D
                                cmd.Parameters.Clear()
                                cmd.CommandType = CommandType.Text
                                cmd.CommandText = ("UPDATE Maintenance_Table 
                                                       SET WARRANTY_TOTAL = WARRANTY_TOTAL + @upd
                                                     WHERE PETS_LOAD_NUMBER = @ln")
    
                                cmd.Parameters.AddWithValue("@upd", a)
                                cmd.Parameters.AddWithValue("@ln", tbPetLoadNumb.Text)
    
                                cmd.CommandType = CommandType.Text
    
                                 }
                                
                                cmd.ExecuteNonQuery()
                                
    
                            Case Else
                                Exit Sub
                        End Select
    
                    Catch ex As Exception
                        MessageBox.Show(ex.Message)
                    End Try
                End If
    
            Catch ex As Exception
                'If a calculation error occurs, show Error message box
                Dim frm As New MeMsgCalcError(ex, "'Truck Repair Warranty' DB write Error." & vbCrLf & "Lines 161-189")
                frm.Show()
            End Try
    
            Calc()
    
             cmd.Connection.Close()
        End Sub
    This wouldn't be my choice of methods.

    Since you seem to have declared "cmd" as a form level object, I would also create the connection at the form level.

    Code:
                Private con as New SqlConnection(My.MySettings.Default.PETS_DatabaseConnectionString)
    Then open it in the Form Load event and close it on the form closing event.

    Better yet would be to create these objects in the Sub. Probably wouldn't make much difference in your case but it would a better technique. that said I've created a lot of forms with the connection, command. dataadapter and datatables were declared at the form level.

    I would also add, you shouldn't use a Select Case structure if there is only 1 Case, why not a simple If/Then
    Last edited by wes4dbt; Dec 18th, 2020 at 03:12 PM.

  24. #24

    Thread Starter
    Lively Member
    Join Date
    Apr 2016
    Posts
    97

    Re: Trouble populating then updating a text box

    Quote Originally Posted by wes4dbt View Post
    Sure you can. Noticed your using two Try/Catch structures, do you really want to continue to the second Try/Catch if there is an error in the first?

    How about,

    Code:
        Private Sub TruckRepairCost_LostFocus(sender As Object, e As EventArgs) Handles tbTruckRepairCost.LostFocus
            ' Write value of Truck Repair to DB (unsure yet if will need to add to existing number. May need: (TRUCK_REPAIR_COST = TRUCK_REPAIR_COST + @upd))
            Try
                cmd.Connection = New SqlConnection With {
                       .ConnectionString = My.MySettings.Default.PETS_DatabaseConnectionString
                cmd.Connection.Open()
            Catch ex as Exception
                MessageBox.Show (ex.ToString)
                Exit Sub
            End Try
            Try
                cmd.Parameters.Clear()
                cmd.CommandType = CommandType.Text
                cmd.CommandText = ("UPDATE Maintenance_Table 
                                       SET TRUCK_REPAIR_COST = @upd
                                     WHERE PETS_LOAD_NUMBER = @ln")
    
                cmd.Parameters.AddWithValue("@upd", tbTruckRepairCost.Text)
                cmd.Parameters.AddWithValue("@ln", tbPetLoadNumb.Text)
    
                cmd.CommandType = CommandType.Text
    
                 }
                
                cmd.ExecuteNonQuery()
               
    
            Catch ex As Exception
                'If a calculation error occurs, show Error message box
                Dim frm As New MeMsgCalcError(ex, "'Truck Repair Cost' DB Write Error." & vbCrLf & "Lines 126-148")
                frm.Show()
            End Try
    
            Dim a, b As Decimal
            ' If warranty checkbox is checked, also write the value of tbTruckRepairCost.Text to the Warranty Column of the DB
            Try
                b = CDec(IIf(tbTruckRepairCost.Text.Trim = "", 0D, tbTruckRepairCost.Text.Trim))
    
                If cbTrkMaintWarranty.Text = "No" Or cbTrkMaintWarranty.Text = "N / A" Then
                    tbTruckRepairCost.Text = b.ToString("C2")
                ElseIf cbTrkMaintWarranty.Text = "Yes" Then
                    a = b
                    tbTruckRepairCost.Text = b.ToString("C2")
    
                    Try
                        Select Case True
                            Case a > 0D
                                cmd.Parameters.Clear()
                                cmd.CommandType = CommandType.Text
                                cmd.CommandText = ("UPDATE Maintenance_Table 
                                                       SET WARRANTY_TOTAL = WARRANTY_TOTAL + @upd
                                                     WHERE PETS_LOAD_NUMBER = @ln")
    
                                cmd.Parameters.AddWithValue("@upd", a)
                                cmd.Parameters.AddWithValue("@ln", tbPetLoadNumb.Text)
    
                                cmd.CommandType = CommandType.Text
    
                                 }
                                
                                cmd.ExecuteNonQuery()
                                
    
                            Case Else
                                Exit Sub
                        End Select
    
                    Catch ex As Exception
                        MessageBox.Show(ex.Message)
                    End Try
                End If
    
            Catch ex As Exception
                'If a calculation error occurs, show Error message box
                Dim frm As New MeMsgCalcError(ex, "'Truck Repair Warranty' DB write Error." & vbCrLf & "Lines 161-189")
                frm.Show()
            End Try
    
            Calc()
    
             cmd.Connection.Close()
        End Sub
    This wouldn't be my choice of methods.

    Since you seem to have declared "cmd" as a form level object, I would also create the connection at the form level.

    Code:
                Private con as New SqlConnection(My.MySettings.Default.PETS_DatabaseConnectionString)
    Then open it in the Form Load event and close it on the form closing event.

    Better yet would be to create these objects in the Sub. Probably wouldn't make much difference in your case but it would a better technique. that said I've created a lot of forms with the connection, command. dataadapter and datatables were declared at the form level.

    I would also add, you shouldn't use a Select Case structure if there is only 1 Case, why not a simple If/Then
    Couple of issues... I noticed you have 2 'stray' closing brackets '}' that needed to go. Secondly, after making that edit, testing, during save I immediately end up with "CONCURRENCY Violation: the UpdateCommand affected 0 of the 1 records". Does not elude to which update command it is erroring on. Could it be an issue of it trying to run 2 update commands on the same open connection?

  25. #25
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,196

    Re: Trouble populating then updating a text box

    You need to post your current code and indicate where the error occurs. We have no idea what you've done since the last code you posted.

  26. #26

    Thread Starter
    Lively Member
    Join Date
    Apr 2016
    Posts
    97

    Re: Trouble populating then updating a text box

    Quote Originally Posted by wes4dbt View Post
    You need to post your current code and indicate where the error occurs. We have no idea what you've done since the last code you posted.
    It is posted - 3 or 4 messages up.

    I tried the code change as submitted just above this message and thus I ended up with the concurrency violation. Nothing else has been changed other than what I most recently posted a few posts prior and the 'suggested code' just above.

  27. #27
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,196

    Re: Trouble populating then updating a text box

    If you don't want to post your current code and explain where the error occurs that's fine but I can't help you. The code I posted was meant to be an example and not cut and pasted. There is not way for me to test it. But I know the concept works.

    This works,
    Code:
        Private con As New SqlConnection(My.Settings.BooksDBConnectionString)
        Private da As New SqlDataAdapter("Select bookId, bookname from books", con)
        Private cmd As New SqlCommand
        Private dt As New DataTable
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            da.Fill(dt)
            Me.DataGridView1.DataSource = dt
            con.Open()
            cmd.Connection = con
            cmd.CommandText = "Update books set bookname = 'bob' Where BookId = 22"
            cmd.ExecuteNonQuery()
    
            cmd.CommandText = "Update books set bookname = 'wes' Where BookId = 3"
            cmd.ExecuteNonQuery()
        End Sub
    I just added the dataadpter and datatable to verify the data updated correctly.

    good luck

  28. #28

    Thread Starter
    Lively Member
    Join Date
    Apr 2016
    Posts
    97

    Re: Trouble populating then updating a text box

    Quote Originally Posted by wes4dbt View Post
    If you don't want to post your current code and explain where the error occurs that's fine but I can't help you. The code I posted was meant to be an example and not cut and pasted. There is not way for me to test it. But I know the concept works.
    Sorry, I did not mean to assume I would not post my code but that I used your original example and it did not worked so I reverted back to my original. I will continue to work on this a bit now that you have given a few ideas. I am coding in a button at the moment so I'll be a bit. Once I get it figured out, I promise to repost what I have and let you know if or if not working.

  29. #29

    Thread Starter
    Lively Member
    Join Date
    Apr 2016
    Posts
    97

    Re: Trouble populating then updating a text box

    OK, adding a button with the code adjusted works perfectly - adds the value to the correct columns and even works and adds the total to the Warranty column. With that being resolved ((thanks for the button suggestion as well as the idea to open one connection and run both updates individually).

    LAST thing I need help in is the calc() area. I am returning nothing back. The SQL ran in MSSMS returns 2 values back (both correct), as it should. I suspect my issue lies in the read area where I pull the values from the DB. It should be reading a decimal value in. Then it is converted to a string value to populate the textbox. The textboxes are both formatted as 'currency 2 dec' Is my read statement incorrect? Running Debug shows the proper return form the database... in other words tbInvoiceTotal.Text shows to be coming back at $300.00 and tbWarrantyTotal.Text shows $100.00 (TEST NUMBERS). Yet the end result on the form is 000.0000 on both the textboxes. Suggestions as to what I need to do to correct?

    The code:

    Code:
    Private Sub Calc()
            If tbPetLoadNumb.Text.Length = 0 Then
                Exit Sub
            End If
    
            Dim cmd As New SqlCommand
            Dim reader As SqlDataReader
    
            Try
                cmd.Parameters.Clear()
                ' Get running total of the REPAIR total first followed by a running total of the Warranty Total
                cmd.CommandText = "SELECT SUM(IsNull(APU_PM_COST, 0)
                                            + IsNull(APU_REPAIR_COST, 0)
                                            + IsNull(BODY_REPAIR_COST, 0)
                                            + IsNull(TOW_COST, 0)
                                            + IsNull(TRAILER_REPAIR_COST, 0)
                                            + IsNull(TRUCK_REPAIR_COST, 0)
                                            + IsNull(TRUCK_PM_COST, 0)
                                            - IsNull(WARRANTY_TOTAL, 0))
                                     FROM Maintenance_Table
                                    WHERE PETS_LOAD_NUMBER = @ln
    
                                    UNION
                  
                                   SELECT IsNull(WARRANTY_TOTAL, 0)
                                     FROM Maintenance_Table
                                    WHERE PETS_LOAD_NUMBER = @ln"
    
                cmd.Parameters.AddWithValue("@ln", tbPetLoadNumb.Text)
    
                cmd.CommandType = CommandType.Text
                cmd.Connection = New SqlConnection With {
                    .ConnectionString = My.MySettings.Default.PETS_DatabaseConnectionString
                }
                cmd.Connection.Open()
    
                Try
                    reader = cmd.ExecuteReader()
                    Dim Cost As Decimal
                    Dim Warranty As Decimal
    
                    With reader
                        .Read()
    
                        'Both must be GetDecimal(0) as only one result per SELECT is returned.
                        Cost = .GetDecimal(0)
                        Warranty = .GetDecimal(0)
    
                        tbInvoiceTotal.Text = CStr(Cost)
                        tbWarrantyTotal.Text = CStr(Warranty)
    
                        Return
    
                    End With
    
                    reader.Close()
    
                Catch ex As Exception
                    'If a calculation error occurs, show Error message box
                    Dim frm As New MeMsgCalcError(ex, "'Invoice and Warranty Total' Calculation Error." & vbCrLf & "Lines 127-178")
                    frm.Show()
                End Try
    
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
            cmd.Connection.Close()
        End Sub
    Last edited by K3JAE; Dec 18th, 2020 at 07:19 PM.

  30. #30
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,196

    Re: Trouble populating then updating a text box

    After reading your post a couple of times I'm not sure if your returning any values or not. You say it does went you check it using the debugger but that nothing shows up in the textboxes, I think that's what your saying.

    Well, a couple of things.

    Your assigning the same value to both varibles,
    Code:
    Cost = .GetDecimal(0)
                        Warranty = .GetDecimal(0)
    Second, check if returning any rows, here is a simple example

    Code:
            con.Open()
            cmd.Connection = con
            cmd.CommandText = "select grossacres, netacres from lots"
            Dim rdr As OleDbDataReader
    
            rdr = cmd.ExecuteReader
            If rdr.HasRows Then
                rdr.Read()
                Me.TextBox2.Text = rdr(0).ToString
                Me.TextBox3.Text = rdr(1).ToString
            Else
                MessageBox.Show("No rows")
            End If
    Also, no need to create the variables at all if all you want to do is display the data in TextBoxes.

  31. #31
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: Trouble populating then updating a text box

    Quote Originally Posted by K3JAE View Post
    OK, adding a button with the code adjusted works perfectly - adds the value to the correct columns and even works and adds the total to the Warranty column. With that being resolved ((thanks for the button suggestion as well as the idea to open one connection and run both updates individually).

    LAST thing I need help in is the calc() area. I am returning nothing back. The SQL ran in MSSMS returns 2 values back (both correct), as it should. I suspect my issue lies in the read area where I pull the values from the DB. It should be reading a decimal value in. Then it is converted to a string value to populate the textbox. The textboxes are both formatted as 'currency 2 dec' Is my read statement incorrect? Running Debug shows the proper return form the database... in other words tbInvoiceTotal.Text shows to be coming back at $300.00 and tbWarrantyTotal.Text shows $100.00 (TEST NUMBERS). Yet the end result on the form is 000.0000 on both the textboxes. Suggestions as to what I need to do to correct?

    The code:

    Code:
                cmd.Parameters.Clear()
                ' Get running total of the REPAIR total first followed by a running total of the Warranty Total
                cmd.CommandText = "SELECT SUM(IsNull(APU_PM_COST, 0)
                                            + IsNull(APU_REPAIR_COST, 0)
                                            + IsNull(BODY_REPAIR_COST, 0)
    '... etc...
    I don't get this Sql
    you are checking for Null Values = IsNull ?

    the result will obviously be Null
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  32. #32
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Trouble populating then updating a text box

    Quote Originally Posted by ChrisE View Post
    I don't get this Sql
    you are checking for Null Values = IsNull ?

    the result will obviously be Null
    Actually the IsNull function used there is basically "If (field) Is Null Then 0 Else (field)"

    So it is just a way to avoid returning Null - any rows that are Null will be treated as 0 instead. In this case it means that the Sum will always return a number, even if any of the records have a Null for any of the relevant fields.

  33. #33

    Thread Starter
    Lively Member
    Join Date
    Apr 2016
    Posts
    97

    Re: Trouble populating then updating a text box

    NO, if the specific value in a record is NULL assign it '0' - this prevents a null read / calc error exactly as si_the_geek notes. SQL I know, VB . not so much... LoL

  34. #34

    Thread Starter
    Lively Member
    Join Date
    Apr 2016
    Posts
    97

    Re: Trouble populating then updating a text box

    Quote Originally Posted by wes4dbt View Post
    After reading your post a couple of times I'm not sure if your returning any values or not. You say it does went you check it using the debugger but that nothing shows up in the textboxes, I think that's what your saying.

    Well, a couple of things.

    Your assigning the same value to both varibles,
    Code:
    Cost = .GetDecimal(0)
                        Warranty = .GetDecimal(0)
    Second, check if returning any rows, here is a simple example

    Code:
            con.Open()
            cmd.Connection = con
            cmd.CommandText = "select grossacres, netacres from lots"
            Dim rdr As OleDbDataReader
    
            rdr = cmd.ExecuteReader
            If rdr.HasRows Then
                rdr.Read()
                Me.TextBox2.Text = rdr(0).ToString
                Me.TextBox3.Text = rdr(1).ToString
            Else
                MessageBox.Show("No rows")
            End If
    Also, no need to create the variables at all if all you want to do is display the data in TextBoxes.
    Actually the way I have it is correct.... There are 2 separate SQL statements put together by a UNION Each statement returns a single 'column' or value... thus result '0'
    As an added Caveat - I did exactly as you suggested prior to current and that resulted in bad things.

    In DEBUG the values show properly... they are not being put into the form in their proper location AFTER I make my edits to a field AND then Rerun CALC. So I need to figure out what is missing.

    I'm sort of out of touch right now as at my In-Laws 3 states away and Internet is limited and access to my SQL Tables is not available.

  35. #35
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,196

    Re: Trouble populating then updating a text box

    Actually the way I have it is correct.... There are 2 separate SQL statements put together by a UNION Each statement returns a single 'column' or value... thus result '0'
    Your code is not correct, unless you want the same value in both variables. Then why do the Union, you are assigning the SAME value to both variables

    Code:
    Cost = .GetDecimal(0)
    Warranty = .GetDecimal(0)
    If you want both values then you need to do a second Read

    Code:
    Cost = .GetDecimal(0)
    .Read
    Warranty = .GetDecimal(0)
    I tested this example,
    Code:
            cmd.CommandText = "select grossacres from lots where lotid='AC010' Union select netacres from lots where lotid='AC010'"
            Dim rdr As OleDbDataReader
    
            rdr = cmd.ExecuteReader
            If rdr.HasRows Then
                rdr.Read()
                Me.TextBox2.Text = rdr(0).ToString
                rdr.Read
                Me.TextBox3.Text = rdr(0).ToString
            Else
                MessageBox.Show("No rows")
            End If

  36. #36
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: Trouble populating then updating a text box

    Another point that you may or may not care about is that if you use a textbox, the user will expect that they can change the value (which they can), and have it do something meaningful (which....well, does it? It doesn't seem like it should, in your case). So, a label might make more sense than a textbox.

    Yet another is that you might consider using a NumericUpDown control rather than a textbox. You can type into both, but you can only type numbers into a NUD, and numbers with the right format (two decimal places, I would assume, in your case). With a textbox, you have to do a bunch of validation, or the user could just as easily type in something that will cause trouble, such as a non-numeric character that will then cause an exception when the string is used as a number. There are more obscure validation scenarios you'd have to deal with when using a textbox, too. They do look a bit better, in my opinion, but they aren't great for numbers.
    My usual boring signature: Nothing

  37. #37

    Thread Starter
    Lively Member
    Join Date
    Apr 2016
    Posts
    97

    Re: Trouble populating then updating a text box

    Quote Originally Posted by Shaggy Hiker View Post
    Another point that you may or may not care about is that if you use a textbox, the user will expect that they can change the value (which they can), and have it do something meaningful (which....well, does it? It doesn't seem like it should, in your case). So, a label might make more sense than a textbox.
    This is a solid suggestion and thus I shall indeed swap this over to a label. No user should be able to change this so your point is well taken.

    Quote Originally Posted by Shaggy Hiker View Post
    Yet another is that you might consider using a NumericUpDown control rather than a textbox. You can type into both, but you can only type numbers into a NUD, and numbers with the right format (two decimal places, I would assume, in your case). With a textbox, you have to do a bunch of validation, or the user could just as easily type in something that will cause trouble, such as a non-numeric character that will then cause an exception when the string is used as a number. There are more obscure validation scenarios you'd have to deal with when using a textbox, too. They do look a bit better, in my opinion, but they aren't great for numbers.
    I've never tried a NUD control, but definitely something I will look into. I'll experiment with this.

  38. #38

    Thread Starter
    Lively Member
    Join Date
    Apr 2016
    Posts
    97

    Re: Trouble populating then updating a text box

    OK, in relation to the above.. This confuses me then...

    When I run the SQL in MSSMS it returns the 2 proper values side by side. I tried coding initially this way and this did not work whatsoever.

    So as a shot in the dark I tried it the way I had originally (item(0), item(0)) and it appeared to return back values properly.

    My assumption then came out that a SELECT statement UNIONED to a 2nd Select brings back one result each, thus item(0) and item(0).

    I do not fully understand why the 2nd read (in no way am I questioning the validity just asking for clarification for future understanding).

    This will have to rest until I return back home in a week or so... however I am adapting the code to your suggested way of doing things and will update as soon as I can test it on a live DB.

    Much appreciated the ideas and thoughts.

  39. #39
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Trouble populating then updating a text box

    You will repeatedly get values from the same record, unless you use .Read to move to the next record.

  40. #40

    Thread Starter
    Lively Member
    Join Date
    Apr 2016
    Posts
    97

    Re: Trouble populating then updating a text box

    Quote Originally Posted by wes4dbt View Post
    Your code is not correct, unless you want the same value in both variables. Then why do the Union, you are assigning the SAME value to both variables

    Code:
    Cost = .GetDecimal(0)
    Warranty = .GetDecimal(0)
    If you want both values then you need to do a second Read

    Code:
    Cost = .GetDecimal(0)
    .Read
    Warranty = .GetDecimal(0)
    I tested this example,
    Code:
            cmd.CommandText = "select grossacres from lots where lotid='AC010' Union select netacres from lots where lotid='AC010'"
            Dim rdr As OleDbDataReader
    
            rdr = cmd.ExecuteReader
            If rdr.HasRows Then
                rdr.Read()
                Me.TextBox2.Text = rdr(0).ToString
                rdr.Read
                Me.TextBox3.Text = rdr(0).ToString
            Else
                MessageBox.Show("No rows")
            End If
    The message just above refers to this post... Sorry, thought I had done a quote.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width