Datagrid Update "problem"

Discussion in 'OT Technology' started by VBGOD, Aug 21, 2003.

  1. VBGOD

    VBGOD Guest

    Code:
    Imports System.Data.OleDb
    
    Public Class FrmFood
        Inherits System.Windows.Forms.Form
    
        'The Data Adapter pushes data into the DataSet.
        Private DA As OleDbDataAdapter
    
        'The Data Set stores the information.
        Private DS As New DataSet
    
        'Command objects used for select, update, Insert, and delete.
        Private SelectCommand As OleDbCommand
        Private DeleteCommand As OleDbCommand
        Private UpdateCommand As OleDbCommand
        Private InsertCommand As OleDbCommand
    
        'Our connection to the database.
        Private Conn As OleDbConnection = New OleDbConnection(User.ConnectionString)
    
        Private Sub FrmFood_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            LoadGrid()
        End Sub
    
        Private Sub LoadGrid()
    
            Try
                'Open connection to the database.
                Conn.Open()
    
                'Prepare the data adapter
                DA = New OleDbDataAdapter
    
                'Fill in the command objects
                SelectCommand = New OleDbCommand("SELECT FoodID, FoodName, Measurement, Sugar, Carbohydrates, Calories, Fat FROM Library_Food", Conn)
                DeleteCommand = New OleDbCommand("DELETE FROM Library_Food WHERE (FoodID = ?) AND (Calories = ? OR ? IS NULL AND Calories IS NULL) AND (Carbohydrates = ? OR ? IS NULL AND Carbohydrates IS NULL) AND (Fat = ? OR ? IS NULL AND Fat IS NULL) AND (FoodName = ? OR ? IS NULL AND FoodName IS NULL) AND (Measurement = ? OR ? IS NULL AND Measurement IS NULL) AND (Sugar = ? OR ? IS NULL AND Sugar IS NULL)", Conn)
                UpdateCommand = New OleDbCommand("UPDATE Library_Food SET Calories = ?, Carbohydrates = ?, Fat = ?, FoodName = ?, Measurement = ?, Sugar = ? WHERE (FoodID = ?) AND (Calories = ? OR ? IS NULL AND Calories IS NULL) AND (Carbohydrates = ? OR ? IS NULL AND Carbohydrates IS NULL) AND (Fat = ? OR ? IS NULL AND Fat IS NULL) AND (FoodName = ? OR ? IS NULL AND FoodName IS NULL) AND (Measurement = ? OR ? IS NULL AND Measurement IS NULL) AND (Sugar = ? OR ? IS NULL AND Sugar IS NULL)", Conn)
                InsertCommand = New OleDbCommand("INSERT INTO Library_Food(Calories, Carbohydrates, Fat, FoodName, Measurement, Sugar) VALUES (?, ?, ?, ?, ?, ?)", Conn)
    
                'Associate these command objects with the data adapter.
                DA.SelectCommand = SelectCommand
                DA.DeleteCommand = DeleteCommand
                DA.UpdateCommand = UpdateCommand
                DA.InsertCommand = InsertCommand
    
                DA.Fill(DS, "Food")
                DS.Tables("Food").Columns(0).ColumnMapping = MappingType.Hidden
                DataGrid1.DataSource = DS.Tables("Food")
                FormatGridWidth(DataGrid1)
    
            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.Exclamation, "")
            End Try
    
        End Sub
    
        Private Sub CmdUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CmdUpdate.Click
            Dim ChangedData As DataSet = DS.GetChanges
    
            Try
    
                If ChangedData Is Nothing Then
                    MsgBox("No new changes to record.", MsgBoxStyle.Information, "")
                    Exit Sub
                End If
    
                DA.Update(DS, "Food")
    
                MessageBox.Show("Information has been updated.", "Saved", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Catch ex As Exception
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
    
        End Sub
    End Class
    
    The table loads fine...

    [​IMG]

    When I go to update, it wants a shitload of parameters or something. This can't be the way!
     
  2. Scoob_13

    Scoob_13 Anything is possible, but the odds are astronomica

    Joined:
    Oct 5, 2001
    Messages:
    73,785
    Likes Received:
    38
    Location:
    Fort Worth. Hooray cowgirls.
    Screenshot of the error message?
     
  3. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    shitload of parameters = you not referencing the dataset properly.
     
  4. VBGOD

    VBGOD Guest

    Code updated, but still does not work.

    Code:
    Imports System.Data.OleDb
    
    Public Class FrmFood
        Inherits System.Windows.Forms.Form
    
        'The Data Adapter pushes data into the DataSet.
        Private DA As OleDbDataAdapter
    
        'The Data Set stores the information.
        Private DS As New DataSet
    
        'Command objects used for select, update, Insert, and delete.
        Private SelectCommand As OleDbCommand
        Private DeleteCommand As OleDbCommand
        Private UpdateCommand As OleDbCommand
        Private InsertCommand As OleDbCommand
    
        'Our connection to the database.
        Private Conn As OleDbConnection = New OleDbConnection(User.ConnectionString)
    
        Private Sub FrmFood_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            LoadGrid()
        End Sub
    
        Private Sub LoadGrid()
    
            Try
                'Open connection to the database.
                Conn.Open()
    
                'Prepare the data adapter
                DA = New OleDbDataAdapter
    
                'Fill in the command objects
                SelectCommand = New OleDbCommand("SELECT FoodID, FoodName, Measurement, Sugar, Carbohydrates, Calories, Fat FROM Library_Food", Conn)
                DeleteCommand = New OleDbCommand("DELETE FROM Library_Food WHERE (FoodID = ?) AND (Calories = ? OR ? IS NULL AND Calories IS NULL) AND (Carbohydrates = ? OR ? IS NULL AND Carbohydrates IS NULL) AND (Fat = ? OR ? IS NULL AND Fat IS NULL) AND (FoodName = ? OR ? IS NULL AND FoodName IS NULL) AND (Measurement = ? OR ? IS NULL AND Measurement IS NULL) AND (Sugar = ? OR ? IS NULL AND Sugar IS NULL)", Conn)
                UpdateCommand = New OleDbCommand("UPDATE Library_Food SET Calories = ?, Carbohydrates = ?, Fat = ?, FoodName = ?, Measurement = ?, Sugar = ? WHERE (FoodID = ?) AND (Calories = ? OR ? IS NULL AND Calories IS NULL) AND (Carbohydrates = ? OR ? IS NULL AND Carbohydrates IS NULL) AND (Fat = ? OR ? IS NULL AND Fat IS NULL) AND (FoodName = ? OR ? IS NULL AND FoodName IS NULL) AND (Measurement = ? OR ? IS NULL AND Measurement IS NULL) AND (Sugar = ? OR ? IS NULL AND Sugar IS NULL)", Conn)
                InsertCommand = New OleDbCommand("INSERT INTO Library_Food(Calories, Carbohydrates, Fat, FoodName, Measurement, Sugar) VALUES (?, ?, ?, ?, ?, ?)", Conn)
    
                'Associate these command objects with the data adapter.
                DA.SelectCommand = SelectCommand
                DA.DeleteCommand = DeleteCommand
                DA.UpdateCommand = UpdateCommand
                DA.InsertCommand = InsertCommand
    
                DA.Fill(DS, "Food")
                DS.Tables("Food").Columns(0).ColumnMapping = MappingType.Hidden
                DataGrid1.DataSource = DS.Tables("Food")
    
                Conn.Close()
            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.Exclamation, "")
            Finally
                If Conn.State = ConnectionState.Open Then
                    Conn.Close()
                End If
            End Try
    
        End Sub
    
        Private Sub CmdUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CmdUpdate.Click
            Dim ChangedData As DataSet = DS.GetChanges
    
            Try
    
                If ChangedData Is Nothing Then
                    MsgBox("No new changes to record.", MsgBoxStyle.Information, "")
                    Exit Sub
                End If
    
                Conn.Open()
                DA.Update(DS, "Food")
                Conn.Close()
    
                MessageBox.Show("Information has been updated.", "Saved", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Catch ex As Exception
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Finally
                If Conn.State = ConnectionState.Open Then
                    Conn.Close()
                End If
            End Try
    
        End Sub
    End Class
    
     
  5. VBGOD

    VBGOD Guest

  6. VBGOD

    VBGOD Guest

    WOW! That was so helpful. :eek3:
     
  7. VBGOD

    VBGOD Guest

  8. Jericho

    Jericho Active Member

    Joined:
    Oct 21, 2002
    Messages:
    34,140
    Likes Received:
    0
    who makes a website dedicated to one control? :wtf:
     
  9. VBGOD

    VBGOD Guest

    Answer: One smart girl.
     
  10. Scoob_13

    Scoob_13 Anything is possible, but the odds are astronomica

    Joined:
    Oct 5, 2001
    Messages:
    73,785
    Likes Received:
    38
    Location:
    Fort Worth. Hooray cowgirls.
    God damn, smart AND cute/hot! Daddy likes!

    As for your issue, where's the actual error thrown by .Net, and not the abbreviated general bullshit one :p

    Hmm, I don't see anything that would cause an issue, but I'm still looking. It doesn't help that I don't know VB all that well :p
     
    Last edited: Aug 21, 2003
  11. Scoob_13

    Scoob_13 Anything is possible, but the odds are astronomica

    Joined:
    Oct 5, 2001
    Messages:
    73,785
    Likes Received:
    38
    Location:
    Fort Worth. Hooray cowgirls.
    Try this:
    Code:
    DA.Update(DS)
    
     
  12. CompiledMonkey

    CompiledMonkey New Member

    Joined:
    Oct 26, 2001
    Messages:
    8,528
    Likes Received:
    0
    Location:
    Richmond, VA
    I really want to learn this stuff. :hs:
     
  13. Scoob_13

    Scoob_13 Anything is possible, but the odds are astronomica

    Joined:
    Oct 5, 2001
    Messages:
    73,785
    Likes Received:
    38
    Location:
    Fort Worth. Hooray cowgirls.
    I really want Herb to tell me if that's right :mad:
     
  14. VBGOD

    VBGOD Guest

    LOL! I tried that.
     
  15. Scoob_13

    Scoob_13 Anything is possible, but the odds are astronomica

    Joined:
    Oct 5, 2001
    Messages:
    73,785
    Likes Received:
    38
    Location:
    Fort Worth. Hooray cowgirls.
    I guess that didn't work then.
     
  16. Scoob_13

    Scoob_13 Anything is possible, but the odds are astronomica

    Joined:
    Oct 5, 2001
    Messages:
    73,785
    Likes Received:
    38
    Location:
    Fort Worth. Hooray cowgirls.
    Hey, check this out (from MSDN)

    Code:
    [Visual Basic] 
    Public Function CreateCmdsAndUpdate(myDataSet As DataSet, myConnection As String, mySelectQuery As String, myTableName As String) As DataSet
        Dim myConn As New OleDbConnection(myConnection)
        Dim myDataAdapter As New OleDbDataAdapter()
        myDataAdapter.SelectCommand = New OleDbCommand(mySelectQuery, myConn)
        Dim custCB As OleDbCommandBuilder = New OleDbCommandBuilder(myDataAdapter)
    
        myConn.Open()
    
        Dim custDS As DataSet = New DataSet
        myDataAdapter.Fill(custDS)
    
        ' Code to modify data in DataSet here 
    
        ' Without the OleDbCommandBuilder this line would fail.
        myDataAdapter.Update(custDS)
    
        myConn.Close()
    
        CreateCmdsAndUpdate = custDS
    End Function 'SelectOleDbSrvRows
    
    Notice the " ' Without the OleDbCommandBuilder this line would fail. " line - I don't see that in your code.

    Silly VB :p
     
  17. VBGOD

    VBGOD Guest

    Even tried DataGrid1.Update()
     
  18. Scoob_13

    Scoob_13 Anything is possible, but the odds are astronomica

    Joined:
    Oct 5, 2001
    Messages:
    73,785
    Likes Received:
    38
    Location:
    Fort Worth. Hooray cowgirls.

    That would only update the DataGrid though, and it wouldn't update your database.

    You are looking to update the database, right?
     
  19. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    I thought it was - I can almost bet it has something to do with your SQL statement but I don't know the table layouts, you do.
     
  20. VBGOD

    VBGOD Guest

    It's one table.

    Query was posted.
     
  21. Scoob_13

    Scoob_13 Anything is possible, but the odds are astronomica

    Joined:
    Oct 5, 2001
    Messages:
    73,785
    Likes Received:
    38
    Location:
    Fort Worth. Hooray cowgirls.
    Pssst, did you attempt the solution that I posted for you? I'm curious to know if it worked or not...
     

Share This Page