Solved

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

Posted on 2008-10-08
11
193 Views
Last Modified: 2010-04-23
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
0
Comment
Question by:nwjordan1
  • 6
  • 5
11 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22670007
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.
0
 

Author Comment

by:nwjordan1
ID: 22670161
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.
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 125 total points
ID: 22671080
@alias varchar=4 means that if no value is specifed for the 30 character @alias parameter, implicitly convert the number 4 into the string '4' and use it as the value for @alias.


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


As for the second part:

datediff(d, BeginDate,getdate()) <=13

0
 

Author Comment

by:nwjordan1
ID: 22671286
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.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22671634
do you need all of it, or just help?

I can help if you show me what you have to start with.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:nwjordan1
ID: 22671716
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?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22671827
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.
0
 

Author Comment

by:nwjordan1
ID: 22673004
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.&nbsp; You will need
            to delete one of your current ads if you wish to start a new ad to stay under
            the maximum.&nbsp;
        </PagerTemplate>
    </asp:GridView>
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22673027
Obviously this must be an older copy since you said you integrated the procedure in.
0
 

Author Comment

by:nwjordan1
ID: 22673064
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>
0
 

Author Closing Comment

by:nwjordan1
ID: 31504272
My stored procedure problem was handled, but I never got any other assistance on the VB.net part of my question.
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now