Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 204
  • Last Modified:

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
0
nwjordan1
Asked:
nwjordan1
  • 6
  • 5
1 Solution
 
BrandonGalderisiCommented:
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
 
nwjordan1Author Commented:
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
 
BrandonGalderisiCommented:
@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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
nwjordan1Author Commented:
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
 
BrandonGalderisiCommented:
do you need all of it, or just help?

I can help if you show me what you have to start with.
0
 
nwjordan1Author Commented:
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
 
BrandonGalderisiCommented:
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
 
nwjordan1Author Commented:
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
 
BrandonGalderisiCommented:
Obviously this must be an older copy since you said you integrated the procedure in.
0
 
nwjordan1Author Commented:
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
 
nwjordan1Author Commented:
My stored procedure problem was handled, but I never got any other assistance on the VB.net part of my question.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now