none
Newbie:VB2005 UPDATE table in SQL Server 2005 RRS feed

  • Question

  • Im having a problem updating a table using Tableadapter.update(dataset) using a datagridview.  I think the problem might be in my SQL updatecommand where would be the best place to ask about this problem?

    Sunday, August 19, 2007 10:26 AM

Answers

  • I see the problem:

     

    UPDATE    Draft07
    SET              Round = @Round, [Max's Marauders] = @p1, [Snarln' Bunnie Humpers] = @p4, [Geek Squad] = @Geek_Squad, Ditzy = @Ditzy,
                          [Dee's Dust Bunnies] = @p7, BallGrabbers = @BallGrabbers, [E LEMON ators] = @E_LEMON_ators, [Big Al's] = @p10

     

     

    There is no "WHERE" clause in the update command therefore it will update all the records as you are experiencing.

     

    So you need to add a WHERE clause and give it another parameter, @p11 which would be the ID of that record to update.

     

     

    UPDATE    Draft07
    SET              Round = @Round, [Max's Marauders] = @p1, [Snarln' Bunnie Humpers] = @p4, [Geek Squad] = @Geek_Squad, Ditzy = @Ditzy,
                          [Dee's Dust Bunnies] = @p7, BallGrabbers = @BallGrabbers, [E LEMON ators] = @E_LEMON_ators, [Big Al's] = @p10 WHERE [IDField] = @p11

     

     

    you need to of course give it the right name of the field which is the ID field and then when updating, add the ID field as a parameter.

     

    Does this help you further?

    Sunday, August 19, 2007 1:08 PM

All replies

  • 1) What problem are you having?

    2) Any errors? If so what are they?

    3) Can you post the code you are using?

     

    Sunday, August 19, 2007 10:55 AM
  • My code is as follows:

     

    Public Class Form1

    Private Sub Draft07BindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Draft07BindingNavigatorSaveItem.Click

    'Try

    Me.Draft07TableAdapter.Update(Me.Fantasydataset.Draft07)

    'Catch ex As Exception

    ' MessageBox.Show("Save Failed")

    'End Try

    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    'TODO: This line of code loads data into the 'Fantasydataset.Draft07' table. You can move, or remove it, as needed.

    Me.Fantasydataset.EnforceConstraints = False

    Me.Draft07TableAdapter.Fill(Me.Fantasydataset.Draft07)

    End Sub

    End Class

     

    Selectcommand is as follows:

    SELECT Round, [Max's Marauders], [Snarln' Bunnie Humpers], [Geek Squad], Ditzy, [Dee's Dust Bunnies], BallGrabbers, [E LEMON ators], [Big Al's] FROM dbo.Draft07

     

    Updatecommand is as follows:

    UPDATE    Draft07
    SET              Round = @Round, [Max's Marauders] = @p1, [Snarln' Bunnie Humpers] = @p4, [Geek Squad] = @Geek_Squad, Ditzy = @Ditzy,
                          [Dee's Dust Bunnies] = @p7, BallGrabbers = @BallGrabbers, [E LEMON ators] = @E_LEMON_ators, [Big Al's] = @p10

     

    I'm a complete newbie to databases and SQL.  Everything I know about VB2005 is self taught so take that for what its worth.  I Fill a datagridview and it populates with my Select colums. I can edit the dgv adding rows editing anything and it will save to the database fine.  If I close the program and reopen everything is OK.  If I then edit a row or rows it will pick one at random and populate the entire database with that row at which point unless I turn off constraints i can't even open the database and Fill my dgv. I know next to nothing about SQL and copied my update command from my insertcommand (with some changes) since the wizard will not create an Updatecommand no matter what I try.
    Sunday, August 19, 2007 11:31 AM
  • Not sure I quite follow.

     

    Why are you turning off the constraints? Don't do that otherwise you will have problems with data consistancy and avoiding constraints when there needs to be in your database schema.

     

    The update command seems ok. If you are not seeing any changes taking effect, be sure that if you included the database file in your project, to select "copy to output" to "never" or "only when newer". This will prevent it from overwriting the database everytime you build the project.

     

    I don't follow this:

     

    If I then edit a row or rows it will pick one at random and populate the entire database with that row

    Sunday, August 19, 2007 11:47 AM
  • I enter all data in the datagridview.  The first time I run, the data enters fine no problems.  If I close the application and run again when I make an edit and click save it will save a row into all the rows overwriting the data that was in that row with a random? row this data is saved to all the rows so that they all have the same data.

    Sunday, August 19, 2007 12:54 PM
  • I see the problem:

     

    UPDATE    Draft07
    SET              Round = @Round, [Max's Marauders] = @p1, [Snarln' Bunnie Humpers] = @p4, [Geek Squad] = @Geek_Squad, Ditzy = @Ditzy,
                          [Dee's Dust Bunnies] = @p7, BallGrabbers = @BallGrabbers, [E LEMON ators] = @E_LEMON_ators, [Big Al's] = @p10

     

     

    There is no "WHERE" clause in the update command therefore it will update all the records as you are experiencing.

     

    So you need to add a WHERE clause and give it another parameter, @p11 which would be the ID of that record to update.

     

     

    UPDATE    Draft07
    SET              Round = @Round, [Max's Marauders] = @p1, [Snarln' Bunnie Humpers] = @p4, [Geek Squad] = @Geek_Squad, Ditzy = @Ditzy,
                          [Dee's Dust Bunnies] = @p7, BallGrabbers = @BallGrabbers, [E LEMON ators] = @E_LEMON_ators, [Big Al's] = @p10 WHERE [IDField] = @p11

     

     

    you need to of course give it the right name of the field which is the ID field and then when updating, add the ID field as a parameter.

     

    Does this help you further?

    Sunday, August 19, 2007 1:08 PM
  • I assumed that when you said ID field you meant the primary key which I used and it works great.  Thank you for taking the time to help.

    Sunday, August 19, 2007 4:58 PM