BH
Hi all,
I'm trying to use the below c# syntax to update a single row in my table.
tenantTableAdapter.Update(new[] { (tenantBindingSource.Current asDataRowView).Row });
However, when I run the above code I get an exception, saying "Data type mismatch in criteria expression."
I know it's related to the fact that my SELECT statement has "LEFT OUTER JOIN" while my update statement does not. How do I modify my Update statement to match with my SELECT statement? Or how do I change my c# code (above) to exclude the joined columns and just update the columns in my "tenant" table? Either of the solutions will work for me. I've been strugling with this for several days now. Any help would be appreciated.
My SELECT statement is as follows:
SELECT Tenant.ID, Tenant.[First Name], Tenant.[Last Name], Tenant.Address, Tenant.City, Tenant.State, Tenant.Zip, Tenant.[Home Phone], Tenant.[Cell Phone], Tenant.[Work Phone], Tenant.[Rented Unit], Tenant.[Security Deposit], Tenant.[Move In], Tenant.[Move Out], Tenant.TenantID, Tenant.UnitID, Tenant.PropertyID, Tenant.OwnerID, Owner.Company, Owner.ID AS Expr1, Property.[Property Address], Units.[Unit Address] FROM (((Tenant LEFT OUTER JOIN Units ON Tenant.UnitID = Units.ID) LEFT OUTER JOIN Property ON Tenant.PropertyID = Property.ID) LEFT OUTER JOIN Owner ON Tenant.OwnerID = Owner.ID)
And my UPDATE statement is as follows:
UPDATE Tenant SET [First Name] = ?, [Last Name] = ?, Address = ?, City = ?, State = ?, Zip = ?, [Home Phone] = ?, [Cell Phone] = ?, [Work Phone] = ?, [Rented Unit] = ?, [Security Deposit] = ?, [Move In] = ?, [Move Out] = ?, TenantID = ?, UnitID = ?, PropertyID = ?, OwnerID = ? WHERE (ID = ?) AND (? = 1 AND [First Name] IS NULL OR [First Name] = ?) AND (? = 1 AND [Last Name] IS NULL OR [Last Name] = ?) AND (? = 1 AND Address IS NULL OR Address = ?) AND (? = 1 AND City IS NULL OR City = ?) AND (? = 1 AND State IS NULL OR State = ?) AND (? = 1 AND Zip IS NULL OR Zip = ?) AND (? = 1 AND [Home Phone] IS NULL OR [Home Phone] = ?) AND (? = 1 AND [Cell Phone] IS NULL OR [Cell Phone] = ?) AND (? = 1 AND [Work Phone] IS NULL OR [Work Phone] = ?) AND (? = 1 AND [Rented Unit] IS NULL OR [Rented Unit] = ?) AND (? = 1 AND [Security Deposit] IS NULL OR [Security Deposit] = ?) AND (? = 1 AND [Move In] IS NULL OR [Move In] = ?) AND (? = 1 AND [Move Out] IS NULL OR [Move Out] = ?) AND (? = 1 AND TenantID IS NULL OR TenantID = ?) AND (? = 1 AND UnitID IS NULL OR UnitID = ?) AND (? = 1 AND PropertyID IS NULL OR PropertyID = ?) AND (? = 1 AND OwnerID IS NULL OR OwnerID = ?)
I've posted this question in asp.net but with no successful answer yet. Thanks so much in advance.
Aron
Hi all,
I'm trying to use the below c# syntax to update a single row in my table.
tenantTableAdapter.Update(new[] { (tenantBindingSource.Current asDataRowView).Row });
However, when I run the above code I get an exception, saying "Data type mismatch in criteria expression."
I know it's related to the fact that my SELECT statement has "LEFT OUTER JOIN" while my update statement does not. How do I modify my Update statement to match with my SELECT statement? Or how do I change my c# code (above) to exclude the joined columns and just update the columns in my "tenant" table? Either of the solutions will work for me. I've been strugling with this for several days now. Any help would be appreciated.
My SELECT statement is as follows:
SELECT Tenant.ID, Tenant.[First Name], Tenant.[Last Name], Tenant.Address, Tenant.City, Tenant.State, Tenant.Zip, Tenant.[Home Phone], Tenant.[Cell Phone], Tenant.[Work Phone], Tenant.[Rented Unit], Tenant.[Security Deposit], Tenant.[Move In], Tenant.[Move Out], Tenant.TenantID, Tenant.UnitID, Tenant.PropertyID, Tenant.OwnerID, Owner.Company, Owner.ID AS Expr1, Property.[Property Address], Units.[Unit Address] FROM (((Tenant LEFT OUTER JOIN Units ON Tenant.UnitID = Units.ID) LEFT OUTER JOIN Property ON Tenant.PropertyID = Property.ID) LEFT OUTER JOIN Owner ON Tenant.OwnerID = Owner.ID)
And my UPDATE statement is as follows:
UPDATE Tenant SET [First Name] = ?, [Last Name] = ?, Address = ?, City = ?, State = ?, Zip = ?, [Home Phone] = ?, [Cell Phone] = ?, [Work Phone] = ?, [Rented Unit] = ?, [Security Deposit] = ?, [Move In] = ?, [Move Out] = ?, TenantID = ?, UnitID = ?, PropertyID = ?, OwnerID = ? WHERE (ID = ?) AND (? = 1 AND [First Name] IS NULL OR [First Name] = ?) AND (? = 1 AND [Last Name] IS NULL OR [Last Name] = ?) AND (? = 1 AND Address IS NULL OR Address = ?) AND (? = 1 AND City IS NULL OR City = ?) AND (? = 1 AND State IS NULL OR State = ?) AND (? = 1 AND Zip IS NULL OR Zip = ?) AND (? = 1 AND [Home Phone] IS NULL OR [Home Phone] = ?) AND (? = 1 AND [Cell Phone] IS NULL OR [Cell Phone] = ?) AND (? = 1 AND [Work Phone] IS NULL OR [Work Phone] = ?) AND (? = 1 AND [Rented Unit] IS NULL OR [Rented Unit] = ?) AND (? = 1 AND [Security Deposit] IS NULL OR [Security Deposit] = ?) AND (? = 1 AND [Move In] IS NULL OR [Move In] = ?) AND (? = 1 AND [Move Out] IS NULL OR [Move Out] = ?) AND (? = 1 AND TenantID IS NULL OR TenantID = ?) AND (? = 1 AND UnitID IS NULL OR UnitID = ?) AND (? = 1 AND PropertyID IS NULL OR PropertyID = ?) AND (? = 1 AND OwnerID IS NULL OR OwnerID = ?)
I've posted this question in asp.net but with no successful answer yet. Thanks so much in advance.
Aron
