Solved

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

Posted on 2008-10-08
11
198 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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

740 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