1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2004
    Shawnee, KS
    Rep Power

    Add new DB record

    I have a form with textboxes and a DataGridView for creating and editing batch recipes for a factory. The master table is called Recipes and the child table, that holds the ingredients and setpoints as called RecipeSteps. The main form has a DataGridView the displays the recipes. When you double click or press the edit button it opens the recipe edit form I think I got all that correctly. (FYI: I am using Visual Studio Express 2010) See my form load and SaveEdits functions below:

    VB.Net Code:
        Private Sub dlgRecipe_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
            Dim cmd As SqlClient.SqlCommand
                dgvSteps.RowsDefaultCellStyle.BackColor = Color.White
                dgvSteps.AlternatingRowsDefaultCellStyle.BackColor = Color.LightCyan
                dgvSteps.MultiSelect = False
                ' LOAD Recipe INFO
                If txtRecipeName.Text.Chars(0) = " " Then
                    Me.Text = "New Recipe From Existing (" & txtRecipeName.Text.Trim() & ")"
                    m_fNewRecipe = True
                    Me.Text = "Editing Recipe: " + txtRecipeName.Text
                End If
                ' ACTUAL Recipe (From RECIPES table)
                cmd = New SqlClient.SqlCommand("SELECT RecipeID, Name, Comment, Minutes, Steps FROM Recipes WHERE RecipeID = @RecipeID", g_DBConn)
                Call cmd.Parameters.AddWithValue("@RecipeID", m_iRecipeID)
                m_daRecipeTable.SelectCommand = cmd
                m_daRecipeTable.Fill(m_dsRecipeData, "Recipe")
                Call txtRecipeName.DataBindings.Clear()
                Call txtRecipeName.DataBindings.Add("Text", m_dsRecipeData.Tables(0), "Name", True, DataSourceUpdateMode.OnPropertyChanged)
                Call txtComment.DataBindings.Clear()
                Call txtComment.DataBindings.Add("Text", m_dsRecipeData.Tables(0), "Comment", True, DataSourceUpdateMode.OnPropertyChanged)
                Call txtTotalMins.DataBindings.Clear()
                Call txtTotalMins.DataBindings.Add("Text", m_dsRecipeData.Tables(0), "Minutes", True, DataSourceUpdateMode.OnPropertyChanged)
                Call txtTotalSteps.DataBindings.Clear()
                Call txtTotalSteps.DataBindings.Add("Text", m_dsRecipeData.Tables(0), "Steps", True, DataSourceUpdateMode.OnPropertyChanged)
                ' Not sure how to ADD / CREATE a new recipe here
                If m_iRecipeID < 1 Then
                    Call m_dsRecipeData.Tables(0).Rows.Add(m_dsRecipeData.Tables(0).NewRow())
                End If
                ' NOW GET THE STEPS (PALL)
                cmd = New SqlClient.SqlCommand("SELECT RecipeStepID, RecipeID, Step, IngredientID, Quantity FROM RecipeSteps WHERE RecipeID = @RecipeID ORDER BY Step", g_DBConn)
                Call cmd.Parameters.AddWithValue("@RecipeID", m_iRecipeID)
                m_daStepTable.SelectCommand = cmd
                m_daStepTable.Fill(m_dsStepData, "Steps")
                cmd = New SqlClient.SqlCommand("SELECT IngredientID, Ingredient FROM Ingredients WHERE IngredientID > 0 ORDER BY IngredientID", g_DBConn)
                m_daIngredientTable.SelectCommand = cmd
                m_daIngredientTable.Fill(m_dsIngredientData, "Ingredients")
                m_DefaultIngredient = m_dsIngredientData.Tables(0).Rows(0).Item(0)
                ' Create columns
                Call dgvSteps.Rows.Clear()
                Call dgvSteps.Columns.Add("RecipeStepID", "RecipeStepID")
                Call dgvSteps.Columns.Add("RecipeID", "RecipeID")
                Call dgvSteps.Columns.Add("Step", "Step")
                Dim col As New DataGridViewComboBoxColumn()
                col.DataSource = m_dsIngredientData.Tables("Ingredients")
                col.DisplayMember = "Ingredient"
                col.ValueMember = "IngredientID"
                col.Width = col.GetPreferredWidth(DataGridViewAutoSizeColumnMode.AllCells, True)
                col.Name = "IngredientID"
                col.HeaderText = "Ingredients"
                col.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
                Call dgvSteps.Columns.Add(col)
                Call dgvSteps.Columns.Add("Minutes", "Mins")
                Call dgvSteps.Columns.Add("Amps", "Amps")
                Call dgvSteps.Columns.Add("AmpHours", "Amp Hrs")
                ' Bind STEP Data to columns
                dgvSteps.AutoGenerateColumns = False
                dgvSteps.DataSource = m_dsStepData.Tables("Steps")
                dgvSteps.Columns("RecipeStepID").DataPropertyName = "RecipeStepID"
                dgvSteps.Columns("RecipeID").DataPropertyName = "RecipeID"
                dgvSteps.Columns("Step").DataPropertyName = "Step"
                dgvSteps.Columns("IngredientID").DataPropertyName = "IngredientID"
                dgvSteps.Columns("Quantity").DataPropertyName = "Quantity"
                ' Setup STEP formating
                Call dgvSteps.AutoResizeColumns()
                dgvSteps.Columns("RecipeStepID").ReadOnly = True
                dgvSteps.Columns("RecipeStepID").DefaultCellStyle.BackColor = Color.LightGray
                dgvSteps.Columns("RecipeID").ReadOnly = True
                dgvSteps.Columns("RecipeID").DefaultCellStyle.BackColor = Color.LightGray
                dgvSteps.Columns("Step").ReadOnly = True
                dgvSteps.Columns("Step").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
                dgvSteps.Columns("Step").DefaultCellStyle.BackColor = Color.LightGray
                dgvSteps.Columns("Minutes").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
                dgvSteps.Columns("Amps").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
                dgvSteps.Columns("AmpHours").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
                If m_iRecipeID < 0 Then
                    Me.Text = "Creating New Recipe"
                    m_fNewRecipe = True
                End If
                If m_fNewRecipe Then
                    m_iRecipeID = 0	' I set this to 
                    txtRecipeName.Text = ""
                    For i = 0 To dgvSteps.RowCount - 1
                        dgvSteps.Rows(i).Cells("RecipeID").Value = m_iRecipeID
                    cmdWhosAffected.Enabled = False
                End If
                Call UpdateRecipeData()
            Catch ex As Exception
                Call MsgBox("Error in: " & System.Reflection.MethodInfo.GetCurrentMethod().ToString() _
                            & vbCrLf & ex.Message, MsgBoxStyle.Critical)
                ' Cleanup
                Call cmd.Dispose()
                Call m_dsIngredientData.Dispose()
                Call m_daIngredientTable.Dispose()
                m_fLoading = False
            End Try
        End Sub

    VB.Net Code:
        Private Sub SaveEdits()
            Dim cb As SqlClient.SqlCommandBuilder
            Dim newID As SqlClient.SqlParameter
            Dim r As Integer
                Call m_dsRecipeData.Tables(0).Rows(0).EndEdit()
                If m_dsRecipeData.HasChanges() Then
                    If m_iRecipeID > 0 Then
                        cb = New SqlClient.SqlCommandBuilder(m_daRecipeTable)
                        m_daRecipeTable.UpdateCommand = cb.GetUpdateCommand()
                        ' I just made this part up, I don't know how to get the new identity so I can then populate
                        ' the ReciepeSteps table with the correct RecipeID
                        newID = m_daRecipeTable.InsertCommand.Parameters.Add("@Identity", SqlDbType.Int, 0, "RecipeID")
                        newID.Direction = ParameterDirection.Output
                    End If
                    Call m_daRecipeTable.Update(m_dsRecipeData.Tables("Recipe"))
                    If m_iRecipeID < 0 Then
                        m_iRecipeID = m_daRecipeTable.InsertCommand.Parameters("@Identity").Value
                    End If
                End If
                If m_dsStepData.HasChanges() Then
                    For r = 0 To dgvSteps.RowCount - 1
                        dgvSteps.Rows(r).Cells("RecipeID").Value = m_iRecipeID
                    cb = New SqlClient.SqlCommandBuilder(m_daStepTable)
                    m_daStepTable.UpdateCommand = cb.GetUpdateCommand()
                    m_daStepTable.InsertCommand = cb.GetInsertCommand()
                    m_daStepTable.DeleteCommand = cb.GetDeleteCommand()
                End If
            Catch ex As Exception
                Call MsgBox("Error in: " & System.Reflection.MethodInfo.GetCurrentMethod().ToString() _
                            & vbCrLf & ex.Message, MsgBoxStyle.Critical)
                ' Clean up
                Call cb.Dispose()
                Call m_dsRecipeData.Dispose()
                Call m_daRecipeTable.Dispose()
                Call m_dsStepData.Dispose()
                Call m_daStepTable.Dispose()
            End Try
        End Sub

    I'm not sure what to do differently when I am creating a new recipe (or if I can even use a substantial portion of this code).
  2. #2
  3. No Profile Picture
    Offensive Member
    Devshed Novice (500 - 999 posts)

    Join Date
    Oct 2002
    in the perfect world
    Rep Power
    I assume you are using SQL Server as you talk about 'Identities' (not Sequences / Oracle or Autonumbers / ACCESS).

    Usually the DB will add the identity value (if NULL), so you do not have to explicitly set the identity field.

    If you need to find the next identity it can be found by adding the amount the tables identity increments by (usually 1) to the current maximum ID value.

    Been a few years since I use SQL Server so I think it is;

    SELECT (IDENT_CURRENT('[SomeTable]') + IDENT_INCR('[SomeTable]')) as NewIDValue FROM [SomeTable]
    The essence of Christianity is told us in the Garden of Eden history. The fruit that was forbidden was on the Tree of Knowledge. The subtext is, All the suffering you have is because you wanted to find out what was going on. You could be in the Garden of Eden if you had just kept your f***ing mouth shut and hadn't asked any questions.

    Frank Zappa

IMN logo majestic logo threadwatch logo seochat tools logo