Solved

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

Posted on 2008-10-08
11
196 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

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
 

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

810 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