Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 147
  • Last Modified:

best practice for update and insert statement

Hi,
I'm using VS2010 w/ VB.Net
SQL Server 2008 R2

I have a form that displays some records that the user needs to be able to edit and/or add to.  The Insert works fine by itself, but the only way I've found to update the modified records is to delete all the records and then insert them all back in.  The records are displayed in a dgv. The problem here is since the records are all Deleted first if an error occurs during the Insert then those records are lost.

I've been trying to use a transaction object to rollback the delete if the insert errors out, but I haven't figured out how to make that work.  What do I need to do here?

Thanks,
Dave
Dim iCount As Integer = dtMaint.Rows.Count
        Dim sql As String
        'Dim sqlDelete As String = "DELETE * FROM "
        Dim sqlDelete As String = "DELETE FROM "
        Dim sEvent, sCat, sShift, sTeam, sTeamName, sStart, sEnd As String
        Dim sID, sFirst, sLast, sEmp As String
        Dim dtStart, dtEnd, dtCreate As DateTime

        'Open connection to database.
        Dim cConn As New SqlConnection
        cConn.ConnectionString = gsDbPath
        cConn.Open()

        'delete any empty rows that have been added.
        Dim xRow As Integer = 0

        Do While Not xRow = iCount
            If IsDBNull(dtMaint.Rows(xRow)(0)) Then
                dtMaint.Rows(xRow).Delete()
                xRow = 0
                iCount = dtMaint.Rows.Count
            Else
                xRow = xRow + 1
            End If
        Loop

        'Get count of rows after deleting empties.
        iCount = dtMaint.Rows.Count - 1

        ''Set transaction object, so we can rollback delete statement incase of error.
        'Dim myTrans As SqlTransaction
        'myTrans = cConn.BeginTransaction
        'Dim myTrans2 As SqlTransaction
        'myTrans2 = cConn.BeginTransaction

        'Update the given table.
        Try
            Select Case gsMaint
                Case "Events"
                    'Delete current rows and then we add them back.
                    sqlDelete = sqlDelete & "tbl_event"
                    Dim cmd1 As New SqlCommand(sqlDelete, New SqlConnection(gsDbPath))
                    'cmd1.Transaction = myTrans
                    cmd1.Connection.Open()
                    cmd1.ExecuteNonQuery()
                    cmd1.Connection.Close()

                    'Loop through datatable and write to database.
                    For i As Integer = 0 To iCount
                        If dtMaint.Rows(i).RowState <> DataRowState.Deleted Then ' make sure row referenced was not deleted by user.
                            sql = "INSERT INTO tbl_event (Event,Category) VALUES (@Event,@Category)"
                            Dim cmd2 As SqlCommand = New SqlCommand(sql, cConn)
                            'cmd2.Transaction = myTrans2

                            sEvent = dtMaint.Rows(i)("Event")
                            sCat = dtMaint.Rows(i)("Category")

                            With cmd2.Parameters
                                .Add(New SqlParameter("@Event", sEvent))
                                .Add(New SqlParameter("@Category", sCat))
                            End With

                            Dim y As Long = cmd2.ExecuteNonQuery

                            cmd2.Dispose()
                        End If
                    Next

                Case "Categories"
                    'Delete current rows and then we add them back.
                    sqlDelete = sqlDelete & "tbl_category"
                    Dim cmd1 As New SqlCommand(sqlDelete, New SqlConnection(gsDbPath))
                    cmd1.Connection.Open()
                    cmd1.ExecuteNonQuery()
                    cmd1.Connection.Close()

                    'Loop through datatable and write to database.
                    For i As Integer = 0 To iCount
                        If dtMaint.Rows(i).RowState <> DataRowState.Deleted Then ' make sure row referenced was not deleted by user.
                            sql = "INSERT INTO tbl_category (Category) VALUES (@Category)"
                            Dim cmd2 As SqlCommand = New SqlCommand(sql, cConn)
                            sCat = dtMaint.Rows(i)("Category")

                            With cmd2.Parameters
                                .Add(New SqlParameter("@Category", sCat))
                            End With

                            Dim y As Long = cmd2.ExecuteNonQuery

                            cmd2.Dispose()
                        End If
                    Next

                Case "Shifts"
                    ''Delete current rows and then we add them back.
                    'sqlDelete = sqlDelete & "tbl_Shift"
                    'Dim cmd1 As New OleDbCommand(sqlDelete, New OleDbConnection(gsDbPath))
                    'cmd1.Connection.Open()
                    'cmd1.ExecuteNonQuery()
                    'cmd1.Connection.Close()

                    ''Loop through datatable and write to database.
                    'For i As Integer = 0 To iCount
                    '    If dtMaint.Rows(i).RowState <> DataRowState.Deleted Then ' make sure row referenced was not deleted by user.
                    '        sql = "INSERT INTO tbl_Shift (Start,End,Shift) VALUES (@Start,@End,@Shift)"
                    '        Dim cmd2 As OleDb.OleDbCommand = New OleDb.OleDbCommand(sql, cConn)

                    '        sShift = dtMaint.Rows(i)("Shift")
                    '        dtStart = dtMaint.Rows(i)("Start")
                    '        sStart = dtStart.ToString("hh:mm tt")
                    '        dtStart = CDate(sStart)
                    '        dtEnd = dtMaint.Rows(i)("End")
                    '        sEnd = dtEnd.ToString("hh:mm tt")
                    '        dtEnd = CDate(sEnd)

                    '        With cmd2.Parameters
                    '            .Add(New OleDb.OleDbParameter("@Shift", sShift))
                    '            .Add(New OleDb.OleDbParameter("@Start", dtStart))
                    '            .Add(New OleDb.OleDbParameter("@End", dtEnd))
                    '        End With

                    '        Dim y As Long = cmd2.ExecuteNonQuery

                    '        cmd2.Dispose()
                    '    End If
                    'Next

                Case "Sorter Teams"
                    'Delete current rows and then we add them back.
                    sqlDelete = sqlDelete & "tbl_teams"
                    Dim cmd1 As New SqlCommand(sqlDelete, New SqlConnection(gsDbPath))
                    cmd1.Connection.Open()
                    cmd1.ExecuteNonQuery()
                    cmd1.Connection.Close()

                    'Loop through datatable and write to database.
                    For i As Integer = 0 To iCount
                        If dtMaint.Rows(i).RowState <> DataRowState.Deleted Then ' make sure row referenced was not deleted by user.
                            sql = "INSERT INTO tbl_teams (Team,Team_Name,Shift) VALUES (@Team,@Team_Name,@Shift)"
                            Dim cmd2 As SqlCommand = New SqlCommand(sql, cConn)

                            sTeam = dtMaint.Rows(i)("Team")
                            sTeamName = dtMaint.Rows(i)("Team_Name")
                            sShift = dtMaint.Rows(i)("Shift")

                            With cmd2.Parameters
                                .Add(New SqlParameter("@Team", sTeam))
                                .Add(New SqlParameter("@TeamName", sTeamName))
                                .Add(New SqlParameter("@Shift", sShift))
                            End With

                            Dim y As Long = cmd2.ExecuteNonQuery

                            cmd2.Dispose()
                        End If
                    Next

                Case "Add User"
                    'Delete current rows and then we add them back.
                    sqlDelete = sqlDelete & "tbl_login_user"
                    Dim cmd1 As New SqlCommand(sqlDelete, New SqlConnection(gsDbPath))
                    cmd1.Connection.Open()
                    cmd1.ExecuteNonQuery()
                    cmd1.Connection.Close()

                    'Loop through datatable and write to database.
                    For i As Integer = 0 To iCount
                        If dtMaint.Rows(i).RowState <> DataRowState.Deleted Then ' make sure row referenced was not deleted by user.
                            sql = "INSERT INTO tbl_login_user (ID,f_name,l_name,emp_nbr,created_dt) VALUES (@id,@f,@l,@Emp,@Created)"
                            Dim cmd2 As SqlCommand = New SqlCommand(sql, cConn)

                            sID = dtMaint.Rows(i)("ID")
                            sFirst = dtMaint.Rows(i)("f_name")
                            sLast = dtMaint.Rows(i)("l_name")
                            sEmp = dtMaint.Rows(i)("emp_nbr")
                            If IsDBNull(dtMaint.Rows(i)("Created_dt")) Then
                                dtCreate = Format(Now(), "MM/dd/yy hh:mm tt")
                            Else
                                dtCreate = dtMaint.Rows(i)("created_dt")
                            End If


                            With cmd2.Parameters
                                .Add(New SqlParameter("@id", sID))
                                .Add(New SqlParameter("@f", sFirst))
                                .Add(New SqlParameter("@l", sLast))
                                .Add(New SqlParameter("@Emp", sEmp))
                                .Add(New SqlParameter("@Created", dtCreate))
                            End With

                            Dim y As Long = cmd2.ExecuteNonQuery

                            cmd2.Dispose()
                        End If
                    Next

                Case Else

            End Select

            ''If we made it this far we can commit the changes to the db.
            'myTrans.Commit()
            'myTrans2.Commit()

        Catch ex As Exception
            'myTrans.Rollback()
            'myTrans2.Rollback()

            psEx = ex.ToString
            'Call ErrCatcher()
            MessageBox.Show(ex.Message)
            MessageBox.Show("Transaction Terminated!", "The changes could not be saved!")

        Finally
            cConn.Close()
        End Try

Open in new window

0
coperations07
Asked:
coperations07
  • 10
  • 9
  • 2
  • +1
1 Solution
 
Faiga DiegelSr Database EngineerCommented:
Can I suggeste stored procedure style something like this?

CREATE PROCEDURE ProcedureName
@yourparameters here
@yourparameters here
...
...
AS
BEGIN

BEGIN TRY
    BEGIN TRANSACTION
    {Your delete transactio here}
    {Your update transaction here}
    COMMIT
END TRY
BEGIN CATCH
    ROLLBACK
END CATCH


END
0
 
coperations07Author Commented:
can that type of code be executed in vb.net? I've never used it there before.
0
 
CodeCruiserCommented:
>but the only way I've found to update the modified records is to delete all the records and then insert them all back in.

You dont have to do that. Do you have a primary key field in your select statement?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
coperations07Author Commented:
I do not. But I can add one easy enough.
0
 
CodeCruiserCommented:
If you add the primary key field, the update will work automatically. Primary key field is required because its used in the Where clause of update statement.
0
 
coperations07Author Commented:
I added the primary key, but it isn't saving automatically.  I'm using the code below to fill the dgv. Will I have to do this a different way?
sql = "Select Event,Category,Event_Type from tbl_event"
                Dim myAdapter As New SqlDataAdapter(sql, gsDbPath)
                myAdapter.AcceptChangesDuringFill = False
                myAdapter.Fill(dtMaint)
                dgMaint.DataSource = dtMaint

Open in new window

0
 
CodeCruiserCommented:
Are you then calling the MyAdapter.Update method after making the changes?
0
 
coperations07Author Commented:
I'm declaring myAdapter in the form load event, so it's a local variable. I want to update the records when the UPDATE button is clicked, but I can't use myAdapter there because I declared it in the form load event.  I've tried declaring it publicly, but I haven't figured out how to set the sql and connection after declaring it. So is there a way to declare it as public and then set it? Or am I just thinking about this wrong?
0
 
CodeCruiserCommented:
Yes. Example

Public Class MyForm
Dim myAdapter As SqlDataAdapter

Private Sub Form_Load(...)
  myAdapter = New SqlDataAdapter(...)
End Sub

Private Sub btnUpdate_Click(...)
myAdapter.Update()
End Sub
0
 
coperations07Author Commented:
I think I've got it set up like you're showing, but I'm getting an error on the myAdapter.Update line stating: Overload resolution failed because no accessible 'Update' accepts this number of arguments.

I attached the code in case I'm just missing something, which is probable :)
Imports System.Data
Imports System.IO
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports System.Data.Sql

Public Class frmMaint


    Private dtMaint As New DataTable
    Private psEx As String
    Private pdtErrTime As DateTime
    Dim myAdapter As SqlDataAdapter

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

        Dim sql As String
        lblMaint.Text = gsMaint

        Select Case gsMaint
            Case "Events"
                sql = "Select * from tbl_event"
                'sql = "Select Event,Category,Event_Type from tbl_event"
                myAdapter = New SqlDataAdapter(sql, gsDbPath)
                myAdapter.AcceptChangesDuringFill = False
                myAdapter.Fill(dtMaint)
                dgMaint.DataSource = dtMaint
            Case "Categories"
                sql = "Select * from tbl_category"
                'Dim myAdapter As New SqlDataAdapter(sql, gsDbPath)
                myAdapter = New SqlDataAdapter(sql, gsDbPath)
                myAdapter.AcceptChangesDuringFill = False
                myAdapter.Fill(dtMaint)
                dgMaint.DataSource = dtMaint
            Case "Shifts"
                sql = "Select [Start],[End],[Shift] from tbl_Shift"
                'Dim myAdapter As New SqlDataAdapter(sql, gsDbPath)
                myAdapter = New SqlDataAdapter(sql, gsDbPath)
                myAdapter.AcceptChangesDuringFill = False
                myAdapter.Fill(dtMaint)
                dgMaint.DataSource = dtMaint
                dgMaint.Columns("Start").DefaultCellStyle.Format = "hh:mm tt"
                dgMaint.Columns("End").DefaultCellStyle.Format = "hh:mm tt"
            Case "Sorter Teams"
                sql = "Select * from tbl_teams"
                'Dim myAdapter As New SqlDataAdapter(sql, gsDbPath)
                myAdapter = New SqlDataAdapter(sql, gsDbPath)
                myAdapter.AcceptChangesDuringFill = False
                myAdapter.Fill(dtMaint)
                dgMaint.DataSource = dtMaint
            Case "Add User"
                sql = "Select * from tbl_login_user"
                'Dim myAdapter As New SqlDataAdapter(sql, gsDbPath)
                myAdapter = New SqlDataAdapter(sql, gsDbPath)
                myAdapter.AcceptChangesDuringFill = False
                myAdapter.Fill(dtMaint)
                dgMaint.DataSource = dtMaint
                dgMaint.Columns("Created_dt").Visible = False
            Case Else

        End Select


    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click

        Dim iCount As Integer = dtMaint.Rows.Count
        Dim sql As String
        'Dim sqlDelete As String = "DELETE * FROM "
        Dim sqlDelete As String = "DELETE FROM "
        Dim sEvent, sCat, sShift, sTeam, sTeamName, sStart, sEnd As String
        Dim sID, sFirst, sLast, sEmp As String
        Dim dtStart, dtEnd, dtCreate As DateTime

        'Open connection to database.
        Dim cConn As New SqlConnection
        cConn.ConnectionString = gsDbPath
        cConn.Open()

        'delete any empty rows that have been added.
        Dim xRow As Integer = 0

        Do While Not xRow = iCount
            If IsDBNull(dtMaint.Rows(xRow)(0)) Then
                dtMaint.Rows(xRow).Delete()
                xRow = 0
                iCount = dtMaint.Rows.Count
            Else
                xRow = xRow + 1
            End If
        Loop

        'Get count of rows after deleting empties.
        iCount = dtMaint.Rows.Count - 1

        ''Set transaction object, so we can rollback delete statement incase of error.
        'Dim myTrans As SqlTransaction
        'myTrans = cConn.BeginTransaction
        'Dim myTrans2 As SqlTransaction
        'myTrans2 = cConn.BeginTransaction

        'Update the given table.
        Try
            Select Case gsMaint
                Case "Events"
                    myAdapter.Update()

Open in new window

0
 
CodeCruiserCommented:
Try

myAdapter.Update(dtMaint)
0
 
coperations07Author Commented:
I tried that and I'm getting a msg: update requires a valid insertcommand when passed datarow collection with new rows.

So I tried adding this:
                Case "Events"
                    Dim cb As SqlCommandBuilder = New SqlCommandBuilder(myAdapter)
                    myAdapter.UpdateCommand = cb.GetUpdateCommand()

                    myAdapter.Update(dtMaint)

And I'm getting a msg: violation of primary key constraint...
0
 
CodeCruiserCommented:
Do you have any code manually inserting values?
0
 
coperations07Author Commented:
I have an Add new Record button that does this:
dtMaint.Rows.Add()
0
 
CodeCruiserCommented:
Is your primary key Identity field?
0
 
coperations07Author Commented:
No.It's not an identity field.
0
 
CodeCruiserCommented:
Then you need to insert values into that field for new rows. Otherwise it would be null and will cause problems. Test this code with a field that has Identity primary key.
0
 
Bob_Schmidt_Data_ModelerCommented:
Did you consider using a MERGE command?  http://technet.microsoft.com/en-us/library/bb510625.aspx
0
 
coperations07Author Commented:
Thanks Bob. I'm not familiar with the MERGE command. I've never used that type of syntax w/ vb.net.  I can give it a shot though.  It says you can use it to insert, delete OR update.  So I'm not sure it would work if there was a row edited and then a new row added...

Thanks for all the help CodeCruiser. The new row is getting values before the update.

I think I'm going to make the user decide whether they want to Edit something OR Add a new record and disable the other option so they can't do both before they save it. Thoughts on that?

Thanks,
Dave
0
 
CodeCruiserCommented:
I dont think there should be a problem allowing both. We just need to make sure the setup is configured correctly.
0
 
Bob_Schmidt_Data_ModelerCommented:
Merge is very fast (In Teradata and Oracle, never used in SQL Server myself) and very elegant.   However, I have never heard of using it for a delete; I just took this opportunity to read about it.  Note that you can either update OR delete.
0
 
coperations07Author Commented:
Sorry about the delay guys. I've been researching and testing some things to try to fix this, but to no avail so far. From what I understand my dataadapter is being created with a select command but not an insert or update command. I've tried to add these using the command builder but no luck.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 10
  • 9
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now