Evaluere en update sql query der ikke opdaterer nogle rækker
HejHvordan analyserer jeg bedst denne sql-statement. Får at vide, at den opdaterer 0 rækker men kan ikke lige finde ud af, hvor det går galt.
Function updateEmployee(ByVal employeeId As Integer, ByVal surName As String, ByVal lastname As String, ByVal shortName As String, ByVal address As String, ByVal address2 As String, ByVal zipcode As Integer, ByVal telephone As String, ByVal mobile As String, ByVal email As String, ByVal active As Boolean, ByVal userName As String, ByVal password As String) As Integer
Dim dbConnection As System.Data.IDbConnection = New System.Data.OleDb.OleDbConnection(ConfigurationSettings.AppSettings("connectionstring"))
Dim queryString As String = "UPDATE [Employee] SET [Telephone]=@Telephone, [ShortName]=@ShortName, [Mobile]=@Mobile, [Lastname]=@Lastname, [Email]=@Email, [Password]=@Password, [Address]=@Address, [SurName]=@SurName, [UserName]=@UserName, [Zipcode]=@Zipcode, [Address2]=@Address2, [Active]=@Active WHERE ([Employee].[EmployeeId] = @EmployeeId)"
Dim dbCommand As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
Dim dbParam_employeeId As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
dbParam_employeeId.ParameterName = "@EmployeeId"
dbParam_employeeId.Value = employeeId
dbParam_employeeId.DbType = System.Data.DbType.Int32
dbCommand.Parameters.Add(dbParam_employeeId)
Dim dbParam_surName As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
dbParam_surName.ParameterName = "@SurName"
dbParam_surName.Value = surName
dbParam_surName.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_surName)
Dim dbParam_lastname As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
dbParam_lastname.ParameterName = "@Lastname"
dbParam_lastname.Value = lastname
dbParam_lastname.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_lastname)
Dim dbParam_shortName As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
dbParam_shortName.ParameterName = "@ShortName"
dbParam_shortName.Value = shortName
dbParam_shortName.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_shortName)
Dim dbParam_address As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
dbParam_address.ParameterName = "@Address"
dbParam_address.Value = address
dbParam_address.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_address)
Dim dbParam_address2 As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
dbParam_address2.ParameterName = "@Address2"
dbParam_address2.Value = address2
dbParam_address2.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_address2)
Dim dbParam_zipcode As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
dbParam_zipcode.ParameterName = "@Zipcode"
dbParam_zipcode.Value = zipcode
dbParam_zipcode.DbType = System.Data.DbType.Int32
dbCommand.Parameters.Add(dbParam_zipcode)
Dim dbParam_telephone As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
dbParam_telephone.ParameterName = "@Telephone"
dbParam_telephone.Value = telephone
dbParam_telephone.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_telephone)
Dim dbParam_mobile As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
dbParam_mobile.ParameterName = "@Mobile"
dbParam_mobile.Value = mobile
dbParam_mobile.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_mobile)
Dim dbParam_email As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
dbParam_email.ParameterName = "@Email"
dbParam_email.Value = email
dbParam_email.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_email)
Dim dbParam_active As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
dbParam_active.ParameterName = "@Active"
dbParam_active.Value = active
dbParam_active.DbType = System.Data.DbType.Boolean
dbCommand.Parameters.Add(dbParam_active)
Dim dbParam_userName As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
dbParam_userName.ParameterName = "@UserName"
dbParam_userName.Value = userName
dbParam_userName.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_userName)
Dim dbParam_password As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
dbParam_password.ParameterName = "@Password"
dbParam_password.Value = password
dbParam_password.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_password)
Dim rowsAffected As Integer = 0
dbConnection.Open
Try
rowsAffected = dbCommand.ExecuteNonQuery
Finally
dbConnection.Close
End Try
Return rowsAffected
End Function
Databasen indeholder følgende felter:
SurName - string
Lastname - string
ShortName - string
Address - string
Address2 - string
Zipcode - integer
Telephone - string
Mobile - string
Email - string
Created - integer
Active - boolean
UserName - string
Password - string
Har forsøgt med trace men blev ik klogere.
Har kørt query'en direkte i access og får at vide, at der er en typekonverteringsfejl, men kan ikke finde den.
mvh
Dennis
