'OleDb.ExecuteNonQuery always returning 0
I looked into previously asked such questions but none of the solutions worked for me.
I am having trouble updating a Boolean column in my Access database. But the query works just fine in Access.
What I have already tried:
- Directly using
TRUEorFALSEin theSqlCommand - Using
Parameters.Add()instead ofParameters.AddWithValue()
My ClickEvent:
Private Sub Update_Click(sender As Object, e As RoutedEventArgs) Handles btn_update.Click
Dim mc As New UsersModule() With {
.UserID = tbx_userid.Text,
.UserName = tbx_username.Text,
.UserPassword = tbx_password.Text,
.UserRole = combx_role.SelectedValue.ToString,
.UserFullname = tbx_fullname.Text,
.UserActive = cbx_active.IsChecked
}
Dim bridge As New UsersBridge()
If bridge.UpdateUser(mc) Then
MsgBox("User's Data Updated Successfully")
Else
MsgBox("Something went wrong :/")
End If
End Sub
My Module class:
Public Class UsersModule
Private ID As Integer, Name As String, Pass As String, Role As String, Fullname As String, isActive As Boolean
Public Property UserID() As Integer
Get
Return ID
End Get
Protected Friend Set(value As Integer)
ID = value
End Set
End Property
Public Property UserName() As String
Get
Return Name
End Get
Protected Friend Set(value As String)
Name = value
End Set
End Property
Public Property UserPassword() As String
Get
Return Pass
End Get
Protected Friend Set(value As String)
Pass = value
End Set
End Property
Public Property UserRole() As String
Get
Return Role
End Get
Protected Friend Set(value As String)
Role = value
End Set
End Property
Public Property UserFullname() As String
Get
Return Fullname
End Get
Protected Friend Set(value As String)
Fullname = value
End Set
End Property
Public Property UserActive() As Boolean
Get
Return isActive
End Get
Protected Friend Set(value As Boolean)
isActive = value
End Set
End Property
End Class
My Bridge class:
Public Class Accounts_Bridge
Shared conStr As String = Windows.Application.Current.FindResource("connectionString")
Public Function Update(mc As AccountsData) As Boolean
Dim con As New OleDb.OleDbConnection(conStr)
Dim isSuccessful As Boolean = False
Try
Dim sql As String = "UPDATE Users SET UserName = @user, UserPassword = @pass, UserRole = @role, UserFullname = @name, UserActive = @active WHERE UserID = @id"
Dim cmd As New OleDb.OleDbCommand(sql, con)
cmd.Parameters.Add("@id", OleDbType.Integer).Value = mc.UserID
cmd.Parameters.Add("@user", OleDbType.VarChar).Value = mc.UserName
cmd.Parameters.Add("@pass", OleDbType.VarChar).Value = mc.UserPassword
cmd.Parameters.Add("@role", OleDbType.VarChar).Value = mc.UserRole
cmd.Parameters.Add("@name", OleDbType.VarChar).Value = mc.UserFullname
cmd.Parameters.Add("@active", OleDbType.Boolean).Value = mc.UserActive
con.Open()
Dim i As Integer = cmd.ExecuteNonQuery() 'Always Returns 0
If i > 0 Then
isSuccessful = True
Else
isSuccessful = False
End If
Catch ex As Exception
MsgBox(ex.Message)
Finally
con.Close()
End Try
Return isSuccessful
End Function
End Class
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
