Search Results

Search found 16 results on 1 pages for 'myheadhurts'.

Page 1/1 | 1 

• CONVERT(int, (datepart(month, @search)), (datepart(day, @search)), DateAdd(year, Years.Year - (datepart(year, @search)))

  - by MyHeadHurts

  In the query the top part is getting all the years that will run in the stored procedure. Works fine But at first i just wanted to run the queries for yesterdays date for all the years, but now i realized i want the user to select a date that will be in a parameter @search Booked <= CONVERT(int,DateAdd(year, Years.Year - Year(getdate()), DateAdd(day, DateDiff(day, 2, getdate()), 1))) this should be easy because normally it would just be Booked <= CONVERT(int,@search) but the problem is i want to do something like a Booked <= CONVERT(int, (datepart(month, @search)), (datepart(day, @search)), DateAdd(year, Years.Year - (datepart(year, @search))) would something like that work i dont need to worry about subtracting days but i still need to worry about the years WITH Years AS ( SELECT DATEPART(year, GETDATE()) [Year] UNION ALL SELECT [Year]-1 FROM Years WHERE [Year]>@YearToGet ), q_00 as ( select DIVISION , DYYYY , sum(PARTY) as asofPAX , sum(APRICE) as asofSales from dbo.B101BookingsDetails INNER JOIN Years ON B101BookingsDetails.DYYYY = Years.Year where Booked <= CONVERT(int,DateAdd(year, Years.Year - Year(getdate()), DateAdd(day, DateDiff(day, 2, getdate()), 1))) and DYYYY = Years.Year group by DIVISION, DYYYY, years.year having DYYYY = years.year ),

  Read the article

• tigra calendar and asp.net help, or using javascript in asp.net

  - by MyHeadHurts

  <input type="text" name="testinput" /> <script language="JavaScript"> new tcal ({ // form name 'formname': 'testform', // input name 'controlname': 'testinput' }); </script> <form id="form2" runat="server"> <div style="height: 897px"> <asp:ScriptManager ID="ScriptManager1" runat="server"> </asp:ScriptManager> <br /> <asp:Button ID="Button1" runat="server" Text="Update" Width="122px" /> <br /> <br /> TESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTEST<br /> TESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTEST<br /> TESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTEST<br /> TESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTEST<br /> TESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTEST<br /> aaaaaaaaaaaaaaaaaaaaaaaa<br /> <br /> <br /> <asp:UpdatePanel ID="UpdatePanel1" runat="server"> <ContentTemplate> <asp:GridView ID="GridView2" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None" Height="147px" Width="694px"> <RowStyle BackColor="#E3EAEB" /> <FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" /> <PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" /> <SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" /> <HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" /> <EditRowStyle BackColor="#7C6F57" /> <AlternatingRowStyle BackColor="White" /> </asp:GridView> <br /> <br /> <asp:TextBox ID="TextBox1" runat="server" Height="33px" Width="179px">fsafasfa</asp:TextBox> </ContentTemplate> </asp:UpdatePanel> </div> </form> I am using tigra calender in an asp.net page, but the datepicker will not show up. The img folder path is the same and i even placed it in my apdata. I took the code straight from the sample page, and I have even used tigra calender before, but not with asp.net any ideas. Is there another calender tool i should be using?

  Read the article

• Use a certain select statement in a stored procedure depending on the Exec statement

  - by MyHeadHurts

  Alright so i am not even sure if this is possible I have a q_00 and q_01 and q_02 which are all in my stored procedure. then on the bottom i have 3 select statements that select a certain catagory for example Sales,Net Sales and INS sales What i want to be able to do is if the user types exec (name of my sp) (sales) (and a year which is the @yearparameter) it will run the sales select statement If they type Exec (name of my SP) netsales (@Yeartoget) it will show the net sales is this possible or do i need multiple stored procedures ALTER PROCEDURE [dbo].[casof] @YearToGet int as ; with q_00 as ( select DIVISION , SDESCR , DYYYY , sum(APRICE) as asofSales , sum(PARTY) as asofPAX , sum(NetAmount) as asofNetSales , sum(InsAmount) as asofInsSales , sum(CancelRevenue) as asofCXSales , sum(OtherAmount) as asofOtherSales , sum(CXVALUE) as asofCXValue from dbo.B101BookingsDetails where Booked <= CONVERT(int,DateAdd(year, @YearToGet - Year(getdate()), DateAdd(day, DateDiff(day, 1, getdate()), 0))) and DYYYY = @YearToGet group by DIVISION, SDESCR, DYYYY ), q_01 as ( select DIVISION , SDESCR , DYYYY , sum(APRICE) as YESales , sum(PARTY) as YEPAX , sum(NetAmount) as YENetSales , sum(InsAmount) as YEInsSales , sum(CancelRevenue) as YECXSales , sum(OtherAmount) as YEOtherSales , sum(CXVALUE) as YECXValue from dbo.B101BookingsDetails where DYYYY=@YearToGet group by DIVISION, SDESCR, DYYYY ), q_02 as ( select DIVISION , SDESCR , DYYYY , sum(APRICE) as CurrentSales , sum(PARTY) as CurrentPAX , sum(NetAmount) as CurrentNetSales , sum(InsAmount) as CurrentInsSales , sum(CancelRevenue) as CurrentCXSales , sum(OtherAmount) as CurrentOtherSales , sum(CXVALUE) as CurrentCXValue from dbo.B101BookingsDetails where Booked <= CONVERT(int,DateAdd(year, (year( getdate() )) - Year(getdate()), DateAdd(day, DateDiff(day, 1, getdate()), 0))) and DYYYY = (year( getdate() )) group by DIVISION, SDESCR, DYYYY ) select a.DIVISION , a.SDESCR , a.DYYYY , asofSales , asofPAX , YESales , YEPAX , CurrentSales , CurrentPAX , asofsales/ ISNULL(NULLIF(yesales,0),1) as percentsales , asofpax/yepax as percentpax ,currentsales/ISNULL(NULLIF((asofsales/ISNULL(NULLIF(yesales,0),1)),0),1) as projectedsales ,currentpax/ISNULL(NULLIF((asofpax/ISNULL(NULLIF(yepax,0),1)),0),1) as projectedpax from q_00 as a join q_01 as b on (b.DIVISION = a.DIVISION and b.SDESCR = a.SDESCR and b.DYYYY = a.DYYYY) join q_02 as c on (b.DIVISION = c.DIVISION and b.SDESCR = c.SDESCR) order by a.DIVISION, a.SDESCR, a.DYYYY ; select a.DIVISION , a.SDESCR , a.DYYYY , asofPAX , asofNetSales , YEPAX , YENetSales , CurrentPAX , CurrentNetSales , asofnetsales/ ISNULL(NULLIF(yenetsales,0),1) as percentnetsales , asofpax/yepax as percentpax ,currentnetsales/ISNULL(NULLIF((asofnetsales/ISNULL(NULLIF(yenetsales,0),1)),0),1) as projectednetsales ,currentpax/ISNULL(NULLIF((asofpax/ISNULL(NULLIF(yepax,0),1)),0),1) as projectedpax from q_00 as a join q_01 as b on (b.DIVISION = a.DIVISION and b.SDESCR = a.SDESCR and b.DYYYY = a.DYYYY) join q_02 as c on (b.DIVISION = c.DIVISION and b.SDESCR = c.SDESCR) order by a.DIVISION, a.SDESCR, a.DYYYY ; select a.DIVISION , a.SDESCR , a.DYYYY , asofPAX , asofInsSales , YEPAX , YEInsSales , CurrentPAX , CurrentInsSales , asofinssales/ ISNULL(NULLIF(yeinssales,0),1) as percentsales , asofpax/yepax as percentpax ,currentinssales/ISNULL(NULLIF((asofinssales/ISNULL(NULLIF(yeinssales,0),1)),0),1) as projectedinssales from q_00 as a join q_01 as b on (b.DIVISION = a.DIVISION and b.SDESCR = a.SDESCR and b.DYYYY = a.DYYYY) join q_02 as c on (b.DIVISION = c.DIVISION and b.SDESCR = c.SDESCR) order by a.DIVISION, a.SDESCR, a.DYYYY ;

  Read the article

• Why wont my if statement work, in my stored procedure

  - by MyHeadHurts

  Alright so i am not even sure if this is possible I have a q_00 and q_01 and q_02 which are all in my stored procedure. then on the bottom i have 3 select statements that select a certain catagory for example Sales,Net Sales and INS sales What i want to be able to do is if the user types exec (name of my sp) (sales) (and a year which is the @yearparameter) it will run the sales select statement If they type Exec (name of my SP) netsales (@Yeartoget) it will show the net sales is this possible or do i need multiple stored procedures ALTER PROCEDURE [dbo].[casof] @YearToGet int, @mode VARCHAR(20) as ; with q_00 as ( select DIVISION , SDESCR , DYYYY , sum(APRICE) as asofSales , sum(PARTY) as asofPAX , sum(NetAmount) as asofNetSales , sum(InsAmount) as asofInsSales , sum(CancelRevenue) as asofCXSales , sum(OtherAmount) as asofOtherSales , sum(CXVALUE) as asofCXValue from dbo.B101BookingsDetails where Booked <= CONVERT(int,DateAdd(year, @YearToGet - Year(getdate()), DateAdd(day, DateDiff(day, 1, getdate()), 0))) and DYYYY = @YearToGet group by DIVISION, SDESCR, DYYYY ), q_01 as ( select DIVISION , SDESCR , DYYYY , sum(APRICE) as YESales , sum(PARTY) as YEPAX , sum(NetAmount) as YENetSales , sum(InsAmount) as YEInsSales , sum(CancelRevenue) as YECXSales , sum(OtherAmount) as YEOtherSales , sum(CXVALUE) as YECXValue from dbo.B101BookingsDetails where DYYYY=@YearToGet group by DIVISION, SDESCR, DYYYY ), q_02 as ( select DIVISION , SDESCR , DYYYY , sum(APRICE) as CurrentSales , sum(PARTY) as CurrentPAX , sum(NetAmount) as CurrentNetSales , sum(InsAmount) as CurrentInsSales , sum(CancelRevenue) as CurrentCXSales , sum(OtherAmount) as CurrentOtherSales , sum(CXVALUE) as CurrentCXValue from dbo.B101BookingsDetails where Booked <= CONVERT(int,DateAdd(year, (year( getdate() )) - Year(getdate()), DateAdd(day, DateDiff(day, 1, getdate()), 0))) and DYYYY = (year( getdate() )) group by DIVISION, SDESCR, DYYYY ) IF @mode = 'sales' select a.DIVISION , a.SDESCR , a.DYYYY , asofSales , asofPAX , YESales , YEPAX , CurrentSales , CurrentPAX , asofsales/ ISNULL(NULLIF(yesales,0),1) as percentsales , asofpax/yepax as percentpax ,currentsales/ISNULL(NULLIF((asofsales/ISNULL(NULLIF(yesales,0),1)),0),1) as projectedsales ,currentpax/ISNULL(NULLIF((asofpax/ISNULL(NULLIF(yepax,0),1)),0),1) as projectedpax from q_00 as a join q_01 as b on (b.DIVISION = a.DIVISION and b.SDESCR = a.SDESCR and b.DYYYY = a.DYYYY) join q_02 as c on (b.DIVISION = c.DIVISION and b.SDESCR = c.SDESCR) order by a.DIVISION, a.SDESCR, a.DYYYY ; else if @mode= 'netsales' select a.DIVISION , a.SDESCR , a.DYYYY , asofPAX , asofNetSales , YEPAX , YENetSales , CurrentPAX , CurrentNetSales , asofnetsales/ ISNULL(NULLIF(yenetsales,0),1) as percentnetsales , asofpax/yepax as percentpax ,currentnetsales/ISNULL(NULLIF((asofnetsales/ISNULL(NULLIF(yenetsales,0),1)),0),1) as projectednetsales ,currentpax/ISNULL(NULLIF((asofpax/ISNULL(NULLIF(yepax,0),1)),0),1) as projectedpax from q_00 as a join q_01 as b on (b.DIVISION = a.DIVISION and b.SDESCR = a.SDESCR and b.DYYYY = a.DYYYY) join q_02 as c on (b.DIVISION = c.DIVISION and b.SDESCR = c.SDESCR) order by a.DIVISION, a.SDESCR, a.DYYYY ; ELSE IF @mode = 'inssales' select a.DIVISION , a.SDESCR , a.DYYYY , asofPAX , asofInsSales , YEPAX , YEInsSales , CurrentPAX , CurrentInsSales , asofinssales/ ISNULL(NULLIF(yeinssales,0),1) as percentsales , asofpax/yepax as percentpax ,currentinssales/ISNULL(NULLIF((asofinssales/ISNULL(NULLIF(yeinssales,0),1)),0),1) as projectedinssales from q_00 as a join q_01 as b on (b.DIVISION = a.DIVISION and b.SDESCR = a.SDESCR and b.DYYYY = a.DYYYY) join q_02 as c on (b.DIVISION = c.DIVISION and b.SDESCR = c.SDESCR) order by a.DIVISION, a.SDESCR, a.DYYYY ;

  Read the article

• Everytime user types , in my text box i want it to become ',' or help me do it using a parameter

  - by MyHeadHurts

  I am using a vb.net textbox to become part of my IN sql statement in my program I tryed to use a parameter and it didn't work here is my code TextBox1.Text = "'Cruises','Caribbean and Mexico','CentralSouth America', 'Europe','Far East','France','Italy','London/UK','Middle East/Africa','South Pacific','Spain/Portugal','USA/Canada'" the default value of my textbox although the user can edit the textbox, but they would need to type the ',' which i would rather them just type , . and my other code is If RadioButtonList1.SelectedValue = "Sales" And CheckBox1.Checked = False Then 'saocmd1.CommandText = "SELECT dbo.B605SaleAsOfAdvancedMaster.SDESCR, dbo.B605SaleAsOfAdvancedMaster.DYYYY, dbo.B605SaleAsOfAdvancedMaster.AsOFSales, dbo.B605SaleAsOfAdvancedMaster.ASOFPAX, dbo.B605SaleAsOfAdvancedMaster.YESales, dbo.B605SaleAsOfAdvancedMaster.YEPAX, dbo.B604SalesAsOfAdvanced.Sales AS CurrentSales, dbo.B604SalesAsOfAdvanced.PAX AS CurrentPAX FROM B604SalesAsOfAdvanced INNER JOIN B605SaleAsOfAdvancedMaster ON dbo.B605SaleAsOfAdvancedMaster.SDESCR = B604SalesAsOfAdvanced.SDESCR WHERE (B605SaleAsOfAdvancedMaster.DYYYY =" & DropDownList1.SelectedValue & ") AND (B604SalesAsOfAdvanced.DYYYY = (DatePart(year, GetDate()) +1)) order by B605SaleAsOfAdvancedMaster.SDESCR" saocmd1.CommandText = "SELECT dbo.B605SaleAsOfAdvancedMaster.SDESCR, dbo.B605SaleAsOfAdvancedMaster.DYYYY, dbo.B605SaleAsOfAdvancedMaster.AsOFSales, dbo.B605SaleAsOfAdvancedMaster.ASOFPAX, dbo.B605SaleAsOfAdvancedMaster.YESales, dbo.B605SaleAsOfAdvancedMaster.YEPAX, dbo.B604SalesAsOfAdvanced.Sales AS CurrentSales, dbo.B604SalesAsOfAdvanced.PAX AS CurrentPAX FROM B604SalesAsOfAdvanced INNER JOIN B605SaleAsOfAdvancedMaster ON dbo.B605SaleAsOfAdvancedMaster.SDESCR = B604SalesAsOfAdvanced.SDESCR WHERE (B605SaleAsOfAdvancedMaster.DYYYY =@Dyyyy) AND (B604SalesAsOfAdvanced.DYYYY = (DatePart(year, GetDate()) +1)) and dbo.B605SaleAsOfAdvancedMaster.SDESCR in ('Cruises','Caribbean and Mexico','CentralSouth America', 'Europe','Far East','France','Italy','London/UK','Middle East/Africa','South Pacific','Spain/Portugal','USA/Canada') order by B605SaleAsOfAdvancedMaster.SDESCR" Label2.Text = "Sales" ElseIf RadioButtonList1.SelectedValue = "NetSales" And CheckBox1.Checked = False Then saocmd1.CommandText = "SELECT dbo.B605SaleAsOfAdvancedMaster.SDESCR, dbo.B605SaleAsOfAdvancedMaster.DYYYY, (ISNULL(dbo.B605SaleAsOfAdvancedMaster.AsOFNET,0) + (ISNULL(dbo.B605SaleAsOfAdvancedMaster.AsOFOther,0))) as AsofSales, dbo.B605SaleAsOfAdvancedMaster.ASOFPAX, (ISNULL(dbo.B605SaleAsOfAdvancedMaster.YENET,0) + (ISNULL(dbo.B605SaleAsOfAdvancedMaster.YEOther,0))) as YESales, dbo.B605SaleAsOfAdvancedMaster.YEPAX, (ISNULL(dbo.B604SalesAsOfAdvanced.netSales,0) + (ISNULL(dbo.B604SalesAsOfAdvanced.OtherSales,0))) as CurrentSales, dbo.B604SalesAsOfAdvanced.PAX AS CurrentPAX FROM B604SalesAsOfAdvanced INNER JOIN B605SaleAsOfAdvancedMaster ON dbo.B605SaleAsOfAdvancedMaster.SDESCR = B604SalesAsOfAdvanced.SDESCR WHERE (B605SaleAsOfAdvancedMaster.DYYYY =@Dyyyy) AND (B604SalesAsOfAdvanced.DYYYY = (DatePart(year, GetDate()) +1)) and dbo.B605SaleAsOfAdvancedMaster.SDESCR in ('Cruises','Caribbean and Mexico','CentralSouth America', 'Europe','Far East','France','Italy','London/UK','Middle East/Africa','South Pacific','Spain/Portugal','USA/Canada') order by B605SaleAsOfAdvancedMaster.SDESCR" Label2.Text = "Net Sales" ElseIf RadioButtonList1.SelectedValue = "INSSales" And CheckBox1.Checked = False Then saocmd1.CommandText = "SELECT dbo.B605SaleAsOfAdvancedMaster.SDESCR, dbo.B605SaleAsOfAdvancedMaster.DYYYY, ISNULL(dbo.B605SaleAsOfAdvancedMaster.AsOFINS,0)as AsofSales, dbo.B605SaleAsOfAdvancedMaster.ASOFPAX, ISNULL(dbo.B605SaleAsOfAdvancedMaster.YEINS,0) as YESales, dbo.B605SaleAsOfAdvancedMaster.YEPAX, ISNULL(dbo.B604SalesAsOfAdvanced.INSSales,0) as CurrentSales, dbo.B604SalesAsOfAdvanced.PAX AS CurrentPAX FROM B604SalesAsOfAdvanced INNER JOIN B605SaleAsOfAdvancedMaster ON dbo.B605SaleAsOfAdvancedMaster.SDESCR = B604SalesAsOfAdvanced.SDESCR WHERE (B605SaleAsOfAdvancedMaster.DYYYY =@Dyyyy) AND (B604SalesAsOfAdvanced.DYYYY = (DatePart(year, GetDate()) +1)) and dbo.B605SaleAsOfAdvancedMaster.SDESCR in ('Cruises','Caribbean and Mexico','CentralSouth America', 'Europe','Far East','France','Italy','London/UK','Middle East/Africa','South Pacific','Spain/Portugal','USA/Canada') order by B605SaleAsOfAdvancedMaster.SDESCR" Label2.Text = "Insurance Sales" ElseIf RadioButtonList1.SelectedValue = "CXSales" And CheckBox1.Checked = False Then saocmd1.CommandText = "SELECT dbo.B605SaleAsOfAdvancedMaster.SDESCR, dbo.B605SaleAsOfAdvancedMaster.DYYYY, ISNULL(dbo.B605SaleAsOfAdvancedMaster.AsOFCX,0)as AsofSales, dbo.B605SaleAsOfAdvancedMaster.ASOFPAX, ISNULL(dbo.B605SaleAsOfAdvancedMaster.YECX,0) as YESales, dbo.B605SaleAsOfAdvancedMaster.YEPAX, ISNULL(dbo.B604SalesAsOfAdvanced.CXSales,0) as CurrentSales, dbo.B604SalesAsOfAdvanced.PAX AS CurrentPAX FROM B604SalesAsOfAdvanced INNER JOIN B605SaleAsOfAdvancedMaster ON dbo.B605SaleAsOfAdvancedMaster.SDESCR = B604SalesAsOfAdvanced.SDESCR WHERE (B605SaleAsOfAdvancedMaster.DYYYY =@Dyyyy) AND (B604SalesAsOfAdvanced.DYYYY = (DatePart(year, GetDate()) +1)) and dbo.B605SaleAsOfAdvancedMaster.SDESCR in ('Cruises','Caribbean and Mexico','CentralSouth America', 'Europe','Far East','France','Italy','London/UK','Middle East/Africa','South Pacific','Spain/Portugal','USA/Canada') order by B605SaleAsOfAdvancedMaster.SDESCR" Label2.Text = "Canceled Sales" ElseIf RadioButtonList1.SelectedValue = "Sales" And CheckBox1.Checked = True Then saocmd1.CommandText = "SELECT dbo.B605SaleAsOfAdvancedMaster.SDESCR, dbo.B605SaleAsOfAdvancedMaster.DYYYY, dbo.B605SaleAsOfAdvancedMaster.AsOFSales, dbo.B605SaleAsOfAdvancedMaster.ASOFPAX, dbo.B605SaleAsOfAdvancedMaster.YESales, dbo.B605SaleAsOfAdvancedMaster.YEPAX, dbo.B604SalesAsOfAdvanced.Sales AS CurrentSales, dbo.B604SalesAsOfAdvanced.PAX AS CurrentPAX FROM B604SalesAsOfAdvanced INNER JOIN B605SaleAsOfAdvancedMaster ON dbo.B605SaleAsOfAdvancedMaster.SDESCR = B604SalesAsOfAdvanced.SDESCR WHERE (B605SaleAsOfAdvancedMaster.DYYYY =@Dyyyy) AND (B604SalesAsOfAdvanced.DYYYY = (DatePart(year, GetDate()) +1)) and dbo.B605SaleAsOfAdvancedMaster.SDESCR in (" & TextBox1.Text & ") order by B605SaleAsOfAdvancedMaster.SDESCR" Label2.Text = "Sales" ElseIf RadioButtonList1.SelectedValue = "NetSales" And CheckBox1.Checked = True Then saocmd1.CommandText = "SELECT dbo.B605SaleAsOfAdvancedMaster.SDESCR, dbo.B605SaleAsOfAdvancedMaster.DYYYY, (ISNULL(dbo.B605SaleAsOfAdvancedMaster.AsOFNET,0) + (ISNULL(dbo.B605SaleAsOfAdvancedMaster.AsOFOther,0))) as AsofSales, dbo.B605SaleAsOfAdvancedMaster.ASOFPAX, (ISNULL(dbo.B605SaleAsOfAdvancedMaster.YENET,0) + (ISNULL(dbo.B605SaleAsOfAdvancedMaster.YEOther,0))) as YESales, dbo.B605SaleAsOfAdvancedMaster.YEPAX, (ISNULL(dbo.B604SalesAsOfAdvanced.netSales,0) + (ISNULL(dbo.B604SalesAsOfAdvanced.OtherSales,0))) as CurrentSales, dbo.B604SalesAsOfAdvanced.PAX AS CurrentPAX FROM B604SalesAsOfAdvanced INNER JOIN B605SaleAsOfAdvancedMaster ON dbo.B605SaleAsOfAdvancedMaster.SDESCR = B604SalesAsOfAdvanced.SDESCR WHERE (B605SaleAsOfAdvancedMaster.DYYYY =@Dyyyy) AND (B604SalesAsOfAdvanced.DYYYY = (DatePart(year, GetDate()) +1)) and dbo.B605SaleAsOfAdvancedMaster.SDESCR in (" & TextBox1.Text & ") order by B605SaleAsOfAdvancedMaster.SDESCR" Label2.Text = "Net Sales" ElseIf RadioButtonList1.SelectedValue = "INSSales" And CheckBox1.Checked = True Then saocmd1.CommandText = "SELECT dbo.B605SaleAsOfAdvancedMaster.SDESCR, dbo.B605SaleAsOfAdvancedMaster.DYYYY, ISNULL(dbo.B605SaleAsOfAdvancedMaster.AsOFINS,0)as AsofSales, dbo.B605SaleAsOfAdvancedMaster.ASOFPAX, ISNULL(dbo.B605SaleAsOfAdvancedMaster.YEINS,0) as YESales, dbo.B605SaleAsOfAdvancedMaster.YEPAX, ISNULL(dbo.B604SalesAsOfAdvanced.INSSales,0) as CurrentSales, dbo.B604SalesAsOfAdvanced.PAX AS CurrentPAX FROM B604SalesAsOfAdvanced INNER JOIN B605SaleAsOfAdvancedMaster ON dbo.B605SaleAsOfAdvancedMaster.SDESCR = B604SalesAsOfAdvanced.SDESCR WHERE (B605SaleAsOfAdvancedMaster.DYYYY =@Dyyyy) AND (B604SalesAsOfAdvanced.DYYYY = (DatePart(year, GetDate()) +1)) and dbo.B605SaleAsOfAdvancedMaster.SDESCR in (" & TextBox1.Text & ") order by B605SaleAsOfAdvancedMaster.SDESCR" Label2.Text = "Insurance Sales" ElseIf RadioButtonList1.SelectedValue = "CXSales" And CheckBox1.Checked = True Then saocmd1.CommandText = "SELECT dbo.B605SaleAsOfAdvancedMaster.SDESCR, dbo.B605SaleAsOfAdvancedMaster.DYYYY, ISNULL(dbo.B605SaleAsOfAdvancedMaster.AsOFCX,0)as AsofSales, dbo.B605SaleAsOfAdvancedMaster.ASOFPAX, ISNULL(dbo.B605SaleAsOfAdvancedMaster.YECX,0) as YESales, dbo.B605SaleAsOfAdvancedMaster.YEPAX, ISNULL(dbo.B604SalesAsOfAdvanced.CXSales,0) as CurrentSales, dbo.B604SalesAsOfAdvanced.PAX AS CurrentPAX FROM B604SalesAsOfAdvanced INNER JOIN B605SaleAsOfAdvancedMaster ON dbo.B605SaleAsOfAdvancedMaster.SDESCR = B604SalesAsOfAdvanced.SDESCR WHERE (B605SaleAsOfAdvancedMaster.DYYYY =@Dyyyy) AND (B604SalesAsOfAdvanced.DYYYY = (DatePart(year, GetDate()) +1)) and dbo.B605SaleAsOfAdvancedMaster.SDESCR in (" & TextBox1.Text & ") order by B605SaleAsOfAdvancedMaster.SDESCR" Label2.Text = "Canceled Sales" End If Basically what is happening is, if a certain radio button is selected and the user didn't click the checkbox the default regions are included and they are hardcoded because the query runs much faster. if the user did click the checkbox then the textbox where they type the specific regions shows up and it will run the query that includes the dbo.B605SaleAsOfAdvancedMaster.SDESCR in (" & TextBox1.Text & ") If you can somehow do this using parameters and not with the textbox1.text in the query it will run much faster for me thanks for your help

  Read the article

• sql exception arithmetic overflow?

  - by MyHeadHurts

  In my program the user imports a date and it works whenever the year is in 2011 but if i try a date in 2010 i get this error which is weird [ SqlException (0x80131904): Arithmetic overflow error converting int to data type numeric.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1950890 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4846875 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392 System.Data.SqlClient.SqlDataReader.HasMoreRows() +157 System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout) +197 System.Data.SqlClient.SqlDataReader.Read() +9 System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping) +78 System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue) +164 System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) +282 System.Data.Common.LoadAdapter.FillFromReader(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) +19 System.Data.DataTable.Load(IDataReader reader, LoadOption loadOption, FillErrorEventHandler errorHandler) +222 System.Data.DataTable.Load(IDataReader reader) +14 ( @YearToGet int, @current datetime, @y int, @search datetime ) AS SET @YearToGet = 2006; WITH Years AS ( SELECT DATEPART(year, GETDATE()) [Year] UNION ALL SELECT [Year]-1 FROM Years WHERE [Year]>@YearToGet ), q_00 as ( select DIVISION , DYYYY , sum(PARTY) as asofPAX , sum(InsAmount) as asofSales from dbo.B101BookingsDetails INNER JOIN Years ON B101BookingsDetails.DYYYY = Years.Year where Booked <= CONVERT(int, DateAdd(year, (Years.Year - @y), @search)) and DYYYY = Years.Year group by DIVISION, DYYYY, years.year having DYYYY = years.year ), q_01 as ( select DIVISION , DYYYY , sum(PARTY) as YEPAX , sum(InsAmount) as YESales from dbo.B101BookingsDetails INNER JOIN Years ON B101BookingsDetails.DYYYY = Years.Year group by DIVISION, DYYYY , years.year having DYYYY = years.year ), q_02 as ( select DIVISION , DYYYY , sum(PARTY) as CurrentPAX , sum(InsAmount) as CurrentSales from dbo.B101BookingsDetails INNER JOIN Years ON B101BookingsDetails.DYYYY = Years.Year where Booked <= CONVERT(int,@current) and DYYYY = (year( getdate() )) group by DIVISION, DYYYY ) select a.DIVISION , a.DYYYY , asofPAX , asofSales , YEPAX , YESales , CurrentPAX , CurrentSales ,asofsales/ ISNULL(NULLIF(yesales,0),1) as percentsales, CAST((asofpax) AS DECIMAL(5,1))/yepax as percentpax from q_00 as a join q_01 as b on (b.DIVISION = a.DIVISION and b.DYYYY = a.DYYYY) join q_02 as c on (b.DIVISION = c.DIVISION) JOIN Years as d on (b.dyyyy = d.year) where A.DYYYY <> (year( getdate() )) order by a.DIVISION, a.DYYYY ;

  Read the article

• Use fileupload as template field in a details view

  - by MyHeadHurts

  I have an admin page where a user will select a document path and add that path to a certain column of a database. I am using a fileupload on the page where they can find the document and copy the path and then paste it into the details view. However, I want to skip this step and I want them to select a document and automatically make the path show up in the details view. <asp:FileUpload ID="FileUpload1" runat="server" Visible="False" Width="384px" /><br /> <br /> <div> <asp:UpdatePanel ID="UpdatePanel1" runat="server" UpdateMode="Conditional"> <ContentTemplate> <center> <asp:DetailsView ID="DetailsView1" runat="server" AllowPaging="True" AutoGenerateRows="False" DataKeyNames="ID" DataSourceID="SqlDataSource1" Height="128px" Width="544px" Visible="False" OnModeChanged="Button2_Click" CellPadding="4" ForeColor="#333333" GridLines="None" > <Fields> <asp:BoundField DataField="Order" HeaderText="Order" SortExpression="Order" /> <asp:BoundField DataField="Department" HeaderText="Department" SortExpression="Department"/> <asp:BoundField DataField="DOC_Type" HeaderText="DOC_Type" SortExpression="DOC_Type" /> <asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" /> <asp:BoundField DataField="Revision" HeaderText="Revision" SortExpression="Revision" /> <asp:BoundField DataField="DOC" HeaderText="DOC" SortExpression="DOC" /> <asp:BoundField DataField="Active" HeaderText="Active" SortExpression="Active" /> <asp:BoundField DataField="Rev_Date" HeaderText="Rev_Date" SortExpression="Rev_Date" /> <asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False" ReadOnly="True" SortExpression="ID" Visible="False" /> <asp:CommandField ShowInsertButton="True" /> </Fields> <FooterStyle BackColor="#5D7B9D" BorderStyle="None" Font-Bold="True" ForeColor="White" /> <CommandRowStyle BackColor="#E2DED6" BorderStyle="None" Font-Bold="True" /> <RowStyle BackColor="#F7F6F3" BorderStyle="None" ForeColor="#333333" /> <FieldHeaderStyle BackColor="#E9ECF1" BorderStyle="None" Font-Bold="True" /> <EmptyDataRowStyle BorderStyle="None" /> <PagerStyle BackColor="#284775" BorderStyle="None" ForeColor="White" HorizontalAlign="Center" /> <HeaderStyle BackColor="#5D7B9D" BorderStyle="None" Font-Bold="True" ForeColor="White" /> <InsertRowStyle BorderStyle="None" /> <EditRowStyle BackColor="#999999" BorderStyle="None" /> <AlternatingRowStyle BackColor="White" BorderStyle="None" ForeColor="#284775" /> </asp:DetailsView> &nbsp; <br /> I need to get the fileupload1 into the DOC contenttemplate area so instead of showing an empty textbox it will show just a textbox it will show the fileupload

  Read the article

• help convert pop3 connection to imap

  - by MyHeadHurts

  tcpClient.Connect(hostName, 110) Dim networkStream As NetworkStream = tcpClient.GetStream() Dim bytes(tcpClient.ReceiveBufferSize) As Byte Dim sendBytes As Byte() networkStream.Read(bytes, 0, CInt(tcpClient.ReceiveBufferSize)) sendBytes = Encoding.ASCII.GetBytes("User " + userName + vbCrLf) networkStream.Write(sendBytes, 0, sendBytes.Length) sTemp = networkStream.Read(bytes, 0, CInt(tcpClient.ReceiveBufferSize)) sendBytes = Encoding.ASCII.GetBytes("Pass " + userPassword + vbCrLf) networkStream.Write(sendBytes, 0, sendBytes.Length) sTemp = networkStream.Read(bytes, 0, CInt(tcpClient.ReceiveBufferSize)) sendBytes = Encoding.ASCII.GetBytes("STAT" + vbCrLf) networkStream.Write(sendBytes, 0, sendBytes.Length) sTemp = networkStream.Read(bytes, 0, CInt(tcpClient.ReceiveBufferSize)) sendBytes = Encoding.ASCII.GetBytes("RETR " + messageNumber + vbCrLf) networkStream.Write(sendBytes, 0, sendBytes.Length) networkStream.Read(bytes, 0, CInt(tcpClient.ReceiveBufferSize)) returnMessage = Encoding.ASCII.GetString(bytes) EmailContent.Text = returnMessage sendBytes = Encoding.ASCII.GetBytes("QUIT" + vbCrLf) networkStream.Write(sendBytes, 0, sendBytes.Length) tcpClient.Close() Catch ex As Exception EmailContent.Text = "Could not retrieve email or your inbox is empty" End Try

  Read the article

• need help building a stored procedure that takes rows from one table into another.

  - by MyHeadHurts

  alright i built this stored procedure to take the columns from a stagging table and copy them into my other table, but if these four columns are duplicates it wont insert the rows, works fine. however, what i want to do is if only the tour, taskname and deptdate are the same, then i will update the rest of the information. and if all four columns are the same dont instert. INSERT INTO dashboardtasks1 SELECT [tour], [taskname], [deptdate], [tasktype], [desc], [duedate], [compdate], [comments], [agent], [compby], [graceperiod] FROM staggingtasks WHERE NOT EXISTS(SELECT * FROM dashboardtasks1 WHERE (staggingtasks.tour=dashboardtasks1.tour and staggingtasks.taskname=dashboardtasks1.taskname and staggingtasks.deptdate=dashboardtasks1.deptdate and staggingtasks.duedate=dashboardtasks1.duedate ) ) i saw something like this INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1; UPDATE table SET c=c+1 WHERE a=1; but how could i do it if my stated 3 columns are the samed then update? or is there a way to do this with an if statement and use 2 different queries, but how would my if statement work would it check if the row exists in the table i am uploading to and then run the insert statement? or what if i did something like IF EXISTS (SELECT * FROM dashboardtasks WHERE staggingtasks.tour=dashboardtasks.tour and staggingtasks.taskname=dashboardtasks.taskname and staggingtasks.deptdate=dashboardtasks.deptdate ) begin UPDATE [dashboardtasks] SET [tour] = staggingtasks.tour, [taskname] = staggingtasks.taskname, [deptdate] = staggingtasks.deptdate, [tasktype] = staggingtasks.tasktype, [desc] = staggingtasks.desc, [duedate] = staggingtasks.duedate, [compdate] = staggingtasks.compdate, [comments] = staggingtasks.comments, [agent] = staggingtasks.agent, [compby] = staggingtasks.compby, [graceperiod] = staggingtasks.graceperiod end else EXISTS (SELECT * FROM dashboardtasks WHERE staggingtasks.tour=dashboardtasks.tour and staggingtasks.taskname=dashboardtasks.taskname and staggingtasks.deptdate=dashboardtasks.deptdate and staggingtasks.duedate=dashboardtasks.duedate ) begin INSERT INTO dashboardtasks1 SELECT [tour], [taskname], [deptdate], [tasktype], [desc], [duedate], [compdate], [comments], [agent], [compby], [graceperiod] FROM staggingtasks WHERE NOT EXISTS(SELECT * FROM dashboardtasks1 WHERE (staggingtasks.tour=dashboardtasks1.tour and staggingtasks.taskname=dashboardtasks1.taskname and staggingtasks.deptdate=dashboardtasks1.deptdate and staggingtasks.duedate=dashboardtasks1.duedate ) ) end end

  Read the article

• stored procedure issue, has to do with my where clause and if statement

  - by MyHeadHurts

  right now my stored procedure is returning 2 different result sets one for @booked and the other for @booked1 if you look closely my query is doing the same thing for each @booked and @booked but one is for a user selected year and the other for the current year. I don't want two different result sets, i want to join the selected year and the current year side by side by SDESCR(which is a column that they have in common) another hurdle i am facing is i am use @mode to decide whether the user wants netsales, sales... so on. I know i need sometype of join but, it isnt working because i have a where statement that says where dyyyy= @yeartoget which won't allow the current year data to work ALTER PROCEDURE [dbo].[test1] @mode varchar(20), @YearToGet int AS SET NOCOUNT ON Declare @Booked Int Set @Booked = CONVERT(int,DateAdd(year, @YearToGet - Year(getdate() + 1), DateAdd(day, DateDiff(day, 1, getdate()), 1) ) ) Declare @Booked1 Int Set @Booked1 = CONVERT(int,DateAdd(year, (year( getdate() )) - Year(getdate() + 1), DateAdd(day, DateDiff(day, 1, getdate()), 1) ) ) If @mode = 'Sales' Select Division, SDESCR, DYYYY, Sum(Case When Booked <= @Booked Then NetAmount End) ASofNetSales, SUM(NetAmount) AS YENetSales, Sum(Case When Booked <= @Booked Then PARTY End) AS ASofPAX, SUM(PARTY) AS YEPAX From dbo.B101BookingsDetails Where DYYYY = @YearToGet Group By SDESCR, DYYYY, Division Order By Division, SDESCR, DYYYY else if @mode = 'netsales' Select Division, SDESCR, DYYYY, Sum(Case When Booked <= @Booked Then NetAmount End) ASofNetSales, SUM(NetAmount) AS YENetSales, Sum(Case When Booked <= @Booked Then PARTY End) AS ASofPAX, SUM(PARTY) AS YEPAX From dbo.B101BookingsDetails Where DYYYY = @YearToGet Group By SDESCR, DYYYY, Division Order By Division, SDESCR, DYYYY If @mode = 'Sales' Select Division, SDESCR, DYYYY, Sum(Case When Booked <= @Booked1 Then NetAmount End) currentNetSales, Sum(Case When Booked <= @Booked1 Then PARTY End) AS currentPAX From dbo.B101BookingsDetails Where DYYYY = (year( getdate() )) Group By SDESCR, DYYYY, Division Order By Division, SDESCR, DYYYY else if @mode = 'netsales' Select Division, SDESCR, DYYYY, Sum(Case When Booked <= @Booked1 Then NetAmount End) currentNetSales, Sum(Case When Booked <= @Booked1 Then PARTY End) AS currentPAX From dbo.B101BookingsDetails Where DYYYY = (year( getdate() )) Group By SDESCR, DYYYY, Division Order By Division, SDESCR, DYYYY Else if @mode = 'Inssales' Select Division, SDESCR, DYYYY, Sum(Case When Booked <= @Booked1 Then InsAmount End) currentInsSales, Sum(Case When Booked <= @Booked1 Then PARTY End) AS currentPAX From dbo.B101BookingsDetails Where DYYYY = (year( getdate() )) Group By SDESCR, DYYYY, Division Order By Division, SDESCR, DYYYY

  Read the article

• connecting to exchange server

  - by MyHeadHurts

  I am using this code to connect to my exchange server. I am trying to retrieve an inbox of basically emails that have not been read however, i am just getting a bunch of gibberish and its reading an email. can you help me modify my code to just read the most recent messages. Try tcpClient.Connect(hostName, 110) Dim networkStream As NetworkStream = tcpClient.GetStream() Dim bytes(tcpClient.ReceiveBufferSize) As Byte Dim sendBytes As Byte() networkStream.Read(bytes, 0, CInt(tcpClient.ReceiveBufferSize)) sendBytes = Encoding.ASCII.GetBytes("User " + userName + vbCrLf) networkStream.Write(sendBytes, 0, sendBytes.Length) sTemp = networkStream.Read(bytes, 0, CInt(tcpClient.ReceiveBufferSize)) sendBytes = Encoding.ASCII.GetBytes("Pass " + userPassword + vbCrLf) networkStream.Write(sendBytes, 0, sendBytes.Length) sTemp = networkStream.Read(bytes, 0, CInt(tcpClient.ReceiveBufferSize)) sendBytes = Encoding.ASCII.GetBytes("STAT" + vbCrLf) networkStream.Write(sendBytes, 0, sendBytes.Length) sTemp = networkStream.Read(bytes, 0, CInt(tcpClient.ReceiveBufferSize)) sendBytes = Encoding.ASCII.GetBytes("RETR " + messageNumber + vbCrLf) networkStream.Write(sendBytes, 0, sendBytes.Length) networkStream.Read(bytes, 0, CInt(tcpClient.ReceiveBufferSize)) returnMessage = Encoding.ASCII.GetString(bytes) EmailContent.Text = returnMessage sendBytes = Encoding.ASCII.GetBytes("QUIT" + vbCrLf) networkStream.Write(sendBytes, 0, sendBytes.Length) tcpClient.Close() Catch ex As Exception EmailContent.Text = "Could not retrieve email or your inbox is empty" End Try

  Read the article

• problem of setting cache as datatable and reading it

  - by MyHeadHurts

  and this is how i see if my cache is empty all my syntax errors are coming when i am trying to see if the cache is null and if it is to run the stored procedure If RadioButtonList1.SelectedIndex = 0 Then If Cache["netcache"] = null Then bcmd.CommandText = "yearlynetsales" Else btable = cache["netcache"] end if If RadioButtonList1.SelectedIndex = 1 Then If Cache["salescache"] = null Then bcmd.CommandText = "yearlysales" Else btable = cache["salescache"] end if end if this is how i set my cache If RadioButtonList1.SelectedIndex = 0 Then Cache.Insert("netcache", bTable) ElseIf RadioButtonList1.SelectedIndex = 1 Then Cache.Insert("salescache", bTable) ElseIf RadioButtonList1.SelectedIndex = 2 Then Cache.Insert("cxsalescache", bTable) ElseIf RadioButtonList1.SelectedIndex = 3 Then Cache.Insert("cxvaluecache", bTable) ElseIf RadioButtonList1.SelectedIndex = 4 Then Cache.Insert("othersalescache", bTable) ElseIf RadioButtonList1.SelectedIndex = 5 Then Cache.Insert("inssalescache", bTable) End If

  Read the article

• Union All Won't work in stored procedure

  - by MyHeadHurts

  ALTER PROCEDURE [dbo].[MyStoredProcedure1] @YearToGet int AS Select Division, SDESCR, DYYYY, Sum(APRICE) ASofSales, Sum(PARTY) AS ASofPAX, Sum(NetAmount) ASofNetSales, Sum(InsAmount) ASofInsSales, Sum(CancelRevenue) ASofCXSales, Sum(OtherAmount) ASofOtherSales, Sum(CXVALUE) ASofCXValue From dbo.B101BookingsDetails Where Booked <= CONVERT(int,DateAdd(year, @YearToGet - Year(getdate()), DateAdd(day, DateDiff(day, 1, getdate()), 0) ) ) and (DYYYY = @YearToGet) Group By SDESCR, DYYYY, Division Having (DYYYY = @YearToGet) Order By Division, SDESCR, DYYYY union all SELECT DIVISION, SDESCR, DYYYY, SUM(APRICE) AS YESales, SUM(PARTY) AS YEPAX, SUM(NetAmount) AS YENetSales, SUM(InsAmount) AS YEInsSales, SUM(CancelRevenue) AS YECXSales, SUM(OtherAmount) AS YEOtherSales, SUM(CXVALUE) AS YECXValue FROM dbo.B101BookingsDetails Where (DYYYY=@YearToGet) GROUP BY SDESCR, DYYYY, DIVISION ORDER BY DIVISION, SDESCR, DYYYY The error I am getting is Msg 156, Level 15, State 1, Procedure MyStoredProcedure1, Line 36 Incorrect syntax near the keyword 'union'. But my goal here is the user inputs a year for example 2009, my first query will get all the sales made in 2009 to the same date it is was yesterday 12/23/2009, while the second query is getting 2009 totals up to dec 31 2009. I want the columns to be side by side not in one column

  Read the article

• Converting a selected date from a datetimepicker into my query, along with subtracting a day

  - by MyHeadHurts

  I am currently using this to get yesterdays date, however i need to do something similar where the user will use a javascript datetimepicker in my asp.net page and i will then use the date they select instead of just yesterdays date Declare @dayselection int set @dayselection = CONVERT(int,DateAdd(year, @YearToGet - Year(getdate() + 1), DateAdd(day, DateDiff(day, 1, @dayToGet), 1) , DateAdd(month, DateDiff(month, 0, @monthToGet), 0) ) but it isnt working i keep getting syntax errors I want the day and year functions to stay the same i just need help with the month part. I need to convert the selected date into an int

  Read the article

• output parameter into label

  - by MyHeadHurts

  instead of returning my output paremeter value in my stored procedure to my label it returns the default value i set my output parameter to. why cant i put my output parameter into my text label Dim reader As SqlDataReader cmd.Parameters.AddWithValue("@tour", "2365") cmd.Parameters.Add("@tourname", SqlDbType.VarChar) cmd.Parameters("@tourname").Direction = ParameterDirection.Output cmd.CommandText = "test" cmd.CommandType = CommandType.StoredProcedure cmd.Connection = conn conn.Open() reader = cmd.ExecuteReader() Dim myTable As DataTable = New DataTable() myTable.Load(reader) DropDownList1.DataSource = myTable DropDownList1.DataTextField = "ddate7" DropDownList1.DataBind() Label1.Text = cmd.Parameters("@tourname").ToString conn.Close()

  Read the article

• gridview, order number + 1/- 1

  - by MyHeadHurts

  So I am working on a gridview that uses the order number to order the table. However, my users are able to ad a row. What i want is something similar to pages that have the arrows next to each row and the user can click up if they want that row to go up. I also want it to change the actual number in the row column. for the one being moved and for the other one being affected. I hope i am being clear enough

  Read the article

1