-
Invalid Update Command?
Hi everyone. Can I get some help from you guys? My code block is saying that I provided an invalid Update Command. The situation is this, I want to insert a record into the Violation table, then after the insert has been executed, I want to update the Status and ViolationCount fields of the Patron table. The thing is, when it comes to the Update block, it outputs the error,
Update requires a Valid UpdateCommand when passed DataRow collection with modified rows.
But, I did provide a valid UpdateCommand in the code block (dbUpdate). Why is this?
Code:
Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
Dim Validated As Boolean = False
Dim dbInsert As New SqlCommand("INSERT INTO Violation(PatronNumber, Violation, Remarks, DateFiled, FiledBy, " & _
"PatronID) VALUES (@insPatronNumber, @insViolation, @insRemarks, @insDateFiled, " & _
"@insFiledBy, @insPatronID)", dbConn)
Dim dbUpdate As New SqlCommand("UPDATE Patron SET ViolationCount = @updViolationCount, Status = @updStatus WHERE PatronID = @updPatronID", dbConn)
PatronNumber = Trim(txtPatronNumber.Text)
Violation = Trim(cmbViolation.Text)
Remarks = Trim(txtRemarks.Text)
If PatronNumber = "" Or Violation = "" Or Remarks = "" Then
MsgBox("Please fill up all of the required fields.", MsgBoxStyle.Information, "Required Fields Missing")
txtPatronNumber.Focus()
Exit Sub
End If
'check if values entered are valid
Validated = Validate(PatronNumber)
If MsgBox("Are you sure you want to file this violation to " + PatronNumber + "? Once saved," & vbCrLf & "this action cannot be undone.", MsgBoxStyle.YesNo, "Finalize Violation Filing") = MsgBoxResult.Yes Then
dbConn.Open()
dbAdpViolation = New SqlClient.SqlDataAdapter("SELECT * FROM Violation", dbConn)
dbDsetViolation = New DataSet
dbAdpViolation.Fill(dbDsetViolation, "Violation")
'sql insert cmd parameter declaration
dbAdpViolation.InsertCommand = dbInsert
With dbInsert
.Connection = dbConn
.Parameters.Add(New SqlParameter("@insPatronNumber", SqlDbType.VarChar, 15, "PatronNumber"))
.Parameters.Add(New SqlParameter("@insViolation", SqlDbType.VarChar, 30, "Violation"))
.Parameters.Add(New SqlParameter("@insRemarks", SqlDbType.VarChar, 40, "Remarks"))
.Parameters.Add(New SqlParameter("@insDateFiled", SqlDbType.DateTime, 8, "DateFiled"))
.Parameters.Add(New SqlParameter("@insFiledBy", SqlDbType.VarChar, 20, "FiledBy"))
.Parameters.Add(New SqlParameter("@insPatronID", SqlDbType.Int, 4, "PatronID"))
End With
'sql update cmd parameter declaration
dbAdpViolation.UpdateCommand = dbUpdate
With dbUpdate
.Connection = dbConn
.Parameters.Add(New SqlParameter("@updPatronID", SqlDbType.Int, 4, "PatronID"))
.Parameters.Add(New SqlParameter("@updViolationCount", SqlDbType.Int, 4, "ViolationCount"))
.Parameters.Add(New SqlParameter("@updStatus", SqlDbType.VarChar, 10, "Status"))
End With
If ViolationFilerFlag = True Then
Try
FiledBy = loggedUser
DateFiled = Format(Date.Now, "MM/dd/yyyy")
'initialize the new datarow and populate it with the entered values
dr = dbDsetViolation.Tables(0).NewRow()
dr("PatronNumber") = PatronNumber
dr("Violation") = Violation
dr("Remarks") = Remarks
dr("DateFiled") = DateFiled
dr("FiledBy") = FiledBy
dr("PatronID") = PatronID
'save the datarow
dbDsetViolation.Tables(0).Rows.Add(dr)
dbAdpViolation.Update(dbDsetViolation.Tables("Violation"))
dbDsetViolation.AcceptChanges()
ViolationCount = ViolationCount + 1
If ViolationCount > 3 Then
Status = "Banned"
Else
Status = "Authorized"
End If
'open the connection to the patron table and find the patron using the patronnumber for updating the violationcount and status of the patron
dbAdpViolation = New SqlClient.SqlDataAdapter("SELECT ViolationCount, Status FROM Patron WHERE PatronID='" & PatronID & "'", dbConn)
dbDsetViolation = New DataSet
dbAdpViolation.Fill(dbDsetViolation, "Patron")
'update
'dbAdpViolation.UpdateCommand.CommandText = "UPDATE Patron SET ViolationCount = '" & ViolationCount & "', Status = '" & Status & "' WHERE PatronID = '" & PatronID & "'"
'dbDsetViolation = New DataSet
'dbAdpViolation.Fill(dbDsetViolation, "Patron")
'update the datarow of the patron
dr = dbDsetViolation.Tables(0).Rows(0)
dr.BeginEdit()
dr("ViolationCount") = ViolationCount
dr("Status") = Status
dr.EndEdit()
dbAdpViolation.Update(dbDsetViolation.Tables("Patron"))
dbDsetViolation.AcceptChanges()
'clear the dataset and repopulate it with the data from the violation table
dbDsetViolation.Clear()
dbAdpViolation.SelectCommand.CommandText = "SELECT * FROM Violation"
dbAdpViolation.Fill(dbDsetViolation, "Violation")
'refresh the data on the datagrid
dgViolation.DataSource = dbDsetViolation
dgViolation.DataMember = "Violation"
'move to the last record
CurIndex = dbDsetViolation.Tables(0).Rows.Count - 1
FillFields()
MsgBox("Violation filed for Patron #: " & PatronNumber & ".", MsgBoxStyle.Information, "Violation Filed Successfully")
disableFields()
ViolationFilerFlag = False
Catch parameter As System.Data.SqlClient.SqlException
MsgBox("Unable to save record due to invalid parameters.", MsgBoxStyle.Information, "Invalid Parameters")
Catch invalidparameter As InvalidOperationException
MessageBox.Show(invalidparameter.Message)
End Try
End If
dbConn.Close()
Else
Exit Sub
End If
End Sub