What I mean use the query in your first post, it seems work fine if you enter the correct datatype values in @m_date and @y_date. Based on the data in table trainingTBl, t_date = '30/06/2012 00:00:00' existed in your records, assign @m_date = 6 and @y_date
= 2012 in your query and try it again to check whether it return the correct records or not.
Thanks.
Please mark the replies as answers if they help or unmark if not.
Feedback to us
Dim query As String = "SELECT * FROM userProfilesTbl LEFT JOIN stationsTbl ON userProfilesTbl.st_id = stationsTbl.st_id LEFT JOIN watchTbl ON userProfilesTbl.wa_id = watchTbl.wa_id LEFT JOIN userAssessmentTbl ON userProfilesTbl.UserId = userAssessmentTbl.UserId LEFT JOIN trainingTbl ON userAssessmentTbl.tt_id = trainingTbl.tt_id LEFT JOIN setsTbl ON trainingTbl.s_id = setsTbl.s_id WHERE userProfilesTbl.st_id=@st_id AND userProfilesTbl.wa_id=@wa_id AND DATEPART(m,t_date) = @m_date AND DATEPART(yyyy,t_date) = @y_date ORDER BY surname ASC"
Dim cmd As SqlCommand = New SqlCommand(query, conn)
conn.Open()
cmd.Parameters.AddWithValue("@st_id", myLblStation.Value().ToString)
cmd.Parameters.AddWithValue("@wa_id", myLblWatch.Value().ToString)
cmd.Parameters.AddWithValue("@m_date", ddlMonth.SelectedValue.ToString())
cmd.Parameters.AddWithValue("@y_date", ddlYear.SelectedValue.ToString())
Dim query As String = "SELECT * FROM userProfilesTbl LEFT JOIN stationsTbl ON userProfilesTbl.st_id = stationsTbl.st_id LEFT JOIN watchTbl ON userProfilesTbl.wa_id = watchTbl.wa_id LEFT JOIN userAssessmentTbl ON userProfilesTbl.UserId = userAssessmentTbl.UserId LEFT JOIN trainingTbl ON userAssessmentTbl.tt_id = trainingTbl.tt_id LEFT JOIN setsTbl ON trainingTbl.s_id = setsTbl.s_id WHERE userProfilesTbl.st_id=@st_id AND userProfilesTbl.wa_id=@wa_id AND DATEPART(m,t_date) = @m_date AND DATEPART(yyyy,t_date) = @y_date ORDER BY surname ASC"
Dim conn As New SqlConnection()
conn.ConnectionString = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
Dim cmd As SqlCommand = New SqlCommand(query, conn)
conn.Open()
cmd.Parameters.AddWithValue("@st_id", myLblStation.Value().ToString)
cmd.Parameters.AddWithValue("@wa_id", myLblWatch.Value().ToString)
cmd.Parameters.AddWithValue("@m_date", ddlMonth.SelectedValue.ToString())
cmd.Parameters.AddWithValue("@y_date", ddlYear.SelectedValue.ToString())
Dim reader As SqlDataReader = cmd.ExecuteReader()
GridView1.DataSource = reader
GridView1.DataBind()
conn.Close()
Thanks.
Please mark the replies as answers if they help or unmark if not.
Feedback to us
I-Weedy
Member
67 Points
239 Posts
Re: Issue Filtering Datatable GridView with DropDownLists
Dec 12, 2012 12:43 PM|LINK
Sorry Chen,
not understanding what youre saying here! Can you be more specific?
Chen Yu - MS...
All-Star
21829 Points
2513 Posts
Microsoft
Re: Issue Filtering Datatable GridView with DropDownLists
Dec 13, 2012 01:24 AM|LINK
Hi,
What I mean use the query in your first post, it seems work fine if you enter the correct datatype values in @m_date and @y_date. Based on the data in table trainingTBl, t_date = '30/06/2012 00:00:00' existed in your records, assign @m_date = 6 and @y_date = 2012 in your query and try it again to check whether it return the correct records or not.
Thanks.
Feedback to us
Develop and promote your apps in Windows Store
I-Weedy
Member
67 Points
239 Posts
Re: Issue Filtering Datatable GridView with DropDownLists
Dec 13, 2012 02:11 PM|LINK
Strangly still no reaction or error!
This is what i have up
Dim query As String = "SELECT * FROM userProfilesTbl LEFT JOIN stationsTbl ON userProfilesTbl.st_id = stationsTbl.st_id LEFT JOIN watchTbl ON userProfilesTbl.wa_id = watchTbl.wa_id LEFT JOIN userAssessmentTbl ON userProfilesTbl.UserId = userAssessmentTbl.UserId LEFT JOIN trainingTbl ON userAssessmentTbl.tt_id = trainingTbl.tt_id LEFT JOIN setsTbl ON trainingTbl.s_id = setsTbl.s_id WHERE userProfilesTbl.st_id=@st_id AND userProfilesTbl.wa_id=@wa_id AND DATEPART(m,t_date) = @m_date AND DATEPART(yyyy,t_date) = @y_date ORDER BY surname ASC" Dim cmd As SqlCommand = New SqlCommand(query, conn) conn.Open() cmd.Parameters.AddWithValue("@st_id", myLblStation.Value().ToString) cmd.Parameters.AddWithValue("@wa_id", myLblWatch.Value().ToString) cmd.Parameters.AddWithValue("@m_date", ddlMonth.SelectedValue.ToString()) cmd.Parameters.AddWithValue("@y_date", ddlYear.SelectedValue.ToString())to now
Chen Yu - MS...
All-Star
21829 Points
2513 Posts
Microsoft
Re: Issue Filtering Datatable GridView with DropDownLists
Dec 14, 2012 01:39 AM|LINK
Hi,
Try below code, it works fine with my data.
Dim query As String = "SELECT * FROM userProfilesTbl LEFT JOIN stationsTbl ON userProfilesTbl.st_id = stationsTbl.st_id LEFT JOIN watchTbl ON userProfilesTbl.wa_id = watchTbl.wa_id LEFT JOIN userAssessmentTbl ON userProfilesTbl.UserId = userAssessmentTbl.UserId LEFT JOIN trainingTbl ON userAssessmentTbl.tt_id = trainingTbl.tt_id LEFT JOIN setsTbl ON trainingTbl.s_id = setsTbl.s_id WHERE userProfilesTbl.st_id=@st_id AND userProfilesTbl.wa_id=@wa_id AND DATEPART(m,t_date) = @m_date AND DATEPART(yyyy,t_date) = @y_date ORDER BY surname ASC" Dim conn As New SqlConnection() conn.ConnectionString = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString Dim cmd As SqlCommand = New SqlCommand(query, conn) conn.Open() cmd.Parameters.AddWithValue("@st_id", myLblStation.Value().ToString) cmd.Parameters.AddWithValue("@wa_id", myLblWatch.Value().ToString) cmd.Parameters.AddWithValue("@m_date", ddlMonth.SelectedValue.ToString()) cmd.Parameters.AddWithValue("@y_date", ddlYear.SelectedValue.ToString()) Dim reader As SqlDataReader = cmd.ExecuteReader() GridView1.DataSource = reader GridView1.DataBind() conn.Close()Thanks.
Feedback to us
Develop and promote your apps in Windows Store
I-Weedy
Member
67 Points
239 Posts
Re: Issue Filtering Datatable GridView with DropDownLists
Dec 14, 2012 12:03 PM|LINK
Ah!
it's not the code but the DropDownlist!
After slight tinkering withy my DropDownList ddlMonth the filter has started to work.
thanks Chen.