Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

using Querystring values not able to show events in calendar .

CREATE PROCEDURE sp_stdt
(
@_Date datetime,
@_Date_Plus_1 datetime,
@_course nvarchar
)

AS
      SELECT * FROM table WHERE date1 between @_Date AND @_Date_Plus_1 and student = @_course ORDER BY date1
Go

This query does WELL in db where it retrieves the data according to the values,
-->BUT in the application , i find empty values on calendar


The above store procedure, with 3 parameters , two parameters sets for range of date's and the third one is for
querystring ,
in application,i checked using breakpoint i got the correct values of querystring

here--->>parameterDate3.Value = course.ToString()
,
 but , its not entering into this WHILE loop of Get_DBItems()..


While (myDataReader.Read())
            temp = New MyDates()
            temp._Title = myDataReader.Item("cname")
            temp._Date = CDate(myDataReader.Item("startdate"))
            MyCollection.Add(temp)
        End While


Day render event does well for 2 parameter of storeprocedure, which i was using before,but  now i need
three parametrs including querystring , whether i need to change anything in that event....??


Public Sub Get_DBItems()
        Dim myConnection As SqlConnection
        Dim myCommand As SqlCommand
        Dim course As String = getQueryString("cd")
        myConnection = New SqlConnection(ConfigurationSettings.AppSettings("str"))
        myCommand = New SqlCommand("sp_stdt", myConnection)
        myCommand.CommandType = CommandType.StoredProcedure
        Dim parameterDate As New SqlParameter("@_Date", SqlDbType.DateTime, 8)
        parameterDate.Value = tempDate.AddDays(-40)
        myCommand.Parameters.Add(parameterDate)
        Dim parameterDate2 As New SqlParameter("@_Date_Plus_1", SqlDbType.DateTime, 8)
        parameterDate2.Value = tempDate.AddDays(40)
        myCommand.Parameters.Add(parameterDate2)
        Dim parameterDate3 As New SqlParameter("@_student", SqlDbType.NVarChar, 100)
        parameterDate3.Value = course.ToString()
        myCommand.Parameters.Add(parameterDate3)
        Response.Write(" Hi " & parameterDate3.Value)
        Dim myDataReader As SqlDataReader
        myConnection.Open()
        myDataReader = myCommand.ExecuteReader()
        Dim temp As MyDates
        MyCollection = New Collection()
        While (myDataReader.Read())
            temp = New MyDates()
            temp._Title = myDataReader.Item("cname")
            temp._Date = CDate(myDataReader.Item("startdate"))
            MyCollection.Add(temp)
        End While
        myDataReader.Close()
        myConnection.Close()
        Response.Write(parameterDate.Value)
        Response.Write(parameterDate2.Value)
        'Response.Write(" Hi " & parameterDate3.Value)
    End Sub
 
 
 
 
/****This day render function does well for 2 parameters,on adding third parameter of querystring ***it dint worked***/
 Public Sub Calendar1_DayRender(ByVal sender As Object, ByVal e As DayRenderEventArgs)
        Dim Item As MyDates
        Dim TextColor As String
        Dim DayHold As DateTime = "01/01/1900"
        Dim MultipleItemDay As Boolean = False
        Dim DayTextHasChanged As Boolean = False
        Dim temp As StringBuilder
        If IsNothing(MyCollection) = True Then
            Get_DBItems()
        End If
        For Each Item In MyCollection
            If DayHold <> Item._Date Then
                If DayTextHasChanged = True Then
                    Exit For
                End If
                MultipleItemDay = False
                DayHold = Item._Date
            Else
                MultipleItemDay = True
            End If
            If e.Day.Date.ToString("d") <> Item._Date.ToString("d") Then
                e.Day.IsSelectable = False
            ElseIf e.Day.Date = Item._Date.ToString("d") Then
                e.Day.IsSelectable = True
                e.Cell.BackColor = Drawing.Color.SkyBlue
                If MultipleItemDay = False Then
                    temp = New StringBuilder()
                Else
                    temp.Append("<br>")
                End If
                temp.Append(Item._Title.ToString())
                DayTextHasChanged = True
            End If
        Next
    End Sub

Open in new window

0
Rajar Ahmed
Asked:
Rajar Ahmed
  • 11
  • 10
1 Solution
 
Anurag AgarwalPython DeveloperCommented:
I beleive you are using SQL SERVER as your database.
Try to see the query which is executed by the stored procedure in the SQL Profiler provided by SQL server, it will give you the actual parameters passed to Stored Procedure & then run those into Query Analyzer.
I don't see any problem in your code. Please check data in your database ofr the course you are sending as querystring.
Anurag
0
 
Rajar AhmedConsultantAuthor Commented:
hi  anuragal ,

am using sql server 2005 ,

actually i executed that query with the values from the page  using response.write statements

 in the query analyser , am retriving the details in the db

and offcourse i have the course in db which am passing in querystring..

Dunno why its not entering in to the while loop, w.o querysting it does everything welll (regarding only two parameter i used before)..:(
0
 
Praveen VenuTechnical Project ManagerCommented:
try this

Alter PROCEDURE sp_stdt
(
@_Date datetime,
@_Date_Plus_1 datetime,
@_course nvarchar
)

AS
     SET NOCOUNT ON
      SELECT * FROM table WHERE date1 between @_Date AND @_Date_Plus_1 and student = @_course ORDER BY date1
Go

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Rajar AhmedConsultantAuthor Commented:
hi praveenvenu ,

i used ur storprocedure , but it dint executed the while loop .

but the same while loop is executed , when there is no querystring is added i mean only two parameters , the sp which i used before.

While (myDataReader.Read())
            temp = New MyDates()
            temp._Title = myDataReader.Item("cname")
            temp._Date = CDate(myDataReader.Item("startdate"))
            MyCollection.Add(temp)
        End While


0
 
Praveen VenuTechnical Project ManagerCommented:
can u share the value in the querystring?
0
 
Rajar AhmedConsultantAuthor Commented:
http://localhost:3801/default1.aspx?cd=highergrade

On Breakpoint ::
and i get querystring value in this stmt
 parameterDate3.Value = course.ToString()
0
 
Rajar AhmedConsultantAuthor Commented:
previous one is the url :

this is html code.
<a  href="default1.aspx?cd=highergrade" title="highergrade">.highergrade</a>
0
 
Praveen VenuTechnical Project ManagerCommented:
You have student instead of course (student = @_course ) in the Stored Procedure. Is it correct. ?

If it is correct then
try running this query and see if it enters loop

CREATE PROCEDURE sp_stdt
(
@_Date datetime,
@_Date_Plus_1 datetime,
@_course nvarchar
)

AS
      SELECT * FROM table WHERE date1 between @_Date AND @_Date_Plus_1 and student = 'highergrade' ORDER BY date1
Go

0
 
Rajar AhmedConsultantAuthor Commented:
ya it should be course ,

 this time it entered into the loop , and displayed every events for highergrade .
 
but wat abt many other link which i have , according to the query string i need to display the events...

But this sp works exactly wat i wanted but just for highergrade  .


0
 
Praveen VenuTechnical Project ManagerCommented:

this should work..




CREATE PROCEDURE sp_stdt
(
@_Date datetime,
@_Date_Plus_1 datetime,
@_course nvarchar
)

AS
      SELECT * FROM table WHERE date1 between @_Date AND @_Date_Plus_1 and course= @_course ORDER BY date1
Go




 
 
 
 
 
 
Public Sub Get_DBItems()
        Dim myConnection As SqlConnection
        Dim myCommand As SqlCommand
        Dim course As String = getQueryString("cd")
        myConnection = New SqlConnection(ConfigurationSettings.AppSettings("str"))
        myCommand = New SqlCommand("sp_stdt", myConnection)
        myCommand.CommandType = CommandType.StoredProcedure
        Dim parameterDate As New SqlParameter("@_Date", SqlDbType.DateTime, 8)
        parameterDate.Value = tempDate.AddDays(-40)
        myCommand.Parameters.Add(parameterDate)
        Dim parameterDate2 As New SqlParameter("@_Date_Plus_1", SqlDbType.DateTime, 8)
        parameterDate2.Value = tempDate.AddDays(40)
        myCommand.Parameters.Add(parameterDate2)
        Dim parameterDate3 As New SqlParameter("@_course", SqlDbType.NVarChar, 100)
        parameterDate3.Value = course.ToString().Trim()
        myCommand.Parameters.Add(parameterDate3)
        Response.Write(" Hi " & parameterDate3.Value)
        Dim myDataReader As SqlDataReader
        myConnection.Open()
        myDataReader = myCommand.ExecuteReader()
        Dim temp As MyDates
        MyCollection = New Collection()
        While (myDataReader.Read())
            temp = New MyDates()
            temp._Title = myDataReader.Item("cname")
            temp._Date = CDate(myDataReader.Item("startdate"))
            MyCollection.Add(temp)
        End While
        myDataReader.Close()
        myConnection.Close()
        Response.Write(parameterDate.Value)
        Response.Write(parameterDate2.Value)
        'Response.Write(" Hi " & parameterDate3.Value)
    End Sub
 
 
 
 

Open in new window

0
 
Rajar AhmedConsultantAuthor Commented:
hi praveenvenu ,

i used the exact same code but it dint entered into the while loop ...:(
why it is so..?

0
 
Praveen VenuTechnical Project ManagerCommented:
did u notice the code change

 Dim parameterDate3 As New SqlParameter("@_course", SqlDbType.NVarChar, 100)
        parameterDate3.Value = course.ToString().Trim()
0
 
Rajar AhmedConsultantAuthor Commented:
i used the above code only(by copying n pasting there) , in the place of my previous code

and also ur same sp is now in db ........everthing is just same of above as u  gave......

Thx..
0
 
Praveen VenuTechnical Project ManagerCommented:
try this

http://www.codeproject.com/KB/dotnet/SQLServerProfiler.aspx

it will show the final query that the SQLServer recieves while you run the code.


0
 
Rajar AhmedConsultantAuthor Commented:
This was the query ... it showed in the profiler ...

exec sp_course2 @_Date = 'Jan  1 2009 12:00AM', @_Date_Plus_1 = 'Mar 22 2009 12:00AM', @_course = N'lowergrade'
0
 
Praveen VenuTechnical Project ManagerCommented:
do you get any result when it is run from sql?

0
 
Rajar AhmedConsultantAuthor Commented:
totall mystery same query but one returning result but parameter which contain @_course not returning

I executed the application with two procedure one after other :
Test 1 : Failed
This sp which has parameter for course='@_course'
exec sp_course2 @_Date = 'Jan  1 2009 12:00AM', @_Date_Plus_1 = 'Mar 22 2009 12:00AM', @_course = N'highergrade'
This dint return the details in db

Test 2:Executed
This sp which has parameter for course='highergrade' (as we tried yesterday)
exec sp_stdt @_Date = 'Jan  1 2009 12:00AM', @_Date_Plus_1 = 'Mar 22 2009 12:00AM', @_course = N'highergrade'
This returns the details in db
0
 
Praveen VenuTechnical Project ManagerCommented:
go to sqlserver management studio. Right click these SPs. Script>Alter Script and post pls
0
 
Rajar AhmedConsultantAuthor Commented:
ALTER PROCEDURE [dbo].[sp_stdt]
(
@_Date datetime,
@_Date_Plus_1 datetime,
@_course nvarchar
)

AS
      SELECT * FROM table1 WHERE date1 between @_Date AND @_Date_Plus_1 and course= 'highergrade' ORDER BY date1


ALTER PROCEDURE [dbo].[sp_course1]
(
@_Date datetime,
@_Date_Plus_1 datetime,
@_course nvarchar
)

AS
      SELECT * FROM table1 WHERE date1 between @_Date AND @_Date_Plus_1 and course= @_course ORDER BY date1


0
 
Praveen VenuTechnical Project ManagerCommented:
try this

      SELECT * FROM table1 WHERE date1 between @_Date AND @_Date_Plus_1 and ltrim(rtrim(upper(course)))= ltrim(rtrim(upper(@_course))) ORDER BY date1

0
 
Praveen VenuTechnical Project ManagerCommented:
hup hup hurray...

Got the answer

below sp will work. If you not specify the size of a parameter which is string type then it will consider it as 1. So when you pass parameter value as highergrade it will take only first character

then it will try to compare

course='h'

Thats why no answer

ALTER PROCEDURE [dbo].[sp_course1]
(
@_Date datetime,
@_Date_Plus_1 datetime,
@_course nvarchar(100)
)
 
AS
     SELECT * FROM table1 WHERE date1 between @_Date AND @_Date_Plus_1 and course= @_course ORDER BY date1

Open in new window

0
 
Rajar AhmedConsultantAuthor Commented:
Thanks a lot..:)....Sql Profiler link was great . ANd i hope it will be great for my several test ..before i dunno about that...Thanks again...
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 11
  • 10
Tackle projects and never again get stuck behind a technical roadblock.
Join Now