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
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
Last Comment
ASKER
can that type of code be executed in vb.net? I've never used it there before.
>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?
You dont have to do that. Do you have a primary key field in your select statement?
ASKER
I do not. But I can add one easy enough.
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.
ASKER
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
Are you then calling the MyAdapter.Update method after making the changes?
ASKER
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?
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
Public Class MyForm
Dim myAdapter As SqlDataAdapter
Private Sub Form_Load(...)
myAdapter = New SqlDataAdapter(...)
End Sub
Private Sub btnUpdate_Click(...)
myAdapter.Update()
End Sub
ASKER
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 :)
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()
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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(myAdapte
myAdapter.UpdateCommand = cb.GetUpdateCommand()
myAdapter.Update(dtMaint)
And I'm getting a msg: violation of primary key constraint...
So I tried adding this:
Case "Events"
Dim cb As SqlCommandBuilder = New SqlCommandBuilder(myAdapte
myAdapter.UpdateCommand = cb.GetUpdateCommand()
myAdapter.Update(dtMaint)
And I'm getting a msg: violation of primary key constraint...
Do you have any code manually inserting values?
ASKER
I have an Add new Record button that does this:
dtMaint.Rows.Add()
dtMaint.Rows.Add()
Is your primary key Identity field?
ASKER
No.It's not an identity field.
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.
Did you consider using a MERGE command? http://technet.microsoft.com/en-us/library/bb510625.aspx
ASKER
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
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
I dont think there should be a problem allowing both. We just need to make sure the setup is configured correctly.
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.
ASKER
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.
Visual Basic.NET
Visual Basic .NET (VB.NET) is an object-oriented programming language implemented on the .NET framework, but also supported on other platforms such as Mono and Silverlight. Microsoft launched VB.NET as the successor to the Visual Basic language. Though it is similar in syntax to Visual Basic pre-2002, it is not the same technology,
96K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
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