mathieu_cupryk
asked on
Need to create a routine to call the stored proc above in vb.net.
Can someone help me get started in vb.net asp.net
to call the below proc.
CREATE PROCEDURE usp_GetDatePeriodFromTo
@YEARMONTH VARCHAR(6)
AS
DECLARE @YEAR_NO int
SET @YEAR_NO = CAST(LEFT(@YEARMONTH,4) as INT)
DECLARE @MONTH_NO int
SET @MONTH_NO = CAST(RIGHT(@YEARMONTH,2) as INT)
SELECT *
FROM (
SELECT
CONVERT(int,CONVERT(varcha r, @YEAR_NO)+right ( '00' + CAST(@MONTH_NO AS VARCHAR(2)) ,2))
AS PERIOD_NBR,
MIN(STRT_DTTM) AS BEGIN_PRD,
MAX(END_DTTM) AS END_PRD
FROM BUSINESS_CALENDAR
WHERE CONVERT(varchar(7),YR_NBR, 112) = CAST(@YEAR_NO as varchar) and CASE PRD_NM
WHEN 'Feb' THEN '1'
WHEN 'Mar' THEN '2'
WHEN 'Apr' THEN '3'
WHEN 'May' THEN '4'
WHEN 'Jun' THEN '5'
WHEN 'Jul' THEN '6'
WHEN 'Aug' THEN '7'
WHEN 'Sep' THEN '8'
WHEN 'Oct' THEN '9'
WHEN 'Nov' THEN '10'
WHEN 'Dec' THEN '11'
WHEN 'Jan' THEN '12'END = @MONTH_NO
GROUP BY PRD_NM
) T
ORDER BY PERIOD_NBR
Period_nbr start date end date
200601 2006-01-29 00:00:00.000 2006-02-25 00:00:00.000
to call the below proc.
CREATE PROCEDURE usp_GetDatePeriodFromTo
@YEARMONTH VARCHAR(6)
AS
DECLARE @YEAR_NO int
SET @YEAR_NO = CAST(LEFT(@YEARMONTH,4) as INT)
DECLARE @MONTH_NO int
SET @MONTH_NO = CAST(RIGHT(@YEARMONTH,2) as INT)
SELECT *
FROM (
SELECT
CONVERT(int,CONVERT(varcha
AS PERIOD_NBR,
MIN(STRT_DTTM) AS BEGIN_PRD,
MAX(END_DTTM) AS END_PRD
FROM BUSINESS_CALENDAR
WHERE CONVERT(varchar(7),YR_NBR,
WHEN 'Feb' THEN '1'
WHEN 'Mar' THEN '2'
WHEN 'Apr' THEN '3'
WHEN 'May' THEN '4'
WHEN 'Jun' THEN '5'
WHEN 'Jul' THEN '6'
WHEN 'Aug' THEN '7'
WHEN 'Sep' THEN '8'
WHEN 'Oct' THEN '9'
WHEN 'Nov' THEN '10'
WHEN 'Dec' THEN '11'
WHEN 'Jan' THEN '12'END = @MONTH_NO
GROUP BY PRD_NM
) T
ORDER BY PERIOD_NBR
Period_nbr start date end date
200601 2006-01-29 00:00:00.000 2006-02-25 00:00:00.000
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I just setup
In my default page I have this?
Imports CustomerList.BOL
Private _calendar As New BusinessCalendar()
Protected Sub cmdProceed_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEv entArgs) Handles cmdProceed.Click
_calendar.GetBusinessCalen darPeriod( txtPeriod. Text)
End Sub
where txtPeriod = YYYYMM
Namespace CustomerList.BOL
Public Class BusinessCalendar
Public Shared Function GetBusinessCalendarPeriod( ByVal DatePeriod As String) As DataSet
Dim db As DBAccess = New DBAccess
db.AddParameter("@YEARMONT H", DatePeriod)
Return db.ExecuteDataSet("usp_Get DatePeriod FromTo")
End Function
End Class
End Namespace
C:\SourceControl_mcupryk\I nHouseDev\ APPS\MKT00 03 - CustomerList\src\CustomerL ist\MainPa ge.aspx.vb (58,0): warning BC42025: Access of shared member, constant member, enum member or nested type through an instance; qualifying expression will not be evaluated.
In my default page I have this?
Imports CustomerList.BOL
Private _calendar As New BusinessCalendar()
Protected Sub cmdProceed_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEv
_calendar.GetBusinessCalen
End Sub
where txtPeriod = YYYYMM
Namespace CustomerList.BOL
Public Class BusinessCalendar
Public Shared Function GetBusinessCalendarPeriod(
Dim db As DBAccess = New DBAccess
db.AddParameter("@YEARMONT
Return db.ExecuteDataSet("usp_Get
End Function
End Class
End Namespace
C:\SourceControl_mcupryk\I
http://support.microsoft.com/kb/308049/en-us