asked on

VB.net, sql server 2005, i.e 6.0

I'm trying to figure out the best VB.net code that will call a stored procedure on page load written to count the number of Ads in tblAds submitted by a particular user, then based of the value of the count, restrict the user from entering the page if ad count is greater than 2. If restricted, I would like to display a message that states the user has too many ads and give them the option either go delete one of their existing ads or just go back to the home page. Here is my stored proc code:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[CountAds]

      (
      @alias varchar = 4

      )

AS
      /* SET NOCOUNT ON */
      SELECT COUNT(alias) AS NumberofAds FROM tblAds WHERE (alias = @alias) AND (beginDate >= GETDATE() - 13)
      RETURN

BrandonGalderisi

I'll leave the .Net part to the .Net experts but I want to comment on two things about your procedure.

First: @alias varchar = 4
if no length is specified, your varchar will be varchar(30). Secondly, setting a default of an INT (4) into it. I would recommend that if it is truly '4' do specify that.

So if alias in tblAds is varchar(10)
@alias varchar(10) = '4'

Second: beginDate >= GETDATE() - 13

You may be aware of this, but getdate() is datetime.

So if your begindate is Oct-1-2008 (stored with a time of 00:00:00 which is 12am), and you run this at 9am on Oct-14-2008, Oct-1-2008 will NOT appear because it is less than 9am.

nwjordan1

ASKER

The alias column is set up in the table as varchar(4) because all of our aliases only have 4 characters. So, how would I do I declare the parameter correctly?

Thanks for the info on the date. I have two date columns in my table (beginDate and endDate). They both have the date followed by the time which is stored with the time as you suggested (ex. 10/7/2008 12:00:00 AM). So how would I calculate the date difference in the proc. to yield the correct results. Basically the way it's set up, the view ads page displays ads for 14 days from the beginDate. I'm trying to restrict the number of ads someone can post now because people are abusing the ads and basically running a garage sale every week with multiple posts. Also, they repost the same ad in different categories which bogs down the site. I thought that imposing an ad count restriction based of the count of a particular person's log in credentials (ie. alias) would be the easiest way to help out this problem give the ad site's current set up.

ASKER CERTIFIED SOLUTION

BrandonGalderisi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

nwjordan1

ASKER

Thanks Brandon! That worked great. Now just need to the VB.Net assistance to find the best way to pull the results into my page and evaluate.

BrandonGalderisi

do you need all of it, or just help?

I can help if you show me what you have to start with.

nwjordan1

ASKER

Well, if I'm stuck as to the best way to use that stored proc. I created the sqldatasource on the page and mapped it to the stored proc. Now, in the code behind page, what's the best way to pull in the count, determine if it's greater than 3 and then give the friendly message that they will need to delete an ad first before they can post a new one?

BrandonGalderisi

can you post the code from your .vb file where the sqldatasource is called right now? I'm not a vb.net guy, but i've fiddled enough that i can probably help.

nwjordan1

ASKER

Here's the code I have so far on my main page. I haven't put anything in the code behind page yet. The code below basically shows the datasource and control parameter that I'm passing into the query to base the count off of. (txtAlias). I filled in the pager template with some wording in case the count is 3 or greater. That's as far as I've gotten.

<asp:SqlDataSource ID="CountAds" runat="server"
ConnectionString="Data Source=eucsqd05.unitopr.unitint.test.statefarm.org;Initial Catalog=SFSQL_NET00075;Integrated Security=True"
ProviderName="System.Data.SqlClient" SelectCommand="SELECT Count([alias]) AS NumberofAds FROM [tblAds] WHERE ([alias] = @alias) AND [beginDate] >= GetDate() - 13
">
<SelectParameters>
<asp:ControlParameter ControlID="txtAlias" Name="alias" PropertyName="Text" />
</SelectParameters>
</asp:SqlDataSource>
<asp:GridView ID="gvCountAds" runat="server" AutoGenerateColumns="False"
DataSourceID="CountAds">
<Columns>
<asp:BoundField DataField="NumberofAds" HeaderText="NumberofAds"
ReadOnly="True" SortExpression="NumberofAds" />
</Columns>
<PagerTemplate>
The maximum number of ads you can have live at any one time is 3.  You will need
to delete one of your current ads if you wish to start a new ad to stay under
the maximum. 
</PagerTemplate>
</asp:GridView>

BrandonGalderisi

Obviously this must be an older copy since you said you integrated the procedure in.

nwjordan1

ASKER

True. Sorry about that. Below is the updated code showing the stored proc.

<asp:SqlDataSource ID="CountAds" runat="server"
ConnectionString="Data Source=eucsqd05.unitopr.unitint.test.statefarm.org;Initial Catalog=SFSQL_NET00075;Integrated Security=True"
ProviderName="System.Data.SqlClient" SelectCommand="CountAds"
SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:ControlParameter ControlID="txtAlias" Name="alias" PropertyName="Text" />
</SelectParameters>
</asp:SqlDataSource>

nwjordan1

ASKER

My stored procedure problem was handled, but I never got any other assistance on the VB.net part of my question.