none
Mapping database return results to specific List Items RRS feed

  • Question

  • Hello :) 

    Title is a bit nebulous but I was having a difficult time paraphrasing my dilemma! 

    I have 'requests' that represent students; each request has 6 states that it can be in - Accepted, Open, Closed, Returned, Canceled, Resubmitted.

    I have a page that is used to search through the requests by either first name, last name, or request id. I would like to add an additional filter to the search options that allows the user to specify via drop down whether the request is Accepted, Open, Closed etc... . The code I have now is a functional drop down that populates with the request status; my issue is that it is populating with every status of every request.

    EX:

    Drop Down Issue Example

    My goal is to have the drop down only contain the six possible states that a request can be in: Accepted, Closed, Canceled, Returned, Resubmitted; I've done some looking into it and I think that maybe an SQL variable to store the requests that match a specific status, but then how would one best fetch this data from an aspx.vb Linq to SQL statement?

    Or would it be better practice to have a series of if / then statements in my getData function where if @RequestStatus === "Accepted" then push into an array variable that represents the status of the @RequestStatus. Should I do this in my GetData function or my GetView function? 
    GetData & GetView Code is as follows:

    Private Function GetData() As DataTable
            Dim dt As New DataTable()
            Dim cmd As New SqlCommand()
            Dim strConnString As String = ConfigurationManager.ConnectionStrings("TrackingConnectionString").ConnectionString
            Dim con As New SqlConnection(strConnString)
            Dim sda As New SqlDataAdapter()
    
            cmd.CommandTimeout = 40
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "spSearchGridView"
            cmd.Parameters.Add("@txtRequestID", Data.SqlDbType.NVarChar).Value = txtRequestID.Text
            cmd.Parameters.Add("@RadioButtonIndexID", Data.SqlDbType.Int).Value = RadioButtonList1.SelectedIndex
    
            If txtFromDateView1.Text = "" Then
                cmd.Parameters.Add("@FromDateView1", Data.SqlDbType.DateTime2).Value = DBNull.Value
            Else
                cmd.Parameters.Add("@FromDateView1", Data.SqlDbType.DateTime2).Value = FormatDateTime(txtFromDateView1.Text)
            End If
    
            If txtFromDateView2.Text = "" Then
                cmd.Parameters.Add("@FromDateView2", Data.SqlDbType.DateTime2).Value = DBNull.Value
            Else
                cmd.Parameters.Add("@FromDateView2", Data.SqlDbType.DateTime2).Value = FormatDateTime(txtFromDateView2.Text)
            End If
    
            If txtToDateView1.Text = "" Then
                cmd.Parameters.Add("@ToDateView1", Data.SqlDbType.DateTime2).Value = Date.Now
            Else
                cmd.Parameters.Add("@toDateView1", Data.SqlDbType.DateTime2).Value = FormatDateTime(txtToDateView1.Text)
            End If
    
            If txtToDateView2.Text = "" Then
                cmd.Parameters.Add("@ToDateView2", Data.SqlDbType.DateTime2).Value = Date.Now
            Else
                cmd.Parameters.Add("@toDateView2", Data.SqlDbType.DateTime2).Value = FormatDateTime(txtToDateView2.Text)
            End If
    
            cmd.Parameters.Add("@LocationIDView1", Data.SqlDbType.NVarChar).Value = HiddenField2.Value
            cmd.Parameters.Add("@LocationIDView2", Data.SqlDbType.NVarChar).Value = ddlLocationNameView2.SelectedValue
            cmd.Parameters.Add("@LocationIDView3", Data.SqlDbType.NVarChar).Value = ddlLocationNameView3.SelectedValue
            cmd.Parameters.Add("@LastName", Data.SqlDbType.NVarChar).Value = txtLastName.Text
            cmd.Parameters.Add("@FirstName", Data.SqlDbType.NVarChar).Value = txtFirstName.Text
            cmd.Parameters.Add("@Role", Data.SqlDbType.NVarChar).Value = HiddenField1.Value
            cmd.Parameters.Add("@RequestStatus", Data.SqlDbType.NVarChar).Value = ddlrequestStatus.SelectedValue
            cmd.Connection = con
    
            Try
                con.Open()
                sda.SelectCommand = cmd
                sda.Fill(dt)
                Return dt
                GridView1.DataSource = cmd.ExecuteScalar
                GridView1.DataBind()
            Catch ex As Exception
                Throw ex
            Finally
                con.Close()
                sda.Dispose()
                con.Dispose()
            End Try
        End Function
    
        Private Function GetView() As Data.DataView
    
            Dim ds As Data.DataSet = New Data.DataSet
            Dim dv As Data.DataView = New Data.DataView
            Dim dp As SqlDataAdapter = New SqlDataAdapter
    
            Dim cmd As New SqlCommand
            Dim strConnString As String = ConfigurationManager.ConnectionStrings("TrackingConnectionString").ConnectionString
            Dim con As New SqlConnection(strConnString)
            Dim sda As New SqlDataAdapter()
    
            cmd.CommandTimeout = 40
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "spSearchGridView"
            cmd.Parameters.Add("@txtRequestID", Data.SqlDbType.VarChar).Value = txtRequestID.Text
            cmd.Parameters.Add("@RadioButtonIndexID", Data.SqlDbType.Int).Value = RadioButtonList1.SelectedIndex
    
            If txtFromDateView1.Text = "" Then
                cmd.Parameters.Add("@FromDateView1", Data.SqlDbType.DateTime2).Value = DBNull.Value
            Else
                cmd.Parameters.Add("@FromDateView1", Data.SqlDbType.DateTime2).Value = FormatDateTime(txtFromDateView1.Text)
            End If
    
            If txtFromDateView2.Text = "" Then
                cmd.Parameters.Add("@FromDateView2", Data.SqlDbType.DateTime2).Value = DBNull.Value
            Else
                cmd.Parameters.Add("@FromDateView2", Data.SqlDbType.DateTime2).Value = FormatDateTime(txtFromDateView2.Text)
            End If
    
            If txtToDateView1.Text = "" Then
                cmd.Parameters.Add("@ToDateView1", Data.SqlDbType.DateTime2).Value = Date.Now
            Else
                cmd.Parameters.Add("@toDateView1", Data.SqlDbType.DateTime2).Value = FormatDateTime(txtToDateView1.Text)
            End If
    
            If txtToDateView2.Text = "" Then
                cmd.Parameters.Add("@ToDateView2", Data.SqlDbType.DateTime2).Value = Date.Now
            Else
                cmd.Parameters.Add("@toDateView2", Data.SqlDbType.DateTime2).Value = FormatDateTime(txtToDateView2.Text)
            End If
    
            cmd.Parameters.Add("@LocationIDView1", Data.SqlDbType.NVarChar).Value = HiddenField2.Value
            cmd.Parameters.Add("@LocationIDView2", Data.SqlDbType.NVarChar).Value = ddlLocationNameView2.SelectedValue
            cmd.Parameters.Add("@LocationIDView3", Data.SqlDbType.NVarChar).Value = ddlLocationNameView3.SelectedValue
            cmd.Parameters.Add("@LastName", Data.SqlDbType.NVarChar).Value = txtLastName.Text
            cmd.Parameters.Add("@FirstName", Data.SqlDbType.NVarChar).Value = txtFirstName.Text
            cmd.Parameters.Add("@Role", Data.SqlDbType.NVarChar).Value = HiddenField1.Value
            cmd.Parameters.Add("@RequestStatus", Data.SqlDbType.NVarChar).Value = ddlrequestStatus.SelectedValue
            cmd.Connection = con
    
            Try
                con.Open()
                sda.SelectCommand = cmd
                sda.Fill(ds)
    
                If (ViewState("sortExp") <> Nothing) Then
                    dv = New Data.DataView(ds.Tables(0))
    
                    If (GridViewSortDirection = SortDirection.Ascending) Then
                        GridViewSortDirection = SortDirection.Descending
                        dv.Sort = CType(ViewState("sortExp") & DESCENDING, String)
                    Else
                        GridViewSortDirection = SortDirection.Ascending
                        dv.Sort = CType(ViewState("sortExp") & ASCENDING, String)
                    End If
                Else
                    dv = ds.Tables(0).DefaultView
                End If
    
    
    
            Catch ex As Exception
    
            Finally
                ds.Dispose()
                dp.Dispose()
            End Try
    
            Return dv
        End Function

    SQL is currently fetching the status of each request and storing it as '@RequestStatus' inside of the stored procedure I am using on our Database and I am fetching it in my aspx.vb page

    Thank you for taking the time; it is much appreciated :) 
    I was having trouble finding the correct way to phrase my issue in order to search for it effectively! 


    :)


    Monday, November 6, 2017 4:31 PM

Answers

  • Seems that you are using ADO not Linq.   Not sure what forum handles ADO but I would post in the VB forum as they use ADO quite a bit.

    https://social.msdn.microsoft.com/Forums/vstudio/en-US/home?forum=vbgeneral


    Lloyd Sheen

    Monday, November 6, 2017 9:54 PM

All replies

  • Here is the function where I am grabbing the information!

    Private Sub GetLocationID(ByVal LocationID As Integer, ByVal RequestStatus As Object)
    
            Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("TrackingConnectionString").ConnectionString)
            Dim sql As String
            Dim sql2 As Object
    
            sql = "SELECT LocationID, Description, Active, ID FROM RSContract WITH (NOLOCK) ORDER BY Description"
            sql2 = "SELECT RequestStatus FROM RSRequest WITH (NOLOCK) ORDER BY RequestStatus ASC"
            Dim cmd = New SqlCommand(sql, con)
            Dim cmd3 = New SqlCommand(sql2, con)
            Dim da = New SqlDataAdapter(cmd)
            Dim da2 = New SqlDataAdapter(cmd3)
            Dim ds = New DataSet()
            Dim ds2 = New DataSet()
            da.Fill(ds)
            da2.Fill(ds2)
    
    
            If (ds.Tables(0).Rows.Count <> 0) Then
                ddlLocationNameView2.DataSource = ds
                ddlLocationNameView2.DataTextField = "Description"
                ddlLocationNameView2.DataValueField = "LocationID"
                ddlLocationNameView2.DataBind()
    
                ddlLocationNameView3.DataSource = ds
                ddlLocationNameView3.DataTextField = "Description"
                ddlLocationNameView3.DataValueField = "LocationID"
                ddlLocationNameView3.DataBind()
    
                ddlrequestStatus.DataSource = ds2
                ddlrequestStatus.DataValueField = "RequestStatus"
                ddlrequestStatus.DataTextField = "RequestStatus"
                ddlrequestStatus.DataBind()
    
            End If
    
            Try
                ddlLocationNameView2.Items.FindByValue(LocationID).Selected = True
                ddlLocationNameView3.Items.FindByValue(LocationID).Selected = True
                ddlrequestStatus.Items.FindByValue(RequestStatus).Selected = True
            Catch ex As Exception
                ddlLocationNameView2.SelectedItem.Text = "- Select a Record -"
                ddlLocationNameView2.SelectedItem.Value = ""
                ddlLocationNameView3.SelectedItem.Text = "- Select a Record -"
                ddlLocationNameView3.SelectedItem.Value = ""
                ddlrequestStatus.SelectedItem.Text = "- Select a Status -"
                ddlrequestStatus.SelectedItem.Value = ""
    
            End Try
    
    
            con.Close()
    
        End Sub


    :)

    Monday, November 6, 2017 6:11 PM
  • I now have specific List Items for the different possible states of a request; and it is tracking which one is the selected value; now all I have to do is map the returned values from Linq/SQL to the available matching list item values. 

    How best to do this? 


    :)

    Monday, November 6, 2017 7:25 PM
  • Seems that you are using ADO not Linq.   Not sure what forum handles ADO but I would post in the VB forum as they use ADO quite a bit.

    https://social.msdn.microsoft.com/Forums/vstudio/en-US/home?forum=vbgeneral


    Lloyd Sheen

    Monday, November 6, 2017 9:54 PM
  • Thanks for pointing me in the right direction :) 

    :)

    Tuesday, November 7, 2017 3:00 PM