VB Always tries to edit first row

[tekgod01]

I have a program. No, really, I do!

 

The user inputs a part number into a textbox. The program goes into an SQL database and returns the other rows (based on the part number) and fills in textboxes and radio buttons. No problem.

 

The user can also put in a new part number. When finished, the user clicks on a button and the new part number is inserted into the database. No problem.

 

The user can also edit a part number. Any information (other than the part number) can be changed. The user clicks on a button and the part number is edited. Problem.

 

Every time (yes, every time) the user tries to edit a part number, it deletes the first row of the database and then tries to insert the edited part number as a new part number. Here's my error:

An unhandled exception of type 'System.Data.ConstraintException' occurred in system.data.dll

Additional information: Column 'PartNumber' is constrained to be unique.  Value '10101' is already present.

 

I've looked and tried changing all kinds of settings and code lines but the error never changes (other than the Value '10101' <--is part number).

 

Here's the code behind the curtain.

   Private Sub btnAddChange_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddChange.Click
       'What the program does if the part information is new
       If btnAddChange.Text = "Add New Part" Then
           'Creates new row
           Dim NewRow As DataRow
           NewRow = DataSet1.Tables("SideLabels").NewRow
           With NewRow
               'Adds the information to the new row
               .Item("PartNumber") = Me.txtPartNumber.Text
               .Item("Quantity") = Me.txtQuantity.Text
               .Item("Weight") = Me.txtWeight.Text
               .Item("ULCert") = Me.lblULRU.Text
               .Item("CSACert") = Me.lblCSA.Text
               .Item("Supplier") = Me.txtSupplier.Text
               .Item("Revision") = Me.txtRevision.Text
               .Item("Description") = Me.txtDescription.Text
               .Item("WireGauge") = Me.txtWireGauge.Text
               .Item("HAPNumber") = Me.txtHAPNumber.Text
           End With
           'Sends the new row to the SQL server
           DataSet1.Tables("SideLabels").Rows.Add(NewRow)
           'Updates the program's database to include the new row
           SqlDataAdapter1.Update(DataSet1)
       End If
       'What the program does if the part information is existing
       If btnAddChange.Text = "Accept Changes" Then
           'Finds row that is being updated
           Dim searchPart As String = txtPartNumber.Text
            Dim CurrentRow As DataRow
           CurrentRow = DataSet1.Tables("SideLabels").Rows(Me.BindingContext(Me.DataSet1.Tables("SideLabels")).Position)
           With CurrentRow
               'Changes the information to the row
               .BeginEdit()
               .Item("PartNumber") = Me.txtPartNumber.Text
               .Item("Quantity") = Me.txtQuantity.Text
               .Item("Weight") = Me.txtWeight.Text
               .Item("ULCert") = Me.lblULRU.Text
               .Item("CSACert") = Me.lblCSA.Text
               .Item("Supplier") = Me.txtSupplier.Text
               .Item("Revision") = Me.txtRevision.Text
               .Item("Description") = Me.txtDescription.Text
               .Item("WireGauge") = Me.txtWireGauge.Text
               .Item("HAPNumber") = Me.txtHAPNumber.Text
               .EndEdit()
           End With
           'Updates the SQL server with the changed information
           DataSet1.Tables("SideLabels").AcceptChanges()
           'Updates the program to include the changed information
           SqlDataAdapter1.Update(DataSet1.SideLabels)
       End If
       'Clears all the textboxes (see txtPartNumber_LostFocus)
       txtPartNumber.Text = ""
       txtPartNumber.Focus()
       btnExit.Focus()
       SqlDataAdapter1.Update(DataSet1.SideLabels)
       txtPartNumber.Focus()
   End Sub

 

Obviously, I'm missing something or the code is written wrong. Please - I'm begging you - help me!

 

Thank you.

[betrl8thanever]

I think your problem is in that you're trying to change a unique identifier, probably the part number, from the looks of it. Because of the 'BeginEdit()' it's chaning realtime in the dataset. The dataset is enforcing the contraints of the database. You'll either have to:

 

Change the unique identifier to something else - something that can be unique

 

-or-

Remove the Begin edit and just update once everything has been changed (don't think this will solve it, but it's worth a shot)

 

-or-

 

Not allow the user to change that field.

[pelikan]

for starters - in the 'Accept Changes' subsection (bad programming style, better

to factor out into separate functions) you declare a variable: Dim SearchPart but

you don't use it. You just grab whatever the CurrencyManager happens to be sitting on (why bother with a CurrencyManager if you don't uses UI databinding?).

 

Your code as deeply flawed.

Besides this - it's necessary to see the updating logic of the Adapter's Commands

to know what's going on.

[betrl8thanever]

Your code as deeply flawed.

 

I'm sure what you meant was that the code has some small but very fixable issues, and that we are more than happy to help work through them to resolution. Right?!

;)

[pelikan]

Critical thinking doesn't ask for approval nor does it spare it's target.

[tekgod01]

for starters - in the 'Accept Changes' subsection (bad programming style, better

to factor out into separate functions) you declare a variable: Dim SearchPart but

you don't use it. You just grab whatever the CurrencyManager happens to be sitting on (why bother with a CurrencyManager if you don't uses UI databinding?).

 

Ok, I've taken out the searchPart line (I noticed it wasn't used for anything after I had posted the code). You didn't say anything about CurrentRow so I assume that this line is ok (along with the "With..." statements) and I left them alone. Here's my dim expressions as of now:

 

dim CurrentRow as DataRow
dim myCurrencyManager as CurrencyManager = CType(Me.BindingContext(DataSet1), CurrencyManager)

 

I know that I have to do something with the currency manager, but Microsoft's help doesn't actually help. Go figure. ;) It looks like I need some real help in this area.

 

My SqlDataAdapter1 and DataSet1 were set up using the wizard within VB.

 

Towards the end of the code, I have ".AcceptChanges". Should I change this to Update?

 

Your code as deeply flawed.

I know.

It's my first real program (other than Yahtzee for class). I expected my code to be ugly as I'm learning all this as I'm coding it. I also have a couple of Wrox books but they're a bit confusing if you don't start at the very beginning. I want to learn what they're talking about in Chapter 11. In order to understand it, I have to go through Chapters 1 to 10 since they keep building on the same code. Unfortunately, I don't have that kind of time.

[Arch4ngel]

Don't stress yourself out... learning a new language is always long and painful :p ( I learned 2 in 1 years :p... 2 years of hospital needed :p lolllll)

 

Don't mind either with some "too" critical comment. Some here are pro that work with OOP since they were born (some freak let say ;) ... I like you freaky gang !)

 

Continue learning ! You are on the right track!

 

-A newb supporter that will get his pants back from those girls who wants a kiss from me ;) - lollll

[VBAHole22]

Yahtzee

 

Let's see the Yahtzee code!

 

I'm hooked on that little electronic one. It has replaced all of my ****lit.

[tekgod01]

Let's see the Yahtzee code!

 

I'm hooked on that little electronic one. It has replaced all of my ****lit.

That was last year. Sorry. It's been put into the great recycle bin of the sky. It took me all of about 3 days to do it, though. If you help me with this problem, I'll rewrite and send it to you. Are you bribable? :D

[Arch4ngel]

Yaaahhhhhhhh tzeeeeeeeeeee

 

Gwahahahahahaa!

 

Everyone is bribable unless he work for principe (principe can go to recycle bin with enough $$$).

 

Will you post it on the forum ? :D

[tekgod01]

Gwahahahahahaa!

 

Everyone is bribable unless he work for principe (principe can go to recycle bin with enough $$$).

 

Will you post it on the forum ? :D

Certainly.

 

Getting this solved is pretty important. I have a deployment on hold until I can get this figured out.

 

Would using an SQL statement do a proper update? If so, what VB code would I use? I have a partial statement already written but I'm unsure as to how it would get executed.

 

Dim cmd As New SqlClient.SqlCommand("Update SideLabels SET Quantity = '" & CType(txtQuantity.Text, String) & "' Where PartNumber = '" & CType(txtPartNumber.Text, String) & "'", SqlConnection1)

[tekgod01]

Would using an SQL statement do a proper update? If so, what VB code would I use? I have a partial statement already written but I'm unsure as to how it would get executed.

 

Dim cmd As New SqlClient.SqlCommand("Update SideLabels SET Quantity = '" & CType(txtQuantity.Text, String) & "' Where PartNumber = '" & CType(txtPartNumber.Text, String) & "'", SqlConnection1)

Holy crap. It worked. At least, it looks like it worked.

[tekgod01]

Holy crap. It worked. At least' date=' it looks like it worked.[/quote']

Yep. It worked.

 

Ok, now that I know I can do it using an SQL statement, is there a "cleaner" way of doing it? This statement will be a mile long and I hate dealing with long lines.

[tekgod01]

For future posterity, here's what I have that updates the database using an SQL statement. It's not pretty ("flawed", by some accounts), but it works, dernit...

 

Dim PartDataReader As SqlClient.SqlDataReader
Dim cmd As New SqlClient.SqlCommand("Update SideLabels SET Quantity = '" & CType(txtQuantity.Text, String) & "', Weight = '" & CType(txtWeight.Text, String) & "' Where PartNumber = '" & CType(txtPartNumber.Text, String) & "'", SqlConnection1)
SqlConnection1.Open()
PartDataReader = cmd.ExecuteReader
SqlConnection1.Close()

 

I still have to put in the remaining seven textboxes, but it works.

 

Of course, I'd still like to know a better way of doing this. My next program will require a lot more than 10 textboxes. Preliminary estimates put it closer to 100 and I just don't wanna have 1 line that friggin' long.

[VBAHole22]

Take a look at strongly-typed datasets.

 

It's a way for you to make your UI a little 'smarter' and it can cut down on the amount of code you need to type your input SQL.

 

I can't stand those long lines either. Just about the easiest thing to mess up.

 

Stored procedures on the server side can help too.

 

You setup your query in SQL Server and define your parameters. Then you just pass parameters instead of SQL. It's much faster too.

[tekgod01]

Take a look at strongly-typed datasets.

 

It's a way for you to make your UI a little 'smarter' and it can cut down on the amount of code you need to type your input SQL.

 

I can't stand those long lines either. Just about the easiest thing to mess up.

 

Stored procedures on the server side can help too.

 

You setup your query in SQL Server and define your parameters. Then you just pass parameters instead of SQL. It's much faster too.

Isn't there some other way to do this without the need of all the SQL statements?