一个数据访问层的类库,数据操使用了一个公共类DataAccessor,Department是访问Department的数据访问类,新建没有问题,但是更新却不成功,没有抛出异常,影响的行数是0。因为新建和更新都是使用DataAccessor中的ExecuteNonQuery方法,那么新建成功说明这个方法没有问题,在Access中执行SQL语句SQL_UPDATE_DEPARTMENT(是个常数)也成功,没有问题。那么问题到底在哪里呢?请高手指点,谢谢。
数据访问公用类(为了方便,只提供相关内容):
Imports System.Data.OleDb
Imports System.Windows.FormsFriend Class DataAccessor
Private Shared m_ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\" & System.Configuration.ConfigurationSettings.AppSettings("DatabaseLocation")
Public Shared ReadOnly Property ConnectionString() As String
Get
Return m_ConnectionString
End Get
End PropertyPublic Shared Function ExecuteNonQuery(ByVal commandText As String, ByVal parameters As OleDbParameter()) As Boolean
Dim myRowsAffected As Integer Dim myConnection As New OleDbConnection(ConnectionString)
Dim myCommand As OleDbCommand = PrepareCommand(commandText, myConnection, parameters)
myCommand.Connection.Open()
MessageBox.Show(myConnection.ServerVersion)
myRowsAffected = myCommand.ExecuteNonQuery
myCommand.Connection.Close()
If myRowsAffected > 0 Then
Return True
Else
Return False
End If
End Function Private Shared Function PrepareCommand(ByVal commandText As String, ByVal connection As OleDbConnection, ByVal parameters As OleDbParameter()) Dim myCommand As New OleDbCommand(commandText, connection) myCommand.CommandType = CommandType.Text If Not (parameters Is Nothing) Then
For Each myParameter As OleDbParameter In parameters
myCommand.Parameters.Add(myParameter)
Next
End If Return myCommand
End Function
End Class数据访问类(为了方便,只提供相关内容):
Public Class Department
Const SQL_UPDATE_DEPARTMENT As String = "UPDATE Cas_Department SET Name=@Name,EstablishDate=@EstablishDate,Nature=@Nature,Director=@Director,Address=@Address,Tel=@Tel,Contact=@Contact WHERE DepartmentId=@DepartmentId" Public Function Update(ByVal departmentId As Integer, ByVal name As String, ByVal establishDate As DateTime, ByVal nature As String, ByVal director As String, ByVal address As String, ByVal tel As String, ByVal contact As String) As Boolean
Dim myParameters As OleDbParameter()
myParameters = New OleDbParameter() {New OleDbParameter("@DepartmentId", OleDbType.Integer), New OleDbParameter("@Name", OleDbType.VarChar), New OleDbParameter("@EstablishDate", OleDbType.Date), New OleDbParameter("@Nature", OleDbType.VarChar), New OleDbParameter("@Director", OleDbType.VarChar), New OleDbParameter("@Address", OleDbType.VarChar), New OleDbParameter("@Tel", OleDbType.VarChar), New OleDbParameter("@Contact", OleDbType.VarChar)}
myParameters(0).Value = departmentId
myParameters(1).Value = name
myParameters(2).Value = establishDate
myParameters(3).Value = nature
myParameters(4).Value = director
myParameters(5).Value = address
myParameters(6).Value = tel
myParameters(7).Value = contact
If DataAccessor.ExecuteNonQuery(SQL_UPDATE_DEPARTMENT, myParameters) Then
m_departmentId = departmentId
m_name = name
m_establishDate = establishDate
m_nature = nature
m_director = director
m_address = address
m_tel = tel
m_contact = contact
Return True
Else
Return False
End If
End FunctionEnd Class
数据访问公用类(为了方便,只提供相关内容):
Imports System.Data.OleDb
Imports System.Windows.FormsFriend Class DataAccessor
Private Shared m_ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\" & System.Configuration.ConfigurationSettings.AppSettings("DatabaseLocation")
Public Shared ReadOnly Property ConnectionString() As String
Get
Return m_ConnectionString
End Get
End PropertyPublic Shared Function ExecuteNonQuery(ByVal commandText As String, ByVal parameters As OleDbParameter()) As Boolean
Dim myRowsAffected As Integer Dim myConnection As New OleDbConnection(ConnectionString)
Dim myCommand As OleDbCommand = PrepareCommand(commandText, myConnection, parameters)
myCommand.Connection.Open()
MessageBox.Show(myConnection.ServerVersion)
myRowsAffected = myCommand.ExecuteNonQuery
myCommand.Connection.Close()
If myRowsAffected > 0 Then
Return True
Else
Return False
End If
End Function Private Shared Function PrepareCommand(ByVal commandText As String, ByVal connection As OleDbConnection, ByVal parameters As OleDbParameter()) Dim myCommand As New OleDbCommand(commandText, connection) myCommand.CommandType = CommandType.Text If Not (parameters Is Nothing) Then
For Each myParameter As OleDbParameter In parameters
myCommand.Parameters.Add(myParameter)
Next
End If Return myCommand
End Function
End Class数据访问类(为了方便,只提供相关内容):
Public Class Department
Const SQL_UPDATE_DEPARTMENT As String = "UPDATE Cas_Department SET Name=@Name,EstablishDate=@EstablishDate,Nature=@Nature,Director=@Director,Address=@Address,Tel=@Tel,Contact=@Contact WHERE DepartmentId=@DepartmentId" Public Function Update(ByVal departmentId As Integer, ByVal name As String, ByVal establishDate As DateTime, ByVal nature As String, ByVal director As String, ByVal address As String, ByVal tel As String, ByVal contact As String) As Boolean
Dim myParameters As OleDbParameter()
myParameters = New OleDbParameter() {New OleDbParameter("@DepartmentId", OleDbType.Integer), New OleDbParameter("@Name", OleDbType.VarChar), New OleDbParameter("@EstablishDate", OleDbType.Date), New OleDbParameter("@Nature", OleDbType.VarChar), New OleDbParameter("@Director", OleDbType.VarChar), New OleDbParameter("@Address", OleDbType.VarChar), New OleDbParameter("@Tel", OleDbType.VarChar), New OleDbParameter("@Contact", OleDbType.VarChar)}
myParameters(0).Value = departmentId
myParameters(1).Value = name
myParameters(2).Value = establishDate
myParameters(3).Value = nature
myParameters(4).Value = director
myParameters(5).Value = address
myParameters(6).Value = tel
myParameters(7).Value = contact
If DataAccessor.ExecuteNonQuery(SQL_UPDATE_DEPARTMENT, myParameters) Then
m_departmentId = departmentId
m_name = name
m_establishDate = establishDate
m_nature = nature
m_director = director
m_address = address
m_tel = tel
m_contact = contact
Return True
Else
Return False
End If
End FunctionEnd Class
用?代替参数
myParameters = New OleDbParameter() {New OleDbParameter("@ProfessionalId", OleDbType.Integer), New OleDbParameter("@Name", OleDbType.VarChar), New OleDbParameter("@Number", OleDbType.VarChar), New OleDbParameter("@GetDate", OleDbType.Date), New OleDbParameter("@GetMethod", OleDbType.VarChar), New OleDbParameter("@PaperId", OleDbType.Integer)} myParameters(0).Value = professionalId
myParameters(1).Value = name
myParameters(2).Value = number
myParameters(3).Value = getDate
myParameters(4).Value = getMethod
myParameters(5).Value = paperId
不能颠倒,以前用SQLServer的,没有这个问题,Access真是差啊。谢谢各位了。
myParameters = New OleDbParameter() {New OleDbParameter("@ProfessionalId", OleDbType.Integer), New OleDbParameter("@Name", OleDbType.VarChar), New OleDbParameter("@Number", OleDbType.VarChar), New OleDbParameter("@GetDate", OleDbType.Date), New OleDbParameter("@GetMethod", OleDbType.VarChar)}
myParameters(0).Value = professionalId
myParameters(1).Value = name
myParameters(2).Value = number
myParameters(3).Value = getDate
myParameters(4).Value = getMethod不能颠倒,以前用SQLServer的,没有这个问题,Access真是差啊。谢谢各位了。