 | cwilso8 | | Asp.Net User |
| Adding Columns to the ads table | 10/12/2006 3:23:48 AM |
| 0/0 |   |
|
I have been trying to add a new column to the ads table. In an earlier post I followed the advice given and have performed the following tasks: Starting with the database: 1. Add extra fields to Ads table (You can create an auxiliary table that has AdID as key - but I won't consider that solution here.) 2. Modify ClassifiedsView_Ads view with new fields. 3. Modify the trigger on the Ads table "AdUpdated" to reflect the new fields added to the Ads table. Other triggers are okay. 4. The following stored procedures are affect/not affected (this is a first-pass estimate looking at the functionality quickly so check for yourself):
GetAdByID() - modify to return new fields
ExpireAd() - no change
GeAdsByRandomOrder() - no change - uses ClassifiedView_Ads
GetAdsByStatus() - no change - uses ClassifiedView_Ads
GetAllAdsByQuery() - modify with any new input parameter that involve new fields
GetExpiredAds() - no change - uses ClassifiedView_Ads
GetSavedAds() - modify to return new fields
InsertAd() - modify to insert new fields
InsertSavedAd() - no change
MoveAds() - no change
RelistAd() - modify to account for new fields
RemoveAd() - no change
RemoveAdsByStatus() - no change
RemoveSavedAd() - no change
UpdateAd() - modify to account for new fields
UpdateAdCategory() - no change
UpdateAdLevel() - no change
UpdateAdStats() - no change
UpdateAdStatus() - no change In the Web App: 5. Go into App_Code\DAL\Ads.xsd and add any new parameters that were created in the database stored procedures. You need to add the new columns to the Ads DataTable. 6. In the App_Code\BLL\Ads.cs you will have to make changes to the AdsDB class to account for all the input/output of the new fields. In particular, the UpdateAd method, GetAdsByQuery method, GetActiveAdsByQuery method, and InsertAd method. 7. Aspx files that do the data-related work. EditAd.aspx - modify objectdatasource ID="AdDataSource" to have appropriate <asp:Parameters> PostAd.aspx.cs - change code to reflect new parameters or inserting and relisting operations. Admin/Ads.aspx - similar to EditAd.aspx need to modify ObjectDataSource parameters.
On step 7 is where I run into trouble. On the EditAd.aspx, when I configure the Data Source I am getting the following error: "The type 'AspNet.StarterKits.Classified.BusinessLogicLayer.AdsDB' could not be loaded. If the type is located in the App_Code foler, please check that it compiles. If the type is located in a compiled assembly, please check that the assembly is referenced by the project. Could not load type 'AspNet.StarterKits.Classifieds.BusinessLogicLayer.AdsDB' from assembly 'Microsoft.VisualStudio.Web, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b0" When I compile the BLL, this is the line that has the problems: adId = CInt(db.InsertAd(memberId, categoryId, title, description, url, price, location, Bathrooms, expirationDate, dateCreated, dateApproved, numViews, numResponses, CInt(adLevel), CInt(adStatus), CInt(adType))) The error associated is: Value of type 'System.Nullable(Of Date)' cannot be converted to 'Integer'. I am trying to add the column Bathrooms, and I inserted it here after location. Help appreciated! |
| Caddre | | Asp.Net User |
| Re: Adding Columns to the ads table | 10/12/2006 10:02:54 AM |
| 0/0 | |
|
I am not sure if they were talking about adding a column because I see a lot of code but not one line of SQL which is what you need to ALTER TABLE add column, you can do it by code or do it in management studio with your database open right click on the table then click ALTER and generate the ALTER statement then add the column definition and then execute. Try the link below MSDN ALTER TABLE. Hope this helps. http://msdn2.microsoft.com/en-us/library/ms190273.aspx
Kind regards, Gift Peddie |
| mattbug | | Asp.Net User |
| Re: Adding Columns to the ads table | 10/12/2006 12:43:22 PM |
| 0/0 | |
|
 cwilso8: adId = CInt(db.InsertAd(memberId, categoryId, title, description, url, price, location, Bathrooms, expirationDate, dateCreated, dateApproved, numViews, numResponses, CInt(adLevel), CInt(adStatus), CInt(adType))) The error associated is: Value of type 'System.Nullable(Of Date)' cannot be converted to 'Integer'. I am trying to add the column Bathrooms, and I inserted it here after location.
From the error, it sounds like it's trying to insert the "Bathrooms" value (an Integer) into a date field (maybe "expirationDate"), or possibly it's trying to insert the "dateApproved" value (a nullable Date field) into "numViews" (an Integer). Check the following InsertAd() related items:
classifieds_Ads table
InsertAd() stored procedure
DAL/Ads.xsd DataTable columns
DAL/Ads.xsd TableAdapter InsertAd() query
BLL/Ads.vb InsertAd() function |
| ryancoc22 | | Asp.Net User |
| Re: Adding Columns to the ads table | 10/13/2006 2:36:56 PM |
| 0/0 | |
|
Mattbug, What your saying seems to be what is going on. I am not quite sure on how to fix it though. We did not change anything with that variable all we did was add that column into the code. This is what the bll looks like: #Region "InsertingAds" Public Shared Function InsertAd(ByVal memberId As Integer, ByVal categoryId As Integer, ByVal title As String, ByVal description As String, ByVal Bedrooms As String, ByVal url As String, ByVal price As Decimal, ByVal location As String, ByVal numDaysActive As Integer, ByVal adLevel As AdLevel, ByVal adStatus As AdStatus, ByVal adType As AdType) As Integer Dim s As SiteSettings = SiteSettings.GetSharedSettings() Dim numViews As Integer = 0 Dim numResponses As Integer = 0 Dim dateCreated As DateTime = DateTime.Now Dim dateApproved As System.Nullable(Of DateTime) If numDaysActive > s.MaxAdRunningDays Then numDaysActive = s.MaxAdRunningDays End If If numDaysActive < 1 Then numDaysActive = 1 End If Dim expirationDate As DateTime = DateTime.Today.AddDays(numDaysActive) If s.AdActivationRequired Then adStatus = adStatus.ActivationPending dateApproved = Nothing Else adStatus = adStatus.Activated dateApproved = dateCreated End If If adLevel = adLevel.Unspecified Then adLevel = adLevel.Normal End If Dim adId As Integer = DefaultValues.IdNullValue Dim ad As AdsDataComponent.AdsRow = Nothing Using db As New AdsDataAdapter() adId = CInt(db.InsertAd(memberId, categoryId, title, description, Bedrooms, url, price, location, expirationDate, dateCreated, dateApproved, numViews, numResponses, CInt(adLevel), CInt(adStatus), CInt(adType))) If s.AdminNotification = AdminNotificationSetting.EachAd Then ad = GetFirstRow(db.GetAdById(adId)) End If End Using If Not (ad Is Nothing) Then Maintenance.SendAdNotification(ad) End If Return adId End Function
Date approved is what is underlined with the error Value of type 'System.Nullable(Of Date)' cannot be converted to 'Integer'. any thoughts much appreciated ryan |
| ryancoc22 | | Asp.Net User |
| Re: Adding Columns to the ads table | 10/13/2006 2:36:57 PM |
| 0/0 | |
|
Mattbug, What your saying seems to be what is going on. I am not quite sure on how to fix it though. We did not change anything with that variable all we did was add that column into the code. This is what the bll looks like: #Region "InsertingAds" Public Shared Function InsertAd(ByVal memberId As Integer, ByVal categoryId As Integer, ByVal title As String, ByVal description As String, ByVal Bedrooms As String, ByVal url As String, ByVal price As Decimal, ByVal location As String, ByVal numDaysActive As Integer, ByVal adLevel As AdLevel, ByVal adStatus As AdStatus, ByVal adType As AdType) As Integer Dim s As SiteSettings = SiteSettings.GetSharedSettings() Dim numViews As Integer = 0 Dim numResponses As Integer = 0 Dim dateCreated As DateTime = DateTime.Now Dim dateApproved As System.Nullable(Of DateTime) If numDaysActive > s.MaxAdRunningDays Then numDaysActive = s.MaxAdRunningDays End If If numDaysActive < 1 Then numDaysActive = 1 End If Dim expirationDate As DateTime = DateTime.Today.AddDays(numDaysActive) If s.AdActivationRequired Then adStatus = adStatus.ActivationPending dateApproved = Nothing Else adStatus = adStatus.Activated dateApproved = dateCreated End If If adLevel = adLevel.Unspecified Then adLevel = adLevel.Normal End If Dim adId As Integer = DefaultValues.IdNullValue Dim ad As AdsDataComponent.AdsRow = Nothing Using db As New AdsDataAdapter() adId = CInt(db.InsertAd(memberId, categoryId, title, description, Bedrooms, url, price, location, expirationDate, dateCreated, dateApproved, numViews, numResponses, CInt(adLevel), CInt(adStatus), CInt(adType))) If s.AdminNotification = AdminNotificationSetting.EachAd Then ad = GetFirstRow(db.GetAdById(adId)) End If End Using If Not (ad Is Nothing) Then Maintenance.SendAdNotification(ad) End If Return adId End Function
Date approved is what is underlined with the error Value of type 'System.Nullable(Of Date)' cannot be converted to 'Integer'. any thoughts much appreciated ryan |
| ryancoc22 | | Asp.Net User |
| Re: Adding Columns to the ads table | 10/13/2006 2:36:58 PM |
| 0/0 | |
|
Mattbug, What your saying seems to be what is going on. I am not quite sure on how to fix it though. We did not change anything with that variable all we did was add that column into the code. This is what the bll looks like: #Region "InsertingAds" Public Shared Function InsertAd(ByVal memberId As Integer, ByVal categoryId As Integer, ByVal title As String, ByVal description As String, ByVal Bedrooms As String, ByVal url As String, ByVal price As Decimal, ByVal location As String, ByVal numDaysActive As Integer, ByVal adLevel As AdLevel, ByVal adStatus As AdStatus, ByVal adType As AdType) As Integer Dim s As SiteSettings = SiteSettings.GetSharedSettings() Dim numViews As Integer = 0 Dim numResponses As Integer = 0 Dim dateCreated As DateTime = DateTime.Now Dim dateApproved As System.Nullable(Of DateTime) If numDaysActive > s.MaxAdRunningDays Then numDaysActive = s.MaxAdRunningDays End If If numDaysActive < 1 Then numDaysActive = 1 End If Dim expirationDate As DateTime = DateTime.Today.AddDays(numDaysActive) If s.AdActivationRequired Then adStatus = adStatus.ActivationPending dateApproved = Nothing Else adStatus = adStatus.Activated dateApproved = dateCreated End If If adLevel = adLevel.Unspecified Then adLevel = adLevel.Normal End If Dim adId As Integer = DefaultValues.IdNullValue Dim ad As AdsDataComponent.AdsRow = Nothing Using db As New AdsDataAdapter() adId = CInt(db.InsertAd(memberId, categoryId, title, description, Bedrooms, url, price, location, expirationDate, dateCreated, dateApproved, numViews, numResponses, CInt(adLevel), CInt(adStatus), CInt(adType))) If s.AdminNotification = AdminNotificationSetting.EachAd Then ad = GetFirstRow(db.GetAdById(adId)) End If End Using If Not (ad Is Nothing) Then Maintenance.SendAdNotification(ad) End If Return adId End Function
Date approved is what is underlined with the error Value of type 'System.Nullable(Of Date)' cannot be converted to 'Integer'. any thoughts much appreciated ryan |
| karasakal | | Asp.Net User |
| Re: Adding Columns to the ads table | 10/13/2006 3:11:11 PM |
| 0/0 | |
|
Hi, I have the same problem in C# also.. I checked the list DAL/BLL/ SPROC's several times! (adId = (int) db.InsertAd(memberId, categoryId, title, description, url, price, location, bathrooms, expirationDate, dateCreated, dateApproved, numViews, numResponses, (int)adLevel, (int)adStatus, (int)adType));After Compiling, the first error: Error 1 The best overloaded method match for 'AdsDataComponentTableAdapters.AdsDataAdapter.InsertAd(int?, int?, string, string, string, decimal?, string, System.DateTime?, System.DateTime?, System.DateTime?, int?, int?, int?, int?, int?, string)' has some invalid arguments .... Help is needed! |
| mattbug | | Asp.Net User |
| Re: Adding Columns to the ads table | 10/13/2006 4:01:35 PM |
| 0/0 | |
|
karasakal:
(adId = (int) db.InsertAd(memberId, categoryId, title, description, url, price, location, bathrooms, expirationDate, dateCreated, dateApproved, numViews, numResponses, (int)adLevel, (int)adStatus, (int)adType)); Error 1 The best overloaded method match for 'AdsDataComponentTableAdapters.AdsDataAdapter.InsertAd(int?, int?, string, string, string, decimal?, string, System.DateTime?, System.DateTime?, System.DateTime?, int?, int?, int?, int?, int?, string)' has some invalid arguments ....
The "best overloaded method match..." error means you're passing invalid arguments to the InsertAd method (or in this case it looks like you're missing an argument). Make sure you've correctly updated the code where you call InsertAd() -- I know of one instance in the code-behind for PostAd.aspx: Dim adId As Integer = AdsDB.InsertAd(memberId, categoryId, title, description, url, price, location, numDays, AdLevel.Unspecified, AdStatus.Unspecified, adType) I suggest searching for "InsertAd" throughout your entire project. Make sure every instance is has the correct parameters, with the correct types, in the correct order. |
| karasakal | | Asp.Net User |
| Re: Adding Columns to the ads table | 10/14/2006 7:07:18 PM |
| 0/0 | |
|
Thanks a lot for your help, I found some strings to change as behindcode as you explained! regards |
|